Script To Check If MySQL Master Master Replication Is Working Correctly

This short article explains how you can use a short script to check whether your MySQL master master replication is working as expected or not.

First add a user named "check" with the following mysql queries:

CREATE USER 'check'@'localhost' IDENTIFIED BY 'YOURPASSWORD';

GRANT SUPER , REPLICATION CLIENT ON * . * TO 'check'@'localhost' IDENTIFIED BY 'YOURPASSWORD' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

Then add this bash script to your /root directory. Add it to your crontab if needed :

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

###check if already notified###
cd /root
if [ -f slave_problem.txt ]; then
exit 1;
fi

###Check if slave running###
(
echo "show slave status \G;"
) | mysql -u check -pYOURPASSWORD 2>&1 | grep "Slave_IO_Running: No"
if [ "$?" -ne "1" ]; then
echo "Replication failed" > /root/slave_problem.txt
fi

###Send notification if replication down###
cd /root
if [ -f slave_problem.txt ]; then
mail -s "Replication problem" [email protected] < /root/slave_problem.txt
fi
Share this page:

4 Comment(s)

Add comment

Comments

From:

    there are 2 parameter we need to watch and make sure that replication is running, 1 is Slave IO (the replication engine) and another is Slave SQL,

the Slave IO can  unable to run when we enter the wrong master/ slave paramater like replication user, etc...

 and Slave SQL can unable to run when some sql syntax is replicated to slave but found an error like duplicated key...

 the slave IO still running but when slave sql stopped, your slave will no longer have the same data with your master anymore...

 so you should add another parameter to check Slave_SQL_Running running or not

 best regards

From: Falko

Hi,

 the basic ideas of the script are right. But the implementation is not. The script is only complaining if the string "Slave_IO_Running: No" is matched in the output. That means we're not checking if the Slave is running correctly but rather checking if the Slave is explicitly not running. Any other error message, like password wrong or database not running would be ignored by this script.

 This script should check for Slave_IO_Running: Yes. and only if the is output is present, no notification is send. Additionally it should check for the Replication SQL as well as mentioned in the comment before.

 

From: Jarvis

I just ran into the situation described by the previous comments - the slave IO was running but the slave SQL was not. To fix, I simply changed the script to grep for the string "Slave_.*_Running: No".

From:

Here is a script that improves upon this:

1) Checks for Slave_IO_Running Yes and Slave_SQL_Running Yes

2) Checks for Crashed MySQL Tables

3)  Ties in to Monit for alerting

http://www.widwad.com/content/automated-monitoring-of-mysql-replication-using-monit-how-i-did-it

(It also provides a link back to this article)