SQL for migrating a WordPress DB between domains

When working locally on a wordpress site there’s 2 references to the local server address stored in wordpress’ ‘wp_options’ table. These handy bits of sql will update the DB when you have imported to the live server:

/* update wordpress sql */
UPDATE `wp_options` SET `option_id`='1', `blog_id`='0', `option_name`='siteurl', `option_value`='[NEW DOMAIN NAME HERE]', `autoload`='yes' WHERE `option_id` = '1' AND `blog_id` = '0' AND `option_name` = 'siteurl';
 
UPDATE `wp_options` SET `option_id`='39', `blog_id`='0', `option_name`='home', `option_value`='[NEW DOMAIN NAME HERE]', `autoload`='yes' WHERE `option_id` = '39' AND `blog_id` = '0' AND `option_name` = 'home';
Share and Enjoy:
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • LinkedIn
  • Technorati
Posted by nick on 5 May 2010; This entry is filed under code, wordpress.

2 Comments

  1. Taff May 5th, 2010 at 9:23 pm

    I don’t work with wordpress, but this SQL looks wrong…

    The table and column names shouldn’t be quoted, and you don’t need to set the items that are also in the WHERE clause.

    Wouldn’t this do (for the 1st statement)?

    UPDATE wp_options SET option_value=’[NEW DOMAIN NAME HERE]‘, autoload=’yes’ WHERE option_id= ’1′ AND blog_id = ’0′ AND option_name = ‘siteurl’;

  2. nick May 7th, 2010 at 5:05 pm

    cheers Taff.

    This ones also useful when uploading content via wordpress it stupidly puts the domain address in the image path. This rips through and clears out any references.

    UPDATE wp_posts SET post_content = REPLACE(post_content, ‘[TEST SERVER DOMAIN]‘, ‘/’)

Leave a Reply