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?

4 Answers 4


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.

  • so are you suggesting for me to explore xtracbackup is it? I guess mysqldump is quite slow is it? So how about LVM is even better for huge data is it?
    – newbie14
    Commented Aug 17, 2012 at 17:55
  • Yes yes and yes. You may also check out MyDumper which is a parallel application of mysqldump. Commented Aug 18, 2012 at 17:42
  • Between mydumper and xtrabackup which will you suggest for me? So I keep taking the snapshots manually or let a backup engine to run on its own?
    – newbie14
    Commented Aug 19, 2012 at 6:16
  • Xtrabackup makes for a binary backup. I suggest you start with this. Next you can try snapshots. If you want logical backup try out MyDumper. I have no knowledge about your filesystem, operating system, disks etc. so I can't possibly recommend you which snapshot to take and how... Commented Aug 19, 2012 at 8:01
  • I would like to know the different in binary backup and logical.Is it that binary will be those binary log? I am using centos and fully using innodb and set innodb_file_per_table
    – newbie14
    Commented Aug 19, 2012 at 10:28

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

My Past Articles in the DBA StackExchange on Backups for MySQL

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.


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.

  • @technie we have already setup a slave now is in place ready and is working accordingly.So based on that what is your advice backup from the slave is it? Should we install xtrabackup by Percona there.
    – newbie14
    Commented Aug 16, 2012 at 18:40
  • i insteall percona xtrabackup on all my machines. You need it to prepare a snapshot made by xtrabackup anyway. I usually do a daily full snapshot with Xtrabackup and an hourly incremental snapshot.You could look into code.google.com/p/xtrabackup-manager for how to automate this
    – TechieGurl
    Commented Aug 27, 2012 at 21:25
  • @Techigurl it say it is still in beta version according to the wiki guide page. So is it ok to use then?
    – newbie14
    Commented Sep 1, 2012 at 5:31
  • yes xtrabackup manager is in beta so use it at your own risk. In our shop we went for writing a cron based manager script internally for better or worse.
    – TechieGurl
    Commented Sep 4, 2012 at 18:55
  • @technigurl so any other option for newbies like me to do have the backups done on regular basis?
    – newbie14
    Commented Sep 5, 2012 at 17:05

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 :-) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.