What We Do
We provide top quality services and best solutions based on your requirements and needs.
Share

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.


MXAE Release News

MXAE 2.7.5 Released
We would like to announce the immediate availability of MXAE 2.7.5. This release focuses on...




Tips & Tricks

CSS Conditional Comments on MXAE
Conditional comments make it easy for developers to take advantage of the enhanced features...




MXAE Free Hosting

We are offering FREE hosting on MXAE platform with no string attached. You can use it as a development or demo hosting for your company. You can also use it to build your own website or host your client website.