#!/usr/bin/perl 
#
#-------------------------------------------------------------------------------
# myCheckReplica 2.0.5@2007.04.27
# It is a simple MySQL Replica Status Checker, that write a data in a replicated
# table on master Server and check on Slave if this data is present.
# 
# Table format is:
# CREATE DATABASE replica;
# USE replica;
# CREATE TABLE `check` (
#   `iID` int(11) NOT NULL default '0',
#   `dData` datetime NOT NULL default '0000-00-00 00:00:00',
#   PRIMARY KEY  (`iID`)
# ) TYPE=MyISAM;
#
# You can choose a different database or table name, you can place this in a
# already replicated database. Script is compatible with MySQL 3.23 or 
# above.
#
# Program can send mail, write status on syslog (daemon.info) and return an
# errorlevel that is compatible with Nagios (0 = OK, 2 = Critical)
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 2 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program; if not, write to the Free Software
#    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
# Author: Angelo Conforti <angeloxx@angeloxx.it>
# Patches and problem reports are welcome.
#
# The latest version of this program is available at:
#   http://www.angeloxx.it/software/myCheckReplica/
#-------------------------------------------------------------------------------


use DBI;
use DBI::DBD;
use DBD::mysql;
use Getopt::Long;
use Getopt::Std;

my $syslog = 1;
my $logger = "/usr/bin/logger";
my $logger_opts = "-p daemon.info -t myCheckReplica";
my $sendmail = "/usr/sbin/sendmail -t";
my $version = "2.0.5";

my ($UNKNOWN,$CRITICAL,$WARNING,$OK) = (3,2,1,0);

# Replica Seconds Difference Tolerance
my $rdt = 600;

sub do_log($) {
	my ($logmessage) = @_;
	
	$logmessage = "[M=$hostMaster/S=$hostSlave] ".$logmessage;
	if ($syslog) {
		open (SYSLOG, "| $logger $logger_opts") or die ("Unable to open logger");
		printf SYSLOG "%s\n", $logmessage;
		close (SYSLOG);
	}
}

sub do_mail($$) {
	# Status: ERROR | INFO
	my ($status,$message) = @_;
	$thisHost = `hostname -f`; chomp($thisHost);
	if ($mailto) {
		unless ($status eq 'INFO' and $mailerror) {
			($mU,$mD) = split(/@/,$mailto);

			open(SENDMAIL, "|$sendmail") or die "Cannot open $sendmail: $!";
			print SENDMAIL "From: \"myCheckReplica\" <noreply\@$mD> \n";
			print SENDMAIL "Subject: [myCheckReplica] - $status \n";
			print SENDMAIL "To: $mailto \n";
			print SENDMAIL "X-Sender: myCheckReplica $version \n";
			print SENDMAIL "Content-type: text/plain\n\n";
			print SENDMAIL "MySQL server replication check \n";
			print SENDMAIL "----------------------------------------------------\n";
			print SENDMAIL "Check running on : $thisHost\n";
			print SENDMAIL "Master server    : $hostMaster\n";
			print SENDMAIL "Slave  server    : $hostSlave\n";
			print SENDMAIL "Username used    : $user\n";
			print SENDMAIL "Database         : $database\n";
			print SENDMAIL "Table            : $table\n";
			print SENDMAIL "----------------------------------------------------\n";
			print SENDMAIL $message;
			close(SENDMAIL);
		}
	}
}

	GetOptions( 'master=s'=>\$hostMaster,
				'slave=s'=>\$hostSlave,
				'user=s'=>\$user,
				'pass=s'=>\$pass,
				'database=s'=>\$database,
				'table=s'=>\$table,
				'sleep=i'=>\$sleep,
				'mailto=s'=>\$mailto,
                'nagios'=>\$nagios,
				'mailerror'=>\$mailerror);
	
	unless (defined($nagios)) { print "MySQL Replica Checker $version \n-- \n"; }
        
	unless ($hostMaster & $hostSlave) {
        unless (defined($nagios)) {
            print "Usage: $0 --master=hostname --slave=hostname [--user=username] [--pass=password] [--database=dbname] [--table=tbname]"."\n";
            print "Switches: --master=?    : hostname of master server"."\n";
            print "          --slave=?     : hostname of slave server"."\n";
            print "          --user=?      : MySQL username for connection to master and slave. (default = root)"."\n";
            print "          --pass=?      : MySQL password for this user (default = empty)"."\n";
            print "          --database=?  : database name that can be used for test (default = replica)"."\n";
            print "          --table=?     : table name that can ben used in this database (default = check)"."\n";
            print "          --sleep=?     : seconds between insert on master and verify on slave (default = 30)"."\n";
            print "          --mailto=?    : mail address to send report"."\n";
            print "          --mailerror   : send mail only on error	"."\n";
        }
		exit($UNKNOWN);
	}
	
	unless($database) 	{ $database = "replica"; }
	unless($table) 		{ $table = "check"; }
	unless($user) 		{ $user = "root"; }
	unless($pass) 		{ $pass = ""; }
	unless($sleep) 		{ $sleep = 30; }
	
	
	
	my $dbMaster = DBI->connect("DBI:mysql:".$database.":".$hostMaster,$user,$pass);
	
	unless ( $dbMaster ) {
	  do_log( "ERROR: Unable connect to Master MySQL Server" );
	  do_mail("ERROR","Unable connect to Master MySQL Server");
	  print ("ERROR: Unable connect to Master MySQL Server\n");
	  exit($CRITICAL);
	}
	my $dbSlave = DBI->connect("DBI:mysql:".$database.":".$hostSlave,$user,$pass);
	unless ($dbSlave) {
	  do_log( "ERROR: Unable connect to Slave MySQL Server" );
	  do_mail("ERROR","Unable connect to Slave MySQL Server");
	  print ("ERROR: Unable connect to Slave MySQL Server\n");
	  exit($CRITICAL);
	}
	
	my $rsTableMaster = $dbMaster->prepare( "show tables like '$table'"); $rsTableMaster->execute;
	my $rsTableSlave = $dbSlave->prepare( "show tables like '$table'"); $rsTableSlave->execute;

    unless ( $rsTableMaster->rows ) {
	  do_log( "ERROR: Unable find $table table on Master" );
	  do_mail("ERROR","Unable find $table table on Master");
	  print ("ERROR: Unable find $table table on Master\n");
	  exit($CRITICAL);
	}
    unless ( $rsTableSlave->rows ) {
	  do_log( "ERROR: Unable find $table table on Slave" );
	  do_mail("ERROR","Unable find $table table on Slave");
	  print ("ERROR: Unable find $table table on Slave\n");
	  exit($CRITICAL);
	}
	
	# DB exist, table too
	# Now insert data
	my $rsInsert = $dbMaster->prepare( "truncate $database.$table");
	$rsInsert->execute;
	my $rsInsert = $dbMaster->prepare( "insert into $database.$table (iID,dData) values ($$,now())");
	$rsInsert->execute;
	
	# Wait
	sleep($sleep);
	$rdt = $rdt + $sleep;
	
	my $rsFetch = $dbSlave->prepare( "select * from $database.$table where iID = $$ and DATE_ADD(dData,INTERVAL $rdt SECOND) >= NOW()");
	$rsFetch->execute;
	
	unless ( $rsFetch->rows ) {
	  do_log( "ERROR: Replica error" );
	  do_mail("ERROR","Replica error");
	  print ("ERROR: Replica error\n");
	  exit($CRITICAL);
	} else {
	  do_log( "INFO: Replica check OK" );
	  do_mail("INFO","Replica check OK");
	  print ("INFO: Replica check OK\n");
	  exit($OK);
	}