MySQL Snapshots in Linux using LVM

Say that you are working with some data that is constantly changing, like some MySQL databases, and you are charged to take a valid backup without any data inconsistency or downtime. You may consider using mysqldump; however, this utility will lock tables during the duration of the backup and can cause down time (especially if using MyISAM table engine which performs Table level locking). Then, you think you may just copy or archive the MySQL table files (/var/lib/mysql/DB), however, this can cause data inconsistent as during the copy some of the data may of changed. Not to mention, if it is InnoDB table engine all table data is stored in the ibdata files.

At this point you may be scratching your head not knowing what to do. You could stand up another server and setup MySQL replication. Unfortunately, you do not have any available server's or workstation to setup for this purpose. Fortunately, for you, this server will setup by default with LVM (Logical Volume Management) by your distribution. Using LVM, we are able to create a snapshot of an existing Logical Volume using free space on the Volume Group. Lets go ahead and see some of the syntax and examples below.

• Below we have our Logical Volume mounted on /var/lib/mysql.

  # lvdisplay /dev/mysql/lvol0
    --- Logical volume ---
    LV Name                /dev/mysql/lvol0
    VG Name                mysql
    LV UUID                BW3zO3-KVvQ-ABvt-4ylN-bw8t-ivvj-Vsp48P
    LV Write Access        read/write
    LV Status              available
    # open                 1
    LV Size                600.00 MB
    Current LE             150
    Segments               1
    Allocation             inherit
    Read ahead sectors     auto
    - currently set to     256
    Block device           253:2
  

• Now lets check the Volume Group this Logical Volume is apart of and see if there is any free space.

  # vgdisplay mysql
    --- Volume group ---
    VG Name               mysql
    System ID            
    Format                lvm2
    Metadata Areas        1
    Metadata Sequence No  2
    VG Access             read/write
    VG Status             resizable
    MAX LV                0
    Cur LV                1
    Open LV               1
    Max PV                0
    Cur PV                1
    Act PV                1
    VG Size               1020.00 MB
    PE Size               4.00 MB
    Total PE              255
    Alloc PE / Size       150 / 600.00 MB
    Free  PE / Size       105 / 420.00 MB
    VG UUID               PtaSHo-cdQ4-ofy1-ug6O-7q3z-OLS6-4oAyRt
  

• As we do have an available 420 MB or 105 PE (Physical extents) lets go ahead and create a snapshot, mount it and view its contents.

  # lvcreate --size 400MB --snapshot --name snap /dev/mysql/lvol0
    Logical volume "snap" created
  
  # mount /dev/mysql/snap /mnt/
  
  # ls -l /mnt
  total 20568
  -rw-rw---- 1 mysql mysql 10485760 Feb 24 09:19 ibdata1
  -rw-rw---- 1 mysql mysql  5242880 Feb 24 09:25 ib_logfile0
  -rw-rw---- 1 mysql mysql  5242880 Feb 24 09:18 ib_logfile1
  drwx------ 2 mysql mysql    16384 Feb 24 09:14 lost+found
  drwx------ 2 mysql mysql     4096 Feb 24 09:18 mysql
  srwxrwxrwx 1 mysql mysql        0 Feb 24 09:25 mysql.sock
  drwx------ 2 mysql mysql     4096 Feb 24 09:18 test
  

• Now we have a snapshot of our MySQL data which will not change even though the live data mounted at /var/lib/mysql is contently changing. Using this snapshot we can safely copy the contents and if need be use for a restore in the future.