Jan. 31, 2011

Bash script to mirror WordPress MU installation into a sandbox

This bash script will let you replicate a WordPress MU blog environment from live installation into a sandbox, altering and rewriting wp-config.php and database entries to make sure URLs work the right way.

Most WordPress installations in the corporate world have development cycles like this: Dev -> QA -> Production.

This is wrong.

It should go Production -> Dev -> QA (Presuming if things check out, changes are pushed live.)

Sure, the standard operation might work before you launch the blogs - but - after that point content and configuration ends up mutating server conditions. Thus, replicating the environment to either troubleshoot, modify or install new components becomes a large chore.

Most developers end up with an environment where development is so far removed from production that they end up wasting inordinate amount of time communicating with QA over replicating simplest of bugs.

This example shows one machine replicating to a sandbox subdomain, though this should work fine over mounted fileshares. These are script configuration options that would clone http://blogs.mywebsite.com/ into http://sandbox.mywebsite.com/

Live Site
SOURCE_
Replicated Site
DESTINATION_
_SITE blogs.mywebsite.com sandbox.mywebsite.com
_FOLDER /home/blogs/public_html/ /home/development/public_html/sandbox/
_DATABASE blogs_database dev_database
_DATABASE_USER blogs_dbuser dev_dbuser
_DATABASE_PASS dKm#1!343Xv0 password123
TEMP_FILE /home/development/dbdump.sql
_CHOWN_USER_GROUP devuser.devgroup

General flowchart assumes that it should wipe the sandbox first, and goes like:

  • Delete destination folder (rm -rf)
  • Delete destination tables
  • Grab mysql snapshot of source database
  • Import mysql snapshot into destination database
  • Copy files from source to destination
  • Chown files inside destination as devuser.devgroup
  • Copy WP's .htaccess file, add deny from all + other user lines
  • Disable wp-super-cache, if it exists, by deleting on destination
  • Rewrite destination's wp-config to alter DB_NAME, DB_USER, DB_PASSWORD, DOMAIN_CURRENT_SITE (script not tested without that present.)
  • Update destination wp_blogs, wp_site, sitemeta tables to reflect new URL
  • Loop through all blogs and alter their options table, updating siteurl, home, fileupload_url to reflect new URL

Once run, the script outputs this:

