mysql replication check script

mysql_repl_check.sh

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

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} ]