Checking MySQL Replication – Brandon Checketts

MySQL replication is pretty easy to set up, but needs a few extra things to make it more reliable. I wrote this quick PHP script to alert me when replication has failed and is more than 5 minutes behind the master.

<?php

$user = 'username';
$pass = 'password';
$host = 'localhost';
// Grant this user permission to check the status with this mysql statement
// GRANT REPLICATION CLIENT on *.* TO 'user'@'host' IDENTIFIED BY 'password';

$threshold = 300;

$db = mysql_connect($host, $user, $pass);

$result = mysql_query('SHOW SLAVE STATUS');
if (!$result) {
    // Make sure that your user has the 'REPLICATION CLIENT' privlege
    echo "Error 'SHOW SLAVE STATUS' command failed\n";
    echo mysql_error()."\n";
    exit(1);
}

$status = mysql_fetch_array($result);

if (!isset($status['Seconds_Behind_Master'])) {
    echo "Error: Seconds_Behind_Master column not found in result\n";
    print_r($status);
    exit(2);
}

if ($status['Seconds_Behind_Master'] > $threshold) {
    $minutes = floor($status['Seconds_Behind_Master'] / 60);
    echo "Error: Slave is $minutes minutes behind the master server\n";
    exit(3);
}

exit(0);
?>

This script is intended to be run periodically from cron. It doesn’t generate any output unless something is wrong. The behavior of cron is that when a script generates output, it will email the output to the user, so make sure that you have mail on your system configured to send you the cron output correctly. The script also exits with a non-zero status on each error, so you might include this in a more complicated script that attempts to do something else based on the status.

I use something like this in a non-privileged user’s crontab:

*/15 * * * /usr/bin/php /path/to/check_replication.php

3 thoughts on “Checking MySQL Replication”

Pingback: Brandon Checketts » Blog Archive » Compression for MySQL Replication

Here is the same basic script written in Perl (because I found a machine that had a completely broken PHP installation that I didn’t want to troubleshoot at the time)

#!/usr/bin/perl

$user = 'someuser';
$pass = 'somepassword';
$host = 'localhost';

$threshold = 600;

use DBI;
use Data::Dumper;

$dbh = DBI->connect("DBI:mysql:mysql", $user, $pass
                   ) || die "Could not connect to database: $DBI::errstr";


$sth = $dbh->prepare('SHOW SLAVE STATUS');
$sth->execute();
$result = $sth->fetchrow_hashref();

if (!$result->{'Seconds_Behind_Master'}) {
        print "MySQL replication seems to be broken.    Here is the output of SHOW SLAVE STATUS\n";
        print Dumper($result);
}

if ($result->{'Seconds_Behind_Master'} >= $threshold) {
        $minutes = $result->{'Seconds_Behind_Master'} / 60;
        printf("ERROR: MySQL Slave is %i minutes behind the master server\n", $minutes);
}


exit;

Pingback: Brandon Checketts » Blog Archive » Fixing a Corrupt MySQL Relay Log

3 thoughts on “Checking MySQL Replication”

Leave a Reply