User Tools

Site Tools


mysql_replication_status

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

mysql_replication_status [2020/08/10 02:35] (current)
Line 1: Line 1:
 +====== Replication Status ======
  
 +NOTE : This script is not yet ready.
 +
 +<code perl | my_status.pl>​
 +#​!/​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 < $OKOFFSET) {
 + green;​
 + }
 + else {
 + yellow;​
 + }
 + }
 + else {
 + $masterfile =~ s/​.+\.(\d+)$/​$1/;​
 + $slavefile ​ =~ s/​.+\.(\d+)$/​$1/;​
 + if($masterfile - $slavefile != 1) {
 + yellow;​
 + }
 + elsif($masteroffset < $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";​
 +}
 +</​code>​
mysql_replication_status.txt · Last modified: 2020/08/10 02:35 (external edit)