Skip to content

Instantly share code, notes, and snippets.

@flickerfly
Forked from ssimpson89/MySQL Replication Check
Last active February 22, 2022 15:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 6 You must be signed in to fork a gist
  • Save flickerfly/c64888f3c4de28366869ec6010f94f96 to your computer and use it in GitHub Desktop.
Save flickerfly/c64888f3c4de28366869ec6010f94f96 to your computer and use it in GitHub Desktop.
Just a simple Mysql Replication Health Check script I wrote. You can put this in a cron.
#!/bin/bash
### VARIABLES ### \
EMAIL=flickerfly@email.com
SERVER=$(hostname)
MYSQL_CHECK=$(mysql -e "SHOW VARIABLES LIKE '%version%';" || echo 1)
SLAVE_STATUS=$(/usr/bin/mysql -e "SHOW SLAVE STATUS\G"|grep -v row)
LAST_ERRNO=$(echo "${SLAVE_STATUS}" | grep "Last_Errno:" | awk '{ print $2 }' )
SECONDS_BEHIND_MASTER=$(echo "${SLAVE_STATUS}" | grep "Seconds_Behind_Master:" | awk '{ print $2 }' )
IO_IS_RUNNING=$(echo "${SLAVE_STATUS}" | grep "Slave_IO_Running:" | awk '{ print $2 }' )
SQL_IS_RUNNING=$(echo "${SLAVE_STATUS}" | grep "Slave_SQL_Running:" | awk '{ print $2 }' )
SLAVE_IO_STATE=$(echo "${SLAVE_STATUS}" | grep "Slave_IO_State:" | awk -F':' '{gsub(/^[ \t]+/,"",$2);gsub(/[ \t]+$/,"",$2); print $2 }' )
ERRORS=()
#echo "${SLAVE_STATUS}"
### Run Some Checks ###
## Check if I can connect to Mysql ##
if [ "$MYSQL_CHECK" == 1 ]
then
ERRORS=("${ERRORS[@]}" "Can't connect to MySQL (Check Pass)")
fi
## Check For Last Error ##
if [ "$LAST_ERRNO" != 0 ]
then
ERRORS=("${ERRORS[@]}" "Error when processing relay log (Last_Errno = ${LAST_ERRNO})")
fi
## Check if IO thread is running ##
if [ "$IO_IS_RUNNING" != "Yes" ]
then
ERRORS=("${ERRORS[@]}" "I/O thread for reading the master's binary log is not running (Slave_IO_Running)")
fi
## Check for SQL thread ##
if [ "$SQL_IS_RUNNING" != "Yes" ]
then
ERRORS=("${ERRORS[@]}" "SQL thread for executing events in the relay log is not running (Slave_SQL_Running)")
fi
## Check how slow the slave is ##
if [ "$SECONDS_BEHIND_MASTER" == "NULL" ]
then
ERRORS=("${ERRORS[@]}" "The Slave is reporting 'NULL' (Seconds_Behind_Master)")
elif [ "$SECONDS_BEHIND_MASTER" -gt 60 ]
then
ERRORS=("${ERRORS[@]}" "The Slave is at least 60 seconds behind the master (Seconds_Behind_Master)")
fi
### Send and Email if there is an error ###
if [ "${#ERRORS[@]}" -gt 0 ]
then
MESSAGE_PRTG="$(for i in $(seq 1 ${#ERRORS[@]}) ; do echo "\t${ERRORS[$i]}\n" ; done)"
MESSAGE_EMAIL="An error has been detected on ${SERVER} involving the mysql replciation. Below is a list of the reported errors:\n\n
${MESSAGE_PRTG}
Please correct this ASAP"
#echo -e $MESSAGE_EMAIL #| mail -s "Mysql Replication for $SERVER is reporting Error" ${EMAIL}
echo "2:${SECONDS_BEHIND_MASTER}:${MESSAGE_PRTG}"
else
echo "0:${SECONDS_BEHIND_MASTER}:${SLAVE_IO_STATE}"
fi
@flickerfly
Copy link
Author

I updated this to work with MySQL v 5.6.31. The most notable issue being that two items began with Slave_SQL_Running in the "SHOW SLAVE STATUS" output so I appended a ":" to each name to be sure they were more specifically identified.

@flickerfly
Copy link
Author

Made it only call SQL once

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment