mysql replication manager

# Replicate the manager 
# http://matt.simerson.net/computing/sql/mrm/mysql_replicate_manager.pl

#!/usr/bin/perl -w
use strict;

=head1 NAME

	mysql_replicate_manager.pl - Mysql Replication Manager

=head1 SYNOPSIS

	A handy tool to help manage replicated MySQL servers. 

=head1 DESCRIPTION

	Matt Simerson, Unix Systems Engineer. I have clusters of
	replicated MySQL clusters that I manage. Some are geographically
	redundant, some are used strictly for load balancing. This
	script is VERY useful for me and my co-workers.

	In order to use this program, you must have MATT::Bundle installed.
	See http://matt.simerson.net/computing/perl/

=cut

#######################################################################
#           Don't muck with anything below this line                  #
#######################################################################

use MATT::Perl;
use MATT::Mysql;
use MATT::Utility;

LoadModule("DBI",          "p5-DBI",      "databases" );
LoadModule("DBD::mysql",   "p5-Mysql",    "databases");
LoadModule("File::Spec");

use vars qw/ $opt_a $opt_b $opt_c $opt_d $opt_f $opt_h $opt_i 
             $opt_l $opt_p $opt_q $opt_s $opt_v $opt_x $version/;
use Getopt::Std;
getopts('abcd:fhilpqs:u:vx');

$version = "1.5.1";

$|++;
my $debug = 1; $debug = 0                      if ( $opt_q );

&print_header if $debug;
if (!$opt_a && !$opt_b && !$opt_c && !$opt_f && !$opt_h && !$opt_i 
	&& !$opt_l && !$opt_p && !$opt_q && !$opt_s && !$opt_v && !$opt_x ) 
{
	&mysql_print_usage;
	exit 0;
};

my $sudo                     = &sudo_setup;
my $dot                      = ParseDotFile(".my.cnf", "[mysql_replicate_manager]");
my $ac                       = SetAutocommit($dot);
&mysql_backup                 ($opt_d)          if ( $opt_b );
my $db_mv                    = MysqlDBVars         ( $dot   );
my $db_sv                    = &mysql_slave_db_vars( $dot   );
my ($dbh_m, $dsn_m, $drh_m)  = MysqlConnect        ( $db_mv );

&mysql_get_vars               ($dbh_m);
&mysql_show_master_logs       ($dbh_m)          if ( $opt_l );
&mysql_purge_master           ($dbh_m)          if ( $opt_p );
&mysql_purge_master_force     ($dbh_m)          if ( $opt_f );
&mysql_archive                ($dbh_m)          if ( $opt_a );
&mysql_copy_tarball           ()                if ( $opt_c );
&mysql_extract_archive        ()                if ( $opt_x );
if ( $opt_i ) {
	my $ver_m = &mysql_master_info ($dbh_m, $drh_m);
	&mysql_slave_info          ($db_sv, $ver_m);
};
&mysql_halt_master            ($dbh_m, $drh_m)  if ( $opt_h );
&mysql_shutdown               ($db_mv, $drh_m)  if ( $opt_s && $opt_s =~ /^m/ );
&mysql_shutdown_slaves        ()                if ( $opt_s && $opt_s =~ /^s/ );

$dbh_m->disconnect;
print "\n All done.\n\n" if $debug;

exit 1;

##
# Subroutines
## 
# ----------------------------------------------------------------------

sub print_header() {
	print <<EOHEADER
	
    ****    ****    MySQL Replication Manager $version    ****     ****
                           by Matt Simerson
                           
EOHEADER
;
};

