Check that MySQL slave is running and no errors are present. Optionally send an email if errors are detected.

mysql_check_slave.sh

#!/bin/bash

set -euo pipefail

### CONFIG

EMAIL_ADDR=""  # email this address if errors detected

MAX_SECONDS_BEHIND=5 # max seconds that slave can be behind master

# mysql credentials
MYSQL_USER=""
export MYSQL_PWD=""
MYSQL_HOST="localhost"
MYSQL_PORT=3306


### CODE

SLAVE_IO='null'
SLAVE_SQL='null'
LAST_ERRNO='null'
LAST_SQL_ERRNO='null'
LAST_IO_ERRNO='null'
SECONDS_BEHIND='99999'


STATUS="$(mysql --user="$MYSQL_USER" --host="$MYSQL_HOST" --port="$MYSQL_PORT" --vertical --execute='show slave status')"
if [ -z "$STATUS" ]; then
  echo "ERROR: this server is not a MySQL slave"
  exit 1
fi


while read -r LINE; do
  case "$LINE" in
    'Slave_IO_Running: '*)
      SLAVE_IO=${LINE##*: }
      ;;
    'Slave_SQL_Running: '*)
      SLAVE_SQL=${LINE##*: }
      ;;
    'Last_Errno: '*)
      LAST_ERRNO=${LINE##*: }
      ;;
    'Last_SQL_Errno: '*)
      LAST_SQL_ERRNO=${LINE##*: }
      ;;
    'Last_IO_Errno: '*)
      LAST_IO_ERRNO=${LINE##*: }
      ;;
    'Seconds_Behind_Master: '*)
      SECONDS_BEHIND=${LINE##*: }
      ;;
  esac
done <<< "$STATUS"


ERRORS=()

if [ "$LAST_ERRNO" != "0" ]; then
  ERRORS+=("ERROR: LAST_ERRNO is $LAST_ERRNO, should be 0")
fi
if [ "$LAST_SQL_ERRNO" != "0" ]; then
  ERRORS+=("ERROR: LAST_SQL_ERRNO is $LAST_SQL_ERRNO, should be 0")
fi
if [ "$LAST_IO_ERRNO" != "0" ]; then
  ERRORS+=("ERROR: LAST_IO_ERRNO is $LAST_IO_ERRNO, should be 0")
fi
if [ "$SLAVE_SQL" != "Yes" ]; then
  ERRORS+=("ERROR: SLAVE_SQL is $SLAVE_SQL, should be 'Yes'")
fi
if [ "$SLAVE_IO" != "Yes" ]; then
  ERRORS+=("ERROR: SLAVE_IO is $SLAVE_IO, should be 'Yes'")
fi
if [[ $SECONDS_BEHIND =~ ^-?[0-9]+$ ]] && [ "$SECONDS_BEHIND" -gt "$MAX_SECONDS_BEHIND" ]; then
  ERRORS+=("ERROR: SECONDS_BEHIND is $SECONDS_BEHIND, should be less than $MAX_SECONDS_BEHIND")
fi


if [ "${#ERRORS[@]}" -gt 0 ]; then
  for ERROR in "${ERRORS[@]}"; do
    echo "$ERROR"
  done

  if [ -n "$EMAIL_ADDR" ]; then
    echo "Emailing $EMAIL_ADDR"
    echo -e "${ERRORS[*]}\n\n\n$STATUS" | mail -s "$0 - $(hostname -f)" "$EMAIL_ADDR"
  fi
fi