Skip to content

Instantly share code, notes, and snippets.

@jemiam
Last active October 6, 2016 08:07
Show Gist options
  • Save jemiam/31984893d1b8d20fcc942a090f2fad01 to your computer and use it in GitHub Desktop.
Save jemiam/31984893d1b8d20fcc942a090f2fad01 to your computer and use it in GitHub Desktop.
mysql replication check script
#!/bin/bash
# replication delay threshold
TH_SECONDS_BEHIND=60
set -euo pipefail
IFS=$'\n\t'
SERVER=$(hostname -f)
ERRORS=()
# check that we are running on a slave
MYSQL_SLAVE=$(mysql -e 'show slave status\G')
if [ -z "${MYSQL_SLAVE}" ]
then
echo "ERROR: this server is not a MySQL slave."
exit 1
fi
# get mysql status
MYSQL_STATUS=( $(echo "${MYSQL_SLAVE}" | 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} != 'NULL' ] && [ ${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
for ERROR in "${ERRORS[@]}"
do
echo ${ERROR}
done
exit 1
fi
echo 'Replication Health is OK.'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment