Lenz Grimmer's blog

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:

1. FLUSH TABLES WITH READ LOCK (yes, this won't help for the InnoDB tables)
2. Create the snapshot
3. Store the output of SHOW MASTER/SLAVE status in a file to be part of the backup
4. UNLOCK TABLES
5. Mount the snapshot
6. Start a second MySQL instance that accesses the tables on the snapshot, let InnoDB perform its table recovery
7. 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.

Sorry for the downtime of this site - until around a week ago I hosted my home page on a trusty Genesi Pegasos II system (powered by a PowerPC G4 Processor clocked at 1GHz, using Debian 4.0 PPC with 512 MB of RAM), serving these pages from my home DSL connection. Unfortunately this system provided no means of redundancy - the hard disk drive died.

Luckily I perform frequent backups, so I moved most parts of the site to a shared hosting space now - the picture gallery is unfortunately too big to fit into the space that I have there. I'll try to move the pictures into my Flickr account instead, but this will take some time.

Note that the primary domain name of this site is now lenzg.net - lenzg.org, (the domain that I tried to promote as the official domain for my site) used to redirect to the home machine at lenz.homelinux.org. Both now redirect to the new address instead. I've initiated the move of the lenzg.org domain to the other provider as well, so soon this site will be available from both the .org and .net domain. Please don't link to lenz.homelinux.org anymore, as that site will eventually go out of service. Until then, a small openSUSE Linux box (Intel PIII, 500 MHz, 192 MB of RAM) running lighttpd will perform the URL redirection.