Mysql backup strategies?

Question

We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run partitions. Any good tool to help for this task?

Answer 1

Today there's hardly any discussion (as I see it, of course), with regard to InnoDB backup. Xtrabackup by Percona is a widespread, feature rich & stable solution which offers:

• Hot backups (no locks placed on database)
• Incremental backups (done by backing up pages as opposed to statements)
• I/O throttling (allowing to limit backup I/O as as to alleviate the load from the machine/disks)
• Backup is consistent at time of end of backup (as opposed to time when backup started).

Xtrabackup derives from InnoDB Hot Backup, nowadays called MySQL Enterprise Backup. There's additional wrapping scripts which allow for the extra featureset.

Smaller databases may benefit from the common mysqldump. It really depends on your database size and traffic. mysqldump, event with --single-transaction, places a high load on the DB server, and makes for accumulating locks. It is not as hot as you might think (see this post of mine regarding backup temperatures).

I use LVM snapshots quite a lot. The great advantage is that these are DB ignorant, so no DB-related bugs affect this type of backup. The great disadvantage is an overly increased I/O. Check out mylvmbackup.

If you have ZFS or another file system which supports snapshots, that may be a great solution. If you have a storage device which supports snapshots, that's great too. All snapshots are DB ignorant, though you often want to script this a-la mylvmbackup so as to catch the point in time (DB-wise) for which backup is done.

Answer 2

I am sort of an old school DBA, so hear me out anyway...

My Past Articles in the DBA StackExchange on Backups for MySQL

• Apr 17, 2011 : How can I optimize a mysqldump of a large database?
• Jun 26, 2011 : How to enable all the possible backup options of MySQL
• Oct 01, 2011 : Quick MySQL Backup
• May 16, 2012 : How to Create Snapshot Backups in MySQL
• May 22, 2012 : MySQL Server Backup (includes how to do so with MySQL for Windows)
• Jul 24, 2012 : How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?

In my earlier posts, I recommend doing mysqldumps in parallel as well as one single file. I even suggest trying out XtraBackup. I do not want to limit you to mysqldump. Some databases are so large that LVM Snapshots are the only possible solution.

Answer 3

Important things to think about when picking a backup strategy:

• How long is an acceptable amount of downtime while having to recover data from a backup
• Is recovery going to always mean All the database? or is there a use case for a more frequent backup of specific, more critical tables?
• What acceptable load on the server making the backup is accepted? meaning, are you gonna have a replica dedicated to backups? or are you tight on money as an org and will have to run this against your only slave (I HOPE you don't say there only one and only DB server :) )
• Is paying for a backup solution an option? or free is more preferred?

This is how I would tackle this in order of priorities. and that is then end of my objective answer :)

Now, for the subjective part based on my experience:)

For a large portion of DB setups these days, using mysqldump as the ONLY backup solution is not scalable. It is slow, creates HUGE files, importing mysqldumps takes forever, and it is tricky making mysqldump give you a true 'point in time' recovery with data consistent across the board. I use mysqldump to backup v small tables (less than 200MB each) that are also super critical and more prone to end user foul ups (like user table or user_profile). The common use case when I go back to these is when someone asks "When was this user changed? and what was the value of setting X for them" so it is not really a data is lost rather than a data is messed up situation. For true 'world on fire' backups, I use xtrabackup by Percona. It is free, open sourced, in very active development, and very easy to automate.

Answer 4

When discussing the MySQL Server backup strategy, it should be noted that this topic is not limited to the choice of the tool that will be used to backup your databases. What command should be used for backups is well discussed in other answers.

You also need to consider how to transfer the backups to the storage, of course, if you are not going to store the backups in the same place as the MySQL Server.

In my opinion, the most important point during the MySQL Server backup strategy creation is a notification system. Make sure that you'll receive the notifications in case of any issues, both when creating a backup and when transferring it to storage.

In the simplest case, this can be done by a shell script. In more complex cases, and if you are too lazy to write scripts, then you can take a look at the third-party tools.

Sometimes I'm considering moving my architecture to the cloud, it is more expensive but much more reliable. (Ultimately, what could be more important than my sound sleep :-) )