[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [hobbit] MySQL replication status checker

To: hobbit (at) hswn.dk
Subject: Re: [hobbit] MySQL replication status checker
From: Charles Goyard <charles.goyard (at) orange-ftgroup.com>
Date: Tue, 25 Sep 2007 09:49:46 +0200
References: <46F847B3.7090901 (at) cisco.com>
User-agent: Mutt/1.5.13 (2006-08-11)

Hi,

Charles Jones wrote :
> I just got bit hard by a MySQL replication error, so I'm about to write 
> a Hobbit client-side ext script that will check the local servers slave 
> status (CHECK SLAVE STATUS \G), parse the output, and alert 
> appropriately (red if Slave Running = No, or if Seconds Behind Master 
> >300, yellow if Seconds Behind Master <300 but > 60...otherwise green).

I have one, and I believe there's one included in the perl script
package (already on the shire).

However, here's mine. It runs on the server side, so you need to give
access to a specific user.

There's an obvious improvement to do: the slave status shows on the
master line (column mysqlrep). It should show on the slave's line too,
and the master replication status should stay green if a slave is blue
(that is, a small dependency check).

Regards,

-- 
Charles Goyard - charles.goyard (at) orange-ftgroup.com - (+33) 1 45 38 01 31
Orange Business Services - online multimedia  // ingénierie

#!/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";
}

References:
- MySQL replication status checker
  - From: Charles Jones

Prev by Date: MySQL replication status checker
Next by Date: RE: [hobbit] Sending Data from external script to an RRD - can't see graphs - only links
Previous by thread: MySQL replication status checker
Next by thread: RE: [hobbit] Sending Data from external script to an RRD - can't see graphs - only links
Index(es):
- Date
- Thread