Subscribe by RSS>> Fail-Safe Replication Monitor
I am a big believer in the “belt and suspenders” approach to monitoring. Monitoring the status of database replication can certainly be done from an administrative application, but sometimes you just want to be “extra sure” things are working.
The script below is a very simple .php page that serves two valuable purposes and is easy to deploy:
- It verifies that data from the master is actually making it into the slave.
- It provides diagnostic information when an alert is triggered.
The code below assumes a mysql database…but this approach would work in any situation where you use your scripting language of choice to connect to your database of choice and execute these simple sql commands. Set up a read-only database user so the script cannot be abused to do more than simply select data.
<html>
<head><title>Database Replication Test</title></head>
<body>
<?php
$con = mysql_connect("10.10.10.101","reader","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db("YourDB", $con);
if (!$db_selected)
{
die ("Can't use YourDB database: " . mysql_error());
}
$sql = "select count(*) from hitlog";
$result1 = mysql_query($sql,$con);
$datarow1 = mysql_fetch_row($result1);
$master_count = $datarow1[0];
mysql_close($con);
$con = mysql_connect("10.10.10.102","reader","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db("YourDB", $con);
if (!$db_selected)
{
die ("Can't use YourDB database: " . mysql_error());
}
$sql = "select count(*) from hitlog";
$result2 = mysql_query($sql,$con);
$datarow2 = mysql_fetch_row($result2);
$slave_count = $datarow2[0];
mysql_close($con);
$delta = $master_count - $slave_count;
if ($master_count > 0 && $delta == 0) {
echo "<h1>SUCCESS</h1><p>Database Replication is working. Master database tracking table has " . $master_count . " rows, Slave has " . $slave_count . " rows.</p>";
} else {
echo "<h1>FAILURE</h1><p>Database Replication is not in sync. Master database tracking table has " . $master_count . " rows, Slave has " . $slave_count . " rows.</p>";
}
?>
</body></html>
Save the script above into a file such as “db_replication_check.php” and place it somewhere, prefarably not externally-accessible. Then set up a web page content monitor to load the page every 5 minutes and test for “SUCCESS.” When the page fails, you can load it manually in a web browser to see the alert message.
This is a very simplistic test, but the approach can be used in other situations where you would like to monitor data more precisely and/or lack confidence in the existing administrative tools you have. You could cron such a task and write another script to wget or curl the page…but why bother? Use your monitoring software to schedule and monitor this simple test; don’t write any more code than you have to!
![[Post to Twitter]](http://blog.heroix.com/wp-content/plugins/tweet-this/icons/tt-twitter-micro3.png){kind=icon}