This shows you the differences between two versions of the page.
— |
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> |