Home Home > 2012 > 10 > 14
Sign up | Login

Deprecation notice: openSUSE Lizards user blog platform is deprecated, and will remain read only for the time being. Learn more...

Archive for October 14th, 2012

xtrabackup for MySQL

October 14th, 2012 by

If you run data-driven applications like me, you are probably already running some kind of backup and have plans for disaster recovery. I hope you are not still using SQL dumps?

I have been using Percona XtraBackup professionally for MySQL backups for a while now. Especially if your database access is highly transactional you will find it useful that you can get consistent non-blocking, non-purging backups while continuing to serve transactions. Who wants downtime anyway?

Under the hood the software will take a dirty copy of the InnoDB tablespaces on disk, and extract binary logs required to bring all of these to a specific point in time, or rather LSN, using a patched version of the mysqld binary. The preparation / restore requires applying the binary log to the files which results in MySQL tablespaces and binary log files equivalent to how they would have been with a clean MySQL shutdown.

Mixing transactional with non-transactional database engines is possible if you are willing to accept some blocking time while backing them up. If you are using MySQL replication, you can also use this to create a new slave from either a master or to clone a slave from another without downtime of either.

The upgrade to the 2.0 series adds, among other things, parallel IO and parallel compression. This requires a new streaming file format xbstream in addition the previous tar. Think of it as a tar with multiple input pipes.

I added the xtrabackup package to openSUSE, it is available in the server:database project (repo, SLE 11) right now and will also be part of the next openSUSE release.

Remember that these are only tools. Love your data and protect your business. A copy is not a backup. A backup that isn’t monitored for success is not a backup. A backup that is not proven to restore successfully is barely a backup.

Contact me if you need help setting this up.