Databases Reference
In-Depth Information
Summary
Everyone knows that they need backups, but not everyone realizes that they need
recoverable backups. There are many ways to design backups that contradict your
recovery requirements. To help avoid this problem, we suggest that you define and
document your recovery point objective and your recovery time objective, and use those
requirements when choosing a backup system.
It's also important to test recovery on a routine basis and ensure that it works. It's easy
to set up
mysqldump
and let it run every night, without realizing that your data has
grown over time to the point where it might take days or weeks to import again. The
worst time to find out how long your recovery will take is when you actually need it.
A backup that completes in hours can literally take
weeks
to restore, depending on your
hardware, schema, indexes, and data.
Don't fall into the trap of thinking that a replica is a backup. It's a less intrusive source
for taking a backup, but it's not a backup. The same is true of your RAID volume, your
SAN, and filesystem snapshots. Make sure that your backups can pass the
DROP
TABLE
test (or the “I got hacked” test), as well as the test of losing your datacenter. And
if you take backups from a replica, be sure that you verify replication integrity with
pt-
table-checksum
.
Our two favorite ways to take backups are to copy the data from a filesystem or SAN
snapshot, or to use Percona XtraBackup. Both techniques let you take nonintrusive
binary (raw) backups of your data, which you can then verify by starting a
mysqld
instance and checking the tables. Sometimes you can even kill two birds with one stone:
test recovery every single day by restoring the backup to your development or staging
server. You can also dump the data from that instance to create a logical backup. We
also like to back up binary logs, and to keep enough generations of backups and binary
logs that we can perform recovery or set up a new replica even if the most recent backup
is unusable.
There are good commercial backup tools in addition to the open source ones we've
mentioned, foremost among them MySQL Enterprise Backup. Be careful with
“backup” tools that are included with GUI SQL editors, server management tools, and
the like. Likewise, be careful with “MySQL backup plugins” from companies who make
one-size-fits-all backup tools that claim to support MySQL. You really need a first-class
backup tool that's designed primarily for MySQL, not one that just happens to support
MySQL as well as a hundred other things. A lot of backup tool vendors don't know or
acknowledge the impact of practices such as using
FLUSH TABLES WITH READ LOCK
. The
use of this SQL command automatically disqualifies a solution as a “hot” backup in
our opinion. If you use only InnoDB tables, you usually don't need it.
