MySQL Snapshots using LVM

 Posted on January 22, 2009  |  jgoulah

Introduction

LVM snapshots are a quick and easy way to take a backup of your MySQL server’s data files. The idea is to acquire a read lock on all tables, flush all server caches to disk, and create a snapshot of the volume containing the MySQL data directory, then unlock the tables again. This gives an atomic snapshot that takes only milliseconds. A mysql instance can then be launched to point at this data directory and you can perform a mysqldump to get data for setting up slaves, or you can take a completely destroyed DB and use the data directory to restore it to this point in time.

Setup the LVM

Figure out the partition you want to put the LVM on, in our case we’ll use /data2

$ df -h | grep data2
/dev/sda2             371G  195M  352G   1% /data2

We’ll need to run fdisk on it.  If you have data on here, save it to somewhere else!

$ sudo fdisk /dev/sda2
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 49932.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-49932, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-49932, default 49932):
Using default value 49932

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

You can review the change with the ‘p’ command:

Command (m for help): p

Disk /dev/sda2: 410.7 GB, 410704680960 bytes
255 heads, 63 sectors/track, 49932 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

     Device Boot      Start         End      Blocks   Id  System
/dev/sda2p1               1       49932   401078758+  8e  Linux LVM

You might need to reboot here, so go ahead and run sudo shutdown -r now

Now unmount the disk we want to create the lvm on and create a physical volume

$ sudo umount /dev/sda2
$ sudo pvcreate /dev/sda2
Physical volume "/dev/sda2" successfully created

You can see our new physical volume with pvdisplay

$ sudo pvdisplay
  "/dev/sda2" is a new physical volume of "382.50 GB"
  --- NEW Physical volume ---
  PV Name               /dev/sda2
  VG Name
  PV Size               382.50 GB
  Allocatable           NO
  PE Size (KByte)       0
  Total PE              0
  Free PE               0
  Allocated PE          0
  PV UUID               myxfgQ-alfA-y8sX-af4a-ifoA-3S29-YD3tuK

Now create a volume group on the physical volume, since we’re using this for mysql we’ll call it mysql

$ sudo vgcreate mysql /dev/sda2
  Volume group "mysql" successfully created

And you can view it

$ sudo vgdisplay
  --- Volume group ---
  VG Name               mysql
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  1
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                0
  Open LV               0
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               382.50 GB
  PE Size               4.00 MB
  Total PE              97919
  Alloc PE / Size       0 / 0
  Free  PE / Size       97919 / 382.50 GB
  VG UUID               0VZxkR-873N-E6Vj-H6SV-FCWO-3jHF-ykHRiX

Create a logical volume on the volume group, we’ll put it on our mysql volume group and call it maindb. We want it to be about half (or less) of the entire amount of space we have to make a backup, in this case thats about half of 382GB so we’ll go with 190:

$ sudo lvcreate --name maindb --size 190G mysql
  Logical volume "maindb" created

And you can view it:

$ sudo lvdisplay
  --- Logical volume ---
  LV Name                /dev/mysql/maindb
  VG Name                mysql
  LV UUID                4hlcMf-cs5a-xjYU-yxjE-Fx2f-Pngv-z0RAEH
  LV Write Access        read/write
  LV Status              available
  # open                 0
  LV Size                190.00 GB
  Current LE             48640
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:0

Now we have to put an actual filesystem on it:

$ sudo mkfs.ext3 /dev/mysql/maindb
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
24903680 inodes, 49807360 blocks
2490368 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
1520 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424, 20480000, 23887872

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 26 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

And now we can actually re mount it on the /data2 folder:

$ sudo mount /dev/mysql/maindb /data2

And add it to fstab, making sure to comment out the old entry

#LABEL=/data2            /data2                  ext3    defaults        1 2
/dev/mysql/maindb       /data2                  ext3       rw,noatime   0 0

Note that we do not have to create the other logical volume on the volume group that is used for the snapshot, as the lvmbackup tool will take care of it for us.

Move MySQL to the LVM Partition

Now we want to move our mysql installation into this folder:

$ sudo mkdir -p /data2/var/lib
$ sudo mv /var/lib/mysql/ /data2/var/lib/

We need to ensure the my.cnf has settings pointing to this new directory:

[mysqld]
datadir=/data2/var/lib/mysql
socket=/data2/var/lib/mysql/mysql.sock
log-bin = /data2/var/lib/mysql/bin.log

[mysql.server]
user=mysql
basedir=/data2/var/lib

[client]
socket=/data2/var/lib/mysql/mysql.sock

We can keep the log and pid outside of the LVM if we want:

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Now you can start mysqld:

