Wordpress Database Changes When Moving Site

27th March 2010

When moving a Wordpress install from one place to another there are a number of things you must be aware of. If you have created your templates properly you will have used calls to bloginfo('home') rather than using static links, but you will need to update these links to make your Wordpress install work properly.

Once the files are in place, the database connection details have been edited and the database created there are a number of things to alter in the database to make your Wordpress install work.

The most important changes are in the options table. There are two references to your URL in this table that must be altered to stop Wordpress redirecting back to your old site. This must be done via database access as Wordpress will redirect you when you try to login to the stie.

  1. UPDATE wp_options SET option_value = 'http://www.example.com' WHERE option_name = 'siteurl';
  2. UPDATE wp_options SET option_value = 'http://www.example.com' WHERE option_name = 'home';

The posts table contains one definate reference in the guid column and one possible reference in the post content table. These items are less important, but can Here are the SQL commands you need to update these.

  1. UPDATE wp_posts SET guid = REPLACE(guid, 'http://www.example2.com', 'http://www.example.com');
  2. UPDATE wp_posts SET post_content = REPLACE(post_content , 'http://www.example2.com', 'http://www.example.com');

There might be the odd example of static links being present in your post metadata, in which case you might also want to run the following on your wp_postmeta table.

UPDATE wp_postmeta SET meta_value = REPLACE(meta_value , 'http://www.example2.com', 'http://www.example.com');

If you have any references to your old site in your comments table you might also want to change these.

  1. UPDATE wp_comments SET comment_content = REPLACE(comment_content , 'http://www.example2.com', 'http://www.example.com');
  2. UPDATE wp_comments SET comment_author_url = REPLACE(comment_author_url , 'http://www.example2.com', 'http://www.example.com');

I have put all of these statements together in one block, with each statement executed using the $wpdb object.

  1. $new_site = 'http://www.example.com';
  2. $old_site = 'http://www.example2.com';
  3.  
  4. // Update options
  5. $wpdb->query("UPDATE " . $wpdb->prefix . "options SET option_value = '" . $new_site . "' WHERE option_name = 'siteurl';");
  6. $wpdb->query("UPDATE " . $wpdb->prefix . "options SET option_value = '" . $new_site . "' WHERE option_name = 'home';");
  7.  
  8. // Update posts
  9. $wpdb->query("UPDATE " . $wpdb->prefix . "posts SET guid = REPLACE(guid, '" . $old_site . "', '" . $new_site . "');");
  10. $wpdb->query("UPDATE " . $wpdb->prefix . "posts SET post_content = REPLACE(post_content , '" . $old_site . "', '" . $new_site . "');");
  11.  
  12. // Update postmeta
  13. $wpdb->query("UPDATE " . $wpdb->prefix . "postmeta SET meta_value = REPLACE(meta_value , '" . $old_site . "', '" . $new_site . "');");
  14.  
  15. // Update comments
  16. $wpdb->query("UPDATE " . $wpdb->prefix . "comments SET comment_content = REPLACE(comment_content , '" . $old_site . "', '" . $new_site . "');");
  17. $wpdb->query("UPDATE " . $wpdb->prefix . "comments SET comment_author_url = REPLACE(comment_author_url , '" . $old_site . "', '" . $new_site . "');");

Add this to your Wordpress install so that it is run when the page is loaded. This might be inside the functions.php file for the current theme. Or you might want to run it as a separate file, just include wp-load.php at the top of the script to bootstrap Wordpress.

The one thing you might notice missing from this collection of statements is any reference to plugin specific statements. Plugins provide an added layer of difficulty as some store their data in custom tables, whereas others store their data as serialized arrays in the options table. You would therefore need to add these to the script above in order to change any references they might contain. One thing I have found useful is to simply deactivate then reactivate the plugin, this will sort most issues.

Add new comment

The content of this field is kept private and will not be shown publicly.