The importance of regularly backing up your data cannot be over emphasised or repeated often enough. Whilst all businesses are at risk from data loss, surveys continually indicate it’s the small businesses that are most at risk, with around 50% of businesses experiencing some form of data loss at some point. This is usually because they either don’t have backups at all, or the backups they do have are incomplete or out-of-date.

We have discussed different strategies for backing up web data in previous blog posts, but here we will look an alternative to mysqldump for backing up your databases.

Mysqldump is a perfect tool for backing up databases in most cases, but it does lock the tables while running the backup. In smaller databases this may be barely noticeable, but on larger databases it can lock the tables for long enough to effect the sites they serve.

Xtrabackup, by Percona, is the only open-source, free hot backup software that is capable of performing non-locking backups of your MySQL databases.

On Red Hat/CentOS systems it’s easily installed via yum, but you need to activate the repository first. To do this run:

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

One important thing to do before running yum is to make sure the repo is disabled by default. The reason for this is, with the repo enabled, any yum updates will try and update mysql to Percona’s XtraDB-Cluster format.

To disable it, edit the configuration file:

vim /etc/yum.repos.d/Percona.repo

And change “enabled = 1″ to “enabled = 0″ and save the changes.

To then install Xtrabackup via yum, run the following command:

yum install percona-xtrabackup –enablerepo=percona

Note: The –enablerepo=percona switch enables the repo for this command only.

While there are options you can configure, in most circumstances Xtrabackup is ready to use straight from installation. First create a backup directory – for this example we will assume we’ve created /home/xtrabacups – and then simply run:

innobackupex /home/xtrabacups/

This will backup all of your databases to the target directory.

The option above assumes you have root password details saved in a /root/.my.cnf file. If not, you will need to specify the login details in the command, e.g. -

innobackupex –user=<dbuser> –password=<dbpassword> /home/xtrabacups/

Replace <dbuser> and <dbpassword> with either the root MySQL details, or the details of a specific database user.

Once run, Xtrabackup will create a timestamped file in the target directory, e.g. -

/home/xtrabacups/YYYY-MM-DD_hh-mm-ss/

Note: YYYY-MM-DD_hh-mm-ss will be replaced with the actual time and date that the backup was created.

To restore the whole dataset from this backup, you need to prepare the backup by running doing the following:

cd /home/xtrabacups/YYYY-MM-DD_hh-mm-ss/
innobackupex –apply-log –use-memory=2G ./

Next, back up the current mysql directory:

mv /var/lib/mysql /var/lib/mysql_old

- and replace it with the backup copy:

mv /home/xtrabacups/YYYY-MM-DD_hh-mm-ss /var/lib/mysql

If you would prefer to keep a copy of the backup, you can use ‘cp -pR’ instead of the mv command.

This only scratches the surface of what you can do with Xtrabackup. For more information, see Percona’s documentation at:

http://www.percona.com/doc/percona-xtrabackup/