Monitor MySQL Binary Replication Slave Status (Nagios NRPE)

If you have one or many MySQL Replication Slaves, you may need a handy way to monitor each slaves’ status within your existing Nagios Monitoring Platform. This handy NRPE based bash script will help you out…

#!/bin/bash
# SQL Binary Replication Failure Detection      #
# Dave Byrne @ VooServers Ltd                   #
#################################################

#Is the Slave IO Running?
slaveio=`mysql -u root --password="passwordhere" -Bse "show slave statusG" | grep Slave_IO_Running | awk '{ print $2 }'`

#Is the Slave SQL Running?
slavesql=`mysql -u root --password="passwordhere" -Bse "show slave statusG" | grep Slave_SQL_Running | awk '{ print $2 }'`

#Pull the Last SQL Error just in case
lasterror=`mysql -u root --password="passwordhere" -Bse "show slave statusG" | grep Last_error | awk -F : '{ print $2 }'`

#Some friendly output to screen..
#echo ""
#echo "Slave IO Running? ... "$slaveio
#echo "Slave SQL Running? ... "$slavesql
#echo "Last SQL Error:  "$lasterror
#echo ""

#Work out if its failed or not..
if [ '$slavesql' == 'No' ] || [ '$slaveio' == 'No' ];
then
  #Its failed, go CRITICAL
  echo "Slave IO Running? ... "$slaveio
  echo "Slave SQL Running? ... "$slavesql
  echo "Last SQL Error:  "$lasterror
  echo "CRITICAL - MySQL Replication Failure!"
  exit 2
else
  #Its good, go OK
  echo "OK - MySQL Replication Running"
  exit 0
fi

Notes:
– Enter your MySQL Root users password where applicable.
– If either the Slave IO or the Slave SQL stops running, the check will return CRITICAL in Nagios.
– Does not require SUDO action, run straight from nrpe.cfg
– Could be made prettier, and could also output to shell when run locally if I had more time.. Sue me.