Table of contents
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:
What is yours ?
