xymon mysql slaves sync

xymon_dbsync.pl

use strict;
use DBI;

use constant { 
        SYNC_NOTHING => 0x00, 
        SYNC_SEEMSFINE => 0x01, 
        SYNC_ISFUCKED => 0x02 
};

sub slavehost {
        my($dsn) = @_;
        if ($dsn =~ /;host=(.+)/) { return $1; } 
        else                      { return $dsn; }
}

my $DATA = '';
my $master = "dbi:mysql:;host=x.x.x.x";
my $user = "user";
my $pass = "pass";

my $slaves = { 
        "dbi:mysql:;host=y.y.y.y" => {
                username => $user,
                password => $pass,
        },
};
my $connpool = { master => undef, slaves => { }, metadata => { } };

local *report = sub { $DATA .= shift()."\n" };

## BB and related test constants
#############################################################################
use constant GREEN => 'green';
use constant YELLOW => 'yellow';
use constant RED => 'red';
 
## BB Global variables
#############################################################################
my $bbtest = 'dbsync';
my $color = GREEN;
my $status = $bbtest . " OK";
 
## Main Program
#############################################################################
 
# Connect to slaves
foreach(keys %$slaves) {
        $connpool->{'slaves'}->{$_} = 
                DBI->connect(
                        $_,
                        $slaves->{$_}->{'username'},
                        $slaves->{$_}->{'password'}
                ) or die("Slave failed: $!");
        $connpool->{'metadata'}->{$_}->{'health'} = SYNC_NOTHING;
}

report("Connected to ".(keys %{$connpool->{'slaves'}})." slaves.");

# Connect to master
$connpool->{'master'} = 
        DBI->connect($master,$user,$pass) or die("Master failed: $!");

report("Connected to master.");

my $sth = $connpool->{'master'}->prepare("SHOW MASTER STATUS");
$sth->execute();

while(my $row = $sth->fetchrow_hashref) {
        $connpool->{'metadata'}->{'position'} = $row->{'Position'};
        $connpool->{'metadata'}->{'binlog_name'} = $row->{'File'};
}

report("Master has binlog position ".$connpool->{'metadata'}->{'position'});

foreach my $slave (keys %{$connpool->{'slaves'}}) {
        $sth = $connpool->{'slaves'}->{$slave}->prepare("SHOW SLAVE STATUS");
        $sth->execute();
        while(my $row = $sth->fetchrow_hashref) {
                $connpool->{'metadata'}->{$slave} = $row;
        }
}

# See if slaves are up to date
foreach my $slave (keys %{$connpool->{'slaves'}}) {
        report(" - Verifying ".&slavehost($slave).":");
        if ( 
          $connpool->{'metadata'}->{$slave}->{'Master_Log_File'} ne 
          $connpool->{'metadata'}->{'binlog_name'}) {
                $color = RED;
                $status = $bbtest . " NOT OK"; 
                report("   + Filename mismatch. NOT up to date!");
                $connpool->{'metadata'}->{$slave}->{'health'} |= SYNC_ISFUCKED;
        } else {
                report("   + Filename match.");
                if ( 
                  ($connpool->{'metadata'}->{$slave}->{'Exec_Master_Log_Pos'} != 
                  $connpool->{'metadata'}->{'position'}) && 
                  ($connpool->{'metadata'}->{$slave}->{'Seconds_Behind_Master'} == 0)) {
                        $color = RED;
                        $status = $bbtest . " NOT OK"; 
                        report("   + Binlog position does NOT match!");
                        $connpool->{'metadata'}->{$slave}->{'health'} |= SYNC_ISFUCKED;
                } else {
                        if (
                          $connpool->{'metadata'}->{$slave}->{'Seconds_Behind_Master'} != 0) {
                                $color = YELLOW;
                                $status = $bbtest . " NOT OK";
                                report("   + Binlog position match (slave is delayed with " . 
                                        $connpool->{'metadata'}->{$slave}->{'Seconds_Behind_Master'}." seconds).");
                        } else {
                                report("   + Binlog position match.");
                        }
                        if ($connpool->{'metadata'}->{$slave}->{'Slave_IO_Running'} eq "No") {
                                $color = RED;
                                $status = $bbtest . " NOT OK"; 
                                report("   + Slave IO is NOT running!");
                                $connpool->{'metadata'}->{$slave}->{'health'} |= SYNC_ISFUCKED;
                        } else {
                                report("   + Slave IO running.");
                                $connpool->{'metadata'}->{$slave}->{'health'} = SYNC_SEEMSFINE;
                        }
                }
        }
        report("   = Last Error: $connpool->{'metadata'}->{$slave}->{'Last_Error'} ");
        report("");
}

## Send to Hobbit
#############################################################################
my $report_date = `/bin/date`;
chomp($report_date);

system("$ENV{BB} $ENV{BBDISP} 'status $ENV{MACHINE}.$bbtest $color $report_date - $status\n\n$DATA'\n");