MySQL Backups using LVM Snapshots

Mike Kruckenberg: MySQL Backups using LVM Snapshots

MySQL Backups using LVM Snapshots
The second thing on my list coming away from the MySQL Users Conference (first was a question about limits in mysqldump) was to do a writeup on how to use Linux LVM (Logical Volume Manager) to take quick snapshots of your database. Many folks are using this as an alternative to the tools provided with MySQL. It's a good choice for a backup when you need to backup in order to restore the entire database from files.
Taneli has already done a write-up of the steps to complete this process, I want to add in a few things:
LVM not a True Online Backup
LVM doesn't do a true online backup, although it gets close. The actual LVM snapshot command is fast, but to get a good copy of your data all of your MySQL tables must be read locked to not allow any writes during the snapshot process. When you ask MySQL to flush all tables with read locks it will work through the tables (after it's sorted the list of tables to lock) making sure the data is on disk and then getting a read lock on each table. If tables are locked by another thread your request will wait until the locks are freed up. The bottom line is that if you have a busy database with many tables and very active writing there's a chance your FLUSH TABLES WITH READ LOCK could take time. The issue there is if tables 1, 2, and 3 get locked but then table 4 has just started a massive update you will have to wait until table 4 is done to get the lock while tables 1, 2, and 3 are also locked.
The other thing about getting read locks is that if you're using a transactional engine, transactions are committed as a part of getting the read lock. There's some extra work to be done to avoid the ramifications of committing in-process transactions.
Disk Requirements
LVM snapshots work by keeping an exception log. As long as the snapshot is in existence (until it's removed with lvremove), LVM keeps track of changes on the disk in a log. If you're using the snapshot data it knows that certain parts of the disk must be pulled from the exception log for data that has changed since the snapshot was created.
This is where the --size option comes in. When you issue the lvcreate --snapshot command you tell it how much space to allow for recording changes. If you have a very active database you'll need to make sure that the amount of data that will change over the time you are using the snapshot will fit in your specified allocation. The manpage recommends 15-20% of the disk size.
Performance
While a snapshot is active LVM has to keep track of the changes. Any time a change is made on the disk LVM has to make a note of it in the exception file. I'm not sure how significant this is, it depends on your database activity, size, and how long you need the snapshot. The advice seems to be to take the snapshot, mount it, copy the data off, and remove it as quickly as possible.
Jeremy Cole wrote about an open-source pluggable tool he's writing for things like LVM. At the UC he was talking about doing some more serious testing of the exception file size and performance hit you take with active shapshots. Will be interesting to see what comes from that.
Update: MyISAM vs InnoDB
Harrison (who needs to start a weblog) points out that my post is MyISAM-centric. With InnoDB the LVM snapshot can be taken directly from the filesystem without locking the tables. On restore InnoDB will recover as if there was a system crash, rolling the data forward from the binary logs. Given that, it seems like LVM snapshots give you something very close to the ibbackup tool you can buy from Innobase Oracle.