How to configure MySQL replication alerts using bash script

MySQL replication status can be checked by executing the following query.

>SHOW SLAVE STATUS \G;

This will show all the parameters related to replication including Slave_IO_Running, Slave_SQL_Running and Seconds_Behind_Master.

But this requires some manual effort and this article explains the way to automate this process.

Step1: Login to the MySQL master server as root via ssh.

Step2: Login to the MySQL terminal as root user.

#mysql -u root -p

This will ask for the password as standard input.

Step3: Create a user for replication check.

>CREATE USER 'replication-check'@'localhost' IDENTIFIED BY 'password';

Where password need to be replaced with the required password.

Step4: Assign the required privileges.

>GRANT SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication-check'@'localhost';

Step5: Reload the privileges.

>FLUSH PRIVILEGES;

Step6: Login to the MySQL slave server as root via ssh.

Step7: Create one file for replication check script.

#vi replication-check.sh

Step8: Update the following lines and save.

#!/bin/bash

#Threshold value for replication lag.
maximumSecondsBehind=300

# Checking MySQL replication status.
mysql -e 'SHOW SLAVE STATUS \G' | grep 'Running:\|Master:\|Error:' > replicationstatus.txt

#Checking the replication 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')"

#Send email if detect error or replica lag
if [[ $slaveRunning != 1 || $slaveSQLRunning != 1 || $secondsBehind -gt $maximumSecondsBehind ]]; then
    echo "Problem detected on replication"
    mail -s "Slave2 replication issue found" EMAIL < replicationStatus.txt 
else
    echo "Replication working fine."
fi

Where EMAIL needs to be replaced with the receiver email id for alerts.

Step9: Open the cronjobs.

#crontab -e

Step10: Create the cron to run the replication check script in every 5 minutes.

*/5 * * * * /bin/bash path-to-script

Where path-to-script need to be replaced with the full path to the file replication-check.sh.

That's all…