sub mysql_print_usage() {
	print<<EOUSAGE;

    usage: $0 [-u <root>] [-p <pass>]

        -a    Archive the mysql databases
        -b    Back up the mysql databases [-d backupdir]
        -c    Copy archive from master to slaves
        -h    Halt MySQL master, sync up slaves, and shut down master
        -i    Informative display of replication status
        -l    show master Logs
        -f    Force purge master bin-logs (this can break your replication)
        -p    Purge master bin-logs (w/o breaking slaves)
        -s    Shutdown <master/slaves>
        -x    eXtract archive on slave(s)

    Optional
        -q    Quiet    (no debugging and status information)
        -v    Verbose

EOUSAGE
	return;
};

sub mysql_halt_master($$$) {
	my ($dbh_m, $drh_m) = @_;

	my $ver    = mysql_version($dbh_m);
	my $newer  = is_mysql_newer("3.23.28", $ver);

	my $query  = "FLUSH LOGS";
	my $sth_m  = MysqlQuery($dbh_m, $query);
	sleep 5;
	$sth_m     = mysql_lock_tables($dbh_m);
	my $status = mysql_fetch_master_status($dbh_m);	

	if ($newer) {
		mysql_sync_slaves  ( $db_sv, $status ); 
		mysql_purge_master_old_logs($dbh_m);	
	} else {
		sleep 5;
		mysql_sync_slaves  ( $db_sv, $status ); 
		mysql_reset_master ( $dbh_m, $ver);
		mysql_reset_slaves ( $db_sv, $ver );
	};

	print "halt_master: halting master \n" if $debug;
	mysql_shutdown($db_mv, $drh_m);
};

sub is_mysql_newer($$) {
	my ($min, $cur) = @_;

	$min =~ /^([0-9]+)\.([0-9]{1,})\.([0-9]{1,})$/;
	my @mins = ( $1, $2, $3 );
	$cur =~ /^([0-9]+)\.([0-9]{1,})\.([0-9]{1,})$/;
	my @curs = ( $1, $2, $3 );

	if ( $curs[0] > $mins[0] ) { return 1; };
	if ( $curs[1] > $mins[1] ) { return 1; };
	if ( $curs[2] > $mins[2] ) { return 1; };

	return 0;
};

sub mysql_reset_master($$) {
	my ($dbh_m, $ver) = @_;
	my $query;

	my $newer = is_mysql_newer("3.23.25", $ver);
	if ($newer) { $query = "RESET MASTER"; } else { $query = "FLUSH MASTER"; };

	print "reset_master: executing $query to purge binlogs.\n" if $debug;

	my $sth_m = MysqlQuery($dbh_m, $query);
	$sth_m->finish;
};

sub mysql_sync_slaves($$) {
	my ($db_sv, $status) = @_;

	foreach my $slave ( @$db_sv ) {
		print "sync_slaves: waiting for slave $slave->{'host'}..." if $debug;

		my ($dbh_s, $dsn_s, $drh_s) = MysqlConnect($slave);
		my $query = "SELECT MASTER_POS_WAIT('$status->[0]', $status->[1])";
		my $sth_s = MysqlQuery( $dbh_s, $query);
		$sth_s->finish;
		$dbh_s->disconnect;

		print "done.\n" if $debug;
	};
};

sub mysql_reset_slaves($$) {
	my ($db_sv, $ver) = @_;
	my $query;

	my $newer = is_mysql_newer("3.23.25", $ver);
	if ($newer) { $query = "RESET SLAVE"; } else { $query = "FLUSH SLAVE"; };

	foreach my $slave ( @$db_sv ) {
		print "reset_slave: resetting the slave $slave->{'host'}..." if $debug;

		my ($dbh_s, $dsn_s, $drh_s) = MysqlConnect($slave);
		my $sth_s = MysqlQuery($dbh_s, $query);
		$sth_s->finish;

		print "done.\n" if $debug;
	};
};

