Thursday, March 23, 2017

Creating MySQL backups using LVM

Using the mylvmbackup command you can easily create filesystem LVM snapshots of your MySQL database. The articles on Configuring a new hard drive for LVM and Using MySQL with LVM help with preparing your server to support LVM snapshots.

The latest version can be found at

These commands prepare the software.

sudo su -
mkdir -p /opt
cd opt
tar xvfz mylvmbackup-0.13.tar.gz
cd mylvmbackup-0.13/
make install

You will need to review and update the /etc/mylvmbackup.conf file, defining the correct MySQL permissions in the [mysqld] section and the right partition information in the [lvm] section. You can then run with:

$ mylvmbackup
Can't locate Config/ in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.10.1 /usr/local/share/perl/5.10.1 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.10 /usr/share/perl/5.10 /usr/local/lib/site_perl .) at /usr/local/bin/mylvmbackup line 20.
BEGIN failed--compilation aborted at /usr/local/bin/mylvmbackup line 20.

As you can see, mylvmbackup requires a number of perl dependencies including:

$ sudo apt-get install libsys-syslog-perl libconfig-inifiles-perl libdbi-perl libdbd-mysql-perl
# Run this if you are using a custom MySQL installation as per the referenced blog posts
$ sudo mv /etc/mysql /etc/mysql.deb

A successful execution will produce the following output.

$ sudo su -
$ mylvmbackup
20110902 17:11:24 Info: Connecting to database...
20110902 17:11:24 Info: Flushing tables with read lock...
20110902 17:11:24 Info: Taking position record into /tmp/mylvmbackup-backup-20110902_171124_mysql-RzDzAn.pos...
20110902 17:11:24 Info: Running: lvcreate -s --size=5G --name=p0_snapshot /dev/db/p0
File descriptor 4 (socket:[120367]) leaked on lvcreate invocation. Parent PID 7594: /usr/bin/perl
  Logical volume "p0_snapshot" created
20110902 17:11:31 Info: DONE: taking LVM snapshot
20110902 17:11:31 Info: Unlocking tables...
20110902 17:11:31 Info: Disconnecting from database...
20110902 17:11:31 Info: Mounting snapshot...
20110902 17:11:31 Info: Running: mount -o rw /dev/db/p0_snapshot /var/tmp/mylvmbackup/mnt/backup
20110902 17:11:31 Info: DONE: mount snapshot
20110902 17:11:31 Info: Copying /tmp/mylvmbackup-backup-20110902_171124_mysql-RzDzAn.pos to /var/tmp/mylvmbackup/mnt/backup-pos/backup-20110902_171124_mysql.pos...
20110902 17:11:31 Info: Copying /mysql/etc/my.cnf to /var/tmp/mylvmbackup/mnt/backup-pos/backup-20110902_171124_mysql_my.cnf...
20110902 17:11:31 Info: Taking actual backup...
20110902 17:11:31 Info: Creating tar archive /var/tmp/mylvmbackup/backup/backup-20110902_171124_mysql.tar.gz
20110902 17:11:31 Info: Running: cd '/var/tmp/mylvmbackup/mnt' ;'tar' cvf - backup/  backup-pos/backup-20110902_171124_mysql.pos backup-pos/backup-20110902_171124_mysql_my.cnf| gzip --stdout --verbose --best -> /var/tmp/mylvmbackup/backup/backup-20110902_171124_mysql.tar.gz.INCOMPLETE-BlfzQH
20110902 17:14:59 Info: DONE: create tar archive
20110902 17:14:59 Info: Cleaning up...
20110902 17:14:59 Info: Running: umount /var/tmp/mylvmbackup/mnt/backup
20110902 17:15:00 Info: DONE: Unmounting /var/tmp/mylvmbackup/mnt/backup
20110902 17:15:00 Info: LVM Usage stats:
20110902 17:15:00 Info:   LV          VG   Attr   LSize Origin Snap%  Move Log Copy%  Convert
20110902 17:15:00 Info:   p0_snapshot db   swi-a- 5.00g p0       0.01
20110902 17:15:00 Info: Running: lvremove -f /dev/db/p0_snapshot
  Logical volume "p0_snapshot" successfully removed
20110902 17:15:00 Info: DONE: Removing snapshot

$ ls -lh /var/tmp/mylvmbackup/backup/
total 153M
-rw-r--r-- 1 root root 153M 2011-09-02 17:14 backup-20110902_171124_mysql.tar.gz

mylvmbackup HOWTO: minimal privileges & filesystem copy provides a detailed explanation of the minimum permissions required.