#!/usr/bin/perl
#
# Author: Sean Colombo
# Date: 20080531
#
# Deletes the bin-logs on the master server that are older than those needed by the slave.
####

##### CONFIGURATION #####
my $db_host = "TODO"; # Host of the SLAVE server
my $db_name = "TODO";
my $db_user = "TODO"; # This user must have privileges to do a "SHOW SLAVE STATUS" query.
my $db_pass = "TODO";
my $LOG_DIRECTORY = "/var/log/mysql"; # Location of the mySQL bin-logs.  Omit the trailing slash.
my $emailAddr = "TODO\@YOUR_DOMAIN.com"; # Address to send an email to if something is wrong.
my $emailAddr_reply = "TODO\@YOUR_DOMAIN.com"; # Who the email will be "from".
my $mailServer = "mail.YOUR_DOMAIN.com"; # If using Windows, put the name of the SMTP server here for sending emails.
##### CONFIGURATION #####

# Connect to the slave and find out what bin-log file is being used.
use DBI;
my $dsn = "DBI:mysql:$db_name:$db_host";
my $dbh = DBI->connect($dsn, $db_user, $db_pass) or sendEmail("Error trying to delete bin-logs", "$DBI::errstr", $emailAddr) and die;
my $sth = $dbh->prepare("SHOW SLAVE STATUS");
my $masterLogFile = "";
if($sth->execute()){
	if($row = $sth->fetchrow_hashref('NAME_uc')){
		$masterLogFile = $row->{MASTER_LOG_FILE};
	}
}
$sth->finish();

if($masterLogFile ne ""){
	my $filePrefix = $masterLogFile;
	$filePrefix =~ s/^(.*?)\.[0-9]+$/$1/;

	# Get the file-listing of the log directory.
	opendir(DIRHANDLE, $LOG_DIRECTORY);
	my @allFiles = reverse sort readdir(DIRHANDLE);
	foreach $fileName (@allFiles){
		#Makes sure this is the log file for the same database in case someone uses this script for servers with multiple replicating databases.
		if($fileName =~ /^$filePrefix\.[0-9]+$/){
			# If the log file is older than the current-file, delete it.
			if($fileName lt $masterLogFile){
				print "Deleting \"$LOG_DIRECTORY/$fileName\"...\n";
				unlink($LOG_DIRECTORY."/$fileName");
			} else {
				print "KEEP: $fileName\n";
			}
		}
	}
} else {
	print "ERROR: Unable to find master log file.\n";
	my $msg = "Unable to find the current log-file name on the slave server. ";
	$msg .= "This means that we either couldn't connect to the slave, didn't have permissions ";
	$msg .= "to run 'SHOW SLAVE STATUS', or that replication is not running properly.";
	sendEmail("Error trying to delete bin-logs", $msg, $emailAddr);
}


####
# Sends a title,message to the configured email addr above.
####
sub sendEmail{
	my $addr = $emailAddr;
	if(@_ > 2){
		$addr = $_[2];
	}
	my $title = shift;
	my $content = shift;
	if($content eq ''){
		$content = $title;
	}
	my $sendmail = "/usr/sbin/sendmail -t";
	my $reply_to = "Reply-to: $emailAddr_reply\n";
	my $subject = "Subject: $title\n";
	my $send_to = "To: $addr\n";

	# Send it using sendmail.
	if($^O eq "MSWin32"){
		# Windows version
		use Net::SMTP;
		$smtp = Net::SMTP->new("$mailServer"); # connect to an SMTP server
		$smtp->mail($emailAddr_reply); # use the sender's address here
		$smtp->to($addr); # recipient's address
		$smtp->data(); # Start the mail
		$smtp->datasend("$subject");
		$smtp->datasend("$send_to");
		$smtp->datasend("From: $emailAddr_reply\n");
		$smtp->datasend("Content-type: text/html\n");
		$smtp->datasend("\n");
		$smtp->datasend("$content");
		$smtp->dataend(); # Finish sending the mail
		$smtp->quit; # Close the SMTP connection
	} else {
		# Unix version.
		open(SENDMAIL, "|$sendmail") or die "Cannot open $sendmail: $!";
		print SENDMAIL $reply_to;
		print SENDMAIL $subject;
		print SENDMAIL $send_to;
		print SENDMAIL "Content-type: text/html\n\n";
		print SENDMAIL $content;
		close(SENDMAIL);
	}

	print "Email sent.\n";
}