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.
