mysql replication monitoring

17 May 2010 · *.*

      I tried to get this script working but it never seems to send data back to the hobbit server.
I ran the script in debug mode, and it seems to sending the correct information, but not sure why it not showing up on the hobbit.
[root at cs101 ext]# ./mysql-replication.pl
/home/hobbit/client/bin/bb 10.1.5.18 "status 10,1,5,39.replication green Mon May 17 20:14:22 2010 Mirroring OK
Node: node
master T1      at               mysql-bin.000302:949148896  (10.1.5.39)
slave  RUNNING                                              (10.1.5.44)
slave  ON TIME at               mysql-bin.000302:949164080  (10.1.5.44)
master T2      at               mysql-bin.000302:949179136  (10.1.5.39)
"
#!/usr/bin/perl -w

mysql replication monitoring
generic attempt
use strict;
#use Data::Dumper;
my $DEBUG       = 0;
$ENV{BBPROG}    = "bb-mysql-replication";
my $TESTNAME    = "mysqlrep";
my $BBHOME      = $ENV{BBHOME};
my $BB          = $ENV{BB};    # full path to the bin/bb util
my $BBDISP      = $ENV{BBDISP};       # IP of the BBDISPLAY server
my $BBVAR       = $ENV{BBVAR};
my $MACHINE     = $ENV{MACHINE}; # hostname, fqdn
my $COLOR       = "clear";     # global color for the test
my $MSG         = "";          # body of the message
my $HEAD        = ""; # first line of the message (has to be short, optional)
my $DATA        = "";          # data for NCV records (hobbit only)
if ($DEBUG == 1) {
$BBHOME  = "/tmp" unless $BBHOME;
$BBDISP  = "127.0.0.1" unless $BBDISP;
$BBVAR   = "/tmp" unless $BBVAR;
$MACHINE = "host.priv.com" unless $MACHINE;
}
The Fine Manual :
sub clear;                     # sets status color to clear
sub green;                     # ~ to green
sub yellow;                    # ~ to yellow
sub red;                       # ~ to red
sub setcolor; # safely sets status color                       # setcolor("yellow")
sub head; # sets the first status line                     # head("foo OK")
sub msg; # adds text to the body of the status message    # msg("foo" [, "bar", ...])
sub data; # adds NCV-formatted data to the status message  # data(ds, value)
sub sendreport; # sends the report to the BB server              # sendreport()
sub resetreport; # wipes the report (head, body, data, color)     # resetreport()
You can always use $MSG, $HEAD, $DATA and $COLOR directly.
########
Put your code here
########
What you need :
on the remote masters and slaves, create:
for mysql 4.x : grant replication client on *.* to 'user'@'monitorhost' identified by 'password';
grant select on test.* to 'user'@'monitorhost' identified by 'password';
for mysql 3.x : grant process on *.*   to 'user'@'monitorhost' identified by 'password';
grant select on test.* to 'user'@'monitorhost' identified by 'password';