[root@host.mywebsite.com] >> ./make_sandbox.sh
removing / recreating /home/development/public_html/sandbox/
removing    193 tables from destination database dev_database
grabbing snapshot of source database from blogs_database
/home/development/dbdump.sql 106089428  bytes
importing snapshot into destination database dev_database
copying files from /home/blogs/public_html/* to /home/development/public_html/sandbox/
chown'ing /home/development/public_html/sandbox/ as devuser.devgroup
copying .htaccess manually
   adding to .htaccess:
deny from all
allow from my.intranet.connection
 
removing caching plugin /home/development/public_html/sandbox/wp-content/plugins/wp-super-cache/
rewriting wp-config.php with new information
changing mu blogs tables: source site->destination site
changing mu options tables: source site->destination site

WARNING: read over this script before you run. Seriously. Did you notice there are rm -rf statements in it?

#!/bin/bash
 
# ============================================================
# CONFIGURATION GOES HERE
# ============================================================
 
SOURCE_SITE='blogs.mywebsite.com';
SOURCE_FOLDER='/home/blogs/public_html/';
SOURCE_DATABASE='blogs_database';
SOURCE_DATABASE_USER='blogs_dbuser';
SOURCE_DATABASE_PASS='dKm#1!343Xv0';
 
# this is not using /tmp/ because some databases are larger than /tmp partition
TEMP_FILE='/home/development/dbdump.sql';
 
DESTINATION_SITE='sandbox.mywebsite.com';
DESTINATION_FOLDER='/home/development/public_html/sandbox/';
DESTINATION_DATABASE='dev_database';
DESTINATION_DATABASE_USER='dev_dbuser';
DESTINATION_DATABASE_PASS='password123';
DESTINATION_CHOWN_USER_GROUP='devuser.devgroup';
 
# you may want to lock .htaccess down with "DENY FROM ALL"
# you can add an easy exception - such as ALLOW FROM my.own.hostname.net
ADD_TO_HTACCESS='
deny from all
allow from my.intranet.connection
';
 
# ============================================================
# END CONFIGURATION - DON'T TOUCH BELOW THIS LINE
# ============================================================
 
IFS=$'\n';
 
# ============================================================
# step 1: clear destination folder and database
# ============================================================
echo "removing / recreating ${DESTINATION_FOLDER}";
rm -rf ${DESTINATION_FOLDER}
mkdir ${DESTINATION_FOLDER}
 
tables=\
`echo "show tables from ${DESTINATION_DATABASE}" | \
mysql -u${DESTINATION_DATABASE_USER} -p${DESTINATION_DATABASE_PASS} | \
grep -v "Tables_in_${DESTINATION_DATABASE}"`
 
IFS=$' ';
count_tables=`echo ${tables} | cat -n | tail -n 1 | cut -f1`;
IFS=$'\n';
 
echo "removing ${count_tables} tables from destination database ${DESTINATION_DATABASE}";
for table in $tables
do
	echo \
		"drop table ${DESTINATION_DATABASE}.${table}" | \
		mysql -u${DESTINATION_DATABASE_USER} -p${DESTINATION_DATABASE_PASS};
done
 
 
# ============================================================
# step 2: import database and files
# ============================================================
 
echo "grabbing snapshot of source database from ${SOURCE_DATABASE}";
/usr/bin/mysqldump \
	${SOURCE_DATABASE} \
	-u${SOURCE_DATABASE_USER} \
	-p${SOURCE_DATABASE_PASS} \
	> ${TEMP_FILE}
 
 
echo `stat ${TEMP_FILE} -c "%n %s"` " bytes";
 
echo "importing snapshot into destination database ${DESTINATION_DATABASE}"
mysql -D \
	${DESTINATION_DATABASE} \
	-u${DESTINATION_DATABASE_USER} \
	-p${DESTINATION_DATABASE_PASS} \
	< ${TEMP_FILE}
 
 
echo "copying files from ${SOURCE_FOLDER}* to ${DESTINATION_FOLDER}";
cp -R -p ${SOURCE_FOLDER}* ${DESTINATION_FOLDER}
 
echo "chown'ing ${DESTINATION_FOLDER} as ${DESTINATION_CHOWN_USER_GROUP}";
chown -R ${DESTINATION_CHOWN_USER_GROUP} ${DESTINATION_FOLDER}*
 
echo "copying .htaccess manually";
cp ${SOURCE_FOLDER}.htaccess ${DESTINATION_FOLDER}.htaccess
echo "   adding to .htaccess: ${ADD_TO_HTACCESS}";
echo "${ADD_TO_HTACCESS}" >> ${DESTINATION_FOLDER}.htaccess
 
 
# ============================================================
# step 3: disable caching plugins, if any
# ============================================================
if [ -d ${DESTINATION_FOLDER}wp-content/plugins/wp-super-cache/ ]; then
	echo "removing caching plugin ${DESTINATION_FOLDER}wp-content/plugins/wp-super-cache/";
	rm -rf ${DESTINATION_FOLDER}wp-content/plugins/wp-super-cache/
fi
 
# ============================================================
# step 4: rewrite wp-config.php
# ============================================================
 
echo "rewriting wp-config.php with new information";
 
sed \
	"s/'DB_NAME', '${SOURCE_DATABASE}'/'DB_NAME', '${DESTINATION_DATABASE}'/g" \
		${SOURCE_FOLDER}wp-config.php | \
sed \
	"s/'DB_USER', '${SOURCE_DATABASE_USER}'/'DB_USER', '${DESTINATION_DATABASE_USER}'/g" | \
sed \
	"s/'DB_PASSWORD', '${SOURCE_DATABASE_PASS}'/'DB_PASSWORD', '${DESTINATION_DATABASE_PASS}'/g" | \
sed \
	"s/'DOMAIN_CURRENT_SITE', '${SOURCE_SITE}'/'DOMAIN_CURRENT_SITE', '${DESTINATION_SITE}'/g" \
	> ${DESTINATION_FOLDER}wp-config.php
 
# ============================================================
# step 5: update destination database to reflect new URL
# ============================================================
 
echo "changing mu blogs tables: source site->destination site";
 
echo "update ${DESTINATION_DATABASE}.wp_blogs set domain='${DESTINATION_SITE}'" \
	| mysql -u${DESTINATION_DATABASE_USER} -p${DESTINATION_DATABASE_PASS};
echo "update ${DESTINATION_DATABASE}.wp_site set domain='${DESTINATION_SITE}'" \
	| mysql -u${DESTINATION_DATABASE_USER} -p${DESTINATION_DATABASE_PASS};
echo \
"update 
	${DESTINATION_DATABASE}.wp_sitemeta 
set 
	meta_value='http://${DESTINATION_SITE}' 
where 
	meta_key='siteurl'" \
	| mysql \
		-u${DESTINATION_DATABASE_USER} \
		-p${DESTINATION_DATABASE_PASS};
 
 
echo "changing mu options tables: source site->destination site";
 
tables=`echo "show tables from ${DESTINATION_DATABASE}" \
	| mysql -u${DESTINATION_DATABASE_USER} \
	-p${DESTINATION_DATABASE_PASS} \
	| grep "_options"`
 
for table in $tables
do
	echo \
"update 
	${DESTINATION_DATABASE}.${table} 
set 
	option_value=replace(
		option_value,
		'http://${SOURCE_SITE}/',
		'http://${DESTINATION_SITE}/'
	)
where 
	option_name in 
		('siteurl', 'home', 'fileupload_url')" \
	| mysql \
		-u${DESTINATION_DATABASE_USER} \
		-p${DESTINATION_DATABASE_PASS};
done

If wp-config.php is much altered from a recent release, sed might get thrown off. At any rate, this is just a general idea though the script works for me daily.

Find this interesting, or useful? Consider sharing the post.

10 Responses to “Bash script to mirror WordPress MU installation into a sandbox”

  1. ignacio says:

    Hi!!

    Great!

    Can i use between diferent servers? like a mirror?

  2. Dino says:

    Well, yes. That's pretty much exactly what it does - but - doesn't do content sync reconciliation. Only does blind mirroring.

    This isn't a complete solution (though it works), but it will put you a step closer toward what you need done.

    Cheers!

  3. ignacio says:

    Do you have a sugestion for a wordpress mirror?

    thanks!

  4. samk says:

    This was useful. Thanks for posting it.

  5. Andrea says:

    Thank you a lot Dino!

    This workflow and the script will save a lot of my time!

    It works quite fine with WP (non MU) as well: it's enough to ignore the error messages when updating WPMU specific tables.

    There is only a small issue with the script.

    Deleting wp-supercache folder, when running the cloned site I get a 500 error. Leaving it and then deactivating it from the WP Dashboard, it works.
    So I've changed this part to simply rename the folder, just to allow to restore it if needed.

    I'm totally new to bash scripting, but I've made some change to your script in order to make easier cloning it for multiple projects, to get more details on what the script is doing and to update more data on the target database.

    If anyone is interested, here's the code: http://code.google.com/p/wp-sandboxer/ ...sorry for the ugly name :)

    And if anyone want, feel free to contribute to make it better as I don't have enough skills to do more than that ;)

  6. Jim says:

    I see you're importing the database and then running queries to update the domain options. Wouldn't it make the script more concise to use sed to change the domain options in the exported .sql dump file before you import it? Then you can get rid of those update queries.

    I don't think its that big of a deal, but it makes it so you're just working with sed instead of a mix of sed and update queries. A little simpler.

    Thoughts?

  7. Dino says:

    @Jim: pardon the delayed reply. IMO bash scripting is like a 70s station wagon with wooden panels. About the only compliment I can give it is that it gets the job done.

    With that premise, there are probably a dozen more efficient ways of handling this script, your sed/sql file parse being one of them. But, I thought it most reliable to alter the database after mirroring in case there are similarities in credentials and whatnot.

    And of course, this is not a solution for all time, just one implementation of an idea. Thanks for pointing this out.

  8. [...] before trying it out. Read more about the script and grab you copy from the original article: Bash script to mirror WordPress MU installation into a sandbox Tweet (function() { var li = document.createElement('script'); li.type = [...]

  9. Phil Reese says:

    What if we want to suppress the uploads folder to speed up the process so that we don't have to drag all that fat around with us, when we're just going to need a few images on the sandbox? How can we alter the script to skip writing specific folders to the sandbox?

  10. Dino says:

    @Phil: this script is just a very rough primer, but you can swap the cp command with rsync. Right now there's this line:

    cp -R -p ${SOURCE_FOLDER}* ${DESTINATION_FOLDER}

    This needs testing, but for WPMU, you can replace it with something like:

    rsync -av --exclude=${SOURCE_FOLDER}/wp-content/blogs.dir/ ${SOURCE_FOLDER} ${DESTINATION_FOLDER}

Leave a Reply

Posts on this blog solely represent my personal opinions and technical experience.

© 2009-2014 Edin (Dino) Beslagic