Choosing the right backup solution is most important for any database. People always choose a backup solution that has the below qualities
a) Minimal Downtime
b) Consistent data
c) Less restoration time
There are several backup solutions like mysqldump, mysqlhottcopy, Xtrabackup, raw copy using cp/rsync/scp (and of course MySQL Enterprise backup. we are not going to talk about it here) etc.., I always prefer mysqldump when the data size is small(few GBs) even if it locks the tables. But what if the data size is huge in TB's, you cannot bring down the DB for a whole day. No client is comfortable with this, they need minimal or no downtime. Percona's Xtrabackup actually comes in handy in such situations. Xtrabackup locks the tables only when dumping the meta data.
But what we are really interested here is LVM snapshot. With LVM snapshots we could actually take backups with zero downtime.
a) Disk should be partitioned with LVM
b) All contents of the data directory should reside in the same LVM parititon. No symbolic links
c) There should be sufficient Free Extents(un-partitioned space) available in the same Volume group of the partition containing MySQL data directory. Practically you can create a LVM snapshot with a minimum of 32M. But if your database is a busy system with too many changes the snapshot would get corrupted. So it is always better to keep extra space in the volume group
What are LVM snapshots?
Logical volumes store data in two essential parts
a) metadata pointers ( similar to metadata in MySQL)
b) data block(actual data)
When you create a snapshot of a LVM , it just creates metadata pointers and these metadata pointers occupy very less space . The data block is the actual data. I was able to keep 600 GB data in 32 MB LVM snapshot. Whenever changes are made in the original partition the data block has to be copied to the snapshot and this occupies. So snapshot gets corrupted when these data cannot be occupied ins the space assigne for the snapshot size. So I would always recommend to keep at-least 20% of the space occupied by the data directory to the snapshot.
Now type "vgdispay" list out the available volume groups
vgdisplay --- Volume group --- VG Name vg_www System ID Format lvm2 Metadata Areas 2 Metadata Sequence No 22 VG Access read/write VG Status resizable MAX LV 0 Cur LV 6 Open LV 6 Max PV 0 Cur PV 2 Act PV 2 VG Size 1.09 TiB PE Size 4.00 MiB Total PE 285944 Alloc PE / Size 285652 / 1.09 TiB Free PE / Size 292 / 1.14 GiB VG UUID 37jef7-3q3E-FyZS-lMPG-5Jzi-djdO-Bgopa
I have about 1.14GB free which is enough for me to demonstrate.
Now type lvdisplay to list down all the lvm partitions
lvdisplay --- Logical volume --- LV Path /dev/volume_1/vol LV Name lv_data VG Name vg_www LV UUID ECue1P-lZGa-qeAf-yD6l-eGqH-BtXG-ot5oXr LV Write Access read/write LV Creation host, time 2014-07-18 10:49:57 LV Status available # open 1 LV Size 500.00 GiB Current LE 128000 Segments 1 Allocation inherit Read ahead sectors auto - currently set to 256 Block device 253:4
"/dev/vg_www/lv_data" is my logical volume that has the MySQL data directory.
Now we are ready to take the snapshot. It takes seconds to create snapshots
lvcreate -s -L 1G -n vol_snapshot /dev/volume_1/vol Logical volume "vol_snapshot" created
Now the snapshot is ready and lets create a mount point and mount the snapshot.
mkdir /mnt/vol mount /dev/volume_1/vol_snapshot /mnt/vol
Now you could copy the mysql data directory from the snapshot partiton "/mn/vol to a "NAS" or anywhere you want. This copy process never locks MySQL or hinders the original data directory. If you intend to create a new slave from this backup you could just "rsync" the files to the slave server, change the server id ,relaylog/binary log names and start the MySQL. Then reset the slave and configure the slave to run the exact position
LVM snapshots can also be used as a substitute for the original partition in case you loose the data in the original partition. This could be done easily with LVM commands like lvconvert and lvchange. Perhaps we would talk about this another time