MySQL Replication Monitoring

Database replication is a process of mirroring the data from 1 master location to 1 or more slave locations and at the same time, to ensure the data is consistent among these locations.

Database replication deployment is to facilitate high availability and load balancing where load from the master can be distributed accordingly to the slave(s) database. Basically is just like having a shadow to share the work load.

One feature missing from MySQL replication is that it does not have a built-in notification feature to inform administrator of the replication status. Unless if you are willing to go with a commercial product for MySQL replication monitoring, then you have to manually check the replication status regularly through MySQL command ‘show slave status’. As of standard MySQL replication deployment, there is no standard tool to inform you when the replication system stops.

MySQL replication monitoring script

In order to solve the issue above, a shell script is built to monitor the MySQL replication. This script will check the replication status and send an email notification out to a designated recipient or list of recipients should it detects that MySQL replication stops. This script can be scheduled to run on a desired frequency (eg. every 2 minutes) via a scheduler such as cron.

The shell script works as such:

#!/bin/sh

Set variable.

STATUS=STARTED

Remove log every time script execute to ensure only latest content are stored and send through email.

/bin/rm -rf /root/mailcontent.log

It’s time to check MySQL Replication status. The following lines of codes grab value for variable “Slave_IO_Running” and “Slave_SQL_Running” which can be either ‘Yes’ or ‘No’.

IOSLAVEALIVE="$(/usr/local/mxae/mysql/bin/mysql -u –p -h -e "SHOW SLAVE STATUS \G" | grep Slave_IO_Running | gawk -F: '{ print $2 }')"   SQLSLAVEALIVE="$(/usr/local/mxae/mysql/bin/mysql -u -p -h -e "SHOW SLAVE STATUS \G" | grep Slave_SQL_Running | gawk -F: '{ print $2 }')"

What is the result? If either variable $IOSLAVEALIVE or $SQLSLAVEALIVE contains the value ‘No’, then it will flag MySQL replication status as stopped/down.

if [ $IOSLAVEALIVE = "No" -o $SQLSLAVEALIVE = "No" ]; then         STATUS=STOPPED Fi

Write down the status MySQL replication on mailcontent.log

echo "Replication is $STATUS" >> ~/ mailcontent.log

If status of MySQL replication is STOPPED, an email will be sent to notify database administrator or designated recipient(s).

if [ $STATUS = "STOPPED" ]; then        /bin/mailx -s "WARNING: MySQL replication status" < ~/ mailcontent.log Fi

Complete code

#!/bin/sh   STATUS=STARTED rm -rf /root/mailcontent.log   IOSLAVEALIVE="$(/usr/local/mxae/mysql/bin/mysql -u –p -h -e "SHOW SLAVE STATUS \G" | grep Slave_IO_Running | gawk -F: '{ print $2 }')"   SQLSLAVEALIVE="$(/usr/local/mxae/mysql/bin/mysql -u -p -h -e "SHOW SLAVE STATUS \G" | grep Slave_SQL_Running | gawk -F: '{ print $2 }')"   if [ $IOSLAVEALIVE = "No" -o $SQLSLAVEALIVE = "No" ]; then         STATUS=STOPPED fi   echo "Replication is $STATUS" >> ~/ mailcontent.log   if [ $STATUS = "STOPPED" ]; then        /bin/mailx -s "WARNING: MySQL replication status" < ~/ mailcontent.log Fi

Our last step to do is creating a cronjob entry to run this script on a regular basis. Example below is to run this script every hour on the hour.

0 * * * * sh /path/to/file/monitor.sh > /dev/null

How to test?

It’s easy. You can stop MySQL replication and execute the script above. You will get the email notification. In order for the email to be sent out please make sure your server’s sendmail or any alternative mail application is configured properly.