Performing MySQL Backups Using LVM Snapshots

Modern Linux distributions provide a very advanced technology to maintain and administer storage devices, called “Logical Volume Manager” (LVM). Among providing several benefits over using plain hard disk partitions, one of the unique features of LVM is the capability to create snapshots of file systems on the fly. This functionality comes in handy for performing consistent physical backups of MySQL tables without having to take down the MySQL server for the entire backup operation.

In this session, Lenz will give an introduction to the underlying technology, the actual steps that have to be performed and which caveats there may be. Additionally, he will introduce “mylvmbackup”, a script created to automate and ease the described procedure.

While other operating systems provide similar snapshotting techniques and some aspects covered in this session may apply there, too, this talk will focus on using Linux LVM snapshots.

This session will cover the following topics in more detail:

• Introduction to LVM, features and benefits
  • Storage Virtualization: General Concept, Terminology
  • Related tools (commandline/GUI)
  • Extending of volumes
  • Migrating data between physical volumes
• Explain the concept of LVM Snapshots and the difference between LVM1 and LVM2 snapshots (the latter ones being writable as well)
• Explain the general procedure to perform a MySQL backup using this technique
• Explain caveats with this approach
  • InnoDB inconsistencies (no support for FLUSH tables) and how to work around them.
  • FLUSH TABLES may take time and slow down the operation
  • I/O performance impact while the snapshot is active
• Practical hints and recommendations for storing MySQL tables on Logical Volumes
  • File system recommendations and caveats
  • Size considerations (e.g. how big should I make the snapshot?)
• Introduction to the mylvmbackup script: features, benefits, future plans
• Related technologies: ZFS snapshots/clones, Volume Shadow Service (VSS), Time Machine on Mac OS X “Leopard”, ext3cow, Veritas Volume Manager (VxVM)