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