#!/usr/bin/perl

=head1 NAME

B<purge_replication_log> - Purges all the mysql replication log that are no longer in use.

=head1 SYNOPSIS

On slaves:

    purge_replication_log

On the master:

    purge_replication_log --master

=head1 DESCRIPTION

Mysql doesn't yet automatically purge obsolete replication logs,
so we must do it. This job is run periodically on both the master and slaves;
the slaves connect to the master database and make an entry in a table which
holds the log file that they are currently using, and the master consults
this table to decide which log files are safe to purge.

For safety, the purge job will refuse to purge the logs if there are slaves
that are connected, but don't have entries. If slaves are neither connected,
nor able to write any entries (for whatever reason), tough nuggies for them.

Except the master/slave toggle, all command line options refer to the master's
database, whether or not the job is invoked on the master or the slave.
They are '--host', '--database', '--port', '--password', '--user', '--driver'.
Slave mode is the default, '--master' should be specified when appropriate.

=head1 AUTHOR

Copyright (C) 2000 Joshua Spoerri for Wall Street Source

    $Id: purge_replication_log,v 1.2 2003/08/25 19:37:03 mdprewitt Exp $

=cut
use strict;
use DBI;
use Getopt::Long;

my ( $host, $database, $port, $password, $driver, $table, $user, $master );
$driver   = 'mysql';
$database = 'mon';
$table    = 'slave_status';
GetOptions(
  'host=s'     => \$host,
  'database=s' => \$database,
  'port=i'     => \$port,
  'password=s' => \$password,
  'driver=s'   => \$driver,
  'table=s'    => \$table,
  'user=s'     => \$user,
  'master'     => \$master,
);

my $dsn = "dbi:$driver:database=$database";
$dsn .= ";host=$host" if $host;
$dsn .= ";port=$port" if $port;

if ( !$host ) {
    chomp( $host = `hostname` );
}

$user = scalar getpwuid $< unless $user;
my $dbh = DBI->connect( $dsn, $user, $password )
  or die "couldn't connect to $dsn: $DBI::errstr";

if ( !$port ) {
    my $sth = $dbh->prepare(qq{ SHOW VARIABLES }) or 
        die "couldn't prepare show variables: " . $dbh->errstr;

    $sth->execute() or die "couldn't execute show variables: " . $sth->errstr;

    while ( my $row = $sth->fetchrow_arrayref() ) {
        if ( $row->[0] eq 'port' ) {
            $port = $row->[1];
            last;
        }
    }
    $sth->finish();
}

if ($master) {    #when invoked on the master, actually purge the logs

    #get all slaves that are currently connected
    my @connected_slaves;
    my $sth = $dbh->prepare(qq{ SHOW PROCESSLIST }) or 
        die "couldn't prepare show process list: " . $dbh->errstr;
    $sth->execute() or die "couldn't execute show process list: " . $sth->errstr;

    while ( my $process = $sth->fetchrow_hashref ) {
        push @connected_slaves, $process->{Host}
          if $process->{Command} eq 'Binlog Dump';
    }
    $sth->finish();

    #get all slaves that have entries
    my @logged_slaves;
    my $sth = $dbh->prepare(qq{ SELECT Slave FROM $table WHERE Master like '$host:%' })
        or die "couldn't prepare select in $table: " . $dbh->errstr;

    $sth->execute() or die "couldn't execute select in $table: " . $sth->errstr;

    while ( ($_) = $sth->fetchrow_array() ) {
        s/:.*//;
        push @logged_slaves, $_;
    }
    $sth->finish();

    #make sure that all currently connected slaves have entries
    for my $slave (@connected_slaves) {
        my $logged;
        if ( $slave =~ /\./ ) {
            $logged = grep( $slave =~ /^$_(\.|$)/, @logged_slaves );
        }
        else {
            $logged = grep( /^$slave(\.|$)/, @logged_slaves );
        }
        die "no entry in $table for connected slave \"$slave\"" unless $logged;
    }


    #  mysql> select * from slave_status;
    #  +----------------+--------------+------------------+----------------+
    #  | Slave          | Master       | Log_File         | Timestamp      |
    #  +----------------+--------------+------------------+----------------+
    #  | polar:3306     | fosters:3306 | fosters-bin.1033 | 20030825000205 |
    #  | carlsberg:3306 | fosters:3306 | fosters-bin.1033 | 20030825000205 |
    #  | bohemia:3306   | fosters:3306 | fosters-bin.1018 | 20030825000206 |
    #  +----------------+--------------+------------------+----------------+


    #get the oldest log file that appears to be in use
    my $sth = $dbh->prepare( qq{ SELECT Log_File FROM $table WHERE Master = '$host:$port' } ) 
          or die "couldn't prepare select in $table: " . $dbh->errstr;
    $sth->execute() or die "couldn't execute select in $table: " . $sth->errstr;
    my @logs;
    while (my $log = $sth->fetchrow_array()) {
        push @logs, $log;
    }
    die "no slave entries for master $host:$port in $table" unless @logs;
    # sort the Log_File column by the number after the '.' in reverse
    # order to find the ealiest logfile.
    my $first_log = (sort {substr($b, index($b, '.')) <=> substr($a, index($a, '.')) } @logs)[0];

    #purge up to (but not including) that log file
    print "Purging master logs to '$first_log'\n";
    $dbh->do(qq{ PURGE MASTER LOGS TO '$first_log' }) or die "couldn't purge to $first_log: " . $dbh->errstr;

#print(qq{would like to: "},qq{ PURGE MASTER LOGS TO '$first_log' },qq{"\n});#for testing

}
else
{ #when invoked on a slave, just record an entry for the currently active log file

    #get all the info for 1) connecting to the master, 2) recording our entry
    my ( $status, $master_host, $master_port, $log, $dsn );
    my $sth = $dbh->prepare(qq{ SHOW SLAVE STATUS }) 
        or die "couldn't prepare show slave status: " . $dbh->errstr;
    $sth->execute() 
        or die "couldn't execute show slave status: " . $sth->errstr;
    $status      = $sth->fetchrow_hashref();
    $master_host = $status->{Master_Host}
      or die "$0 called in slave mode for nonslave";
    $master_port = $status->{Master_Port};
    $log         = $status->{Log_File};
    $dsn = "dbi:$driver:database=$database;host=$master_host;port=$master_port";
    $sth->finish();
    $dbh->disconnect();

    my $dbh = DBI->connect( $dsn, $user, $password ) 
        or die "couldn't connect to $dsn: " . $DBI::errstr;

#don't bother to check for existence of the table; always creating if necessary eases maintenance
    $dbh->do( qq{ CREATE TABLE IF NOT EXISTS $table (
     Slave varchar(255),
     Master varchar(255),
     Log_File varchar(255),
     Timestamp timestamp,
     UNIQUE (Slave)
    ) } ) or die "couldn't create $table: " . $dbh->errstr;

    #there should be exactly one currently active log file for each slave,
    #so just replace entry for what was previously active
    print "Saving position for host $host:$port=$log\n";
    $dbh->do( qq{ REPLACE INTO $table (Slave, Master, Log_File)
     VALUES ('$host:$port', '$master_host:$master_port', '$log') } )
      or die "couldn't replace into $table: " . $dbh->errstr;
}

exit 0;