Performing MySQL Backups Using LVM Snapshots

Security and Database Administration
Location: Ballroom B Level: Intermediate
Average rating: ****.
(4.50, 2 ratings)

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)
Photo of Lenz Grimmer

Lenz Grimmer

Sun Microsystems

Lenz Grimmer is a member of the MySQL Community Relations team of the Database Group at Sun Microsystems. He lives in Hamburg, Germany and has worked for MySQL since April, 2002. Before joining the community team in December 2005, he was a member of the release engineering team that is in charge of creating the official release builds of the MySQL server and related products. Prior to working for MySQL AB, he was a developer at the distribution engineering team at SuSE Linux AG, Nuernberg, Germany.

  • Kickfire
  • Zmanda, Inc.
  • Continuent
  • EDS
  • JasperSoft
  • Sun Microsystems
  • Symantec Anti-Virus Software
  • XAware
  • Data Direct Technologies
  • Dolphin Interconnect Solutions
  • Hewlett Packard
  • Infobright, Inc
  • Linagora
  • Microsoft
  • OpSource
  • Oracle
  • Pentaho
  • R1Soft
  • Red Hat
  • Ticketmaster
  • TechRepublic

Contact Us

View a complete list of MySQL contacts.