sub mysql_archive($$) {
	my ($dbh)  = @_;

	my $datadir = $db_mv->{'datadir'};
	if ($datadir =~ /\/$/ ) {
		chop $datadir;
	};

	my $backupdir = check_mysql_backupdir($opt_d);

	if (!$sudo) {
		if ( ! -r $datadir ) {
			print "archive: FAILED. No read permissions on $datadir.\n";
			return 0;
		};
	};

	my $sth    = mysql_lock_tables($dbh);
	my $master = &mysql_fetch_master_status($dbh);
	#mysql_create_tarball($master, $db_mv->{'dir'});
	mysql_create_tarball($master, $datadir, $backupdir);
	mysql_unlock_tables($dbh, $sth);
};

sub check_mysql_backupdir
{
	my ($dir) = @_;
	my $backupdir;

	if ( $opt_d ) {
		$backupdir = $opt_d;
	} elsif ( $dot->{'backupdir'} ) {
		$backupdir = $dot->{'backupdir'};
	} else {
		print "archive: No backup directory! Add backupdir to my.cnf\n";
		return 0;
	};
	
	if (! $sudo) {
		if ( !-w $backupdir ) {
			print "archive: FAILED. No write on $backupdir.\n";
			return 0;
		};
	};
};

sub mysql_create_tarball($$$) {
	my ($master, $datadir, $backupdir) = @_;

	my ($dd, $mm, $yy) = GetTheDate("",$debug);
	my ($up1dir,$data) = StripLastDirFromPath( $datadir );
	my $tarball        = "$backupdir/mysql-$yy-$mm-$dd.tar";
	my $txt            = "$backupdir/mysql-$yy-$mm-$dd.txt";
	my $gzip           = FindTheBin("gzip");

	if ( -e "$tarball.gz" ) {
		print "create_tarball: skipping, $tarball exists already!\n";
	} else {
		print "creating tarball $tarball..." if $debug;
		if ( chdir($up1dir) ) {     # User might not have execute on $up1dir
			system "$sudo tar -cf $tarball $data"; 
			system "$sudo $gzip $tarball"; 
		} else {
			print "NOTICE: Your tarball will have full paths because you don't";
			print "have execute permission on $up1dir. You'll have to manually";
			print "adjust the location of the files in $datadir.\n";
			system "$sudo tar -cf $tarball $datadir";   # full paths in tarball :(
			system "$sudo $gzip $tarball";
		};
		print "done.\n" if $debug;
		print "create_tarball: writing master position to file\n" if $debug;
		system "echo \"$master->[0]:$master->[1]\" > /tmp/foo";
		system "$sudo mv /tmp/foo $txt";
	};
};

sub mysql_copy_tarball($$)
{
	my ($dd, $mm, $yy)  = GetTheDate();
	my ($up1dir, $data) = StripLastDirFromPath( $db_mv->{'dir'} );
	my $scp             = FindTheBin("scp");
	my $tarball         = "$up1dir/mysql-$yy-$mm-$dd.tar.gz";
	my $txt             = "$up1dir/mysql-$yy-$mm-$dd.txt";

	if ( -r $tarball || -r $txt ) {
		foreach my $slave ( @$db_sv ) {
			print "copy_tarball: copying to slave systems" if $debug;
			my $up1dir = &StripLastDirFromPath( $slave->{'dir'} );
			system "$sudo $scp $tarball $slave->{'host'}:$up1dir";
			system "$sudo $scp $txt $slave->{'host'}:$up1dir";
		};
	} else {
		print "copy_tarball: WARNING, either $tarball or $txt does not exist or is";
		print " not readable. That must be fixed before the copy can succeed.\n";
	};
};

sub mysql_extract_archive($) {

	my ($d, $m, $y) = &GetTheDate;
	my $ssh         = FindTheBin("ssh");
	my $tarball     = "mysql-$y-$m-$d.tar.gz";
	my $warn = 1;

	&mysql_shutdown_slaves($warn);
	foreach my $slave ( @$db_sv ) {
		my ($up1dir, $data) = &StripLastDirFromPath( $slave->{'dir'} );
		my $rcmd = "cd $up1dir; rm -rf $data/*; gunzip \"$tarball.gz\"; tar -xzf $tarball";
		#my $rcmd = "cd $up1dir; rm -rf $data/*; tar -xzf $tarball";
		print "executing $rcmd";
		system "$ssh $slave->{'host'} \"$rcmd\"";
	};
};

