If you are in a need for hot backup solution for MySQL RDBMS, you should try out Percona’s XtraBackup. Now, as you can read on the official website, XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. XtraBackup also supports MyISAM backups, although not without table locks.
XtraBackup consists of three tools - innobackupex, xtrabackup and tar4ibd.
Innobackupex is a wrapper script written in Perl that allows you to backup a complete MySQL instance with InnoDB, MyISAM and XtraDB tables. XtraBackup is a tool compiled in C which main job is to copy only InnoDB and XtraDB data. Of course, there is tar4ibd - a tool that safely stores InnoDB data in tar archive.
Although you can use XtraBackup tool directly, the preferred way to backup your databases is with innobackupex, because it’ll execute XtraBackup for you. If you use MyISAM in some or all of your database tables, you won’t have a choice but to use XtraBackup tool.
Installation
The easiest way to install XtraBackup on CentOS 6 (it should also work without problems on CentOS 5) is to add Percona’s repository. To do that, you’ll need to create yum repo file in /etc/yum.repos.d/Percona.repo with the following content:
[percona]
name = CentOS $releasever - Percona
baseurl = http://repo.percona.com/centos/$releasever/os/$basearch/
enabled = 1
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
gpgcheck = 1
Afterwards you’ll need to collect the GPG key and add it to /etc/pki/rpm-gpg/RPM-GPG-KEY-percona. You can use wget, curl or manually copy and paste the key into appropriate file. Below is the example for wget and curl:
# wget -O /etc/pki/rpm-gpg/RPM-GPG-KEY-percona http://www.percona.com/downloads/RPM-GPG-KEY-percona
# curl http://www.percona.com/downloads/RPM-GPG-KEY-percona -o /etc/pki/rpm-gpg/RPM-GPG-KEY-percona
By this point, you’ll be ready to install xtrabackup package with yum:
# yum install xtrabackup
Creating full backup
To create a full backup, you can simply execute innobackupex with path to your backup directory as a single argument:
# innobackupex /path/to/backupdir/
Innobackupex will create a complete backup of MySQL datadir in defined location within a time stamped directory (e.g. /path/to/backupdir/2012-01-22_12-35-34/). If you don’t like this behavior, you can tell innobackupex not to create a time stamped directory by using --no-timestamp switch.
Restoring full backup
To restore full backup created with innobackupex tool (i.e. all databases), you must first prepare full backup for restore:
# innobackupex --use-memory=4G --apply-log /path/to/backupdir/some_timestamp/
Basically, with this command innobackupex replays commited transactions in the log files and rolls back uncommited ones. The most important thing is that in the end innobackupex returns to you a message like "100313 02:43:07 innobackupex: completed OK!". You should be careful using --use-memory switch. By default, innobackupex uses 100 MB of RAM for this operation, but to speed things up, you can always assign more memory. Just be careful not to assign more memory then you have to spare.
After you prepared your backup, you can restore it. I recommend that you stop MySQL…
# service mysqld stop
…move current MySQL datadir to a safe location…
# mv /var/lib/mysql/ /var/lib/mysql-old
…and restore the backup you prepared earlier with:
# innobackupex --copy-back /path/to/backupdir/some_timestamp/
After you restore the backup, be sure to chown MySQL datadir and start MySQL server again:
# chown mysql. /var/lib/mysql<br># service mysqld start
Restoring partial backup
XtraBackup doesn’t support partial backup restore. But if you need to restore a specific database(s) or table(s), there is a solution - restore trough alternate MySQL server. Just like with full backup restore, you’ll start with preparing the backup:
# innobackupex --use-memory=4G --apply-log /path/to/backupdir/some_timestamp/
Afterwards, you should chown backup directory so that you don’t bump into problems when you start alternate MySQL server:
# chown mysql. /path/to/backup/some_timestamp/
And now you are now set to start alternate MySQL instance on different port, so that your production instance can work right beside it:
# mysqld --basedir=/usr --user=mysql --log-error=/path/to/backup/mysqld.log \
--open-files-limit=4096 --pid-file=/path/to/backup/mysqld.pid \
--socket=/path/to/backup/mysql.sock --port 3307 \
--datadir=/path/to/backup/some_timestamp
In the command above you should replace log-error, pid-file, socket and datadir paths. If you already have a service running on port 3307, you should change it too.
Now when you have alternate MySQL instance up and running, you can use mysqldump utility to backup a specific database(s) or table(s) For example, to backup a whole database to a .sql file you’d use something like:
# mysqldump -P 3307 db_name > db_name.sql
To restore a database from .sql file to production MySQL instance you can simply execute:
# mysql db_name < db_name.sql
Even faster way to restore a database from alternate to production MySQL instance would be:
# mysqldump -P 3307 db_name | mysql db_name