WordPress Database – Run MySQL Search and Replace

Once in a while, I need to move a WordPress installation from one server to another, or from root domain to a subdomain (for example – move WordPress from www.mywebsite.com to blog.mywebsite.com).

There are probably easier ways to do this, but sometimes I just like to take the hard way if it means it’s safer. The procedure is straightforward: you need to make sure you backup the database and the files, move them to the new location and replace every string in the database that contains the old address with the new address.

My process is pretty simple:

• Download all of the files from the server through FTP.
• Login to phpmyadmin and export the DB.
• Upload the files to the new location.
• Import the exported DB to the new phpmyadmin.
• Find any traces of the old location in the DB and replace them with the new location.

How to Run a “Find and Replace” MySQL Query for WordPress’ Database

Basically we need to run the following queries for numerous tables:

1. Postmeta
2. Posts
3. Options

And here are the queries:

UPDATE wp_options SET option_value = replace(option_value, 'http://the-old-domain.com', 'http://the-new-domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://the-old-domain.com','http://the-new-domain.com');

UPDATE wp_posts SET post_content = replace(post_content, 'http://the-old-domain.com', 'http://the-new-domain.com');

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://the-old-domain.com', 'http://the-new-domain.com');

Copy 1 line at a time, paste in the phpmyadmin – > SQL box and click “Go”. Do this for all 4 queries.

Don’t forget to check out “Moving WordPress” and “Changing The Site URL” guides for more details about the process.

Good Luck!