How to replace the domain name in a WordPress database?

2 Views

I have a WordPress database which was installed in a development environment… thus, all references to the site itself have a fixed IP address (say 192.168.16.2). Now, I have to migrate that database to a new WordPress installation on a hosting. The problem is that the SQL dump contains a lot of references to the IP address, and I have to replace it with: my_domain.com.

I could use sed or some other command to change the that from the command line, the problem is that there are a lot of configuration data which uses JSON. So what? Well, as you know, JSON arrays uses things like: s:4: to know how many chars an element has, and thus, if I just replace the IP with the domain name, the configuration files will get corrupted.

Read More

I used an app for Windows some years ago that allows to change values in a database and takes care of the JSON arrays. Unfortunately, I forgot the name of the app… so the question is: do you know any app that allows me to do what I want?

Post Views: 2

Related posts

Leave a Reply

10 comments

  2. Codex has decent guide – Changing Site URL.

    Basically there are several places there URL matter or not so much (I may be missing some):

    • home and siteurl options that control where WP thinks site is;
    • post GUIDs, these look like links but really they aren’t – only identifiers. I am not absolutely sure they are irrelevant, but I had URLs change on test stack a lot and never bothered to update these.
    • links in post bodies;
    • possibly links in plugin settings.

  3. As Rarst said above there’s only two settings in the database that NEEDS changing. After importing the database I log into PHPMyAdmin and edit the database direct.

    I use a development server on my PC all the time to import datafeeds and they have the URL attached to the Posts along the lines of http://localhost/testsite/post-name/ and it’s never caused a problem.

    I used to use an SQL search and replace until I realised it didn’t matter. I’ve migrated a few sites from one domain to another and where I’ve had absolute URLs in the content I’ll use the SQL search and replace option.

    update wp_posts set post_content = replace(post_content,'http://www.olddomain/','http://www.newdomain/');

    David

  5. You can set these values with constants in the wp-config.php. After this you can, when you want, change the entries in the database via plugin Adminer.
    Write this in the wp-config.php and the values in the DB are not relevant:

    define('WP_HOME', 'http://example.com/to-wordpress');
define('WP_SITEURL', 'http://example.com/to-blog');

  7. Simple SQL queries – no complicated REPLACE stuff required:

    update wp_options set option_value = 'http://mynewdomain.com' where option_name = 'siteurl';

update wp_options set option_value = 'http://mynewdomain.com' where option_name = 'home';

    Use those with PHPMyAdmin or any other way you prefer to access the database.

  8. Make sure you have the new database selected, then run some sql updates and replacement commands on the tables notably, wp_options, wp_posts, wp_postmeta.

    Use the code as below and swap in your old and new URLs, no trailing slashes. Also if necessary change the table prefix values where applicable (ie wp_ )

    UPDATE wp_options SET option_value = replace(option_value, 'http_www.oldurl', 'http_www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';    
UPDATE wp_posts SET guid = replace(guid, 'http_www.oldurl','http_www.newurl');    
UPDATE wp_posts SET post_content = replace(post_content, 'http_www.oldurl', 'http_www.newurl');    
UPDATE wp_postmeta SET meta_value = replace(meta_value,'http_www.oldurl','http_www.newurl');

  9. This is a very old question, but since I came across it while looking for something else, I thought I would add this for future reference.

    I think the simplest, most complete way to do this is using searchreplacedb2.php. It can be found here: http://interconnectit.com/products/search-and-replace-for-wordpress-databases/ along with an explanation of it’s use.

    It has definitely saved me a lot of time with migrating from dev to live environments on my wordpress sites.

    Just make sure you delete it from a public server after you are done!

  10. I see you want to move your WordPress site from the development environment to a new server or production server.

    Recently, I have moved my site to the production server and I face the same situation. I need to update the site URL because WordPress stored the site URL in Database.

    I found good MySQL query using that I can easily change URL directly from the Database like.

    SET @search := "http://olddomain.com";
SET @replace := "http://newdomain.com";

UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "siteurl";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "home";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "product_list_url";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "shopping_cart_url";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "checkout_url";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "widget_text";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "headerImageLogo";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "cunjo_plugin_url";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "wpsr_socialbt_data";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "issuem";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "_transient_dgxdonate_t70pbg1htu_1396322372";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "dgx_donate_log";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "headerBackgroundImage";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "transact_url";
UPDATE `wp_postmeta` SET `meta_value`=REPLACE(meta_value, @search, @replace);
UPDATE `wp_usermeta` SET `meta_value`=REPLACE(meta_value, @search, @replace);
UPDATE `wp_posts` SET `guid`=REPLACE(guid, @search, @replace);
UPDATE `wp_posts` SET `post_content`=REPLACE(post_content, @search, @replace);

    You just need to replace your site URL from old domain name to new domain then execute the query on the MySQL using PHPMYADMIN.

    For more reference, You can read this Tutorial.

    https://tryvary.com/wordpress-change-url-in-database-using-mysql-query/