cameuh.net Archives

Hot Backuping MySQL with LVM Snapshot (Linux)

Introduction, Rationale

What are the different ways to backup a MySQL database?

The simplest is to shutdown the server and to backup the files using a file backup tool like tar:

tar czvf dump_mysql.tar.gz /var/lib/mysql

But you can’t always just shutdown the server: it can be used by clients for a critical usage. In those cases, there are some tools supplied by the MySQL distribution to achieve the backup.

mysqldump creates a SQL text file and dumps all the database (the schema, the data, etc.) into it.

mysqlhotcopy is very faster because it just copy the files, like we did with tar previously, while the database is online.

Those tools are very handy but have a big problem: they lock entirely the database. Any client requesting or wanting to insert/update/delete a row will have to wait until the backup is finished. While those solutions are often sufficient, if you have a BIG database (like mine) and you CAN’T turn it off to back up the files, locking the requests from clients during HOURS to make a backup is NOT an option.

Here is the ULTIMATE solution: LVM’s snapshotting feature.

Make sure you have the correct kernel options enabled and userland tools installed

The snapshot feature I use is a part of LVM2. Depending on your Linux distribution, the lvm2 package have to be installed.

LVM and the snapshot feature are two kernel options that should be enabled and loaded. On a modular kernel system, lsmod should return dm_mod and dm_snapshot. Basically, kernel options CONFIG_BLK_DEV_DM and CONFIG_DM_SNAPSHOT should be enabled.

Make sure your datadir is on a LVM volume

Of course, to make use of the snapshot feature, the file system under which the MySQL data resides on HAVE TO be a LVM volume.

Quick example to setup the MySQL datadir on a LVM volume

hda4 is a free partition on your hard disk drive:

# we tell LVM to prepare hda4 to be used
pvcreate /dev/hda4

# we create a 'vg' volume group (free space) using hda4
vgcreate vg /dev/hda4

# we create a 1GB 'datadir' volume into 'vg'
lvcreate -L 1G -n datadir vg

# we make a ext3 filesystem
mkfs.ext3 /dev/vg/datadir

# we mount it
mount /dev/vg/datadir /var/lib/mysql

# et voilà. just start mysqld
/etc/init.d/mysqld start

Snapshot and backup made easy

OK, the mysqld database server is running, there are tons of GBs in /var/lib/mysql and you can’t stop the server to make a backup.

Ready? This will be very easy. To reduce the lock time at the minimum, prepare all the commands and type them one after one.

  • Lock the database, create the snapshot (be sure to allow enough space to contain every blocks that will change during the snapshot life) and unlock the database:
SQL> FLUSH TABLES WITH READ LOCK;
# lvcreate --snapshot -L 5G --name snap /dev/vg/datadir
SQL> UNLOCK TABLES;
  • Mount the snapshot and backup files:
# mkdir /mnt/snap
# mount /dev/vg/snap /mnt/snap/
# cd /mnt/snap/
# tar cvzf /home/dump_lvm.tar.gz .
  • Umount and delete the snapshot:
# umount /mnt/snap
# rmdir /mnt/snap
# lvremove /dev/vg/snap

More about the MySQL/LVM Snapshot backup

This backup method isn’t particular to MySQL. LVM’s snapshot feature can be used to backup any filesystem. This documentation uses MySQL as an example because it needs an additional action: the lock of the entire database during the snapshot creation.

Indeed, tables have to be flushed and closed on disk before the backup to capture a consistent filesystem. Between the LOCK and UNLOCK commands, any MySQL request will be stalled. After the snapshot creation, the clients can continue to use MySQL and update data.

When we mount and access file data from the snapshot volume /dev/vg/snap, LVM redirects our system reads to the original datadir volume if the block hasn’t been changed since the snapshot date. If the block to be read has been changed, the original were copied in the area we allocate to the snapshot (5 GB in the example) so we still can read it via LVM.