Implementing WordPress Backups via the command line.

Part 1: Database Backup

WordPress Backup

One of the basic System Administration tasks that you should have implemented for your WordPress installation is automated backup. In addition, you should have a tested proven recovery process in the event your site is compromised. Backing up a WordPress site means generating:

1. A copy of your database and
2. A copy of your installation files.

For a full Disaster Recovery scenario, you should have a script to re-create an empty database so you can recover your backup copy into it at a moments notice. This is also the first process you would use to make a working copy of your WordPress installation.

For this article we shall concentrate on the database backup only, later we will script the WordPress installation file backups in a future article and include the web hosting files as well.

Scripting the Database Backup

I have written a BASH shell script below to make a backup of all the WordPress installations on a server, for each site a date stamped backup file is generated and compressed using gzip. The copy of the database is stored in a directory called /data/db-backups. I run this twice a day via the Linux CRON so two copies of the database are made per day and a cleanup script will ensure I have 14 days of backups. The cleanup process is not shown here but a link to the cleanup scripts is here.

The script features logging of its activity to a date stamped log file which is written to /logs/cron/wp-backup-YYYYMMDD.log. I urge you to use this style logging in all your scripts so you have an audit trail of activity. 

The core part of the script is to find the WordPress wp-config.php file and for each site, extract the DB name, user and password from the config file, then use these details to export a copy of the database to a backup file which is then compressed.

#!/bin/bash
#
#
#
# History
# -------
# 2015-05-04 Sid Young  Find and backup wordpress scripts

#
#
#
BACKUPPATH=/data/db-backups
SITELIST=/tmp/sitelist-wp-sites.sdata

#
# Common Logging Code
#
function LogStart
{
        echo "====== Log Start =========" >> $LF
        echo "Time: `date`" >> $LF
        echo " " >> $LF
}
function LogEnd
{
        echo " " >> $LF
        echo "Time: `date`" >> $LF
        echo "====== Log End   =========" >> $LF
}

function LogMsg
{
        echo "`date '+%Y-%m-%d|%H:%M:%S|'`$$|OK|$1" >> $LF
}

function LogError
{
        echo "`date '+%Y-%m-%d|%H:%M:%S|'`$$|ERROR|$1" >> $LF
}

function LogCritical
{
        echo "`date '+%Y-%m-%d|%H:%M:%S|'`$$|CRITICAL|$1" >> $LF
}

UMASK=002
FILE_DATE=`date '+%Y-%m-%d'`
LF_DIR=/logs/cron
LF=$LF_DIR/wp-backup-$FILE_DATE.log
mkdir -p $LF_DIR
chmod 777 /logs/cron
touch $LF
chmod 644 $LF

#----------------------------------------
#
# Process any command line parameters
#
#----------------------------------------
LogStart
LogMsg "Backup directory: $TARGET_DIR"


	LogStart
	if [ ! -d ${BACKUPPATH} ]
	then
		mkdir -p ${BACKUPPATH}
		LogMsg "Creating DB-Backup Dir"
	fi
	find /var/www/vhosts -type f -name wp-config.php -print > ${SITELIST}
	cnt=0
	while read LINE
	do
		FDT=`date '+%Y%m%d%H%M'`
		FD=`date '+%Y%m%d'`
		DBNAME=`grep DB_NAME ${LINE}|grep -Po '(?<=().*?(?=))'|cut -d',' -f2|tr -d "'"|sed 's/^ *//'|tr -d 'nr'`
		DBUSER=`grep DB_USER ${LINE}|grep -Po '(?<=().*?(?=))'|cut -d',' -f2|tr -d "'"|sed 's/^ *//'|tr -d 'nr'`
		DBPWD=`grep DB_PASSWORD ${LINE}|grep -Po '(?<=().*?(?=))'|cut -d',' -f2|tr -d "'"|sed 's/^ *//'|tr -d 'nr'`
		DBBACKUPFILE=${BACKUPPATH}/$DBNAME-${FDT}.sql
		tstcmd="mysql -u ${DBUSER} -p${DBPWD} $DBNAME -e 'show tables;'"
		$tstcmd > /dev/null
		if [ $? -eq 0 ]
		then
			LogMsg " "
			LogMsg "Database     [${DBNAME}]"
			LogMsg "Virtual Host [${LINE}]"
			LogMsg "Backup File  [${DBBACKUPFILE}]"
			cmd="mysqldump -u ${DBUSER} -p${DBPWD} $DBNAME"
			$cmd > $DBBACKUPFILE 
			gzip $DBBACKUPFILE 
		else
			LogError "ERROR - Unable to access Database ${DBNAME} using ${DBUSER}"
		fi
		((cnt++))
	done < ${SITELIST}
	LogMsg "Found ${cnt} sites to backup."
	ls -la ${BACKUPPATH}/*${FD}* >> $LF
	rm -f ${SITELIST}
	LogEnd
#
# End of file

Logging Progress

The Script calls a number of logging functions, LogStart, LogEnd, LogMsg and LogError. The Logging code is used so that a start and end log message is written to indicate successful execution of the script. If there is an error accessing a database the LogError message is used to write an "ERROR" status which can be detected by monitoring scripts and alerts generated. The test that performs that is a simple fetch of the tables using the stored parameters, if the return code is "0" then the database credentials are valid.

Automating the process

CRON is the tool of choice to automate the backup. Using the standard crontab edit method:

crontab -e

Then a single line to kick off two backups each day.

0 6,18 * * * /apps/scripts/wp-backup.sh > /dev/null 2>&1

The scripts are stored in /apps/scripts, this is totally separate from the OS directory structure and allows for easy replication to other servers and immunity from OS upgrades.

Recovery Process

In the event you wish to recover a copy of the database, locate the most recent backup you wish to use:

ls -la /data/db-backup/*201505*

Then just unzip it and pipe it into your database server using the following command line:

cat /data/db-backup/dbname-201505051042.sql.gz|gunzip| mysql -u DBUSER -p DBNAME

To create a new database use the following SQL syntax substituting the names as appropriate:

create database mydbname;
grant usage on mydbname.* to 'usr_mydbuser'@localhost identified by 'my-complexpassword';
grant all privileges on mydbname.* to usr_mydbuser@localhost;

I usually keep this in a create-my-domain-name.sql file in the /apps/sql directory (outside of the web server path).

You should also review my article on Migrating WordPress via the command line