use DBI;
my $ERR;
my $legalERR = "[^a-zA-Z0-9.:,_ -]";
my ($status, $maitreSql, @slaves, $esclaveSql, $login, $pass, %auth, $node);
my ($positionMaitre_t1, $positionMaitre_t2, $positionEsclave, $slaveRunning, $error);
my $conffile="/usr2/hobbitlocal/etc/bb-mysql-replication.cfg";
my $OKOFFSET = 100;
readConfig();
foreach $node ( keys %auth ) {
msg "Node: $node\n";
green;                 # let's be optimit ;)
    $maitreSql  = $auth{$node}{master};
    @slaves     = keys %{ $auth{$node}{slaves} };
    $login      = $auth{$node}{user};
    $pass       = $auth{$node}{pass};
    $MACHINE    = $maitreSql;

    $positionMaitre_t1 = getSqlMasterStatus($maitreSql,$login,$pass,"test");
    if (not defined $positionMaitre_t1) {
            $ERR =~ s/$legalERR/ /go;
           msg(sprintf("master ERROR                    (%s) &red %s", $maitreSql, $ERR));
           head "mirroring FAILURE";
           sendreport;
           resetreport;
           next;
    }
    sleep 2;
    foreach $esclaveSql (@slaves) {
           ($positionEsclave, $slaveRunning,$error) = getSqlSlaveStatus($esclaveSql,$login,$pass,"test");
           next if not defined $positionEsclave;
           $auth{$node}{slaves}{$esclaveSql}{pos} = $positionEsclave;
           $auth{$node}{slaves}{$esclaveSql}{running} = $slaveRunning;
    }
    sleep 2;
    # on reprend la position sur le maître
    $positionMaitre_t2 = getSqlMasterStatus($maitreSql,$login,$pass,"test");
    if (not defined $positionMaitre_t2) {
           head "mirroring FAILURE";
            $ERR =~ s/$legalERR/ /go;
           msg(sprintf("master ERROR                    (%s) &red %s", $maitreSql, $ERR));
           sendreport;
           resetreport;
           next;
    }
    msg(sprintf("master T1      at % 40s  (%s)", $positionMaitre_t1, $maitreSql));

    foreach $esclaveSql (@slaves) {
           $positionEsclave = $auth{$node}{slaves}{$esclaveSql}{pos};
           $slaveRunning    = $auth{$node}{slaves}{$esclaveSql}{running};
           if (not defined $positionEsclave) {
                   $ERR =~ s/$legalERR/ /go;
                   msg "slave  ERROR                    ($esclaveSql) &red $ERR";
                   next;
           } elsif ($slaveRunning eq "Yes") {
                   msg "slave  RUNNING                                              ($esclaveSql)";
                   if ( ($positionMaitre_t1 le $positionEsclave) and
                        ($positionEsclave le $positionMaitre_t2) ) {
                           msg(sprintf("slave  ON TIME at % 40s  (%s)", $positionEsclave, $esclaveSql));
                   } else {
                           my($masterfile, $masteroffset, $slavefile, $slaveoffset);
                           ($masterfile, $masteroffset) = split(/:/, $positionMaitre_t1, 2);
                           ($slavefile, $slaveoffset) = split(/:/, $positionEsclave, 2);
                           msg(sprintf("slave  LATE    at % 40s  (%s) &yellow", $positionEsclave, $esclaveSql));
                           if($masterfile eq $slavefile) {
                                  if ($masteroffset - $slaveoffset &lt; $OKOFFSET) {
                                          green;
                                  }
                                  else {
                                          yellow;
                                  }
                           }
                           else {
                                  $masterfile =~ s/.+\.(\d+)$/$1/;
                                  $slavefile  =~ s/.+\.(\d+)$/$1/;
                                  if($masterfile - $slavefile != 1) {
                                          yellow;
                                  }
                                  elsif($masteroffset &lt; $OKOFFSET) {
                                          green;
                                  }
                                  else {
                                          yellow;
                                  }
                           }
                   }
           } else {
                   msg "slave  STOPPED                                              ($esclaveSql) &red";
                   red;
           }
           if($error ne "") {
                   red;
                   $error =~ s/$legalERR/ /go;
                   msg "slave  ERROR    $error";
           }
    }
    msg(sprintf("master T2      at % 40s  (%s)", $positionMaitre_t2, $maitreSql));

    if ($COLOR eq "green") {head "Mirroring OK"} else {head "Mirroring FAILURE"}
    sendreport;
    resetreport;
}
exit 0;
###############
###############
########################
conffile has format :
node;masterhost;slavehost1[,slavehost2,...];user;pass
(so it works in multiple slave setups)
example:
authcluster;auth-sql01.priv.com;auth-sql02.priv.com;monitor;coUIc
radius;rad-sql01.priv.com;rad-repsql01.priv.com,rad-backup01.priv.com;monitor;coUIc
add comment lines with '#'
sub readConfig
{
my ($node, $master, $slaves, $user, $pass, @conf, $line);
if (! open(CONF, "$conffile")) {
head("monitoring error");
msg("Erreur à l'ouverture de $conffile : $!");
red;
sendreport;
exit 1;
}
@conf = <CONF>;
close(CONF);
foreach $line (@conf) {
if (substr($line, 0, 1) eq "#") { next }
elsif (substr($line, 0, 1) eq "\n") { next }
else {
($node, $master, $slaves, $user, $pass) = split(/\s*;\s*/, $line, 5);
if ($pass) {
chomp $pass;
$auth{$node}{master} = $master;
foreach my $slave (split(",", $slaves)) {
$auth{$node}{slaves}{$slave}{pos} = undef;
$auth{$node}{slaves}{$slave}{running} = "not tested";
}
$auth{$node}{user}   = $user;
$auth{$node}{pass}   = $pass;
} else {
msg("&red invalid config : $line");
red;
}
}
}
#print Dumper \%auth;
}
Retourne les infos relatives au sql maitre
<- (Position);
sub getSqlMasterStatus
{
my ($host,$user,$pass,$base) = @_;
my $port;
($host, $port) = split(/:/, $host, 2);
if(not defined $port) {
$port = 3306;
}
my $dbLink = DBI->connect("DBI:mysql:$base:$host:$port:mysql_connect_timeout=10","$user","$pass");
if (! $dbLink) {
$ERR = $DBI::errstr;
red;
return (undef);
}
my $query = $dbLink->prepare("SHOW MASTER STATUS");
if (! $query->execute()) {
$ERR = $DBI::errstr;
red;
return (undef);
}
my @results = $query->fetchrow_array;
return "$results[0]:$results[1]";
}
Retourne les infos relatives au sql slave
<- (Exec_Master_Log_Pos, Slave_IO_Running&&Slave_SQL_Running)
sub getSqlSlaveStatus
{
my ($host,$user,$pass,$base) = @_;
my ($port, $slaverunning);
($host, $port) = split(/:/, $host, 2);
if(not defined $port) {
$port = 3306;
}
my $dbLink = DBI->connect("DBI:mysql:$base:$host:$port:mysql_connect_timeout=10","$user","$pass");
if (!$dbLink) {
red;
$ERR = $DBI::errstr;
return (undef);
}
my $query = $dbLink->prepare("SHOW SLAVE STATUS");
$query->execute();
if (! $query->execute()) {
$ERR = $DBI::errstr;
red;
return (undef);
}
my @results = $query->fetchrow_array;
## sloppy but works :-/
## mysql 3.23
if ((scalar @results) == 12) {
return ("$results[4]:$results[5]",$results[6], $results[10]);
}
## mysql 4.0
elsif ((scalar @results) == 18) {
if($results[9] eq "Yes" and $results[10] eq "Yes") {
$slaverunning = "Yes"
}
else {
$slaverunning = "No";
}
return ("$results[8]:$results[16]", $slaverunning, $results[14]);
    }
    ## mysql 4.1
    elsif ((scalar @results) == 33) {
           if($results[10] eq "Yes" and $results[11] eq "Yes") {
                   $slaverunning = "Yes"
           }
           else {
                   $slaverunning = "No"
           }
           return ("$results[9]:$results[21]", $slaverunning, $results[19]);
    }
    ## mysql x.q ?
    else {
           red;
           msg("Unsupported mysql version");
           return (undef, "unknown");
    }
}
###################
###################

