Tuesday, 23 September 2014

How to backup MySQL database with zero downtime

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

--- 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

 --- 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