MySQL backup solutions comparison

Preamble

There are multiple solutions to backup (and later restore/recover) your MySQL instances, here is the high level list with associated products to perform them:

  • Logical export/import: Mysqldump
  • Standby database: MySQL replication
  • Volume manager snapshot: LVM / NFS snapshot
  • Cold backup: Files copy by OS command (mysqlhotcopy is only for MyISAM and ARCHIVE storage engine)
  • Hot backup: Percona XtraBackup (free), MySQL Enterprise Backup (commercial product)

Few acronyms backup/restore oriented that you should be familiar with:

  • RPO: Recovery Point Objective
  • RTO: Recovery Time Objective
  • PITR: Point In Time Recovery
  • LVM: Logical Volume Manager
  • NFS: Network File System

Mysqldump

Pros

  • Validate databases files at same time
  • Logical backup so portable and usable backup files

Cons

  • Impact production server resources
  • Valid only for small instances
  • Very slow restore time
  • Historically buggy product
  • List of options must be chosen carefully (doing a consistent backup put a read lock on tables)

MySQL replication

Pros

  • Slave server can be used to decrease production server load
  • Lowest RTO as always on

Cons

  • Has never been a valid backup solution as mistake on source goes to slave
  • No PITR recover
  • A bit complex to setup

LVM / NFS snapshot

Pros

  • Easy to setup
  • Short RTO and flexible RPO

Cons

  • Read lock on all tables (warm backup), including InnoDB storage engine

Cold backup

Pros

  • Easy to setup

Cons

  • Instance must be down so only for QA/Dev instances

Percona XtraBackup

Pros

  • Incremental backup
  • Minimal impact on application (warm and hot backup where possible)

Cons

  • New tool to learn

MySQL backup comparison

We have all our “must have” list of features, this below one is mine, ordered by preference, and I rate those are the mandatory ones when putting in place a backup solution:

mysql backup solutions comparison
mysql backup solutions comparison

What is yours ?

About Post Author

Share the knowledge!

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>