MySQL database backup using Linux LVM

LVM - or simply Logical Volume Manager, Linux LVM is the implementation of Logical Volume Manager for the Linix kernel. Most of the Linux distributions include LVM. Besides obvious benefits of using Logical Volume Manager there is also a great feature - LVM snapshot capability that is included in Linux LVM

LVM snapshot is an awesome tool that is included with your Linux distribution at no charge to you! LVM snapshot allows you to create a new block device that is the exact copy of the logical volume frozen in time.

Why this is such a great capability? Say you have a 500GB database and need to create a replicated slave or take a database backup. In order to do that you have to resort to one of the following:

1. Stop the database (or flush tables with read lock) and copy the files - that could take several hours.

2. Use INNODB backup tools - still will take quite some time and that does not copy your MYISAM tables

3. Use MySQL dump - not even practical for 500 GB database could take days!

Enter the LVM snapshot greatness - very simple, fast and least downtime way to create your MySQL backup and recovery strategy. Most importantly unlike dedicated storage engine backup solutions (IBBACKUP etc...) LVM snapshot will work with your MYISAM tables also.

Here's how to implement it:

First and foremost you must have LVM enabled, assuming that you have you can use LVM commands to explore your logical volumes. LVM commands are located in /sbin/ directory.

Start using LVM

[root@db22 /]# ./sbin/lvm
lvm>

View your logical volumes:


[root@db22 /]# ./sbin/lvm
lvm> lvdisplay
--- Logical volume ---
LV Name /dev/VolGroup00/LogVol00
VG Name VolGroup00
LV UUID u8PhLr-6dfg-WcmL-qzCr-4E6a-2wYB-IlNsrh
LV Write Access read/write
LV Status available
# open 1
LV Size 31.66 GB
Current LE 1013
Segments 1
Allocation inherit
Read ahead sectors 0
Block device 253:0

--- Logical volume ---
LV Name /dev/VolGroup00/LogVol01
VG Name VolGroup00
LV UUID gA8rvW-QwxV-GbLB-KADy-Ank9-9vJx-D3VKz0
LV Write Access read/write
LV Status available
# open 1
LV Size 1.94 GB
Current LE 62
Segments 1
Allocation inherit
Read ahead sectors 0
Block device 253:1

--- Logical volume ---
LV Name /dev/VolGroup01/LogVol01
VG Name VolGroup01
LV UUID KLND46-bfYW-MpaZ-hr2y-6nF4-L1yo-zeT4Zr
LV Write Access read/write
LV Status available
# open 1
LV Size 772.91 GB
Current LE 197866
Segments 2
Allocation inherit
Read ahead sectors 0
Block device 253:2

lvm>

Now you can figure out which logical volume is hosting your database data by viewing /etc/fstab file - your Linux partition mount file:

/dev/VolGroup00/LogVol00 / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
none /dev/pts devpts gid=5,mode=620 0 0
none /dev/shm tmpfs defaults 0 0
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0
/dev/VolGroup00/LogVol01 swap swap defaults 0 0
/dev/VolGroup01/LogVol01 /data ext2 suid,dev,exec 0 0
/dev/hda /media/cdrom auto pamconsole,exec,noauto,managed 0 0


Based on the output of /etc/fstab we can determine that our MySQL data is located in /data mount point that in turn is on /dev/VolGroup01/LogVol01 logical volume. Very easy.

Now to the creating the actual snapshot of the logical volume
/dev/VolGroup01/LogVol01:

Since the data we are taking snapshot of is dynamic (that is true of all database systems), we need to place MySQL in read-only mode for the duration of time it take to create a snapshot in many cases less then a minute:

mysql> flush tables with read lock;

You must maintain the lock while the LVM snapshot is being taken. If you exit your mysql session the tables will be unlocked. There several ways to assure that the tables remain locked:

1. Stay in your mysql session open another terminal and run snapshot
2. Place mysql>  session in the backround using "CTRL+z"  (to get back to it use "fg" command"
3. Run mysql> session in screen

That will stop the updates to the MySQL tables so we can generate a "consistent" view of the data using LVM snapshot.

Since we know the name of the volume we are planning to use /dev/VolGroup01/LogVol01 we will use the LVM and create a snapshot as follows:

./sbin/lvm
lvm> lvcreate -L2000M -s -n snap /dev/VolGroup01/LogVol01
Logical volume "snap" created

Once snapshot is created be sure to unlock MySQL tables:

mysql> unlock tables;


Where is "snap" the name of our snapshot volume and "/dev/VolGroup01/LogVol01" is the name of the volume where MySQL data is located, "L2000M" is the size of the snapshot in megabytes.

Once the snapshot is create we can view it using lvm tool "lvdisplay":


--- Logical volume ---
LV Name /dev/VolGroup01/snap
VG Name VolGroup01
LV UUID ze9d5L-LQWa-bsxY-ubI2-eyYy-t3Uw-v5EbXI
LV Write Access read/write
LV snapshot status active destination for /dev/VolGroup01/LogVol01
LV Status available
# open 0
LV Size 772.91 GB
Current LE 197866

Once the snapshot volume is created it must be mounted to copy the data from it, in order to do that new snapshot volume must be mounted:

Create new mount destination:

/mysql_snap

>mkdir mysql_snap

Mount the new snapshot using the mount command:

mount /dev/VolGroup01/snap /mysql_snap


32G 1.2G 29G 4% /
/dev/sdc1 99M 15M 80M 16% /boot
none 7.9G 0 7.9G 0% /dev/shm
/dev/mapper/VolGroup01-LogVol01
761G 524G 207G 72% /data
/dev/mapper/VolGroup01-snap
761G 524G 207G 72% /mysql_snap

Now since the new volume is mounted we can copy the data anywhere:

Here is the example using rsync:

rsync -av --progress /mysql_snap/ aalexander@db15:/data

Once the copy is completed do not forget to remove the snapshot using the LVM command:

lvm> lvremove /dev/VolGroup01/snap