Wednesday, May 24, 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 https://launchpad.net/mylvmbackup

These commands prepare the software.

sudo su -
mkdir -p /opt
cd opt
wget http://launchpad.net/mylvmbackup/trunk/0.13/+download/mylvmbackup-0.13.tar.gz
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/IniFiles.pm 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
...
backup-pos/backup-20110902_171124_mysql.pos
backup-pos/backup-20110902_171124_mysql_my.cnf
 72.4%
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.