#! /usr/bin/perl -w

### ONLY FOR MyISAM TABLES! ###
### InnoDB requires an other solution
# This script prepares your MySQL server so that the databases can be snapshotted.

# you might need to adjust these 3 parameters
my $db_host='localhost';
my $db_user='root';
my $db_pass='password';
# dummy: according to http://dev.mysql.com/doc/refman/5.0/en/flush.html the
# lock command is for all tables on all databases
my $db_name='mysql';
# replace this by the script which invokes the snapshot on your netapp
my $snapshot_command='/usr/local/bin/netapp-create-snapshot';	# just an example

use DBI;

# connect to database
$dbh = DBI -> connect("dbi:mysql:database=$db_name:host=$db_host", $db_user, $db_pass, { RaiseError => 0, AutoCommit => 1 }) or die 'cannot connect to database';

# lock database
$db_lock = 'FLUSH TABLES WITH READ LOCK';
$db_lock_sth = $dbh -> prepare($db_lock) || die 'Couldn\'t prepare query: '.$dbh -> errstr;
$db_lock_sth -> execute() or die "Couldn't execute statement: " . $sth->errstr;

system("echo 3 > /proc/sys/vm/drop_caches");
system("sync");
system("sync");


# database is now ready to snapshot
system($snapshot_command);


system("echo 0 > /proc/sys/vm/drop_caches");

# and unlock database (altough stopping the script would work too as it
# closes the connection to the database which forces an implicit unlock)
$db_lock = 'UNLOCK TABLES';
$db_lock_sth = $dbh -> prepare($db_lock) || die 'Couldn\'t prepare query: '.$dbh -> errstr;
$db_lock_sth -> execute() or die "Couldn't execute statement: " . $sth->errstr;