MySQL Hotbackups with XtraBackup


Hotbackups
are a staple of many different database platforms, but they remain sadly absent
from the core MySQL distribution.  Not to worry, the open-source
XtraBackup utility has just arrived to rescue you.

All Different Types of Backups

Relational Databases typically have a lot in common in terms of backups.  They have
different names for things, but they boil down to the same thing.  

First,
you have logical backups.  These are also known by the name database
dumps, or exports.  What are they?  When you perform a logical
backup, the database engine digs through your database, to create all the
statements required to build it again.  Think of it as the instructions to
build the building, not the building itself.  Like an architect’s
drawing.  These instructions can be fed into the database engine at a
later time, to rebuild everything from scratch.  Inside a logical backup,
you’ll find statements like CREATE DATABASE, CREATE TABLE, CREATE VIEW, INSERT,
and so on.  

Next,
we have cold backups.  These are created by first shutting down the
database and stopping all the software that mounts the physical files. 
When it is not running, your database sits in datafiles on the
filesystem.  A cold backup is then made by making copies of all of these
files, then tar & compressing them in the backup directory.  Therefore,
to restore from a cold backup, unlike a logical backup, no rebuilding needs to
happen, and no SQL statements need to run.  You simple need to configure
your database software to point to the restored files, and start it up!

Lastly,
you have hotbackups.  These have many of the properties of cold backups,
except you can create them while the database is still running.  The
obvious advantage here is always on availability.  This is key, as many
24×7 databases cannot afford to shutdown while performing a backup.  

MySQL Hotbackups

MySQL’s
original storage engine MyISAM was a much less sophisticated storage engine,
lacking transactions, and other features we find in modern relational
databases.  When InnoDB was introduced, Innobase did offer a commercial
hotbackup solution for their storage engine, but being commercial and not
available in the core database offering, it didn’t really catch on widely.

In
the last few years, Percona has stepped into the fray with their own MySQL
server including the XtraDB storage engine.  Percona’s server retains the
core MySQL distribution but also with some great scalability patches, and of
course, that means there is full backward compatibility with MyISAM and InnoDB
storage engines.  Even better, they’ve released an open-source hotbackup
utility which the whole community can enjoy and take advantage of.  That
tool – XtraBackup – supports MyISAM, InnoDB and XtraDB.

Using XtraBackup and Innobackupex

The
XtraBackup tool includes a myriad of different options.  You can call
XtraBackup directly if you want, however most likely you’ll just use the
included innobackupex script which simplifies its use and options.

The
simplest invocation would look like this:

$ innobackupex /backups

Where
/backups is your backup destination directory.  Now backing up using no
options like that will do a hotbackup of everything in the MySQL datadir,
including MyISAM tables, InnoDB tables & tablespaces, and xtradb tables if
you are using them.  It’ll even backup your my.cnf file for you. 
This is a handy feature when it comes time to restore, and you’re looking
around for an important file like that. 

With this configuration, however, there is one thing to keep in mind. You will still have to perform some recovery to startup MySQL from that backup. That’s because the backup software hasn’t applied changes captured in the InnoDB transaction logs, i.e. some changes that are still in memory, but durable by commits to written to the transaction log. The remedy? Use the –apply-log option and XtraBackup will go ahead and apply additional changes captured in the logs. Your resulting backup will now contain a datadir that is consistent and ready to run.

Other
useful options include –stream allowing you to generate a tar.gz file in your
backup directory, and also –slave-info, which will write additional
information about replication status to another file.

All
together, something like this should work well:

$ /usr/bin/innobackupex-1.5.1 --stream=tar --slave-info /backups | gzip - >
/backups/mysql_hot_`/bin/date +%m%d%Y`.tar.gz

Note
that we’ve used an [XtraBackup] section in the .my.cnf file to specify the user
& password options, so they are not prompted or required on the command
line.  This also means they won’t show up in the "ps" command.

Restoring From Your Backup

The
obvious next thing you’ll want to do is run through various restore and
recovery scenarios.  You do test your backups, don’t you?  

Luckily,
this is quite simple with these hotbackups.  There are four steps:

1.
stop running mysql

$ /etc/init.d/mysqld stop

2.
untar the backup into a new directory

$ cd /data
$ mkdir new_mysql
$ cd new_mysql
$ tar ixvzf mysql_hot_10222010.tar.gz

3.
edit my.cnf file

datadir=/data/new_mysql

4.
startup mysql again:

$ /etc/init.d/mysqld start

Point-In-Time Recovery

MySQL
achieves point-in-time recovery like other databases, by applying changes &
transactions that are captured in the binary logs.  Once you’ve restored
your MySQL database from a backup, and started mysql, you’ll use the
mysqlbinlog tool to get new transactions, and apply them.  

$ mysqlbinlog /data/new_mysql/NAME--bin.000001

More
specifically, suppose your hotbackup above is from 4am, and you want to apply
transactions to bring the database up to 4pm on the same day.  You would
do the following:

$ mysqlbinlog /data/new_mysql/NAME--bin.000001 --start-datetime='2010-10-22 04:00:00'
  --stop-datetime='2010-10-22 16:00:00' | mysql -u root -ppass

Conclusion

XtraBackup
brings powerful hotbackup functionality to MySQL deployments.  This brings
faster backup support, because MySQL doesn’t have to scan every object, and
every bit of data as it would to create a dump file.   This means backups
happen faster, and there is much less impact to database throughput while
backups are running.  On the restore side, it makes things almost
instantaneous.  Your backup is a fully functioning datadir waiting only
for a mysqld instance to mount it.  Powerful indeed!

»


See All Articles by Columnist

Sean Hull

Sean Hull
Sean Hull
Sean Hull is a business and technology consultant, author and speaker. He is the founder and senior consultant at Heavyweight Internet Group in New York and has worked as a database consultant and expert for ten years. He is the author of "Oracle and Open Source" on O'Reilly & Associates and his articles have appeared in Oracle's own OTN website, DBA Zine, ChangeThis.com, and many others. Using techniques, technologies, and perspectives discussed in the book, articles, and seminars, he has taken his career into exciting areas. He has served as Technology Consultant, Project Manager, CEO, and mentor with a background in programming, systems integration & administration, project development and management. He can be reached at shull@iheavy.com or visit http://www.iheavy.com for more info about consulting services.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles