Skip to content

Instantly share code, notes, and snippets.

@KitJacky
Forked from ssimpson89/MySQL Replication Check
Last active October 25, 2018 03:05
Show Gist options
  • Save KitJacky/f7ddab5fd055b87e98b81752b00b0d3c to your computer and use it in GitHub Desktop.
Save KitJacky/f7ddab5fd055b87e98b81752b00b0d3c 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 ### \
SCRIPTWHERE="/jk/monitor"
EMAIL=""
TMP_STATUS="$SCRIPTWHERE/slave_status"
SERVER=$(hostname)
MYSQL_CHECK=$(mysql -e "SHOW VARIABLES LIKE '%version%';" || echo 1)
$(/usr/bin/mysql -e "SHOW SLAVE STATUS \G" > $TMP_STATUS)
$(date >> $TMP_STATUS)
LAST_ERRNO=$(grep "Last_Errno" $TMP_STATUS | awk '{ print $2 }')
#SECONDS_BEHIND_MASTER=$(grep "Seconds_Behind_Master" $TMP_STATUS | awk '{ print $2 }')
IO_IS_RUNNING=$(grep "Slave_IO_Running" $TMP_STATUS | awk '{ print $2 }')
SQL_IS_RUNNING=$(grep "Slave_SQL_Running" $TMP_STATUS | awk '{ print $2 }')
ERRORS=()
### 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)")
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" > 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="An error has been detected on ${SERVER} 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 | mail -s "Mysql Replication for $SERVER is reporting Error" ${EMAIL}
# For Slack
echo "$MESSAGE" > $SCRIPTWHERE/local-slave-err.log
date >> $SCRIPTWHERE/local-slave-err.log
cat $SCRIPTWHERE/local-slave-err.log
SUBMSG="Mysql Replication for $SERVER is reporting Error"
bash $SCRIPTWHERE/slack.sh -c test-JK -u "$(hostname)" -i comet -C 1974D2 -p enable -T "$SUBMSG" -F "$SCRIPTWHERE/local-slave-err.log"
fi
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment