Today I gave my first MySQL University session as a speaker, talking about Backing up MySQL using file system snapshots. The talk went quite well (at least that was my impression) and we had ~10 people attending. The slides (PDF) and a recording of the session are now available from the Wiki page. Unfortunately the recording lacks the audio track, which is a bit of a bummer. We've submitted a support request with the DimDim folks, so hopefully they can provide us with a complete recording.
There was one question during the session that I was not able to answer myself, so I'm asking for your insights here:
Consider we're using InnoDB and MyISAM tables on a file system that can be snapshotted (e.g. Linux LVM or ZFS) and we're performing the following operations:
- FLUSH TABLES WITH READ LOCK (yes, this won't help for the InnoDB tables)
- Create the snapshot
- Store the output of SHOW MASTER/SLAVE status in a file to be part of the backup
- UNLOCK TABLES
- Mount the snapshot
- Start a second MySQL instance that accesses the tables on the snapshot, let InnoDB perform its table recovery
- Shut down the second instance and perform the backup of the snapshot
The question that came up was if this actually still is a consistent backup, considering that InnoDB rolled back uncommited transactions. Does the state of the tables still match the binary log positions we noted before? I assume yes, as long as the transaction does not involve modifications non-transactional tables.
Another suggestion that came up was to change InnoDB's configuration variable innodb_max_dirty_pages_pct to "0" prior to performing the snapshot, to minimize the amount of dirty pages that have not been written to disk (and thus reducing the time required for recovering later). I wonder if this would make a difference...
What other InnoDB variables might have a noteworthy effect in the context of snapshot backups? I am looking forward to your comments.
