เขียน php script ง่ายๆ สั่งให้ทำงานด้วย cron เพื่อ monitor status ของ mysql master-slave replication และถ้ามี error ก็ส่ง mail แจ้งผู้เกี่ยวข้อง พร้อมด้วยผลของ mysql command SHOW SLAVE STATUS

<?php

#--------------------------------------------#
# mysql replication slave monitoring & alert #
#--------------------------------------------#

$now = date('l jS \of F Y h:i:s A');

$mailname = ':: Name via RT ::';
$mailfrom = 'root@slavehost.mydomain';
$replyto = 'replyto@mydomain';
$mailto = 'to@mydomain';
$mailcc = 'cc@mydomain';
$mailbody = '';
$subject = '[mon@slavehost] Error: MySQL-Slave-Replication failed';
$header = "From: ". $mailname . " <" . $mailfrom . ">\r\n" .
"Cc: ". " <" . $mailcc . ">\r\n" .
"Reply-To: ". " <" . $replyto . ">\r\n" .
"X-Mailer: PHP/". phpversion();

$dbhost = 'localhost';
$dbuser = 'myuser';
$dbpass = 'mypass';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql\n');

$query = "show slave status";
$result = mysql_query($query) or die('Error, select query failed\n');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
mysql_close($conn);
if( $row['Slave_IO_Running'] == 'No' || $row['Slave_SQL_Running'] == 'No' ) {

$mailbody =
"$now\n\n" .
"Event on slavehost.mydomain\n\n" .
"SHOW SLAVE STATUS\n" .
"-----------------\n" .
"Slave_IO_State: {$row['Slave_IO_State']}\n" .
"Master_Host: {$row['Master_Host']}\n" .
"Connect_Retry: {$row['Connect_Retry']}\n" .
"Master_Log_File: {$row['Master_Log_File']} -----(*)\n" .
"Read_Master_Log_Pos: {$row['Read_Master_Log_Pos']} -----(*)\n" .
"Relay_Log_File: {$row['Relay_Log_File']}\n" .
"Relay_Log_Pos: {$row['Relay_Log_Pos']}\n" .
"Relay_Master_Log_File: {$row['Relay_Master_Log_File']}\n" .
"Slave_IO_Running: {$row['Slave_IO_Running']} -----(*)\n" .
"Slave_SQL_Running: {$row['Slave_SQL_Running']} -----(*)\n" .
"Replicate_Do_DB: {$row['Replicate_Do_DB']}\n" .
"Replicate_Ignore_DB: {$row['Replicate_Ignore_DB']}\n" .
"Last_Errno: {$row['Last_Errno']} -----(*)\n" .
"Last_Error: {$row['Last_Error']} -----(*)\n" .
"Skip_Counter: {$row['Skip_Counter']}\n" .
"Exec_Master_Log_Pos: {$row['Exec_Master_Log_Pos']}\n" .
"Relay_Log_Space: {$row['Relay_Log_Space']}\n" .
"Until_Condition: {$row['Until_Condition']}\n" .
"Until_Log_File: {$row['Until_Log_File']}\n" .
"Until_Log_Pos: {$row['Until_Log_Pos']}\n" .
"Seconds_Behind_Master: {$row['Seconds_Behind_Master']} -----(*)\n";
mail($mailto, $subject, $mailbody, $header);
}

?>