Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Generate the necessary sql statements to move a WordPress site from one environment to another
#!/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
@xentek

This comment has been minimized.

Copy link
Owner Author

@xentek xentek commented Nov 19, 2011

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');
@xentek

This comment has been minimized.

Copy link
Owner Author

@xentek xentek commented Nov 19, 2011

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';
@xentek

This comment has been minimized.

Copy link
Owner Author

@xentek xentek commented Nov 19, 2011

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).
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment