How to replace the domain name in a Wordpress database?

Question

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.

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?

Answer 1

The data you're looking at is not JSON formatted. JSON doesn't (normally) store value types and lengths like that. What you're looking at is SERIALIZED data. A google search for "mysql replace serialized" yields this page, which might help: http://davidcoveney.com/mysql-database-search-replace-with-serialized-php/

Answer 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.

Answer 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

Answer 4

This is an amazing resource i have bookmarked that I go back to time and time again http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

They state

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';

Answer 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');

Answer 6

Take a look my answer to this question:

• Easily Move a WordPress Install from Development to Production?

It addresses your issues with cleaning up data and can be customized for specific data migration needs by using using hooks, too.

Hope it helps.

Answer 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.

Answer 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');

Answer 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!