send the report

sub sendreport
{
$MACHINE =~ s/\./,/g;
my $date = localtime;
my $cmd = "$BB $BBDISP \"status $MACHINE.$TESTNAME $COLOR $date $HEAD\n$DATA\n$MSG\"";
if($DEBUG == 1) {
print "$cmd\n";
}
else {
system($cmd);
}
}
sub resetreport
{
$MSG = $DATA = $HEAD = '';
$COLOR = 'clear';
}
sets the global color of the test
prevents downgrading severity
clear == green < yellow < red
sub setcolor
{
my $newcolor = shift;
if ($newcolor eq "red") {
$COLOR = "red";
} elsif ($COLOR eq "green" or $COLOR eq "clear") {
$COLOR = "$newcolor";
}
    return $COLOR;
}
sub clear  { setcolor 'clear'  }
sub green  { setcolor 'green'  }
sub yellow { setcolor 'yellow' }
sub red    { setcolor 'red'    }
sub data
{
my ($n, $v) = @_;
$DATA .= "$n: $v\n";
}
sub head
{
$HEAD = "@_";
}
sub msg
{
$MSG .= join("\n", @_) . "\n";
}

Question about MySQL Replication Script

deger＠cobercafe.net

mysql replication monitoring

generic attempt

The Fine Manual :

You can always use $MSG, $HEAD, $DATA and $COLOR directly.

Put your code here

What you need :

on the remote masters and slaves, create:

for mysql 4.x : grant replication client on . to 'user'@'monitorhost' identified by 'password';

grant select on test.* to 'user'@'monitorhost' identified by 'password';

for mysql 3.x : grant process on . to 'user'@'monitorhost' identified by 'password';

grant select on test.* to 'user'@'monitorhost' identified by 'password';

conffile has format :

node;masterhost;slavehost1[,slavehost2,...];user;pass

(so it works in multiple slave setups)

example:

authcluster;auth-sql01.priv.com;auth-sql02.priv.com;monitor;coUIc

radius;rad-sql01.priv.com;rad-repsql01.priv.com,rad-backup01.priv.com;monitor;coUIc

add comment lines with '#'

Retourne les infos relatives au sql maitre

<- (Position);

Retourne les infos relatives au sql slave

<- (Exec_Master_Log_Pos, Slave_IO_Running&&Slave_SQL_Running)

send the report

sets the global color of the test

prevents downgrading severity

clear == green < yellow < red

tags

participants (1)