mysqldump alternative: MySQL backups with Percona Xtrabackup
By: Xavier Cousin | May 1, 2012 | Web solutions
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.