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.
Step6: Login to the MySQL slave server as root via ssh.
Step7: Create one file for replication check script.
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.
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.