sub mysql_shutdown_slaves(;$) {
	my ($warn) = @_;

	foreach my $slave ( @$db_sv ) {
		print "shutdown_slave: connecting to $slave->{'host'}\n" if $debug;

		my ($dbh_s, $dsn_s, $drh_s) = MysqlConnect($slave, $warn);

		print "shutdown: shutting down server $slave->{'host'}..." if $debug;
		my $rc = $drh_s->func('shutdown', $slave->{'host'}, $slave->{'user'}, 
		   $slave->{'pass'}, 'admin');

		if ($debug) {
			if ( $rc ) { print "success.\n" } else { print "failed.\n" }
		};
	};
};

sub mysql_fetch_master_status($) {
	my ($dbh) = @_;

	my $query = "SHOW MASTER STATUS";
	if (my $sth   = MysqlQuery($dbh, $query)) {
		my $r     = $sth->fetchrow_arrayref;
		print "master_info->status:     $r->[0], $r->[1]\n";
		$sth->finish;

		return $r;
	};
};

sub mysql_binlog_enabled {
	my ($db_mv) = @_;
	if ( $db_mv->{log_bin} ne "ON" ) {
		print <<EOBINLOG;

Hey there! In order for this server to act as a master, binary logging
must be enabled! Please edit /etc/my.cnf or $db_mv->{datadir}/my.cnf and
add "log-bin". You must also set server-id as documented at mysql.com.

EOBINLOG
;
		return 0;
	};
	return 1;
};

sub mysql_master_db_vars($) {
	my ($val) = @_;
	my ($driver, $db, $host, $port, $user, $pass, $dir);

	if ( $val->{'driver'} && $val->{'driver'} eq "" ) { 
		$driver= "mysql"     } else { $driver= $val->{'driver'}
	};
	if ( $val->{'db'}     && $val->{'db'}     eq "" ) { 
		$db    = "mysql"     } else { $db    = $val->{'db'}    
	};
	if ( $val->{'host'}   && $val->{'host'}   eq "" ) { 
		$host  = "localhost" } else { $host  = $val->{'host'}  
	};
	if ( $val->{'port'}   && $val->{'port'}   eq "" ) { 
		$port  = "3306"      } else { $port  = $val->{'port'}  
	};
	if ( $val->{'user'}   && $val->{'user'}   eq "" ) { 
		$user  = "root"      } else { $user  = $val->{'user'}  
	};
	if ( $val->{'pass'}   && $val->{'pass'}   eq "" ) { 
		$pass  = ""          } else { $pass  = $val->{'pass'}  
	};
	if ( $val->{'dir_m'}  && $val->{'dir_m'}  eq "" ) { 
		$dir ="/var/db/mysql"} else { $dir   = $val->{'dir_m'} 
	};

	my %master = ( driver  => $driver,     db      => $db,      host    => $host,
                  port    => $port,       user    => $user,    pass    => $pass,
                  dir     => $dir );
	return \%master;
};

