Blog» mysqldump alternative: MySQL backups with Percona Xtrabackup

mysqldump alternative: MySQL backups with Percona Xtrabackup

By Xavier Cousin  | May 01, 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:

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.

Join our mailing list for news, events, tips, and tools

Comments

blog comments powered by Disqus

Hi, we're Mugo Web - Nice to meet you!

We're a group of web experts who solve complex web problems.

Learn more about us »

Search


Categories


Yes - we can do that.

Many years of experience with complex websites allows us to offer total solutions.

Learn more about what we can do »

We love our clients (and they love us too)

We've solved problems across North America and around the world.

Learn more about what we've done »

We tweet too

Follow us on Twitter for the latest Mugo happenings

mugo twitter page @mugo

© 2008 - 2019 Mugo Web. All rights reserved.