Whenever you upgrade your WordPress installation or do development work, it is always a good idea to be working on a copy of your main site and not on the real thing. Copying WordPress installation is not exactly difficult, but it is a bit tedious, which is the reason why many Webmasters apply changes directly to the production site and hope for the best. As a Sysadmin I understand the value of this approach. I also understand that eventually it will cost you weeks of work or even your job.

I say, when in doubt – write a script. Here’s one that will create a near-mirror image of your WordPress installation – plugins, themes, galleries and all. The script will duplicate your database and make the necessary changes to the copy to reflect the changed URL. Look through this script carefully: depending on your system configuration, there are a few things you may need to change. Always back up your site and your database before running any scripts (especially my scripts). Pay particular attention to the “configure()” section. Once your site is copied, there is one plugin that you may need to reconfigure: WP-DBManager. If you have it, just open the options page and re-enter the required paths and other settings.

The basic syntax is simple: wp-copy . You will then be prompted for database login and password, as well as for the name of the new database. The and are just folder names in your server home – not paths. Here’s an example: say, the absolute path to your site root is /srv/www/htdocs/original_site. You will run the script like so: “wp-copy original_site copy_site“. This will copy your site to /srv/www/htdocs/copy_site.

Another point to make: there are two ways to copy a MySQL database. One method is to use mysqldump | mysql construct. This does not always work and you may encounter some SQL errors. The more direct approach is to temporarily shut down the database and duplicate the entire database directory. This is what this script does. If you wish to try mysqldump method first, change this section:

#$MYSQLDUMP $DBNAME | $MYSQL $DBNEW
/etc/init.d/mysql stop
$RSYNC "${DBHOME}/${DBNAME}/" "${DBHOME}/${DBNEW}/"
/etc/init.d/mysql start

to look like this:

$MYSQLDUMP $DBNAME | $MYSQL $DBNEW
#/etc/init.d/mysql stop
#$RSYNC "${DBHOME}/${DBNAME}/" "${DBHOME}/${DBNEW}/"
#/etc/init.d/mysql start

And here is the entire script. Save is as /sbin/wp-copy and chmod 700.

#!/bin/bash

DATE=$(date +'%Y-%m-%d_%H-%M-%S')
WORKDIR="/srv/www/htdocs/krazyworks/solobox"

if [  ]
then
	SOURCE=""
else
	echo "Must specify source directory. Exiting..."
	exit 1
fi

if [ ! -d "${WORKDIR}/${SOURCE}" ]
then
	echo "Source ${WORKDIR}/${SOURCE} not found. Exiting..."
	exit 1
fi

if [  ]
then
	TARGET=""
else
	echo "Must specify target directory. Exiting..."
	exit 1
fi

configure() {
	HTTPUSER="wwwrun"
	HTTPGROUP="www"
	DBNAME=$(grep DB_NAME "${WORKDIR}/${SOURCE}/wp-config.php" | awk -F"'" '{print $4}')
	echo -n "Enter database username: " ; read DBUSER
	echo -n "Enter $DBUSER password: " ; stty -echo ; read DBPASS ; stty echo ; echo ""
	echo -n "Enter new database name: " ; read DBNEW
	MYSQL="/usr/bin/mysql -u${DBUSER} -p${DBPASS}"
	MYSQLDUMP="/usr/bin/mysqldump -u${DBUSER} -p${DBPASS}"
	DBHOME="/var/lib/mysql"
	RSYNC="/usr/local/bin/rsync -avu"
}

db_copy() {
	echo "Copying database $DBNAME to $DBNEW"
	echo "CREATE DATABASE $DBNEW;" | $MYSQL
	echo "GRANT ALL PRIVILEGES ON ${DBNEW}.* to ${DBUSER}@'%' IDENTIFIED BY '$DBPASS' WITH GRANT OPTION ;" |
	$MYSQL
	#$MYSQLDUMP $DBNAME | $MYSQL $DBNEW
	/etc/init.d/mysql stop
	$RSYNC "${DBHOME}/${DBNAME}/" "${DBHOME}/${DBNEW}/"
	/etc/init.d/mysql start
}

modify_wp_conf() {
	echo "Modifying new database"
	cat "${WORKDIR}/${TARGET}/wp-config.php" | 
	sed "s/define('DB_NAME', '$DBNAME')/define('DB_NAME', '$DBNEW')/g" > /tmp/wp-copy.tmp
	mv /tmp/wp-copy.tmp "${WORKDIR}/${TARGET}/wp-config.php"
	
	options_table=$($MYSQL $DBNEW << "	EOF" | grep "_options$" | tail -1
	SHOW TABLES ;
	quit
	EOF )

	echo "SHOW TABLES;" | $MYSQL $DBNEW | grep -v "Tables_in_$DBNEW" | while read TABLE
	do
		echo "SHOW COLUMNS FROM $TABLE;" | $MYSQL $DBNEW | awk -F't' '{print $1}' |
		grep -v "Field" | while read COLUMN
		do
			echo "update $TABLE set ${COLUMN} = replace(${COLUMN}, '/${SOURCE}', '/${TARGET}');" |
			$MYSQL $DBNEW
		done
	done
}

site_copy() {
	echo "Copying site from ${SOURCE} to ${TARGET}"
	mkdir "${WORKDIR}/${TARGET}"
	$RSYNC "${WORKDIR}/${SOURCE}/" "${WORKDIR}/${TARGET}/"
	chown -R ${HTTPUSER}:${HTTPGROUP} "${WORKDIR}/${TARGET}"
}

configure
db_copy
site_copy
modify_wp_conf