Install:
$: wget -O /usr/local/bin/generate_update.sh https://raw.github.com/gist/1380503/3137a4bb8f6ccbcd9a7113a6105ab9da0e8a8cf4/generate_update.sh $: chmod +x /usr/local/bin/generate_update.sh
Single Site example:
generate_update.sh -s http://dev.example.com -r http://example.com
Will produce this sql:
UPDATE wp_options SET option_value = REPLACE(option_value, 'http://dev.example.com', 'http://example.com') WHERE option_name = 'siteurl' OR option_name = 'home' OR option_name = 'fileupload_url' OR option_name = 'woo_custom_favicon' OR option_name = 'woo_logo'; UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://dev.example.com', 'http://example.com'); UPDATE wp_posts SET guid = REPLACE(guid, 'http://dev.example.com', 'http://example.com'); UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'http://dev.example.com', 'http://example.com');
Multisite Example:
generate_update.sh -s dev.example.com -r example.com -m 4
(Notice that we removed the protocol from the URLs and passed in the number of multisites there are. This number should equal the highest numbered site in the db, i.e. wp_4_*) If you have ‘holes’in your numbering (because a site was deleted during development, but new ones created after it) these will appear in the sql. You will likely need to tell mysql to ‘skip errors’when you execute the files (else it will stop at the first statement it can’t run because the table doesn’t exist) OR manually remove them from the sql output before you run it.
UPDATE wp_options SET option_value = REPLACE(option_value, 'dev.example.com', 'example.com') WHERE option_name = 'siteurl' OR option_name = 'home' OR option_name = 'fileupload_url' OR option_name = 'woo_custom_favicon' OR option_name = 'woo_logo'; UPDATE wp_posts SET post_content = REPLACE(post_content, 'dev.example.com', 'example.com'); UPDATE wp_posts SET guid = REPLACE(guid, 'dev.example.com', 'example.com'); UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'dev.example.com', 'example.com'); UPDATE wp_site SET DOMAIN = REPLACE(DOMAIN, 'dev.example.com', 'example.com'); UPDATE wp_sitemeta SET meta_value = REPLACE(meta_value, 'dev.example.com', 'example.com'); UPDATE wp_blogs SET DOMAIN = REPLACE(DOMAIN, 'dev.example.com', 'example.com'); UPDATE wp_2_posts SET post_content = REPLACE(post_content, 'dev.example.com', 'example.com'); UPDATE wp_2_posts SET post_content = REPLACE(post_content, 'dev.example.com', 'example.com'); UPDATE wp_2_posts SET guid = REPLACE(guid, 'dev.example.com', 'example.com'); UPDATE wp_2_options SET option_value = REPLACE(option_value, 'dev.example.com', 'example.com') WHERE option_name = 'siteurl' OR option_name = 'home' OR option_name = 'fileupload_url' OR option_name = 'woo_custom_favicon' OR option_name = 'woo_logo'; UPDATE wp_3_posts SET post_content = REPLACE(post_content, 'dev.example.com', 'example.com'); UPDATE wp_3_posts SET post_content = REPLACE(post_content, 'dev.example.com', 'example.com'); UPDATE wp_3_posts SET guid = REPLACE(guid, 'dev.example.com', 'example.com'); UPDATE wp_3_options SET option_value = REPLACE(option_value, 'dev.example.com', 'example.com') WHERE option_name = 'siteurl' OR option_name = 'home' OR option_name = 'fileupload_url' OR option_name = 'woo_custom_favicon' OR option_name = 'woo_logo'; UPDATE wp_4_posts SET post_content = REPLACE(post_content, 'dev.example.com', 'example.com'); UPDATE wp_4_posts SET post_content = REPLACE(post_content, 'dev.example.com', 'example.com'); UPDATE wp_4_posts SET guid = REPLACE(guid, 'dev.example.com', 'example.com'); UPDATE wp_4_options SET option_value = REPLACE(option_value, 'dev.example.com', 'example.com') WHERE option_name = 'siteurl' OR option_name = 'home' OR option_name = 'fileupload_url' OR option_name = 'woo_custom_favicon' OR option_name = 'woo_logo';
What this script does and doesn’t do:
- Creates a sql file, called update_wp.sql, that you can use to generate statements that change URLs in common areas of a WordPress database so that you can move it from one server to another (i.e. dev to prod, etc)
- Only updates wp_options that are known to NOT be serialized. The big ones are ‘siteurl’and ‘home’
- Some options are specific to particular theme frameworks (such as WOO). They are inert if you are not using them. If you have other frameworks you’d like to see included, let me know what the option names are. The only options that matter are those with the site URL in them.
- It does not try to handle serialized options, such as widgets. Many times you won’t need to change widget options. Others are easy to do once the site is migrated. A small few have to be removed, re-added, and re-configured after the migration due to the way its options are stored.
- The sql statements do attempt to search and replace URLs in the postmeta table meta_values. These may also contain serialized values, and care should be taken that you don’t break the data in these fields in the process (or in the case of custom meta_values that you store those with URLs as scalar values in the database).
The source code is below: