Skip to content

Instantly share code, notes, and snippets.

@gchaix
Created April 19, 2016 22:36
Show Gist options
  • Save gchaix/6f8ea36f2ce0b9bb22657cbac5072e1e to your computer and use it in GitHub Desktop.
Save gchaix/6f8ea36f2ce0b9bb22657cbac5072e1e to your computer and use it in GitHub Desktop.
mysql replication check script
#!/bin/bash
#--- Configurables ------------------------------------------
# replication delay threshold
TH_SECONDS_BEHIND=5
# notifcation email (if blank, no email will be sent)
EMAIL_ADDR=""
#------------------------------------------------------------
set -euo pipefail
IFS=$'\n\t'
SERVER=$(hostname -f)
declare -a ERRORS
# check that we are running on a slave
MYSQL_SLAVE=$(mysql -e 'show slave status\G' | wc -l)
if [ ${MYSQL_SLAVE} -eq 0 ]
then
echo "ERROR: this server is not a MySQL slave"
exit 1
fi
# get mysql status
MYSQL_STATUS=( $(mysql -e 'show slave status\G' | sed -e 's/^[[:space:]]*//g' ) )
for STATUS in "${MYSQL_STATUS[@]}"
do
case "${STATUS}" in
'Master_Host: '*)
MYSQL_MASTER=${STATUS##*: }
;;
'Slave_IO_Running: '*)
MYSQL_SLAVE_IO=${STATUS##*: }
;;
'Slave_SQL_Running: '*)
MYSQL_SLAVE_SQL=${STATUS##*: }
;;
'Last_Errno: '*)
MYSQL_LAST_ERRNO=${STATUS##*: }
;;
'Seconds_Behind_Master: '*)
MYSQL_SECONDS_BEHIND=${STATUS##*: }
;;
esac
done
# Check For Last Error
if [ ${MYSQL_LAST_ERRNO} != 0 ]
then
ERRORS+=("ERROR: expected last_errno=0 but found last_errno=${MYSQL_LAST_ERRNO}")
fi
# Check if IO thread is running
if [ "${MYSQL_SLAVE_IO}" != "Yes" ]
then
ERRORS+=("ERROR: expected slave_io_running=Yes but found slave_io_running=${MYSQL_SLAVE_IO}")
fi
# Check for SQL thread
if [ "${MYSQL_SLAVE_SQL}" != "Yes" ]
then
ERRORS+=("ERROR: expected slave_sql_running=Yes but found slave_sql_running=${MYSQL_SLAVE_SQL}")
fi
# Check replication delay
if [ ${MYSQL_SECONDS_BEHIND} -gt ${TH_SECONDS_BEHIND} ]
then
ERRORS+=("ERROR: expected seconds_behind_master<${TH_SECONDS_BEHIND} but found seconds_behind_master=${MYSQL_SECONDS_BEHIND}")
fi
# process (output/log/email any errors we found
if [ "${#ERRORS[@]}" -gt 0 ]
then
# if an email address was configured, send an email
if [ -z ${EMAIL_ADDR} ]
then
echo -e "${ERRORS[@]}\n\n\n${MYSQL_STATUS[*]}" | mail -s "mysql_repl_check.sh - $SERVER" ${EMAIL_ADDR}
fi
for ERROR in "${ERRORS[@]}"
do
# if running interactively, echo error
if [[ $- == *i* ]]
then
echo ${ERROR}
fi
logger ${ERROR}
done
fi
@zek72
Copy link

zek72 commented Sep 20, 2016

The small improvements I had to add :

  • ${MYSQL_SECONDS_BEHIND} can be null, check if it is integer :
# Check replication delay
if [[ ${MYSQL_SECONDS_BEHIND} =~ ^-?[0-9]+$ ]] # if it is integer
then
   if [ ${TH_SECONDS_BEHIND} -lt ${MYSQL_SECONDS_BEHIND} ]
   then
       ERRORS+=("ERROR: expected seconds_behind_master<${TH_SECONDS_BEHIND} but found seconds_behind_master=${MYSQL_SECONDS_BEHIND}")
   fi
fi
  • If ${EMAIL_ADDR} is not null : if [ ! -z ${EMAIL_ADDR} ]

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