MySQL replication status alerts with bash script

MySQL replication should work without problems even if the servers are restarted, databases created and removed, data flowing in and out. Sometimes, however, there are errors that are breaking the replication process. It is good to know once such situation appears.

Please find the bash script below which gives the alerts when replica lags or stops.

#!/bin/bash
#
# Set the maximum number of seconds behind master that will be ignored. 
# If the slave is be more than maximumSecondsBehind, an email will be sent. 
#
maximumSecondsBehind=300
#
# Checking MySQL replication status...
#
mysql -u replicationStatusLocal -p'password' -e 'SHOW SLAVE STATUS \G' | grep 'Running:\|Master:\|Error:' > replicationStatus.txt
#
# displaying results, just in case you want to see them 
#
echo "Results:"
cat replicationStatus.txt
#
# checking parameters
#
slaveRunning="$(cat replicationStatus.txt | grep "Slave_IO_Running: Yes" | wc -l)"
slaveSQLRunning="$(cat replicationStatus.txt | grep "Slave_SQL_Running: Yes" | wc -l)"
secondsBehind="$(cat replicationStatus.txt | grep "Seconds_Behind_Master" | tr -dc '0-9')"
#
# Sending email if needed
#
if [[ $slaveRunning != 1 || $slaveSQLRunning != 1 || $secondsBehind -gt $maximumSecondsBehind ]]; then
  echo ""
  echo "Sending email"
  mail -s "MyServer.com - replication issue found" my@email.com < replicationStatus.txt 
else
  echo ""
  echo "Replication looks fine."
fi

You can adjust the maximumSecondsBehind to the value of your choice. If you will notice that the alert is triggered too frequently, you can change this value to a higher number. You should also set the proper password in the MySQL execution string. As the last step, you should adjust an email subject and address in mail execution part.