Checking MySQL Replication

Posted on June 23rd, 2008 in General,Linux System Administration,PHP,Programming by Brandon

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 Responses to 'Checking MySQL Replication'

Subscribe to comments with RSS or TrackBack to 'Checking MySQL Replication'.


  1. on July 25th, 2008 at 7:02 pm

    [...] noticed today that the slave server was falling behind the master and had trouble keeping up. I noticed that there was a sizable amount of bandwidth between the two [...]

  2. Brandon said,

    on August 14th, 2008 at 3:32 pm

    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;
    

  3. on September 30th, 2008 at 1:10 pm

    [...] had to fix this several times in the past couple days. If that keeps up, I may add this fix to my Replication checking-script until I’m able to upgrade to a version of MySQL that contains this [...]

Post a comment

Please copy the string BVIKup to the field below: