#!/usr/bin/perl -w
use strict;
use Getopt::Long;
use DBI;

# Changelog:
#  20050111 - added support to detect Slave_IO_Running and Slave_SQL_Running critical states
#  20041209 - For mysql 4.1.7 or higher:
#             Simple check of "Seconds_Behind_Master" at the slave.
#             Based on the more complex script "check_replication.pl" 
#             from James Bromberger <jbromberger\@fotango.com>.
#             This simple version has the advantage that there is ONLY ONE 
#             connection needed that goes to the slave.
#             The connection to the master is obsolate because we totaly 
#             rely on the "Seconds_Behind_Master" value.
#             Disadvantage: mysql 4.1.7 or higher is required since the 
#             "Seconds_Behind_Master" value is not available on older versions.
#  
my $options = { 'slave-port' => 3306, 'slave' => 'slavehost', 'crit' => 3600, 'warn' => 900 , 'dbuser' => 'replcheck', 'dbpass' => 'password'};
GetOptions($options, "slave=s", "dbuser=s", "dbpass=s", "crit=i", "warn=i", "help", "slave-port=i");
my $max_binlog;

if (defined $options->{'help'}) {
	print <<FOO;
$0: check mysql database slave status by requesting the variable 'Seconds_Behinder_Master' of 'Show Slave Status' 

 check_mysql_seconds_behind_master.pl [ --slave <host> ] 
 [ --crit <positions> ] [ --warn <positions> ] [ --dbuser <user> ] 
 [ --dbpass <pass> ]

  --slave <host>     - MySQL instance running as a slave server
  --slave-port <d>   - port for the slave
  --crit <seconds>   - seconds for critical state (default 3600)
  --warn <seconds>   - seconds for warning state (default 900)
  --dbuser <user>    - Username (with REPL_CLIENT privs) to check status
  --dbpass <pass>    - Password for above user
  --help             - This help page

The user that is testing must exist on the slave, eg:
  GRANT REPL_CLIENT, Process on *.* TO repl_test\@192.168.0.% IDENTIFIED BY <pass>

Note: Any mysqldump tables (for backups) may lock large tables for a long 
time. If you dump from your slave for this, then your master will gallop 
away from your slave, and the difference will become large. The trick is to 
set crit above this differnce and warn below.

(c) 2004 www.lars-schenk.com, <info\@lars-schenk.de>.
Code based on the more complex script 'check_replication_status.pl' from 
(c) 2004 Fotango. James Bromberger <jbromberger\@fotango.com>.
FOO
exit;
}


# returns -1 if Slave_IO_Running is false
# returns -2 if Salve_SQL_Running is false
sub get_seconds_behind {
	my $host = shift;
	my $port = shift;

	my $dbh = DBI->connect("DBI:mysql:host=$host:port=$port", $options->{'dbuser'}, $options->{'dbpass'});
	if (not $dbh) {
		print "UNKNOWN: cannot connect to $host";
		exit 3;
	}

	my $sql = "show slave status";   
	my $sth = $dbh->prepare($sql);
	my $res = $sth->execute;
	my $ref = $sth->fetchrow_hashref;
	my $seconds_behind = $ref->{'Seconds_Behind_Master'};
    my $slave_sql_running = $ref->{'Slave_SQL_Running'};
    my $slave_io_running = $ref->{'Slave_IO_Running'};
    if (index($slave_io_running,"No")>=0){
       $seconds_behind = -1;
    }
    if (index($slave_sql_running,"No")>=0){ 
      $seconds_behind = -2;
    }
    $sth->finish;
	$dbh->disconnect;
	return $seconds_behind;
}

sub check_status {
    my $seconds_behind = shift;	
	my $state = sprintf "%d seconds behind master\n", $seconds_behind;

	if ($seconds_behind == -1) {
        print "SLAVE CRITICAL: Slave_IO_Running is false";
        exit 2;
    } elsif ($seconds_behind == -2) {
        print "SLAVE CRITICAL: Slave_SQL_Running is false";
        exit 2;
    } elsif ($seconds_behind >= $options->{'crit'}) {
		print "SLAVE CRITICAL: $state";
		exit 2;
	} elsif ($seconds_behind >= $options->{'warn'}) {
		print "SLAVE WARN: $state";
		exit 1;
	}
	print "SLAVE OK: $state";
	exit 0;
}

check_status(get_seconds_behind($options->{'slave'}, $options->{'slave-port'}));