Databases Reference
In-Depth Information
magnitude or more. In fact, one of the scariest things about logical backups is their
unpredictable restore time.
Here are some disadvantages of raw backups:
• InnoDB's raw files are often far larger than the corresponding logical backups. The
InnoDB tablespace typically has lots of unused space. Quite a bit of space is also
used for purposes other than storing table data (the insert buffer, the rollback seg-
ment, and so on).
• Raw backups are not always portable across platforms, operating systems, and
MySQL versions. Filename case sensitivity and floating-point formats are places
where you might encounter trouble. You might not be able to move files to a system
whose floating-point format is different (however, the vast majority of processors
use the IEEE floating-point format).
Raw backups are generally easier and much more efficient.
5
You should not rely on
raw backups for long-term retention or legal requirements, though; you must make
logical backups at least periodically.
Don't consider a backup (especially a raw backup) to be good until you've tested it.
For InnoDB, that means starting a MySQL instance and letting InnoDB recovery run,
then running
CHECK TABLES
. You can skip this, or just run
innochecksum
on the files,
but we don't recommend it. For MyISAM, you should run
CHECK TABLES
or use
myi-
samchk
. You can run
CHECK TABLES
on all tables with the
mysqlcheck
command.
We suggest a blend of the two approaches: make raw copies, then start a MySQL server
instance with the resulting data and run
mysqlcheck
. Then, at least periodically, dump
the data with
mysqldump
to get a logical backup. This gives you the advantages of both
approaches, without unduly burdening the production server during the dump. It's
especially convenient if you have the ability to take filesystem snapshots—you can take
a snapshot, copy the snapshot to another server and release it, then test the raw files
and perform a logical backup.
What to Back Up
Your recovery requirements will dictate what you need to back up. The simplest strat-
egy is to just back up your data and table definitions, but this is a bare-minimum ap-
proach. You generally need a lot more to recover a server for use in production. Here
are some things you might consider including with your MySQL backups:
Nonobvious data
Don't forget data that's easy to overlook: your binary logs and InnoDB transaction
logs, for example.
5. It's worth mentioning that raw backups can be more error-prone; it's hard to beat the simplicity of
mysqldump
.
