How to backup MySQL database with zero downtime

By -

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.

Requirements

 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 








































Comments











Post a Comment






























Popular posts from this blog









Drop Table slow







By







-















    A few days back we were given a MySQL database hosted in EC2 with around 5TB data to move some of the tables to a new server.  The intention was to share the tables between 2 EC2 instances. Since AWS had the option to take online snapshots, the plan was to take a snapshot, create a new machine with that snapshot and drop the unwanted tables.   So everything went as planned until creating a new machine with the snapshot.  The real challenge was dropping the unwanted tables.    It took around 4 minutes to Drop a table whose size is 20GB.  It took 20 minutes to drop a 100GB table. The time kept on increasing for larger tables. MySQL even went to “defunct” when we killed the drop query and at times crashed.   To track down this issue we executed drop table in one session and checked the processlist from another session that gave the below output.     mysql> show processlist \G   *************************** 1. row ***************************      ...








Read more










How to install MyRocks into mariaDB as a plugin?







By







-















       Whats is MyRocks?  MyRocks is a storage engine that integrates RocksDB into MySQL with most features of InnoDB and the storage efficiency of RocksDB. It has been said that it provides 3-4x compression when compared to uncompressed InnoDB tables and 10x less write amplification when compared to InnoDB   What is RocksDB?   RocksDB is a embedded key-value data storage(a fork of LevelDB) which stores data in the disk unlike redis(redis stores data in memory). It uses Log-structured merge-tree algorithm that reduces write amplification.   https://en.wikipedia.org/wiki/Log-structured_merge-tree    Now let's come to the post topic.  We are going to compile MyRocks engine as a plugin into mariaDB.   Requirements  1) gcc 4.8.1 and above   MyRocks document says it could be installed in centos 6.8 too. But it's a bit tedious since a MyRocks requires a gcc 4.8 which is not available through default repositories in Centos 6.8 . So let's choose Centos 7 which has gcc 4.8.5  2) gfl...








Read more










Configuring multiple Galera cluster nodes in a single machine







By







-














Image







    What I love about Galera cluster is it's simplicity, SST/IST protocol and the native MySQL feel.   SST(state transfer protocol) is nothing but a mechanism used by Galera to transfer data between the nodes. Galera supports 3 types of SST’s                  1)       mysqldump                  2)       rsync                  3)       xtrabackup( Xtrabackup should be installed separately)   Xtrabackup is the most preferred one with less downtime since it locks only when the schema is fetched.     There are lots of superb features in Galera that we could talk about, but we shall skip that and concentrate on what the topic is about     Practically speaking I wouldn't recommend running multiple Galera nodes in a single machine. But you might run into a situation where you want to create a developme...








Read more