sub mysql_slave_db_vars($) {
	my ($val) = @_;
	my ($driver, $db, $host, $port, $user, $pass, $dir);
	my @array;

	if ( $val->{'driver'} && $val->{'driver'} eq "" ) { 
		$driver= "mysql"    } else { $driver= $val->{'driver'}
	};
	if ( $val->{'db'}     && $val->{'db'}     eq "" ) { 
		$db    = "mysql"    } else { $db    = $val->{'db'}    
	};
	if ( $val->{'port'}   && $val->{'port'}   eq "" ) { 
		$port  = "3306"     } else { $port  = $val->{'port'}  
	};
	if ( $val->{'user'}   && $val->{'user'}   eq "" ) { 
		$user  = "root"     } else { $user  = $val->{'user'}  
	};
	if ( $val->{'pass'}   && $val->{'pass'}   eq "" ) { 
		$pass  = ""         } else { $pass  = $val->{'pass'}  
	};
	if ( $val->{'dir_s'}  && $val->{'dir_s'}  eq "" ) { 
		$dir="/var/db/mysql"} else { $dir   = $val->{'dir_s'} 
	};

	if ( $val->{'slaves'} ne "" ) {
		my @hosts = split(/ /, $val->{'slaves'});
		foreach my $host (@hosts) {
			my %slave  = ( driver  => $driver,     db      => $db,      host    => $host,
                        port    => $port,       user    => $user,    pass    => $pass,
                        dir     => $dir  );
			push @array, \%slave;
		};
	};
	return \@array;
};

sub mysql_backup($) {
	my ($dir) = @_;
	my ($backupfile);

	my $backupdir  = check_mysql_backupdir($dir);
	my $gzip       = FindTheBin("gzip");
	my $cronolog   = FindTheBin("cronolog");
	my $mysqldump  = FindTheBin("mysqldump");

	if (! $cronolog or ! $gzip ) {
		die "You must have cronolog and gzip installed!\n";
	};

	my $mysqlopts = "--all-databases --opt --password=$dot->{'pass'}";
	my ($dd, $mm, $yy) = &GetTheDate;

	if ( $dir ) {
		$backupdir = $dir;
	} else {
		if ( $dot->{'backupdir'} ) {
			$backupdir = $dot->{'backupdir'};
		} else {
			$backupdir = "/var/backups/mysql";
		};
	};

	if ( $dot->{'backupfile'} ) {
		$backupfile = $dot->{'backupfile'};
	} else {
		$backupfile = "mysql_full_dump";
	};

	if ( !-e "$backupdir/$yy/$mm/$dd/$backupfile" &&
		!-e "$backupdir/$yy/$mm/$dd/$backupfile.gz" ) {
		system "$mysqldump $mysqlopts | $cronolog $backupdir/%Y/%m/%d/$backupfile";
		print "backup: running $gzip $backupdir/$yy/$mm/$dd/$backupfile\n" if $debug;
		system "$gzip $backupdir/$yy/$mm/$dd/$backupfile";
	} else { 
		print "Yikes! Backup for today is already done!\n"; 
	};
};

sub mysql_get_hashes($$) {
	my ($dbh, $sql) = @_;
	my @records;

	if (my $sth = MysqlQuery($dbh, $sql)) {
		while (my $ref = $sth->fetchrow_hashref) {
			push @records, $ref;
		}
		$sth->finish;
	}
	return @records;
};

sub mysql_get_vars($) {
	my ($dbh) = @_;

	#my (%vars, %status);
	my @rows = mysql_get_hashes($dbh, "SHOW VARIABLES");

	print "mysql_get_vars-> reading variables.\n" if $opt_v;
	foreach my $row (@rows) {
		my $name  = $row->{Variable_name};
		my $value = $row->{Value};

		%$db_mv->{$name} = $value;
		#$vars{$name} = $value;
	}

	print "mysql_get_vars-> reading extended status.\n" if $opt_v;

	@rows = mysql_get_hashes($dbh, "SHOW STATUS");
	foreach my $row (@rows) {
		my $name  = $row->{Variable_name};
		my $value = $row->{Value};

		%$db_mv->{$name} = $value;
		#$status{$name} = $value;
	}

	#return \%vars, \%status;
};


##
# MySQL Reporting
##
# ----------------------------------------------------------------------

sub mysql_master_info($$) {
	my ($dbh, $drh) = @_;
	my (%val_m, $db_name, $status);

	$val_m{'ver'} = &mysql_version($dbh);
	print "master_info->version:    $val_m{'ver'}\n";

	my $binlog = mysql_binlog_enabled($db_mv);

	if ($binlog) {
		$status = &mysql_fetch_master_status($dbh);	
		$val_m{'log'} = $status->[0];
		$val_m{'pos'} = $status->[1];
	};

	print "data dir: $db_mv->{datadir}\n" if ($db_mv->{datadir});
	print "conf dir: $db_mv->{dir}\n"     if ($db_mv->{dir});
	
	if ( $opt_v ) {
		print "master_info->databases:  ";
		mysql_list_databases($dbh);
		
		my @tables = $dbh->tables();        
		print "master_info->tables:     @tables\n";
		
		my $threadId = $dbh->{'thread_id'}; 
		print "master_info->threadId:   $threadId\n";

		#foreach my $var (keys %$db_mv) {
		#	if ( $db_mv->{$var} ) {
		#		printf "%25s: %s\n", $var, $db_mv->{$var};
		#	};
		#};

		#print "master_info->extended:   \n";
		#mysql_show_status($dbh);
		
		#my $infoString = $dbh->{'info'};    
		#print "master_info->infostring: $infoString\n";
		
		#my $insertId = $dbh->{'mysql_insertid'}; 
		#print "master_info->insertID: $insertId\n";
	};
	print "\n";

	return \%val_m;
};

sub mysql_slave_info($$) {
	my ($db_sv, $ver_m) = @_;

	foreach my $slave ( @$db_sv ) {

		print "slave_info:   connecting to $slave->{'host'}\n" if ($opt_v);

		my ($dbh_s, $dsn_s, $drh_s) = &MysqlConnect($slave);
		my $ver = &mysql_version($dbh_s);

		print "slave_info->version:     $ver\n";

		if ( $ver ne $ver_m->{'ver'} ) {
			print "\n\tWARNING: The MySQL Server version on this slave ($ver) is not\n";
			print "\tthe same version as the master($ver_m->{'ver'}). Although\n";
			print "\tthis might work fine, there are known problems with replication\n";
			print "\tand varying versions of MySQL. I recommend updating all servers\n";
			print "\tto the same version! \n\n";
		};

		my $query = "SHOW SLAVE STATUS";
		my $sth_s = MysqlQuery($dbh_s, $query);

		print "slave_info->status:      ";

		my ($host, $user, $port, $retry, $file, $pos, $run) = $sth_s->fetchrow_array;

		print "$file, $pos, $run, $slave->{'host'}\n";
		print "\t WARNING: Slave not in sync! \n" if ( $pos ne $ver_m->{'pos'} );
		print "\t WARNING: Slave not running! \n" if ( $run ne "Yes" );

		if ($opt_v) {
			print "slave_info->databases:    ";
			&mysql_list_databases($dbh_s);

			#print "slave_info->extended:     \n";     
			#&mysql_show_status($dbh_s);
			
			my @tables = $dbh_m->tables();
			print "slave_info->tables:       @tables \n";
		};
		print "\n";
		$sth_s->finish;
		$dbh_s->disconnect;
	};
};

sub mysql_show_status($) {
	my ($dbh) = @_;

	if (my $sth = MysqlQuery($dbh, "SHOW STATUS")) {
		while ( my $r = $sth->fetchrow_arrayref ) {
			print "\t\t\t  $r->[0] \t $r->[1]\n";
		};
		$sth->finish;
	};
};


##
# MySQL Logging
##
# ----------------------------------------------------------------------

sub mysql_get_master_logs($) {
	my ($dbh_m) = @_;
	my @vars;

	my $query = "SHOW MASTER LOGS";
	my $sth_m = MysqlQuery($dbh_m, $query);

	if (!$ac) { 
		$dbh_m->commit or die "couldn't commit: $sth_m->errstr\n";
	};

	while ( my $r = $sth_m->fetchrow_arrayref ) {
		print "\t$r->[0]\t$r->[1]\n" if $opt_v;
		chomp $r->[0];
		if ( ! $sth_m->err) {
			push @vars, $r->[0];
		} else {
			print "WARNING: $sth_m->errstr\n";
		};
	};
	$sth_m->finish;
	return \@vars;
};

