MySQL Backups Using LVM Snapshots

If your MySQL data lives on an LVM volume, using an LVM snapshot is a great low-impact method of getting a point-in-time backup of your entire database. The only real gotcha is that snapshot must be made while the data files are in a "safe" state (meaning that all of the data has been written to file, and not only present in memory). Here are the basic steps needed to get a backups

1. Connect to MySQL and flush your tables to disk and lock them so nothing new can be written to memory with
   

   FLUSH TABLES WITH READ LOCK

   Do not do this with mysqladmin, and be sure to leave your database session open, otherwise the read lock will be removed as soon the the client disconnects.

2. In another terminal session, create the LVM snapshot of the filesystem where your MySQL data resides. For this example, we will assume that /var/lib/mysql is its own volume at /dev/vg0/mysql. Make sure to make this snapshot big enough so that there is enough room to accommodate new data coming in to the database as we perform the backup
   

   lvcreate -L8G -s -n mysql-backup /dev/vg0/mysql

3. Back at your open MySQL connection, release the table lock so that normal database operation can resume
   

   UNLOCK TABLES

4. Mount the LVM snapshot somewhere convenient
   

   mkdir -p /mnt/mysql-backup
   mount -o nouuid -t xfs /dev/vg0/mysql-backup /mnt/mysql-backup

   If you look at the contents of /mnt/mysql-backup now, you should see a replica of /var/lib/mysql exactly as it appeared when the snapshot was created.

5. Copy the entire directory to a location of your choice, using the method of your choice. For example
   

   tar -C /mnt -czf ~/mysql-backup.tar.gz mysql-backup

6. Once the backup is done, unmount and remove the snapshot
   

   umount /mnt/mysql-backup
   lvremove -f /dev/vg0/mysql-backup

As mentioned above, the database connection must remain open while the snapshot is created in order for the lock to be maintained. The only way to script this is to make use of a language that supports database connections. Here is a perl example.

#!/usr/bin/perl

use DBI;

# Connect to the local database
$dbh = DBI->connect('DBI:mysql:host=localhost;database=mysql', 'root', 'password') || die;

# Flush and lock tables to prepare for LVM snapshot
$dbh->do('FLUSH TABLES WITH READ LOCK;');

# Create LVM snapshot volume
system('lvcreate -L8G -s -n mysqlbackup /dev/vg0/mysql');

# Release table lock
$dbh->do('UNLOCK TABLES;');

# Disconnect from database
$dbh->disconnect();