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