WordPress Database Backup Script for Testing

Before I release the next version of the easy WordPress upgrade script, I would like to have some solid testing done specifically on the Database backup section.

Anyone familiar with backup up with MySQLDump and restoring from that backup is invited to try this script.

I suggest you have a known good backup of the DB in case any thing goes wrong.

This file is meant to be run from your blog’s directory and will get all of the information it needs from the wp-config file. That’s really the part I want to test. This script will display your database and password information on the screen. Please verify that it matches what you have in wp-config.php. It then uses that information to get a dynamic list of the tables in your database having the prefix you specified in wp-config. Then MySQLDump is used to export ONLY those files from the database.

So, you have a targeted database backup that is agnostic to the version of WP that you are using. It will work on WP 2.1 or 1.2 (assuming there were no major wp-config format changes). It will even withstand minor changes to the way the info is stored in wp-config.php.

So I hope it is resiliant.

The easy way to test is to:
1. Telnet to your system (having verified you have a good backup of the DB already)
2. Cd to your root WP directory containing wp-config
3. Execute the following wget:
wget http://www.thecodecave.com/downloads/wpbackup4testing
4. Give yourself execution permission
chmod +x wpbackup4testing
5. Call the script
wpbackup4testing

Then just look for the file named SQLDump.gz

IMPORTANT!!!! DELETE THE DUMP FILES WHEN YOU ARE DONE!!!!
Nothing in the public areas of your folder is safe. Google will find it. Then your passwords and everything else are theirs for the download. Do not keep DB backups online.

The code looks like this:

#!/bin/bash
#
# WordPress Backup script from wwwTheCodeCave.com
#
# This is the incomplete testing version. The final version will be better.
#
# Go find it from http://www.TheCodeCave.com
#
# Brian Layman
# Jan 11 2007
#
RANDOM=$$$(date +%N)
Dumpfile=”SQLDump$RANDOM.gz”
echo “DFName: $Dumpfile”
DB_NAME=$(sed -n “/define(‘DB_NAME’, ‘/s/.*, ‘\([^’]*\).*/\1/p” wp-config.php)
DB_USER=$(sed -n “/define(‘DB_USER’, ‘/s/.*, ‘\([^’]*\).*/\1/p” wp-config.php)
DB_PASSWORD=$(sed -n “/define(‘DB_PASSWORD’, ‘/s/.*, ‘\([^’]*\).*/\1/p” wp-config.php)
DB_HOST=$(sed -n “/define(‘DB_HOST’, ‘/s/.*, ‘\([^’]*\).*/\1/p” wp-config.php)
TABLE_PREFIX=$(sed -n “/\$table_prefix = ‘/s/.*= ‘\([^’]*\).*/\1/p” wp-config.php)

echo “Gathering information:”
echo “DB_NAME: $DB_NAME”
echo “DB_PASSWORD: $DB_PASSWORD”
echo “DB_USER: $DB_USER”
echo “DB_HOST: $DB_HOST”
echo “TABLE_PREFIX: $TABLE_PREFIX”
mysql $DB_NAME –port=3306 –protocol=TCP –host=$DB_HOST –user=$DB_USER –password=$DB_PASSWORD -e”show tables like ‘$TABLE_PREFIX%'”>TableNamesForSQLDump.txt
WPTables=$(sed -e “/^\($TABLE_PREFIX[^ ]*\).*/!d;s//\1/” -e :b -e “N;s/\n/ /;bb” TableNamesForSQLDump.txt)
echo “WPTables: $WPTables”
echo
echo “Performing backup”
(mysqldump –opt –port=3306 –protocol=TCP –host=$DB_HOST –user=$DB_USER –password=$DB_PASSWORD $DB_NAME $WPTables | gzip > $Dumpfile)||{
echo “SQL Dump Failed”
exit 999
}
echo “Backup successful. Please examine file $Dumpfile”