Mysql backup strategies

backupinnodbMySQLmysqldump

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?

Best Answer

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.

Related Question