MySQL backup methods comparison

This post is mostly a summary of my recent MySQL Backups webinar. I talked about several backup methods, and I feel that it would be good to write down a comparison between these methods, which highlights the pro’s and the con’s con each of them.

Commercial and non-open source tools are not considered here, and in my opinion they are not essential.

You can of course watch the webinar recording, or the slides, by following the link above.

The fire of Alexandria
When 40,000 scrolls from Alexandria Library were burnt by Julius Ceasar,
there was no backup

Preamble: the importance of multiple strategies and tests

As a foreword, let me stress again some points:

  • Backups are not perfect and may fail.
  • If something changes in your infrastructure (versions, used features…) your backups may fail consistently.
  • Therefore automated tests are necessary.
  • Tests are not perfect and may not detect a failed backup.
  • Even if they detect a failed backup, this doesn’t change that you lack a recent backup.
  • Therefore multiple backup strategies are necessary.

Pro’s and con’s

Let’s see the pro’s and con’s of each backup type.

Logical backups (dumps)

Pro’s:

  • Dumps allow to only backup the table structures.
  • Dumps are very flexible in terms of what you backup. You could even backup the result of a query.
  • A dump can generally be restored into any version of any MySQL variant.
  • Dumps are generally smaller than other backup forms, because they don’t include indexes.

Con’s:

  • Taking a dump is slow, and making it consistent requires a long transaction.
  • Restoring a dump is slow, as it requires executing queries.
  • Simplistic backup tools make the process even slower, for example by making it mono-thread or creating indexes before populating the tables.
  • Making a dump incremental is not possible or requires extra work.

Snapshots

Pro’s:

  • Snapshots can be taken while MySQL is running, with no locks.
  • Virtually instantaneous.
  • Snapshots can easily be sent to other servers.
  • Technologies that support snapshots usually support incremental snapshots.

Con’s:

  • Taking a snapshot requires using specific technologies (zfs, lvm…).
  • After a snapshot, transactional tables (InnoDB) need to be repaired and non-transactional tables (MyISAM) may lose data.
  • We cannot backup single tables or exclude some tables.

Raw copy of the files

Pro’s:

  • Does not require any particular technology or tool.
  • Can be done incrementally or via a network (rsync).
  • The process is widely understood.

Con’s:

  • The copy process is slow.
  • We cannot exclude any InnoDB table from a backup.

Xtrabackup / Mariabackup

Pro’s:

  • The copy can be taken while MySQL is running and it’s not locking.
  • Supports incremental backups.
  • We can exclude tables from the backup.

Con’s:

  • MySQL routinely introduces big changes to GA versions. In one case (at the time of this writing) this broke Xtrabackup.
  • A fork must be used for MariaDB. They are similar but not identical.
  • Taking a backup is a 2 steps process (backup, preparation). A failure in the second step may ruin the whole backup.
  • Preparation is also needed for each incremental backup. A failure may make both the full backup and the incremental backup unusable.

Binary Log

Pro’s:

  • Simplest way to get an incremental backup.
  • It works independently from the backup method used to get a full backup.

Con’s:

  • It needs to be applied. Just copying it to the right place won’t work.
  • For some workloads, the binary log can be very big, as it includes inserted/updated rows that later are updated/deleted.

General advice

Some generic advice for companies that can spend enough resources for backups. Take them as hints if you don’t know how to define good backup strategies. It could be far from optimal for your specific case.

  • Implement at least two backup strategies.
  • Always take a dump of the structures. It can be useful.
    • The tool you use for migrations is not a completely reliable backup form.
  • Use snapshots if possible. Consider using a technology that supports snapshots, for example add slaves with zfs or lvm.
  • Use Xtrabackup.

Conclusions

This article is a summary of existing MySQL backup methods (not individual tools), with their pro’s and con’s. I may have forgotten something important, so please feel free to post a comment with your contributions. I will be happy to integrate this article, unless we disagree.

Let me use this opportunity to thank again the people who are contributing this website by pointing out mistakes or adding precious information.

Toodle pip,
Federico

Pic credit

Comment (1)

Leave a Reply

Your email address will not be published. Required fields are marked *