MySQL Backups using LVM snapshots

There are a couple of options available to get consistent backups from MySQL.

  1. Use mysqldump with FLUSH TABLES WITH READ LOCK
  2. Use a slave with STOP SLAVE and your favourite backup tool.
  3. For innodb, use the commerical backup tool ibbackup
  4. Use LVM (Logical Volume Manager) snapshots with FLUSH TABLES WITH READ LOCK
  5. Shutdown the database.

We are going to demostrate how to use LVM snapshots to reduce time required to hold MySQL consistent. I used these two good articles here and here as a basis for this one.

To use LVM on EC2, you need to umount the /mnt partition and create a physical volume (pvcreate) and then a bunch of logical volumes (lvcreate). Once you have the use of LVM, you can use lvcreate -s to create a snapshot of the logical volume.

The ease of taking backups like this means the time that the mysql database must be unable to handle writes (due to the read lock) is as short as the time takes for the lvcreate to finish.

As normal I have provided a cleared up screen dump of my terminal at the end of this post.

I was able to successfully take a backup, and tar the snapshot and create a slave from the backup.

Here is the procedure to take a backup using LVM.

  1. mysql connection (left open): FLUSH TABLES WITH READ LOCK;
  2. lvcreate -L16G -s -n dbbackup /dev/vg/myvmdisk1
  3. mysql connection: UNLOCK TABLES;

Notes:

  • You must wait for the FLUSH TABLES to finish, otherwise your backup will NOT be consistent. Peter Zaitsev mentions flushing individual tables can help the speed, followed by a final FLUSH TABLES
  • Leave the mysql connection open, so the READ LOCK is held until you issue the UNLOCK
  • lvcreate -s can fail with an error snapshot: Required device-mapper target(s) not detected in your kernel The solution: modprobe dm-snapshot

There is a perl script from Lenz Grimmer called mylvmbackup which I will check out soon.

Have Fun

Paul

This script with create two logical volumes /data1/mysql, /data2/mysql both 30 Gigabytes in size


### BEGIN OF SCRIPT ###

#!/bin/sh
#
# Script to make EC2 /mnt into a LVM volume

umount /mnt
pvcreate /dev/sda2
vgcreate vg /dev/sda2
lvcreate -L30720M -n myvmdisk1 vg
mkfs -t ext3 /dev/vg/myvmdisk1
mkdir -p /data1/mysql/data
mount /dev/vg/myvmdisk1 /data1/mysql

lvcreate -L30720M -n myvmdisk2 vg
mkfs -t ext3 /dev/vg/myvmdisk2
mkdir -p /data2/mysql/data
mount /dev/vg/myvmdisk2 /data2/mysql

lvcreate -L30720M -n myvmdisk3 vg
mkfs -t ext3 /dev/vg/myvmdisk3
mkdir -p /backup/mysql/data
mount /dev/vg/myvmdisk3 /backup/mysql

lvdisplay

mkdir -p /data1/mysql/data
mkdir -p /data2/mysql/data

chown -R mysql:mysql /data1/mysql
chown -R mysql:mysql /data2/mysql
chown -R mysql:mysql /backup/mysql

### END OF SCRIPT ###

Do the snapshot backup

Flush the tables with read lock to dump to disk and get consistent state of data.

mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.20-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sakila |
| test |
+--------------------+
4 rows in set (0.01 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Try the create the snapshot...

lvcreate -L16G -s -n dbbackup /dev/vg/myvmdisk1
snapshot: Required device-mapper target(s) not detected in your kernel
lvcreate: Create a logical volume

modprobe dm-snapshot

lvcreate -L16G -s -n dbbackup /dev/vg/myvmdisk1
Logical volume "dbbackup" created

Check what logical volumes are available

lvdisplay
--- Logical volume ---
LV Name /dev/vg/myvmdisk1
VG Name vg
LV UUID 1IpGD0-c3e0-DauB-Xj8p-AZAk-VpNG-RRDbUP
LV Write Access read/write
LV snapshot status source of
/dev/vg/dbbackup [active]
LV Status available
# open 1
LV Size 30.00 GB
Current LE 7680
Segments 1
Allocation inherit
Read ahead sectors 0
Block device 253:0

--- Logical volume ---
LV Name /dev/vg/myvmdisk2
VG Name vg
LV UUID bIz7vN-zWy8-PT3N-j1GT-URwi-RN8p-o6eRFQ
LV Write Access read/write
LV Status available
# open 1
LV Size 30.00 GB
Current LE 7680
Segments 1
Allocation inherit
Read ahead sectors 0
Block device 253:1

--- Logical volume ---
LV Name /dev/vg/dbbackup
VG Name vg
LV UUID c4GXbG-3dpv-7zEI-sm8r-eLFq-t3Ud-XdQHAx
LV Write Access read/write
LV snapshot status active destination for /dev/vg/myvmdisk1
LV Status available
# open 0
LV Size 30.00 GB
Current LE 7680
COW-table size 16.00 GB
COW-table LE 4096
Allocated to snapshot 0.00%
Snapshot chunk size 8.00 KB
Segments 1
Allocation inherit
Read ahead sectors 0
Block device 253:4


mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 3840558 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


mkdir /mnt/backup
mount /dev/vg/dbbackup /mnt/backup

df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 7.9G 1.3G 6.3G 17% /
/dev/mapper/vg-myvmdisk1
30G 3.6G 25G 13% /data1/mysql
/dev/mapper/vg-myvmdisk2
30G 77M 28G 1% /data2/mysql
/dev/mapper/vg-dbbackup
30G 3.6G 25G 13% /mnt/backup

Make a tarball of /mnt/backup/data

cd /mnt/backup
tar -czvf mysql_backup_`date +%Y%m%d`.tar.gz data/ /etc/my.cnf

7 thoughts on “MySQL Backups using LVM snapshots

  1. Hey Paul, great post. Why the three different logical volumes? I understand two, one for the data you are working with and the other to store the snapshot. Also, when you tar the snapshot does it have to be kept in a single file or is fine to break it up into multiple files to be moved to S3? Does it matter that the file is small for S3?

  2. Thanks Michael.You could just have had one logical volume for data.It does help with administration tasks to break up large volumes. Especially if there is some difference in the backup regime for specific datasets. For example you need to backup up transactional data more often than reporting data. In that situation you would put the transactional data on one LVM and the reporting data on another.Having one file or multiple files shouldn’t matter. I am not sure what the optimal size for writing to S3 is. The Amazon forums will have more information.

  3. Paul,I am playing around with your idea hear, and I have a small EC2 question . I know you’re not EC2 tech support, but I figured you probably ran into this issue.First of all I was attempting your strategy on a m1.large instance on EC2 running Fedora core. Note: the /mnt is mounted on /dev/sdb instead of /dev/sda2 but that’s no big deal. I got the volumns created just fine…BUT, I was hoping to be able to make an EC2 “bundled image” to capture this configuration. And my first stab at it didn’t quite work… basically it doesn’t capture the LVM config settings in the image.How would you handle this? I will admit I’m not an LVM expert, so I will keep sniffing around, maybe there’s some setting in /etc/lvm that would make these volumes automatically get setup and mounted.I realize as I type this that this question probably doesn’t make sense since obviously I wouldn’t want the contents of these directories in the image… but I’d like the configuration.Thanks in advance.

  4. Hi Brad,I would take the Rightscale method on this i.e. build and bundle your image without mucking with trying to make LVM stick on boot.Rather run a script on boot (either by bundling it) or have it grab a script from S3 which will rebuild /mnt as you want.The second method is preferred as it means you can change the script rather than having to bundle a new image for each change.Given no storage is persistent on EC2 across instance terminations you will be replicating the data from somewhere else anyway (probably S3).Hope this helpsPaul

  5. Paul,This is a great post, and I’ve made some good progress on getting this to work on EC2… but I have a couple comments/questions.1) On my question earlier about getting Logical volumes to work in an AMI, I gave up on this, and as you suggested just have this getting setup with a script similar to what you described that is run in my “firstrun” case of rc.local. Seems to work find, but since the mkfs step can be slow, it’s a bit of a pain and my (large) instances take almost 20 minutes to start up… not really a concern or question, just a comment, and info for anyone else who may be interested.2) I am not clear why you created the /dev/vg/myvmdisk3 logical volume? You don’t seem to be using it. The backup seems to actually go to /dev/vg/dbbackup. And when you do your lvdisplay later I no longer see disk3… was disk 3 a mistake in the earlier part of the post? Maybe you can explain more what your intent was with this.3) Finally, I am seeing something really strange which is probably not at all related to mysql, lvm, or ec2, but I’m not enough of a linux guru to understand why it’s happening…. namely, I made a shell script that handles all of the backup steps including tar-ing up the backup for passing along to S3. My databases are about 1.5GB on disk and they tar/gzip to about 80MBs… for some reason my script is taking about 20 minutes to run (on an EC2 large image, with 8GB of ram and 4 virtualized processors… a ridiculously fast box)… but if I run the TAR from the command line it takes about 30 seconds to produce the same result. There’s clearly something about my “backup” script which is being tagged as “low priority” but when I issue the exact same commands from a terminal window, they all happen very fast. Thoughts on what might be causing this?Thanks again for a great post!

  6. Sorry for the delay in answering.1) Yeh, even Amazon recommends you warm up the disks before using them. There is more in their guide.2) Third volume. There was no reason. It was to demonstrate that you can slice up the existing raw space into as many or as few volumes as you like.What would be really interesting is if there is any measurable difference between volumes due to the place on disk. Given this is all virtual disk I doubt it.3) Break it into parts, are you using tar -xvzf (gzip) or tar -cjvf (bzip). Also see if piping tar standard output through gzip makes a difference.You may be right maybe crond has been niced. :)Have Fun

  7. Just a followup.You don’t have to allocate all the physical volume at once.Plus if you db is under 8 Gig, so read my recent sysbench fileio results.I would sticking the write intensive parts on /dev/sda rather /mnt.So binlog, innodb log files. You can still and should still back these up as well.Have Fun

Comments are closed.