Updated SQL for moving a wordpress.org install to a new domain

While trying to duplicate a wordpress.org install recently I hit a snag that all the instructions I found on google for duplicating a wordpress blog or transitioning to a new domain are incomplete. It would still try and load the wp-admin off the old domain and what not. I finally hit on the sql and the procedure to run to fix this. I’ll show and example of duplicating an install, in this case for a translation.

First thing. BACKUP YOUR DATABASE. If possible export your database to a .sql file (phpmyadmin can do this with the export tab)

Copy your wordpress directory. example: cp -R olddomain.com newdomain.com

Next, use whatever method your hosting provider provides for creating a new database.

Import your database backup into your new database. (the import tab on phpmyadmin can do this)

Now, edit the wp-config.php file in the newdomain.com directory and update your database config.

Now, on the database, you need to run the following sql commands, making sure to replace the ‘olddomain.com’ and ‘newdomain.com’ witht he right names.

UPDATE wp_options SET option_value = replace(option_value, ‘http://olddomain.com’, ‘http://newdomain.com’);
UPDATE wp_options SET option_value = replace(option_value, ‘feed://www.olddomain.com’, ‘feed://newdomain.com’);
UPDATE wp_posts SET guid = replace(guid, ‘http://olddomain.com’,’http://newdomain.com’);
UPDATE wp_posts SET post_content = replace(post_content, ‘http://olddomain.com’, ‘http://newdomain.com’);
UPDATE wp_postmeta SET meta_value = replace(meta_value, ‘http://olddomain.com’, ‘http://newdomain.com’);

Lastly, change this to reflect the new directories in use, this is important, as otherwise it will continue to load the wrong files in places.

UPDATE wp_options SET option_value = replace(option_value, ‘/olddomain.com/’, ‘/newdomain.com/’);

I hope this helps save people some aggravation over using old instructions which are no longer comprehensive.