Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 16, 2010

MySQL Hotbackups with XtraBackup

By Sean Hull

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 24x7 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


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


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

MySQL Archives

Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM