Making backups with XtraBackup
XtraBackup is a backup tool from Percona.
Getting ready
The precompiled XtraBackup packages are only available for Linux. Percona provides both YUM and APT repositories.
You can follow the XtraBackup installation instructions on the Percona website available at http://www.percona.com/doc/percona-xtrabackup/. Also, create a backup user by following the instructions in the Creating a backup user recipe.
How to do it...
Let's get started by following the ensuing steps:
Run the following command by changing the
--user,--password, and/path/to/backupsparts to the correct values:sudo innobackupex --user=backupuser \ --password=p455w0rd /path/to/backups
The
innobackupexscript will callXtraBackupand copy all of the files to a timestamped subdirectory of the specified backup directory. When it has finished, if everything went well, it will print a line similar to the following line of output:130729 12:05:12 innobackupex: completed OK!
How it works...
The innobackupex script is a wrapper around XtraBackup. By itself, the XtraBackup program only backs up InnoDB and XtraDB databases. When the innobackupex script is used, MyISAM, Aria, and other non-InnoDB tables are also backed up.
There's more...
Backups created by XtraBackup and the innobackupex scripts are not ready to be used to restore a database as is. Backups must be prepared prior to restoring. There are also some things that we need to be aware of when backing up to an NFS-mounted disk.
Restoring from a backup
In order to prepare an XtraBackup backup to be restored, we must first prepare it as follows:
sudo innobackupex --apply-log /path/to/backups
Then, we can restore it with the following command:
sudo innobackupex --copy-back /path/to/backup
As with running the script for the initial backup, look for the completed OK! message at the end of the preparing and restoring steps.
The innobackupex script will refuse to overwrite the files in the data directory, so it must be empty before a restore can happen.
As a final step, we will also likely need to fix permissions on the restored files with something similar to the following command:
sudo chown -R mysql:mysql /var/lib/mysql
XtraBackup and NFS
When backing up to an NFS-mounted volume, check to make sure that it is mounted with the sync option. Data may appear corrupt if our NFS volume is mounted with the async option. Refer to the XtraBackup documentation for more information.
