Backup and Recovery - High Performance MySQL - page 644

connection waits for the global read lock, and it's impossible to predict how long this
will take.
Filesystem Snapshots and InnoDB
InnoDB's background threads continue to work even if you've locked all tables, so it
is probably still writing to its files even as you take the snapshot. Also, because InnoDB
hasn't performed its shutdown sequence, the snapshot's InnoDB files will look the way
these files would have looked if the server had lost power unexpectedly.
This is not a problem, because InnoDB is an ACID system. At any instant (such as the
instant you take the snapshot), every committed transaction is either in the InnoDB
data files or in the log files. When you start MySQL after restoring the snapshot, InnoDB
will run its recovery process, just as though the server had lost power. It will look in
the transaction log for any committed transactions that haven't yet been applied to the
data files and apply them, so you won't lose any transactions. This is why it's mandatory
to snapshot the InnoDB data and log files together.
This is also why you should test your backups when you make them. Start an instance
of MySQL, point it at the new backup, let InnoDB's recovery run, and check all the
tables. This way you won't back up corrupted data without knowing it (the files could
be corrupt for any number of reasons). Another benefit to this practice is that restoring
from the backup will be faster in the future, because you've already run the recovery
process.
You can optionally run this process on the snapshot before even copying it to the
backup, but that can add quite a bit of overhead. Just be sure you plan for it. (More on
this later.)
Lock-free InnoDB backups with LVM snapshots
Lock-free backups are only a little different. The distinction is that you don't do a FLUSH
TABLES WITH READ LOCK . This means there won't be any guarantee that your MyISAM
files will be consistent on disk, but if you use only InnoDB, that's probably not an issue.
You'll still have some MyISAM tables in the mysql system database, but if your workload
is typical, they're unlikely to be changing at the moment you take the snapshot.
If you think the mysql system tables might be changing, you can lock and then flush
them. You shouldn't have any long-running queries on these tables, so this will nor-
mally be very fast:
mysql> LOCK TABLES mysql.user READ, mysql.db READ, ...;
mysql> FLUSH TABLES mysql.user, mysql.db, ...;
You're not getting a global read lock, so you won't be able to get anything useful from
SHOW MASTER STATUS . However, when you start MySQL on the snapshot (to verify your
backup's integrity), you'll see something like the following in the log file: