#!/usr/bin/perl -w

# This script checks for data consistency between the Master and the Slaves

# and sends out an email to the admin if they are out of sync ..

# Kishore Jalleda

 

use strict;

use Fcntl;

use DBIx::DWIW; 

 

sub email;

 

$|=1;       # unbuffer stdout

 

# The Common Login details to the MYSQL Server

# Make sure you make this file unreadable by others....

# and change these values appropriately  

 

my $db_user = 'user';

my $db_pass = 'password';

my $db_name = 'database';

my $master  = '192.168.0.5';

 

# Deleting the old status file before writing to it …

 

if (-e "status_file" )
{
    unlink('status_file');
}

 

# All the Slaves that need to be checked for data consistency with the Master

# are defined here as an array

# change these as necessary

 

my @slaves = qw(

    192.168.0.6

    192.168.0.7

    192.168.0.8

);

 

# All the Databases that need to be checked are defined here as an array

# change these as necessary

 

my @databases = qw(

    test1

    test2

    test3

    test4

);

 

# Some Variables

 

my %master_count;

my $count = 0;

my $fail_count = 0;

my $status_file;

 

# First we check the Master and record the row counts on each tables....

 

for my $server ($master)

{

    print "Starting to Analyze the Master\n";

    for my $database_M (@databases)

    {

       print "Checking the Database $database_M on the Master... ";

       my $conn = DBIx::DWIW->Connect(User => $db_user, Host => $server,

                  Pass => $db_pass, DB => $database_M) or die "$!";

 

       for my $table ($conn->FlatArray("SHOW TABLES"))

       {

           my $count = $conn->Scalar("SELECT COUNT(*) FROM $table");

           $master_count{$table} = $count;

       }

       print "OK\n"

    }

    print "Done With the Master\n\n";

}

 

# Now we check the slaves, record the row counts on each table and compare

# these values with those on the Master

 

for my $server (@slaves)

{

    print "Starting to Analyze the Slaves\n";

    for my $database_S (@databases)

    {

       print "Checking the Database $database_S on the the Slave $server...\n";

       my $conn = DBIx::DWIW->Connect(User => $db_user, Host => $server,

                  Pass => $db_pass, DB => $database_S) or die "$!";

 

       for my $table ($conn->FlatArray("SHOW TABLES"))

       {

           my $count = $conn->Scalar("SELECT COUNT(*) FROM $table");

           if ($count != $master_count{$table})

           {

               sysopen (FILE, 'status_file', O_RDWR|O_CREAT, 0644)

               || die "Can't open $status_file for writing";          

               print FILE "Count mismatch on <TABLE> \"$table\" on <DATABASE> \"$database_S\" on the   <SLAVE> \"$server\"\n" ;

               print FILE "Count is $count but expecting $master_count{$table}\n";

               close (FILE);

               $fail_count++;

           }

 

       }

 

   }

     

}

 

# The fail count is cumulative and we send out the report if it was ever incremented......

 

if ($fail_count > 0)

{

       open (FH,'status_file')

       || die "Can't open $status_file";

       open (MAIL, "|/usr/sbin/sendmail -t ");

       print MAIL "From: mysql\@mysql.yourdomain.com\n";

       print MAIL "To: admin\@mysql.yourdomain.com\n";

       print MAIL "Content-Type: text/plain\n";

       print MAIL "Subject: Mysql Slaves Replication Status\n\n";

       while (my $record = <FH>)

       {

           print MAIL "$record\n";

           print "$record\n";

       }

       close (FH);

       close (MAIL);

 

 

}

 

exit;

 

<end of script>

 

Kishore Jalleda

http://kjalleda.googlepages.com

 

References:

High Performance Mysql by Jeremy Jawodny