Backups are an integral and very important part of any system. Backups allow you to recover your data and be up and running again, in the advent of problems such as system crashes, hardware failures or users deleting data by mistake. Backups are also important in case you are upgrading your MySQL installation and something goes wrong. Not only this, but backups can also be used to clone a MySQL installation or to setup slave servers.

Types of backups

There are mainly two types of backups:

  • Cold Backups:
    Cold backups are taken after making the database go offline, whereby during the process of backing up the database is not accessible to the user.
  • Hot Backups:
    Hot backups are dynamic or active backups and can be taken while the server is running and the data is being updated, added or read by the users. With a hot backup there is no need to take the database offline and hence there is no downtime as such.

Evaluating a backup solution

Now that we have discussed the two types of backups, we can pretty easily see that cold backups are pretty much unusable for live systems that cannot afford down-times. That is exactly the reason why this post would only be dealing with hot backups.

Now the two most important factors that I use in evaluating a backup solution that I use are:

  • Ability to do hot backup
  • Point-in-time data consistency of backed up data
  • Minimal restore times

And to be honest, I really wasn’t satisfied with any of the backup solutions available that could deal with backing up both the MyISAM tables and InnoDB tables in a consistent way. Of course there is the InnoDB hot backup tool available but that is a commercial tool and then I came across XtraBackup and I have been loving it since.

Introducing XtraBackup

XtraBackup is an excellent tool that handles both the MyISAM tables and the InnoDB tables seamlessly while maintaining data consistency. The other excellent aspect is that its an opensource tool and is free to use.

Following are some of the benefits of using XtraBackup that I found out while evaluating it:

  • Opensource tool with no cost attached, and of course you can get support from Percona if you need.
  • Handles both MyISAM and InnoDB tables seamlessly.
  • Manages data consistency very efficiently, there is a read-lock taken for a small time while the MyISAM tables are being copied for data consistency, while the InnoDB tables don’t require any read lock, instead the transaction log is used for managing the consistency (more on this later in this post).
  • Supports very fast, instant restores, literally. The most important thing that XtraBackup does is prepare the backup to be used on a live server, and with that all you need to do is copy the backup files to the MySQL data directory and off you go.
  • Supports incremental backups, which enables you to take a complete backup as a base once and then take incremental backups say everyday only for the changes recorded only during those days.
  • Supports throttling, to decrease the load caused on a live server while the backup is being taken.
  • Supports compressed backups that can greatly reduce the backup size and allow it to be moved around quickly and efficiently.
  • Supports streaming backups so that the backup can be piped to another server on the network, making it very efficient for cloning a server or setting up slaves on the fly.

Now that we have seen all the wonderful benefits of using XtraBackup let’s get up to speed on installing and using XtraBackup on a Ubuntu machine.

Installing XtraBackup

Installing XtraBackup on Ubuntu is very easy. In the following steps we will be installing XtraBackup by using apt:

  • Get the Percona GPG key 
    $ gpg --keyserver keys.gnupg.net --recv-keys CD2EFD2A
  • Import the key into apt 
    $ gpg -a --export CD2EFD2A | sudo apt-key add -
  • Add the Percona repository to the apt sources’ list by editing the file /etc/apt/sources.list and adding the following lines to the end of the file: 
    ## Percona sources
deb http://repo.percona.com/apt lucid main
deb-src http://repo.percona.com/apt lucid main

    One thing to note here is that XtraBackup is not yet available in the Maverick repository, but you can safely use the lucid as your distribution name as shown above, in case you are looking to install on Maverick.

  • Update the apt local cache 
    $ apt-get update
  • Now install using apt 
    $ apt-get insall xtrabackup

And there you go you have XtraBackup installed fairly easily. Now let’s get our hands dirty!

Backing up your databases using XtraBackup

Backing up using XtraBackup is pretty easy, there are two steps as follows:

  • Do a backup: 
    $ innobackupex-1.5.1 --user=root --password=somepass /var/backups/mysql/

    But what is this innobackupex, innobackupex is actually a wrapper over XtraBackup, that adds more functionality to XtraBackup. Also XtraBackup in itself doesn’t handle backing up of MyISAM tables, neither does it backup the schema.

    /var/backups/mysql/ is the directory where you want the backup to be saved. Within this directory a timestamped directory, corresponding with the date and time the backup is being taken at, will be created automatically and the backup files will be saved in the timestamped directory.

    Now if you take a look inside the directory where the backup is residing you would see two other important files, one is a backed up version of the my.cnf configuration file, and the other is a binlog info file that has important information about the binlog coordinates of the time the backup was taken.

  • Prepare the backup: 
    $ innobackupex-1.5.1 --user=root --password=somepass --apply-log /var/backups/mysql/2011-03-19_19-08-58/

    Note a time-stamped directory in the path /var/backups/mysql/2011-03-19_19-08-58/, that is the directory that was created automatically in the previous step. Also the --apply-log parameter executes xtrabackup --prepare twice on the data you just backed up.

    Now let’s understand what exactly the preparing step is for. Preparing the backup is an important step, the InnoDB data that is backed up is not consistent because it might have been changed while the backup was being taken or there might be any other reason. Also remember that there was no read-lock acquired during the process of backing up InnoDB data so changes might have taken place. But that’s not an issue, the preparing step is exactly for the reason of ensuring data consistency. As I mentioned above the --apply-log parameter executes xtrabackup --prepare twice, the first time it apply the transaction logs to the data to make them point-in-time consistent, and the second time to create fresh transaction logs.

    After the prepare step is completed, the backup is ready to be deployed and restored. The other most important advantage of this preparing step is that when you restore this backup its going to be instant, no waiting of any sorts, yeah instant!

Now you can do all sorts of things with this backup, archive and compress it, copy it over the network to create a clone and all sorts of other things.

Restoring the backup

Restoring the backup is as simple as copying over the backup files to the MySQL data directory defined in the my.cnf file. You could simply use cp to copy the files, also make sure the data directory and files are owned by the mysql user.

$ sudo mv /var/lib/mysql /var/lib/mysql-old
$ sudo cp -R /var/backups/mysql/2011-03-19_19-08-58 /var/lib/mysql
$ sudo chown -R mysql:mysql /var/lib/mysql/

Here I am assuming that /var/backups/mysql/2011-03-19_19-08-58 is the directory where the backup is residing that you want to restore, and /var/lib/mysql is the MySQL data directory as defined in the my.cnf file.

Conclusion

Now that’s it, see how easy it is to do a non-blocking hot backup with the server running. And how the restore happens in no time. Comparing it to all the available backup tools available the free ones (including mysqldump and mysqlhotcopy) and the commercial ones (including InnoDB hot backup), I feel XtraBackup is to me the best option. Look backup does take time and it would, depending on the size of the data, but the most important thing is instant restores, be it for doing a restore over a crashed machine or to bring up a clone or creating a new slave. So for me, instant restores together with the hot backup functionality is what makes XtraBackup a killer tool.