Shell script to check MySQL Replication Status

Working as MySQL DBA I’ve faced the MySQL replication issue almost every other day, I would say one of the weak point of MySQL RDBMS is the replication and replication down/delay is one of the most common error which can be seen in MySQL replication setups.

Let’s discuss how as a DBA or as site owner you can create a simple script which will send an alert once we have any issue with the replication.

Generally we use the below command to check the replication status:

show slave status\G

There are 3 things which we need to get to know from this:

  1. Slave_IO_Running
  2. Slave_SQL_Running
  3. Seconds_Behind_Master

Now we can create a simple script which will tell if there is any issue with our replication setup.

Shell Script to check the MySQL Replication Status:

RUNDT=`date +%m%d%Y_%H%M`
hostname=`hostname`
password="your_password"
port=3306
threshold=500

Last_IO_Error=`mysql -uadmin -p$password -P $port -e"show slave status\G"|grep -i Last_IO_Error|awk -F ":" '{print $2}'|sed 's/ //g'`
Last_SQL_Error=`mysql -uadmin -p$password -P $port -e"show slave status\G"|grep -i Last_SQL_Error|awk -F ":" '{print $2}'|sed 's/ //g'`
Last_Error=`mysql -uadmin -p$password -P $port -e"show slave status\G"|grep -i Last_Error|awk -F ":" '{print $2}'|sed 's/ //g'`
Seconds_Behind_Master=`mysql -uadmin -p$password -P $port -e"show slave status\G"|grep -i Seconds_Behind_Master|awk -F ":" '{print $2}'|sed 's/ //g'

if [ -z "$Last_IO_Error" ] && [ -z "$Last_SQL_Error" ] && [ -z "$Last_Error" ]
then
echo "fine"
else
mail -s "replication not working for server $hostname for  $RUNDT" admin@proprogramming.org </dev/null
fi

if [ "$Seconds_Behind_Master" -gt "$threshold" ]
mail -s "Replication lag is greater than 500 on server $hostname for $RUNDT" admin@proprogramming.org </dev/null
fi

This way you will get an alert for 2 conditions:

  1. When the replication is down alert will have the errors.
  2. When replication lag is greater than 500 seconds.

Let me know in case you have face any error or in case you want to help by improving the script. Thank you.

Leave a Comment