Backups is one of the most important part of any MySQL deployment, and nowadays, there’s a number of tools to choose from depending on how your organization implements them. The purpose of this post is to enumerate the main tools and some helpers that makes backing up and testing/restoring your backups more convenient. By all means this is not the complete list, I’m sure I am missing some, so feel free to add them through the comments.

The Core Tools

• mysqldump – is a logical backup tool for MySQL. It creates plain text files with SQL statements which you can directly import back to the server. Some would say mysqldump is not really a backup tool as you cannot get a consistent backup without disrupting operations while the server is running. I’d say this is just a limitation, if your dataset is small and still happen to have some zero operation hours within your backup frequency period, mysqldump is still a plausible backup solution.
• mydumper – like mysqldump is also a logical backup tool, the only difference is that it can do the same in parallel. It’s complementary tool, myloader, allows the same parallel operations for loading your data back. At the time of this writing, there are no specific packages you can easily install, so you will have to compile the tools yourself.
• mylvmsnapshot / Filesystem Snapshots – LVM snapshots was previously a really hot option for backups when MyISAM roams the land and there was no native way to take hot backups from the server itself. In fact, if you’re still living with non-transactional tables like MyISAM, filesystem snapshots would still be an option for you. On the other hand, LVM snapshots in particular have significant overhead if you decide to leave the snapshot in place for too long. Ideally as soon as you’ve taken the snapshot, copy the contents and purge it, especially if you’re taking them from your production where all reads and writes happens. Yves from mysqlperformanceblog.com recently implemented ZFS snapshots and have nice things to say about it. Other filesystems based snapshots would be from your SAN storage.
• XtraBackup – has been almost the defacto for MySQL backups, for pure InnoDB workloads, it can do a completely non-intrusive backups. It does this by copying by copying the files then copying the changes from the transactions at the same time. Between the time the backup starts and finishes, all changes while the files are being copied are also tracked. These changes are logged into a special file which are then used to prepare the backup when you need it.
• MySQL Enterprise Backup – is actually where XtraBackup was patterned from. MEB started as InnoDB Hot Backup and has been a commercial software from the beginning and is now part of an Oracle MySQL Enterprise subscription.
• mysqlhotcopy – is an almost forgotten piece of software, it was originally targetted for MyISAM and Archive workloads and requires the MySQL server to be locked for the duration of the backup because it has to copy the files while the server is running. If a logical dump like mysqldump or my dumper does not fit but you can tolerate the constraints, mysqlhotcopy might still serve  a purpose in your stack.

The Helpers

• Zmanda Recovery Manager for MySQL – is a multi-purpose backup manager for several different objects including MySQL. It supports a number of neat features for managing your backups, scheduling, alerts, full and incrementals and plugins, one of which is it can use XtraBackup and another, filesystem based snapshots.
• s9s_backup – is a free offering from SeveralNines, it is a wrapper for XtraBackup and also integrated to their own ClusterControl offering.
• Holland Backup – is another plugins based backup manager from Rackspace. It takes a few notch to get up and running though based on documentation, but I suspect it has its own advantages. I have never used this one yet.
• xbackup.sh – is my own spinup based using bash scripting for XtraBackup. I created this for a customer bearing in mind only specific need, however it can easily be extended  to fill other gaps.
• XtraBackup Manager – is a PHP based backup wrapper for XtraBackup, although it has taken little traction lately, if you are a serious PHP shop, you might want to consider testing it out or even picking it up and contributing.

These are great backup tools, however, you only have chose what will work best for you. Do you have any interesting stories about dumping one for the other? Feel free to share them on the comments 🙂