sub mysql_show_master_logs($) {
	my ($dbh_m) = @_;

	my $logs = mysql_get_master_logs($dbh_m);
	print "show_master_logs: (SHOW MASTER LOGS)\n\n" if ($debug);

	foreach my $log ( @$logs ) {
		print "\t$log\n";
	};
};

sub mysql_purge_master_old_logs($) {
	my ($dbh_m) = @_;
	my ($last, $sec);

	my $logs = mysql_get_master_logs($dbh_m);
	foreach my $l ( @$logs ) {
		if ($l ne "") { 
			$sec = $last;
			$last = $l; 
		};
		print "last: $last\tsecond: $sec\n" if $debug;
	};

	my $query = "PURGE MASTER LOGS TO '$sec'";
	my $sth = MysqlQuery($dbh_m, $query);
	if (!$ac) { 
		$dbh_m->commit or die "couldn't commit: $sth->errstr\n";
	};
};

sub mysql_purge_master($$) {
	my ($dbh_m) = @_;

	my $ver    = mysql_version($dbh_m);
	my $newer  = is_mysql_newer("3.23.28", $ver);

	my $query  = "FLUSH LOGS";
	my $sth_m  = MysqlQuery($dbh_m, $query);
	sleep 5;
	$sth_m     = mysql_lock_tables($dbh_m);
	my $status = mysql_fetch_master_status($dbh_m);	

	if ($newer) {
		mysql_sync_slaves  ( $db_sv, $status ); 
		mysql_purge_master_old_logs($dbh_m);	
	} else {
		sleep 5;
		mysql_sync_slaves  ( $db_sv, $status ); 
		mysql_reset_master ( $dbh_m, $ver);
		mysql_reset_slaves ( $db_sv, $ver );
	};

	print "purge_master: unlocking master \n" if $debug;
	mysql_unlock_tables($dbh_m, $sth_m);
};

sub mysql_purge_master_force($) {
	my ($dbh_m) = @_;

	print "WARNING! Proceeding will break any existing replication! Are you sure? ";
	my $ans = YesOrNo();
	if ( $ans ) {
		my $ver   = mysql_version($dbh_m);
		my $sth_m = mysql_lock_tables($dbh_m);

		mysql_reset_master ( $dbh_m, $ver);
		my $status = mysql_fetch_master_status($dbh_m);	

		print "\n\n WARNING! You just broke your replication! To restore it, you'll\n";
		print " need to restore a snapshot of your database on each slave and then\n";
		print " execute something like this command on each slave server:\n\n";
		print " CHANGE MASTER TO MASTER_HOST='$db_mv->{'host'}', MASTER_USER";
		print "='$db_mv->{'user'}', MASTER_PASSWORD='$db_mv->{'pass'}', ";
		print "MASTER_LOG_FILE='$status->[0]', MASTER_LOG_POS=$status->[1]\n";

		mysql_unlock_master($dbh_m, $sth_m);
	} else {
		print "\npurge_master_force: Cancelled! Good choice.\n\n";
	};
};

##
# General MySQL Subroutines
##
# ----------------------------------------------------------------------

sub mysql_shutdown($$) {
	my ($db_v, $drh) = @_;

	print "shutdown: shutting down server $db_v->{'host'}..." if $debug;

	my $rc = $drh->func('shutdown', $db_v->{'host'}, $db_v->{'user'}, 
     $db_v->{'pass'}, 'admin');

	if ($debug) {
		print "mysql_shutdown->rc: $rc\n";
		if ( $rc ) { print "success.\n" } else { print "failed.\n" }
	};
};

