Generate the necessary sql statements to move a WordPress site from one environment to another

generate_update.sh

#!/usr/bin/env bash

echo "@@@  @@@  @@@@@@@@  @@@  @@@  @@@@@@@  @@@@@@@@  @@@  @@@  "
echo "@@@  @@@  @@@@@@@@  @@@@ @@@  @@@@@@@  @@@@@@@@  @@@  @@@  "
echo "@@!  !@@  @@!       @@!@!@@@    @@!    @@!       @@!  !@@  "
echo "!@!  @!!  !@!       !@!!@!@!    !@!    !@!       !@!  @!!  "
echo " !@@!@!   @!!!:!    @!@ !!@!    @!!    @!!!:!    @!@@!@!   "
echo "  @!!!    !!!!!:    !@!  !!!    !!!    !!!!!:    !!@!!!    "
echo " !: :!!   !!:       !!:  !!!    !!:    !!:       !!: :!!   "
echo ":!:  !:!  :!:       :!:  !:!    :!:    :!:       :!:  !:!  "
echo " ::  :::   :: ::::   ::   ::     ::     :: ::::   ::  :::  "
echo " :   ::   : :: ::   ::    :      :     : :: ::    :   :::  "
echo " Replacing you with very small shell scripts since 1996    "
echo "-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-"
echo ""
echo ""
echo ""
usage()                                                                                                                                                                                              
{
cat << EOF
useage: $0 options

Generates a SQL script that can update a WordPress database to replace one instance of a URL with another. Useful for synchronizing a multi-site installation to your local or migrating a site from one environment to another, especially when being pushed to production.
This will generate a file at the same location as where it is run, so be sure to change into the directory you want the file generated at before running it.

The file that is generated is called update_wp.sql. If a file of the same name exists, it will be replaced when this script runs.

OPTIONS:
-h Show this message
-s Search URL. Do NOT include the protocol if this is a Multi-Site installation.
-r Replace URL. Do NOT include the protocol if this is a Multi-Site installation.
-d Database Name. Optional. If passed it will show you how to run the sql file on the command line 
-t Table Prefix. Assumes "wp_"
-m Is Multi-site? Assumes no. If yes, supply the number of sub-sites that exist.
EOF
}
s=
r=
d=
t=
m=
while getopts "hs:r:d:t:m:=" OPTION
do
	case $OPTION in
		h)
			usage
			exit 1
		;;
		s)
			s=$OPTARG
		;;
		r)
			r=$OPTARG
		;;
		d)
			d=$OPTARG
		;;
		t)
			t=$OPTARG
		;;
		m)
			m=$OPTARG
		;;
		?)
		usage >&2
	exit 1                                                                                                                                                                                  
		;;
	esac
done

if [[ -z $s ]] || [[ -z $r ]]
then
	usage
	exit 1
fi

if [[ -z $t ]] ; then
	t="wp_"
fi

if [[ -z $m ]] ; then
	m="no"
fi
echo "" > update_wp.sql
echo "update "$t"options set option_value = REPLACE(option_value, '"$s"', '"$r"') 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.sql
echo "update "$t"posts set post_content = REPLACE(post_content, '"$s"', '"$r"');" >> update_wp.sql
echo "update "$t"posts set guid = REPLACE(guid, '"$s"', '"$r"');" >> update_wp.sql
echo "update "$t"postmeta set meta_value = REPLACE(meta_value, '"$s"', '"$r"');" >> update_wp.sql

if [ "no" != $m ] ; then
echo "update "$t"site set domain = REPLACE(domain, '"$s"', '"$r"');" >> update_wp.sql
echo "update "$t"sitemeta set meta_value = REPLACE(meta_value, '"$s"', '"$r"');" >> update_wp.sql
echo "update "$t"blogs set domain = REPLACE(domain, '"$s"', '"$r"');" >> update_wp.sql
for (( i=2; i<=$m; i++))
	do
		echo "update "$t""$i"_posts set post_content = REPLACE(post_content, '"$s"', '"$r"');" >> update_wp.sql
		echo "update "$t""$i"_posts set post_content = REPLACE(post_content, '"$s"', '"$r"');" >> update_wp.sql
		echo "update "$t""$i"_posts set guid = REPLACE(guid, '"$s"', '"$r"');" >> update_wp.sql
		echo "update "$t""$i"_options set option_value = REPLACE(option_value, '"$s"', '"$r"') where option_name = 'siteurl' OR option_name = 'home' OR option_name = 'fileupload_url' or option_name = 'woo_custom_favicon' or option_name = 'woo_logo';" 1>> update_wp.sql
	done
fi

#if [[ -z $d ]] ; then
#	d="[databasename]"
	echo "Now you can run sometime like this to apply these changes to your database: "
 	echo "mysql -u root -p -D "$d" --force < update_wp.sql"
#else
#	mysql -u root -p -D "$d" --force < update_wp.sql
#fi


sleep 1
echo ""
echo "Have fun storming the castle!"
exit 0

Single Site example:

generate_update -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 -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).