Nightly database backups with Xtra Backup
Here’s an easy way to perform nightly live backups of your database. This technique is safe for both InnoDB and MyISAM tables, though it is a completely fluid “live” backup only if you are using InnoDB tables exclusively.
First, you’ll need to install XtraBackup from Percona. Visit the Xtra Backup downloads page to find the latest version. If you cannot find precompiled binaries, download the source package and compile by following the build instructions. If you are using 32bit CentOS, you can download binary files for version 0.7 here. These are known to work for CentOS 4, but may work on new versions as well.
Now you can create a timestamped, compressed copy of your database:
innobackupex --user=root \ --password=sekrit \ --stream=tar ./ | \ gzip - > backup.`date +%m%d%Y%H%M%S`.tar.gz
If your root password is blank, leave out the password option. If you’re using Ubuntu or another system where the MySQL configuration is not at /etc/my.cnf, then you’ll need to specify the path to the defaults file by passing the option --defaults-file=/etc/mysql/my.cnf
Note that this triggers a momentary global read lock to capture a consistent binary log position for the backup, necessary to restore consistent data if you’re using any MyISAM tables. If you are using exclusively InnoDB tables, you can include a --no-lock option for innobackupex for a truly live backup.
Should you ever need to restore a backup, stop MySQL, move aside your existing MySQL data directory and unzip this tarball in its place:
service mysql stop mv /var/lib/mysql /var/lib/mysql.old mkdir /var/lib/mysql mv /path/to/backup.tar.gz /var/lib/mysql cd /var/lib/mysql tar xzfi backup.tar.gz innobackupex --apply-log chown -R mysql.mysql /var/lib/mysql service mysql start
Note that you must include the i option for tar!
If your aim is to have uninterrupted backup with MyISAM tables, you should use a master-slave database configuration and use the above technique on the slave system. The advantage of Xtra Backup over mysqldump here is that the MySQL data directory strategy makes restoration much faster than with a SQL dump, and much simpler for restoring replication on master-slave configurations. Contact Rails Machine support for help with a master-slave setup and Xtra Backup.