Script for checking MySQL Replication at Nagios.

check_mysql_slavestatus.sh

#!/bin/sh
#
# File: check_mysql_slavestatus.sh
# Release Version: 0.1.1
# Created 2012/12/10
# License: MIT
# Author: Takayuki Saito <github:taka3110>
# How to use: "chmod + x" and please add on plugins directory.
#
########################
###### Base info
########################
#####################################
# Settings
LPORT=3306

#####################################
# Nagios return codes
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3

#####################################
# Command check

while getopts H:u:p:L: OPT
do
  case $OPT in
    "H" ) FLAGH="TRUE" ; HOST="$OPTARG" ;;
    "u" ) FLAGu="TRUE" ; USER="$OPTARG" ;;
    "p" ) FLAGp="TRUE" ; PASS="$OPTARG" ;;
    "L" ) FLAGL="TRUE" ; LIMIT="$OPTARG" ;;
      * ) echo "Usage: $CMDNAME [-H HOST] [-u MySQLUser] [-p MySQLPassword] [-L BehindMasterLimit(sec)]. Example: -H 127.0.0.1 -u root -p hogehoge -L 10" 1>&2
          exit $STATE_UNKNOWN ;;
  esac
done

if [ "$FLAGH" != "TRUE" ]; then
  echo "Usage: $CMDNAME [-H HOST] [-u MySQLUser] [-p MySQLPassword] [-L BehindMasterLimit(sec)]. Example: -H 127.0.0.1 -u root -p hogehoge -L 10" 1>&2
  exit $STATE_UNKNOWN
fi

if [ "$FLAGu" != "TRUE" ]; then
  echo "Usage: $CMDNAME [-H HOST] [-u MySQLUser] [-p MySQLPassword] [-L BehindMasterLimit(sec)]. Example: -H 127.0.0.1 -u root -p hogehoge -L 10" 1>&2
  exit $STATE_UNKNOWN
fi

if [ "$FLAGp" != "TRUE" ]; then
  echo "Usage: $CMDNAME [-H HOST] [-u MySQLUser] [-p MySQLPassword] [-L BehindMasterLimit(sec)]. Example: -H 127.0.0.1 -u root -p hogehoge -L 10" 1>&2
  exit $STATE_UNKNOWN
fi

if [ "$FLAGL" != "TRUE" ]; then
  echo "Usage: $CMDNAME [-H HOST] [-u MySQLUser] [-p MySQLPassword] [-L BehindMasterLimit(sec)]. Example: -H 127.0.0.1 -u root -p hogehoge -L 10" 1>&2
  exit $STATE_UNKNOWN
fi

########################
###### Error Check
########################

### MySQL Up/Down Check for nrpe.
### We don't use on this version.
#netstat -ln|grep LISTEN|grep $LPORT >/dev/null 2>&1
#case "$?" in
#"1" )
#echo "Critical : MySQL is not running. port3306 is closing."
#exit $STATE_CRITICAL;;
#"0" ) ;;
#esac

## Login check
mysql -h $HOST -u $USER -p"$PASS" -e "select 4 + 1;" >/dev/null 2>&1
case "$?" in
"1" )
echo "Critical : Access denied."
exit $STATE_CRITICAL;;
"0" ) ;;
esac

########################
###### Get Rep Status
########################
SIR=`mysql -h $HOST -u $USER -p"$PASS" -e "show slave status\G"|grep Slave_IO_Running|awk '{print $2}'`
SSR=`mysql -h $HOST -u $USER -p"$PASS" -e "show slave status\G"|grep Slave_SQL_Running|awk '{print $2}'`
SBM=`mysql -h $HOST -u $USER -p"$PASS" -e "show slave status\G"|grep Seconds_Behind_Master|awk '{print $2}'`

## First
if [ "$SBM" != "NULL" ]; then

if [ $SBM -ge $LIMIT ]; then
echo "Critical : Slave_IO_Running $SIR / Slave_SQL_Running $SSR / Seconds_Behind_Master $SBM"
exit $STATE_CRITICAL
else
sleep 1
fi
else
echo "" >/dev/null 2>&1
fi

case "$SIR" in
"No" )
echo "Critical : Slave_IO_Running $SIR / Slave_SQL_Running $SSR / Seconds_Behind_Master $SBM"
exit $STATE_CRITICAL;;
"Yes" ) ;;
esac

case "$SSR" in
"No" )
echo "Critical : Slave_IO_Running $SIR / Slave_SQL_Running $SSR / Seconds_Behind_Master $SBM"
exit $STATE_CRITICAL;;
"Yes" )
echo "OK : Slave_IO_Running $SIR / Slave_SQL_Running $SSR / Seconds_Behind_Master $SBM"
exit $STATE_OK;;
esac

exit