As many other features, mySQL does not provide « out of the box » script to monitor the replication.
Here we are going to see how to put in place a shell script that will poll our server any minute and send an alert if the slave is not replicating anymore.
What can we use ?
- mySQL provides a command
SHOW SLAVE STATUS
that gives several informations about the status of a slave
- The most important are Slave_IO_Running and Slave_SQL_Running which basically tell us that everything is fine if they are both on « Yes » and something wrong is happening if one is on « No »
- If one is on « No », this is likelly because there were an error in replication which is shown under the name Last_error
We can use the script of Matthew Montgomery that you can find online here : Check Replication Slave Status (or download directly check_slave.zip from here).
The script is very simple to use, it verifies that mySQL is running, ask for Slave_IO_Running and Slave_SQL_Running, if one is on « No », asks for Last_error and render this to the standard output.
So we copy the content of the script in a file called check_slave.sh that we put somewhere under /home (I use /home/scripts for all these kind of shells).
/usr/bin/wget \ http://blog.grossiord.net/wp-content/uploads/2011/07/check_slave.zip \ --output-document="/home/scripts/check_slave.zip" /usr/bin/unzip /home/scripts/check_slave.zip rm /home/scripts/check_slave.zip
We chmod +x the file to be able to execute it.
chmod +x /home/scripts/check_slave.sh
We try to run « mysql » command directly to ensure that we are able to silently login to mysql. If this is not the case, this means that we need to add or update the password on ~/.my.cnf file. Exit mysql.
Run check_slave.sh, it should return nothing if everythig is fine.
/home/scripts/check_slave.sh
Stop the slave :
mysql -e "stop slave;"
Run check_slave.sh,
/home/scripts/check_slave.sh
it should return lines like
SQL thread not running on server xxxx! Last Error:
Restart the slave :
mysql -e "start slave;"
Run check_slave.sh,
/home/scripts/check_slave.sh
it should return lines like
Replication slave is running Removed Alert Lock
Everything seems to be well configured so we can put this execution in the crontab. I make it run every minute.
echo "*/1 * * * * root /home/scripts/check_slave.sh" \ >> /etc/crontab
which means that the script will run every minute, under the login « root ». If the local email is correctly configured, you will receive an email each time the replication has issues or it come back online.
NB. The script includes a flag mechanism that avoid it to send you an email every minute to tell you that the slave is out. You can parameter the time of the flags in the script on the line
repeat_alert_interval=15 # minutes
And this is it !
Have fun