Backup & Recovery: Ch.22 MySQL Backup & Recovery

This Wiki is brought to you by Backup Central, where you can find the Mr. Backup Blog, Forums, and a mailing list for each forum!
Backup FAQs	Service Providers	Backup Software	Backup Hardware	Backup Book Wiki	Free Stuff	Miscellaneous

This entry will contain updated information for this chapter of Backup & Recovery. If W. Curtis Preston wants to update some information from the chapter, this is where it will go. If you'd like to add your own thoughts or suggestions, feel free to click edit and help out!

---

The MySQL LVM --snapshot backup bit (ch22; pg650) needs some more info, or a link to same. Having said that, I'm not sure what the link is. [1] got me going again, but it's not a HOWTO. [2] is a nice HOWTO, but it does not really address this issue IMHO... The problem is that LVM snapshots won't work unless you've planned for them and set up your filesystem correctly at install-time.

It turns out that if your physical volume (PV) is full, probably because your logical volume(s) (LV) used the max size, there are "Insufficient free extents in volume group" to create a snapshot. [1] So when setting up the system, you have to deliberately leave enough free space in the PV or else you can't use snapshots... I was wondering how that would all work when reading that section of the book...

I used resize2fs/lvresize on my test VM (gotta LOVE VMware) and all is now well. But I'm not sure I'd do that in production, and resize2fs requires that you unmount the volume if it's ext3, at least on the (pre-release) Debian Etch system I just tested.

FWIW, there is a Perl script that takes care of performing LVM snapshot backups of MySQL automatically: mylvmbackup. From the project page:

mylvmbackup is a Perl script for quickly creating backups of MySQL server's data files. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, makes an LVM snapshot of the volume containing the MySQL data directory, and unlocks the tables again. The snapshot process takes only a small amount of time. When it is done, the server can continue normal operations, while the actual file backup proceeds.

Footnotes:

[1] http://mike.kruckenberg.com/archives/2006/05/mysql_backups_u.html See "just a note to anyone hoping to start using [LVM] snapshots on an already running system - even if the machine is already using [LVM], you're probably out of luck."

[2] http://www.linux.org/docs/ldp/howto/LVM-HOWTO/index.html

---