Script To Check If MySQL Master Master Replication Is Working Correctly

Want to support HowtoForge? Become a subscriber!
 
Submitted by marchost (Contact Author) (Forums) on Tue, 2008-09-09 13:14. :: MySQL

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" admin@example.com < /root/slave_problem.txt
fi

Copyright © 2008 Marc
All Rights Reserved.
add comment | view as pdf view as pdf | Display a printer-friendly version of this page. print
Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Here is a script that improves upon this
Submitted by tester321 (registered user) on Tue, 2012-04-24 13:58.

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)


reply | view as pdf view as pdf
Check both slave IO and SQL running
Submitted by Jarvis (not registered) on Wed, 2011-09-14 20:19.
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".
reply | view as pdf view as pdf
idea is good but implementation is ... not so good
Submitted by Falko (not registered) on Thu, 2010-09-23 09:34.

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. 

reply | view as pdf view as pdf
this is what I am looking for but...
Submitted by pakogah (registered user) on Thu, 2008-09-11 10:54.

    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

reply | view as pdf view as pdf