Databases Reference
In-Depth Information
--tz-utc
Makes it possible to move data between servers in different time zones.
--lock-all-tables
Uses
FLUSH TABLES WITH READ LOCK
to get a globally consistent backup.
--tab
Dumps files with
SELECT INTO OUTFILE
.
--skip-extended-insert
Causes each row of data to have its own
INSERT
statement. This can help you se-
lectively restore certain rows if necessary. The cost is larger files that are more
expensive to import into MySQL; you should enable this only if you need it.
If you use the
--databases
or
--all-databases
options to
mysqldump
, the resulting dump's
data will be consistent within each database, because
mysqldump
will lock and dump
all tables a database at a time. However, tables from different databases might not be
consistent with each other. Using the
--lock-all-tables
option solves this problem.
For InnoDB backups, you should add the
--single-transaction
option, which uses
InnoDB's MVCC features to create a consistent backup at a single point in time, instead
of using
LOCK TABLES
. If you add the
--master-data
option, the backup will also contain
the server's binary log coordinates at the moment of the backup, which is very helpful
for point-in-time recovery and setting up replicas. However, be aware that it will use
FLUSH TABLES WITH READ LOCK
to freeze the server so it can get the coordinates.
Scripting Backups
It's pretty standard to need to write some scripts for backups. Rather than showing you
a sample program, which would necessarily have a lot of scaffolding that just takes up
space on the page, we list the ingredients that go into a typical backup script and show
you code snippets for a Perl script. You can view these as building blocks that you can
snap together to create your own script. We show them in roughly the order you'll need
to use them:
Sanity checking
Make life easier on yourself and your teammates—turn on strict error checking
and use English variable names:
use strict;
use warnings FATAL => 'all';
use English qw(-no_match_vars);
If you script in Bash, you can enable stricter variable checking, too. The following
will raise an error when there's an undefined variable in a substitution or when a
program exits with an error:
set -u;
set -e;
