Databases Reference
In-Depth Information
InnoDB: Doing recovery: scanned up to log sequence number 0 40817239
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 3 4 5 6 ...[omitted]... 97 98 99
InnoDB: Apply batch completed
InnoDB:
Last MySQL binlog file position 0 3304937, file name
/var/log/mysql/mysql-bin.000001
070928 14:08:42 InnoDB: Started; log sequence number 0 40817239
InnoDB logs the MySQL binary log position corresponding to the point to which it has
recovered. This is the binary log position you can use for point-in-time recovery.
This approach to lock-free backups with snapshots has a twist in MySQL 5.0 and newer.
These MySQL versions use XA to coordinate transactions between InnoDB and the
binary log. If you restore the backup to a server with a different
server_id
from the one
on which the backup was made, the server might find prepared transactions from a
server whose ID doesn't match its own. In this case, the server can become confused,
and it's possible for transactions to become stuck in
PREPARED
status upon recovery.
This rarely happens, but it is possible. This is why you should always verify your backup
before you consider it a success. It might not be recoverable!
If you're taking the snapshot from a replica, InnoDB recovery will also print some lines
that look like these:
InnoDB: In a MySQL replica the last master binlog file
InnoDB: position 0 115, file name mysql-bin.001717
This output shows you the master's binary log coordinates (as opposed to the replica's
binary log coordinates) at the point to which InnoDB has recovered, which can be very
useful for making backups from replicas or cloning replicas from other replicas.
Planning for LVM backups
LVM snapshot backups aren't free. The more your server writes to the original volume,
the more overhead they cause. When the server modifies many distinct blocks in ran-
dom order, the disk head has to seek back and forth to the copy-on-write space and
write the old version of the data there. Reading from the snapshot also has overhead,
because LVM really reads most of the data from the original volume. It reads from the
copy-on-write space only as needed; thus, a logically sequential read from the snapshot
actually causes the disk head to move back and forth.
You should plan for this to happen. What it really means is that both the original volume
and the snapshot will perform worse than usual for both reads and writes—possibly
much worse if you use a lot of copy-on-write space. This can slow down both your
MySQL server and the process of copying the files for the backup. We benchmarked
and found that the overhead of an LVM snapshot is much greater than it ought to be—
we found that performance could be as much as five times slower, depending on the
workload and the filesystem. Keep this in mind when you're planning your backups.
The other important thing to plan for is allocating enough space for the snapshot. We
take the following approach:
