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

my $host = 'localhost';
my $dbname = 'mysql';
my $user = 'backup';
my $passwd = 'WD8Y2YOraM';
my $pool = 'rpool/sql';
my $backupdir = '/home/backup/mysql';

### SCRIPT ###

use POSIX;
use DBI;

my $snapshot = 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("zfs snapshot -r $pool\@$snapshot");
$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("zfs send -vR $pool\@$snapshot | gzip > $backup_dir/$snapshot.gz");
system("zfs destroy -r $pool\@$snapshot");

print "Done.\n"