sub mysql_list_databases($) {
	my ($dbh) = @_;

	if (my $sth = MysqlQuery($dbh, "SHOW DATABASES")) {
		while ( my ($db_name) = $sth->fetchrow_array ) { 
			print "$db_name "; 
		};
		if ($sth->err) { 
			print "FAILED!\n";
		} else {
			$sth->finish;
			print "\n";
		};
	};

	### Documented (but non-working methods for listing databases ###
	# my @databases = $drh->func($db_mv->{'host'}, $db_mv->{'port'}, '_ListDBs');
	# print "mysql_info->databases:\t@databases\n";
	#
	# my @databases2 = DBI->data_sources("mysql");
	# print "mysql_info->databases2:\t@databases2\n";
};

sub mysql_version($) {
	my ($dbh) = @_;
	my ($sth, $minor);

	if (my $sth = MysqlQuery($dbh, "SELECT VERSION()")) {
		my $r = $sth->fetchrow_arrayref;
		($minor) = split(/-/, $r->[0]);
		$sth->finish;
	};

	return $minor;
};

sub mysql_lock_tables($) {
	my ($dbh) = @_;

	# Table locking is done at the per-thread level. If we did a $sth->finish
	# the thread would end and we'd lose our lock. So, instead we pass the $sth
	# handle back and close it after we've done our deeds.

	print "lock_tables: locking tables.\n" if $debug;
	if (my $sth = MysqlQuery($dbh, "FLUSH TABLES WITH READ LOCK")) {
		return $sth;
	};
};

sub mysql_unlock_tables($$) {
	my ($dbh, $sth) = @_;

	print "unlock_tables: unlocking mysql tables.\n" if $debug;

	my $query = "UNLOCK TABLES";  # unnecessary, simply calling finish does this
	$sth = MysqlQuery($dbh, $query) 
		or die "FATAL: couldn't unlock tables: $sth->errstr\n";
	$sth->finish;
};


##
# Utility Subroutines (general Unix stuff)
##
# ----------------------------------------------------------------------

sub sudo_setup() {
 	my $sudo = "";
	use POSIX qw(uname);

	my $sudobin = FindTheBin("sudo");
	if (! $sudobin ) {
		if ( $< eq 0 ) {
			my $os = (uname)[0]; 
			if ( $os eq "FreeBSD" ) { 
				print "sudo is not installed, shall I install it? ";
				my $answer = YesOrNo;
				if ($answer) {
					InstallPort("sudo", "security"); 
					$sudobin = FindTheBin("sudo");
				} else {
					print "very well then, continuing.\n";
					return $sudo;
				};
			} else {
				print "WARNING: Sudo is not installed. Some features will not \n";
				print "work for non-root users.\n";
				return $sudo;
			};
		} else {
			print "\n\n\tWARNING: Couldn't find sudo. Some features require root ";
			print "permissions and will not work without it. If it's installed, ";
			print "please report it's location to Matt so future versions of ";
			print "this script will know where to find it. \n\n";
		};
	};

	if ( $< ne "0" ) {
		$sudo = "$sudobin -p 'Password for %u@%h:'";
	};

	return $sudo;
};

1;
__END__

=head2 Dependencies

	In order to use this program, you must have MATT::Bundle installed.
	See http://matt.simerson.net/computing/perl/

=head1 AUTHOR

	Matt Simerson <matt@cadillac.net>

=head1 BUGS

	None known. Report any to author.

=head1 TODO

	Add MySQL slave promotion feature

	Make restoring a failed slave brain dead easy

	Documentation, documentation, documentation

	Add commit/rollback support where appropriate

	check $sth->err and report any premature returns

=head1 SEE ALSO

	http://matt.simerson.net/computing/sql/mrm
	http://matt.simerson.net/computing/sql/mrm/faq.shtml     

=head1 COPYRIGHT

	Copyright 2003, Matt Simerson. All Right Reserved.

=cut