On Hot Backups and Restore using XtraBackup

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.

  • http://www.lexiconn.com/blog/ Rob Mangiafico

    Nice article! I’d love to see a follow-up article that discusses how to take individual database backups and how to restore a specific database instead of all of MySQL (especially with innoDB). This is quite tricky, and I’ve not found the right combination that allows a granular backup and restore on a database by database basis.

  • http://www.ovaistariq.net/ Ovais Tariq

    Yup I would sure follow up with a post on “taking backups of databases individually”, may be sometime coming week :)

  • Shlomi Noach

    Hi,

    Nice writeup.
    I would like to point out, though, that XtraDB does not hold locks for a short period of time while backing up MyISAM.
    “there is a read-lock taken for a small time while the MyISAM tables are being copied for data consistency,” — the sentence is mostly correct, but the “small time” depends on the size of your MyISAM tables. If you happen to have large MyISAM tables, this becomes a “long time”, in which your entire database is locked down in read mode.

    The only way of taking very small locks while backing up MyISAM is using file system snapshots, where lock time is only for the period of flushing down the tables and taking the snapshot.

    In fact, file systems snapshots, when available, answer for most of your requirements above.

    Regards

  • http://www.ovaistariq.net/ Ovais Tariq

    Hello,

    Thanks for reading my post :)

    Yeah you are absolutely write the time period for which the lock is taken depends on the size of MyISAM tables, but still its not as much as say mysqldump would take,. its mostly because of the way how MyISAM works and thats one reason why I recommend switching over to InnoDB,.

    And yes file system snapshot is another way of taking backups., but what are your thoughts on the performance implications with respect to snapshots,. they were pointed out a long time ago by Peter in this post http://www.mysqlperformanceblog.com/2009/02/05/disaster-lvm-performance-in-snapshot-mode/

  • Shlomi Noach

    I just happened to kill a mylvmbackup taken on a master, a couple hours ago. The impact on the mysql server was overwhelming.
    There are other file system snapshots: those embedded within filesystems (ZFS, btrfs). These do not do copy-on-write. I do not have experience with these.
    Also, there are hardware specific solutions (e.g. NetApp, Dell, etc. disk snapshots). I have good experience with these with regard to impact on database (I used both quite a while back).

  • http://www.ovaistariq.net/ Ovais Tariq

    Well yes lvm snapshots can really have quite a lot of performance impact,. thats why I have mostly been avoiding it,.

    I would love to see a post on your blog http://code.openark.org/blog/ about your preferred backup strategy :)

  • nav

    tariq i would like to know the size of database on which you have tested persona’s extra backup.

    Thanks..

    navjyot

  • http://www.ovaistariq.net/ Ovais Tariq

    varying sizes,,. 2G to 4G.,

  • nav

    Yeah but i am concern about millions of data or say GB or TB of data. Have you ever tested it on such a huge amount of data on a production server?

    –Navjyot

  • http://www.ovaistariq.net/ Ovais Tariq

    Like I said I have tested it on about 2GB to 4GB of data, on a production server,.
    What you can do is, test out XtraBackup on a slave., that will give you a better understanding whether XtraBackup suits your need or not,.

  • nav

    Yeah will do that..

    thanks..

  • http://www.youtube.com/watch?v=fmpOiOpYFrg getting rid of belly fat

    You certainly deserve a round of applause for your post and more specifically, your blog in general. Very high quality material!

  • Mlaray2k

    Hi, do Youtube know if Innobackupex woks with Mysql 5.6 community edition?

    Thanks, Manu

  • http://www.deivison.com.br/blog/2013/02/05/voce-ja-conhece-o-percona-server/ Você já conhece o Percona Server? | Desenvolvedor, Webdesigner e Pesquisador | deivison.com.br
  • Marc Castrovinci

    One note, MyIsam does not do real “incremental” backups. The full table is backed up everytime.

    For the below comment about DB size…. I use Xtrabackup on a production system in AWS. The DB is 180GB and incremental backups started getting into the 40 minute range. There is a certain level where Xtrabackup just isn’t feasable ( just like mysqldump does ).

    Finally, as of the newer releases Xtrabackup does support 5.6

  • http://www.ovaistariq.net/ Ovais Tariq

    Marc,

    Indeed MyISAM does not support real incremental backups, and as such if you only have MyISAM tables or a lot of your tables are MyISAM, you are not going to see much benefit from doing incremental backups.

    The reason in your case why incremental backups could be slow, could be due to IO saturation on the host, did you try doing parallel backups, using more than one parallel threads would give a lot of improvement in backup time if innodb_file_per_table is enabled.

    Well for InnoDB dataset, there can be nothing more feasible then XtraBackup if you care about backup and recovery time, and if you care about making sure that the backup is hot. The main point of taking backups is to be able to restore them. With XtraBackup, restores are very fast as they simply involve copying the backup into place and starting MySQL on it, on the other hand mysqldump based backups involve a lot of recovery time, and when you are down for some reason then its really a pain if recovery time is large.

    Lastly, XtraBackup 2.0.6 does support MySQL 5.6 now, but currently its only basic support, work on complete support is still in progress and XtraBackup release 2.0.7 should support more 5.6 features such as transportable tablespaces.

  • Suraj

    Explained in more sophisticated way !!! Thanks A Lot :)

blog comments powered by Disqus