sudo /usr/bin/mysqld_safe &

Install mylvmbackup

Grab the latest mylvmbackup utility:

$ wget http://www.lenzg.org/mylvmbackup/mylvmbackup-current.tar.gz

Extract and install, its just a script so there is no compilation necessary:

$ tar xzvf mylvmbackup-0.11.tar.gz
$ cd mylvmbackup-0.11
$ sudo make install

You’ll also need some perl modules:

*Config::IniFiles

*DBI

*DBD::mysql

*Sys::Syslog

*Date::Format

Now we need to setup the config file for mylvmbackup, located at /etc/mylvmbackup.conf

There are lots of options but at the very least we need to set:

[mysql]
user=root
password={your root pass here}

socket=/data2/var/lib/mysql/mysql.sock

[lvm]
vgname=mysql
lvname=maindb
lvsize=190G

[misc]
innodb_recover=1

[tools]
lvcreate=/usr/sbin/lvcreate
lvremove=/usr/sbin/lvremove
lvs=/usr/sbin/lvs

You may also want to change the backupdir parameter, as this is where the tarball of your database backup is stored,  so you’ll need the space for that.

You’ll want to make sure this backup directory exists, so if you are using the default:

$ sudo mkdir -p /var/tmp/mylvmbackup/backup

And also the mount directory (this is where the LVM snapshot is mounted so a mysqldump can be taken:

$ sudo mkdir -p /var/tmp/mylvmbackup/mnt

Create the Backup

Now you can simply run the mylvmbackup command as root.   Heres what the output looks like:

$ sudo mylvmbackup
20090122 13:26:47 Info: Connecting to database...
20090122 13:26:47 Info: Flushing tables with read lock...
20090122 13:26:47 Info: Taking position record...
20090122 13:26:47 Info: Running: /usr/sbin/lvcreate -s --size=190G 
--name=maindb_snapshot /dev/mysql/maindb
File descriptor 3 (socket:[101615]) leaked on lvcreate invocation.
 Parent PID 7209: /usr/bin/perl
  Logical volume "maindb_snapshot" created
20090122 13:26:47 Info: DONE: taking LVM snapshot
20090122 13:26:47 Info: Unlocking tables...
20090122 13:26:47 Info: Disconnecting from database...
20090122 13:26:47 Info: Mounting snapshot...
20090122 13:26:47 Info: Running: /bin/mount -o rw /dev/mysql/maindb_snapshot 
/var/tmp/mylvmbackup/mnt/backup
20090122 13:26:47 Info: DONE: mount snapshot
20090122 13:26:47 Info: Running: /bin/mount -o bind,ro 
/tmp/mylvmbackup-backup-20090122_132647_mysql-9zMEJA/pos 
/var/tmp/mylvmbackup/mnt/backup-pos
20090122 13:26:47 Info: DONE: bind-mount position directory
20090122 13:26:47 Info: Recovering InnoDB...
20090122 13:26:47 Info: Running: echo 'select 1;' | mysqld_safe 
--socket=/tmp/mylvmbackup.sock 
--pid-file=/var/tmp/mylvmbackup_recoverserver.pid 
--datadir=/var/tmp/mylvmbackup/mnt/backup 
--skip-networking 
--skip-grant --bootstrap --skip-ndbcluster --skip-slave-start
Starting mysqld daemon with databases from /var/tmp/mylvmbackup/mnt/backup
STOPPING server from pid file /var/tmp/mylvmbackup_recoverserver.pid
090122 13:26:47  mysqld ended
20090122 13:26:47 Info: DONE: InnoDB recovery on snapshot
20090122 13:26:47 Info: Copying my.cnf...
20090122 13:26:47 Info: Taking actual backup...
20090122 13:26:47 Info: Creating tar archive 
/var/tmp/mylvmbackup/backup/backup-20090122_132647_mysql.tar.gz
20090122 13:26:47 Info: Running: cd '/var/tmp/mylvmbackup/mnt' ;
'/bin/tar' cvf - backup/  
backup-pos/backup-20090122_132647_mysql.pos 
backup-pos/backup-20090122_132647_mysql_my.cnf
 /bin/gzip --stdout --verbose 
--best ->
 /var/tmp/mylvmbackup/backup/backup-20090122_132647_mysql.tar.gz.INCOMPLETE-uqO2w7
backup/
backup/test
backup/var/
backup/var/lib/
backup/var/lib/mysql/
backup/var/lib/mysql/bin.index
backup/var/lib/mysql/test/
backup/var/lib/mysql/ibdata1
backup/var/lib/mysql/ib_logfile1
/bin/tar: backup/var/lib/mysql/mysql.sock: socket ignored
backup/var/lib/mysql/mysql/
backup/var/lib/mysql/mysql/time_zone_transition.MYI
backup/var/lib/mysql/mysql/func.frm
backup/var/lib/mysql/mysql/procs_priv.MYD
backup/var/lib/mysql/mysql/db.MYI
backup/var/lib/mysql/mysql/proc.frm
backup/var/lib/mysql/mysql/proc.MYD
backup/var/lib/mysql/mysql/proc.MYI
backup/var/lib/mysql/mysql/time_zone_leap_second.MYI
backup/var/lib/mysql/mysql/help_category.MYI
backup/var/lib/mysql/mysql/tables_priv.frm
backup/var/lib/mysql/mysql/user.MYI
backup/var/lib/mysql/mysql/help_relation.MYD
backup/var/lib/mysql/mysql/columns_priv.frm
backup/var/lib/mysql/mysql/columns_priv.MYI
backup/var/lib/mysql/mysql/time_zone_name.frm
backup/var/lib/mysql/mysql/help_keyword.MYI
backup/var/lib/mysql/mysql/help_relation.MYI
backup/var/lib/mysql/mysql/host.frm
backup/var/lib/mysql/mysql/user.frm
backup/var/lib/mysql/mysql/procs_priv.MYI
backup/var/lib/mysql/mysql/help_relation.frm
backup/var/lib/mysql/mysql/time_zone_transition_type.MYD
backup/var/lib/mysql/mysql/help_keyword.frm
backup/var/lib/mysql/mysql/time_zone_transition_type.MYI
backup/var/lib/mysql/mysql/time_zone.MYD
backup/var/lib/mysql/mysql/tables_priv.MYD
backup/var/lib/mysql/mysql/host.MYI
backup/var/lib/mysql/mysql/help_category.frm
backup/var/lib/mysql/mysql/user.MYD
backup/var/lib/mysql/mysql/time_zone_leap_second.MYD
backup/var/lib/mysql/mysql/time_zone.MYI
backup/var/lib/mysql/mysql/time_zone_name.MYD
backup/var/lib/mysql/mysql/time_zone_transition.MYD
backup/var/lib/mysql/mysql/tables_priv.MYI
backup/var/lib/mysql/mysql/columns_priv.MYD
backup/var/lib/mysql/mysql/db.MYD
backup/var/lib/mysql/mysql/time_zone_transition_type.frm
backup/var/lib/mysql/mysql/help_topic.MYI
backup/var/lib/mysql/mysql/procs_priv.frm
backup/var/lib/mysql/mysql/time_zone_name.MYI
backup/var/lib/mysql/mysql/func.MYD
backup/var/lib/mysql/mysql/time_zone.frm
backup/var/lib/mysql/mysql/help_topic.frm
backup/var/lib/mysql/mysql/time_zone_transition.frm
backup/var/lib/mysql/mysql/func.MYI
backup/var/lib/mysql/mysql/help_keyword.MYD
backup/var/lib/mysql/mysql/help_category.MYD
backup/var/lib/mysql/mysql/db.frm
backup/var/lib/mysql/mysql/time_zone_leap_second.frm
backup/var/lib/mysql/mysql/help_topic.MYD
backup/var/lib/mysql/mysql/host.MYD
backup/var/lib/mysql/jgtest/
backup/var/lib/mysql/jgtest/test2.MYD
backup/var/lib/mysql/jgtest/test2.frm
backup/var/lib/mysql/jgtest/db.opt
backup/var/lib/mysql/jgtest/test2.MYI
backup/var/lib/mysql/jgtest/test.frm
backup/var/lib/mysql/bin.000001
backup/var/lib/mysql/ib_logfile0
backup/lost+found/
backup-pos/backup-20090122_132647_mysql.pos
backup-pos/backup-20090122_132647_mysql_my.cnf
 99.2%
20090122 13:26:48 Info: DONE: create tar archive
20090122 13:26:48 Info: Cleaning up...
20090122 13:26:48 Info: LVM Usage stats:
20090122 13:26:48 Info:LV VG Attr LSize Origin Snap% Move Log Copy% Convert
20090122 13:26:48 Info:   maindb_snapshot mysql swi-a- 190.00G maindb   0.00
  Logical volume "maindb_snapshot" successfully removed

And now you should have a tarball in your backup directory, with all the files needed to restore:

$ ls /var/tmp/mylvmbackup/backup
backup-20090122_132647_mysql.tar.gz
MySQL backup LVM snapshot