MySQL hot backup with XtraBackup on CentOS

Dwight SchruteIf 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

Category: 

5 comments

16
May

ah its not wordpress, its drupal right?
16
May

Yup, like it says in the footer, it's Drupal Wink

16
May

what a great post, just signed up to your RSS feed and hope to read more of your posts in the future. keep it up!
28
Jul

I noticed that the Innobackupex keeps 7 days of backup by default... How can i change this configuration? I need to keep 3-4 days. Is it safe to just delete them in a cron job part of the backup? also is there a way to zip the whole directory created by the innobackupex command as part of the same command? Please advise Thanks John
28
Jul

John, you can use 'find' command to delete xtrabackup directories older than XY days. You can also zip xtrabackup directory after the backup has finished, not in the same innobackupex command, but in additional command that executes as soon as xtrabackup has finished.

Add new comment