#!/usr/local/bin/perl

# Program: repli_lag3.pl
# Purpose: check replication lag using show slave status;
# Env: Perl5, Linux
# Copyright 2003 James Briggs, San Jose, California, USA.
# Version: 0.1 2003 11 10 original
# Install: */5 * * * * /home/prod/scripts/repli_lag3.pl

use strict;
use diagnostics;

$| = 1;

use DBI;
use Fcntl ':flock';

   my $DEBUG = 1;

   my $delay_lag = 0;

   my $logpath = '/var/log';

   my @login = ('DBI:mysql:actionmessage:10.0.0.2', 'prod', 'secret');

   my $logfile = $logpath . "/repli_lag3.log";

   logger($logfile, "*** Replication Lag Started ***");

   my $dbh = DBI->connect(@login, { RaiseError => 0, AutoCommit => 1 }) || die $DBI::errstr;

   my $sql = q{show slave status};

   logger($logfile, $sql) if $DEBUG;

   my $sth = $dbh->prepare($sql) || die $DBI::errstr;

   my $rv = $sth->execute() || die $DBI::errstr;

   my @status = $sth->fetchrow_array();
       
   $sth->finish();

   $dbh->disconnect() || die $DBI::errstr;

   my $Read_Master_Log_Pos = $status[5];
   my $Exec_master_log_pos = $status[16];

   logger($logfile, join(",", @status));

   if ($DEBUG) {
      my $i = 0;
      foreach (@status) {
         print $i++, " $_\n";
      }
   }

   if ($Exec_master_log_pos - $Read_Master_Log_Pos > $delay_lag) {
      logger($logfile, "lag not ok\n");
   }
   else {
      logger($logfile, "lag ok\n");
   }

   logger($logfile, "*** Replication Lag Finished ***\n");

   exit 0;

sub logger {
   my ($file, $msg) = @_;

   my $user = $ENV{'REMOTE_ADDR'} || 'nobody';

   $msg = '' if not defined $msg;

   my ($package, $filename, $line) = caller;

   open   LOG, ">>$file" or die "cannot open $file file: $!";
   flock (LOG, LOCK_EX()) or die "cannot lock $file file: $!";
   seek   LOG, 0, 2 or die "cannot seek $file file: $!";
   print  LOG datestamp() . ", $user, " . $filename . ":" . $line . ", " . $msg . "\n";
   close  LOG or die "cannot close $file file: $!";
}

sub datestamp {
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;

    sprintf '%4d%02d%02d%02d%02d%02d',$year+1900,$mon+1,$mday,$hour,$min,$sec;
}