MySQL: Live Backup using LVM Snapshots

LVM snapshot is an exact copy of an LVM partition that has all the data from the LVM volume from the time the snapshot was created. The advantages of this is that we can get reliable backup in a small matter of time without suspending the MySQL service. Normal backup using mysqldump or mysqlhotcopy will create a logical backup, which usually expensive and CPU intensive.

The idea is like this:

1. Create a new logical volume in new hard disk
2. Mount the logical volume into MySQL data and log directory
3. Create LVM snapshot to the MySQL partition that hold MySQL data and log
4. Mount the LVM snapshot into the server
5. Create MySQL backup from that snapshot

I will use following variables:

OS: CentOS 6.2 64bit
MySQL: Percona 5.5.20
Old MySQL data & log directory: /var/lib/mysql
New MySQL data & log directory: /mysql
Backup MySQL partition: /mysql_snap

1. We will use another hard disk to mount /mysql via logical volume. Lets create the partition first:

$ fdisk /dev/sdb

Sequence pressed on keyboard: n > p > 1 > Enter > Enter > w

2.  You should see disk partition has been created as /dev/sdb1 as below:

$ fdisk -l /dev/sdb
 
Disk /dev/sdb: 11.8 GB, 11811160064 bytes
255 heads, 63 sectors/track, 1435 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xaa7ca5e3
 
Device Boot  Start      End        Blocks         Id     System
/dev/sdb1        1     1435      11526606         83     Linux

3.  Check the current physical volume, volume group and logical volume details:

$ pvs && vgs && lvs
  PV         VG       Fmt  Attr PSize  PFree
  /dev/sda2  VolGroup lvm2 a-   19.51g       0
  VG       #PV #LV #SN Attr   VSize  VFree
  VolGroup   2   2   0 wz--n- 30.50g  1022m
  LV       VG       Attr   LSize  Origin Snap%  Move Log Copy%  Convert
  lv_root  VolGroup -wi-ao 17.54g
  lv_swap  VolGroup -wi-ao  1.97g

You can see that this server has a volume group called VolGroup under /dev/sda2. Inside this volume group we have another 2 logical volume called lv_root and lv_swap.

4. What are we going to do now is to use /dev/sdb1 (our new hard disk) to extend VolGroup and create another logical volume for mysql called lv_mysql:

$ pvcreate /dev/sdb1
$ vgextend VolGroup /dev/sdb1

Now volume VolGroup should be extended and have 10G more. You can check VFree value by using this command:

$ vgs
  VG       #PV #LV #SN Attr   VSize  VFree
  VolGroup 2   2    0  wz--n- 30.50g 10.99g

5. We will use 5G for MySQL and the remaining VFree space will be dedicated to snapshot volume. Now lets create the mysql logical volume called lv_mysql:

$ lvcreate -L 5G -n lv_mysql VolGroup

6. When you run following command, you should see lv_mysql has been created under VolGroup volume:

$ lvs
  LV VG Attr LSize Origin Snap% Move Log Copy% Convert
  lv_mysql VolGroup -wi-a- 5.00g
  lv_root VolGroup -wi-ao 17.54g
  lv_swap VolGroup -wi-ao 1.97g

7.  Logical volume created. Lets format it with ext4 filesystem before we can mount them to /mysql directory:

$ mkfs.ext4 /dev/mapper/VolGroup-lv_mysql

8. Add following line into /etc/fstab and mount the partition:

/dev/mapper/VolGroup-lv_mysql           /mysql                  ext4    defaults        0 0

Mount the logical volume:

$ mount -a

9. Stop MySQL service and copy over the data to newly mounted logical volume. We will using rsync to copy to keep the permission, ownership and timestamp. Dont forget to change ownership for /mysql directory as well:

$ service mysql stop
$ rsync -avzP /var/lib/mysql/ /mysql/
$ chown mysql.mysql /mysql

10. Change following value in /etc/my.cnf to map the new directory:

datadir = /mysql
log_bin = 1

Start the Percona server:

$ service mysql start

11. MySQL should start and mapped to the new directory. We can use LVM snapshot from now onwards since MySQL already inside LVM partition. Now, we can start to create snapshot and I will dedicate 5 GB of space for this purpose:

$ lvcreate -L 5G --snapshot -n mysql_backup /dev/VolGroup/lv_mysql

12. Create snapshot is very fast. Once done, we can check the snapshot status as below:

$ lvs | grep mysql_backup
mysql_backup VolGroup swi-a-  5.00g lv_mysql  31.32

13. Now lets mount the snapshot partition so we can see the backup data:

$ mkdir /mysql_snap
$ mount /dev/mapper/VolGroup-mysql_backup /mysql_snap

Done. As for me, I will use NFS to mount this partition in another server and start backing up the data in another MySQL instance. The snapshot is just a process to capture the data at the moment of back up without compromising high CPU utilization and costly downtime.

Related Posts

• Install OpenFiler from USB Drive
• CentOS: Using XFS File System for MySQL
• Linux: Mount FTP as File System
• Create iSCSI Target in OpenFiler
• CentOS 5: Converting Ext3 to Ext4
• Increasing Disk Space in CentOS using LVM
• Create RAID1 for New Hard Disks via SSH/Online
• Install MySQL Cluster in Debian
• CentOS: Restore/Recover from Amanda Backup
• Customize and Disable PHPmyAdmin ‘Export’ Menu