mysql replication check script

mysql-check-repl.sh

#!/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.'