mysqldump alternative: MySQL backups with Percona Xtrabackup

Many hosting companies will take care of file system and database backups for you.  We have a few clients where we have file system backups configured, but database backups have to be handled manually, since the database is "live data".  Often we can just configure a cronjob that runs mysqldump so that a database snapshot is stored to a file and thus regularly backed up when the file system backup is run.  However, sometimes you have a perfect storm where mysqldump is too heavy for the server (causing severe slowdowns or even crashing some services), even if run in the least intensive way (such as with the --quick flag) and at a low traffic time: partially due to lack of system resources and a large database.

Percona Xtrabackup offers a lower intensity way to back up a MySQL database. It does so by directly copying the database file (in the case of eZ Publish installations, the Innodb file) while maintaining the integrity of live database.

Here is a quick how-to on using Xtrabackup:

• Download and untar the binaries for your operating system from http://www.percona.com/downloads/XtraBackup/LATEST
• Create a bash script similar to the following:

rm -rf /var/www/backup_folder/backup_`date +%A`
mkdir /var/www/backup_folder/backup_`date +%A`
/var/www/backup_folder/xtrabackup-1.6.5/bin/xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/var/www/backup_folder/backup_`date +%A`
gzip /var/www/backup_folder/backup_`date +%A`/*
cp -r /var/lib/mysql/database_name_1 /var/www/backup_folder/backup_`date +%A`
cp -r /var/lib/mysql/database_name_2 /var/www/backup_folder/backup_`date +%A`

• Set up a cronjob to run that script at the desired frequency (such as nightly)
• To restore a backup, you can use a script such as this one:

EXPECTED_ARGS=1
if [ $# -ne $EXPECTED_ARGS ]
then
  echo "Usage: bash restore <backup folder>"
  exit
fi
#shutdown mysql while writing the data
/etc/init.d/mysql stop
 
gunzip ./$1/*.gz
rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./$1 /var/lib/mysql
 
#reset permissions so that mysql can read / write everything
chown -R mysql:mysql /var/lib/mysql
 
/etc/init.d/mysql start

Keep in mind a few limitations of Xtrabackup:

• Since the backed up data is the raw Innodb file for MySQL, you cannot pick and choose which databases to backup and restore.
• Since it does not back up the table descriptions (.frm files), you have to create a batch script to do that manually for all the databases.
• An alternative setup depending on resources, needs, and budget would be to replicate the database to another server (via a master-slave setup or simply by using Xtrabackup to copy the database over) and do a mysqldump from the other server.