MySQL Replication Health Check script

Today in this article we will get an idea about how to monitor MySQL replication. It is today’s requirement to setup replication in your environment but is it enough? My answer is ‘NO’. We need to monitor our replication from time to time. I wrote a small script to monitor our environment, which I am going to share with you guys to make your life as easy as it made mine.

Things that need to monitor on your slave side:

LAST_ERRNO
SECONDS_BEHIND_MASTER
IO_IS_RUNNING
SQL_IS_RUNNING
MASTER_LOG_FILE
RELAY_MASTER_LOG_FILE

These all variables are the part of ‘show slave status’

Save the below script in .sh file and execute is as below:

./mysqlhealthcheck.sh

MYSQL_CHECK=$(./mysql-uUsername -ppassword -e "SHOW VARIABLES LIKE '%version%';" || echo 1)
#echo $MYSQL_CHECK
STATUS_LINE=$(./mysql-uUsername -ppassword -e "SHOW SLAVE STATUS\G")"1"
LAST_ERRNO=$(grep "Last_Errno" <<< "$STATUS_LINE" | awk '{ print $2 }')
SECONDS_BEHIND_MASTER=$( grep "Seconds_Behind_Master" <<< "$STATUS_LINE" | awk '{ print $2 }')
IO_IS_RUNNING=$( grep "Slave_IO_Running" <<< "$STATUS_LINE" | awk '{ print $2 }')
SQL_IS_RUNNING=$(grep "Slave_SQL_Running" <<< "$STATUS_LINE" | awk '{ print $2 }')
MASTER_LOG_FILE=$(grep " Master_Log_File" <<< "$STATUS_LINE" | awk '{ print $2 }')
RELAY_MASTER_LOG_FILE=$(grep "Relay_Master_Log_File" <<< "$STATUS_LINE" | awk '{ print $2 }')
ERRORS=()
MESSAGE="NO ERROR"
bold=$(tput bold)
normal=$(tput sgr0)
echo "${bold}MYSQL_CHECK : ${normal} $MYSQL_CHECK "
echo "${bold}LAST_ERRNO : ${normal} $LAST_ERRNO "
printf "\n"
echo "${bold}SECONDS_BEHIND_MASTER : ${normal} $SECONDS_BEHIND_MASTER"
printf "\n"
echo "${bold}IO_IS_RUNNING : ${normal} $IO_IS_RUNNING"
printf "\n"
echo "${bold}SQL_IS_RUNNING : ${normal} $SQL_IS_RUNNING"
printf "\n"
echo "${bold}MASTER_LOG_FILE : ${normal} $MASTER_LOG_FILE"
printf "\n"
echo "${bold}RELAY_MASTER_LOG_FILE : ${normal} $RELAY_MASTER_LOG_FILE"
printf "\n"
### if there is an error ###
if [ "${#ERRORS[@]}" -gt 0 ]
then
MESSAGE="An error has been detected involving the mysql replciation. Below is a list of the reported errors:\n\n
$(for i in $(seq 1 ${#ERRORS[@]}) ; do echo "\t${ERRORS[$i]}\n" ; done)
Please correct this ASAP
"
echo -e $MESSAGE 
else 
echo -e $MESSAGE
fi
OUTPUT:
MYSQL_CHECK : Variable_name Value
innodb_version 1.1.8
protocol_version 10
slave_type_conversions
version 5.5.19-enterprise-commercial-advanced-log
version_comment MySQL Enterprise Server - Advanced Edition (Commercial)
version_compile_machine i686
version_compile_os linux2.6 
LAST_ERRNO : 0
SECONDS_BEHIND_MASTER : 0
IO_IS_RUNNING : Yes
SQL_IS_RUNNING : Yes
MASTER_LOG_FILE : mysql-bin.000007
RELAY_MASTER_LOG_FILE : mysql-bin.000007
NO ERROR

you can also add this script into your cron jobs and sending the “echo -e $MESSAGE” part to your self by email.

 231 views last month,  1 views today

About Mughees Ahmed

Over 2-year experience of Administration in Oracle, SQL Server, and MySQL databases using various tools and technologies. Keen on learning new database technologies having very good analytical skills. Working knowledge of Red Hat Linux, UNIX, Solaris and Windows Server 2012 is a valuable addition to my knowledge desk. KNOWLEDGE & SKILLS • Oracle Database Server 10g/11g/12c. • Oracle RAC, Data guard. • Oracle Golden Gate (Oracle to Oracle, Oracle to MySQL and MySQL to Oracle) • Oracle Enterprise Manager Grid Control, Toad, SQL developer. • SQL Server 2005/2008/2012/2016. • SQL Server Failover clustering, mirroring & log shipping, Always On availability groups. • MySQL 5 Administration, MySQL Workbench, MySQL Enterprise Monitor, SQLyog • MySQL NDB Cluster Installation,Administration. • MySQL Asynchronous/Semi-synchronous replication. • Oracle OEM Contact me on mughees52@gmail.com

Check Also

Roll Back to Traditional Replication From GTID

If you want to roll back to old : # Now, we are going to …

Leave a Reply