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