Databases Reference
In-Depth Information
comfortable losing, and how long you're comfortable waiting to get it back. Try to
answer the following types of questions when defining your RPO and RTO:
• How much data can you lose without serious consequences? Do you need point-
in-time recovery, or is it acceptable to lose whatever work has happened since your
last regular backup? Are there legal requirements?
• How fast does recovery have to be? What kind of downtime is acceptable? What
impacts (e.g., partial unavailability) can your application and users accept, and
how will you build in the capability to continue functioning when those scenarios
happen?
• What do you need to recover? Common requirements are to recover a whole server,
a single database, a single table, or just specific transactions or statements.
It's a good idea to document the answers to these questions, and indeed your entire
backup policy, as well as the backup procedures.
Backup Myth #1: “My Replica Is My Backup”
This is a mistake we see quite often. A replica is not a backup. Neither is a RAID array.
To see why, consider this: will they help you get back all your data if you accidentally
execute
DROP DATABASE
on your production database? RAID and replication don't pass
even this simple test. Not only are they not backups, they're not a substitute for back-
ups. Nothing but backups fill the need for backups.
Designing a MySQL Backup Solution
Backing up MySQL is harder than it looks. At its most basic, a backup is just a copy of
the data, but your application's needs, MySQL's storage engine architecture, and your
system configuration can make it difficult to make a copy of your data.
Before we go into great detail on all of the available options, here are our recommen-
dations:
• Raw backups are practically a must-have for large databases: logical backups are
simply too slow and resource-intensive, and recovery from a logical backup takes
way too long. Snapshot-based backups, Percona XtraBackup, and MySQL Enter-
prise Backup are the best options. For small databases, logical backups can work
nicely.
• Keep several backup generations.
• Extract logical backups (probably from the raw backups) periodically.
• Keep binary logs for point-in-time recovery. Set
expire_logs_days
long enough to
recover from at least two generations of raw backups, so that you can create a
replica and start it from the running master without applying any binary logs to it.
