MySQL Replcation Monitoring?

[jascha]

I was wondering if anyone has found a way to have Zabbix monitor the status of MySQL replication? We have a Master<>Master setup and want to know if for some reason replication breaks. Thanks for any help or pointing in the right direction.

[abi]

hi,

were doing master -> slave synchronisation. Im running a perl script
from cron which does a short connect to the database and parses
the slave status table:

#!/usr/bin/perl

use strict;
use DBI();

# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost",
                       "user", "password",
                       {'RaiseError' => 1});

my $sth = $dbh->prepare("SHOW SLAVE STATUS");
$sth->execute();
my $ref = $sth->fetchrow_hashref();
$sth->finish();


if( $ref->{Slave_SQL_Running} eq 'No' ||
        $ref->{Slave_IO_Running} eq 'No' ||
        $ref->{Slave_Running} eq 'No') {

        print "MySQL Slave Synchronisation is not running.";
        print "\nWarnung:".$ref->{Last_error} if $ref->{Last_error};
}

$dbh->disconnect();

i think you can adjust this script in order to check the master replication,
too. You could then run it from zabbix (using a UserCommand and return
either 1 or 0, and then set a trigger for it.

Another thing im doing is to have a test table on the master, and one
test table on the slave. A script on the master periodically writes an
timestamp to the table and then, a script on the slave (which does also
run periodically) reads this timestamp from the table and checks the
time difference, if this one is too high (more than an hour) somethings
wrong with the sync and im getting email aswell.

[s0enke]

Here is a shorter one I wrote without having to use perl/.../... (just shell):

UserParameter=mysql.slave_sql_running,mysql -u monitoring -e "show slave status" -E | grep Slave_SQL_Running | cut -d: -f2 | sed "s/\s//g" | sed "s/Yes/1/" | sed "s/No/0/"

It just checks the "Slave_SQL_Running" value and turns Yes/No into 1/0 values.

This, of course, can be extended to the other values, too.

Hope that helps!