# cat mysql_backup.sh
#!/usr/bin/perl -w

my $host = 'localhost';
my $dbname = 'mysql';
my $user = 'backup';
my $passwd = 'kV6qcZUbH0';
my $vg = 'vg';
my $lv = 'sql';
my $size = '1G';
my $backupdir = '/home/backup/mysql';

### SCRIPT ###

use POSIX;
use DBI;

my $snapshot = $lv . "_" . strftime("%Y%m%d_%H%M%S", localtime);
print "Creating snapshot $snapshot...\n";

$dbh = DBI->connect("dbi:mysql:$dbname;host=$host", $user, $passwd)
or die "Connection error: $DBI::errstr\n";

$dbh->do("SET SESSION AUTOCOMMIT=0");
$dbh->do("FLUSH TABLES WITH READ LOCK");

system("lvcreate -s -L$size -n$snapshot $vg/$lv");

$sth = $dbh->prepare("SHOW MASTER STATUS");
$sth->execute();
while ($row = $sth->fetchrow_hashref) {
 foreach $col (keys %{$row}) {
 print "$col: " . $$row{$col}."\n";
 }
}

$dbh->do("UNLOCK TABLES");

system("mkdir -p $backupdir/tmp");
system("mount /dev/mapper/$vg-$snapshot $backupdir/tmp");
system("tar czf $snapshot.tar.gz -C $backupdir/tmp .");
system("umount /dev/mapper/$vg-$snapshot");
system("rm -fr $backupdir/tmp");
system("lvremove -f $vg/$snapshot");

print "Done.\n";