Nagios plugin of MySQL slave check from "SHOW SLAVE STATUS".

check_mysql_slavestatus

#!/bin/sh
#
# Forked at 2014/08/12
# Modifier: OGATA Tetsuji <github:xtetsuji>
# Source: https://gist.github.com/xtetsuji/d82125bfab2247d6cc96
#
# Thanks to original:
#   File: check_mysql_slavestatus.sh
#   Release Version: 0.1.1
#   Created 2012/12/10
#   License: GPLv3
#   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

USAGE="Usage:\n  $CMDNAME [-H HOST] [-u MySQLUser] [-p MySQLPassword] [-L BehindMasterCriticalLimit(sec)] [-l BehindMasterWarningLimit(sec)].\n  Example: -H 127.0.0.1 -u root -p hogehoge -L 10\n"

while getopts H:P:u:p:L:l: OPT ; do
    case $OPT in
        "H" ) FLAGH="TRUE" ; HOST="$OPTARG" ;;
        "P" ) FLAGP="TRUE" ; PORT="$OPTARG" ;;
        "u" ) FLAGu="TRUE" ; USER="$OPTARG" ;;
        "p" ) FLAGp="TRUE" ; PASS="$OPTARG" ;;
        "L" ) FLAGL="TRUE" ; C_LIMIT="$OPTARG" ;;
        "l" ) FLAGl="TRUE" ; W_LIMIT="$OPTARG" ;;
        *   )
            echo -e "$USAGE" 1>&2
            exit $STATE_UNKNOWN
            ;;
    esac
done

if [ "$FLAGH" != "TRUE" ]; then
  echo -e "$USAGE" 1>&2
  exit $STATE_UNKNOWN
fi

if [ "$FLAGu" != "TRUE" ]; then
  echo -e "$USAGE" 1>&2
  exit $STATE_UNKNOWN
fi

if [ "$FLAGp" != "TRUE" ]; then
  echo -e "$USAGE" 1>&2
  exit $STATE_UNKNOWN
fi

# if [ "$FLAGL" != "TRUE" ]; then
#   echo -e "$USAGE" 1>&2
#   exit $STATE_UNKNOWN
# fi

if [ "x$FLAGP" = x ]; then
    PORT=3306
fi

if [ "x$C_LIMIT" = x ]; then
    C_LIMIT=60
fi
if [ "x$W_LIMIT" = x ]; then
    W_LIMIT=30
fi

if [ $C_LIMIT -le $W_LIMIT ]; then
    echo "C_LIMIT less than W_LIMIT. This is wrong."
    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 -P $PORT -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 -P $PORT -u $USER -p"$PASS" -e "show slave status\G"|grep Slave_IO_Running|awk '{print $2}'`
SSR=`mysql -h $HOST -P $PORT -u $USER -p"$PASS" -e "show slave status\G"|grep Slave_SQL_Running|awk '{print $2}'`
SBM=`mysql -h $HOST -P $PORT -u $USER -p"$PASS" -e "show slave status\G"|grep Seconds_Behind_Master|awk '{print $2}'`

## First
if [ "$SBM" != "NULL" ]; then
    : "relax :-)"
    if [ $SBM -ge $C_LIMIT ]; then
        echo "Critical : Slave_IO_Running $SIR / Slave_SQL_Running $SSR / Seconds_Behind_Master $SBM"
        exit $STATE_CRITICAL
    elif [ $SBM -ge $W_LIMIT ] ; then
        echo "Warning : Slave_IO_Running $SIR / Slave_SQL_Running $SSR / Seconds_Behind_Master $SBM"
        exit $STATE_WARNING
    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 $STATE_OK

: <<EOF

=pod

=head1 NAME

check_mysql_slavestatus - Nagios plugin of MySQL "SLAVE STATUS"

=head1 SYNOPSIS

  $CMDNAME [-H HOST] [-P PORT] [-u MySQLUser] [-p MySQLPassword] \
           [-L BehindMasterCriticalLimit(sec)] \
           [-l BehindMasterWarningLimit(sec)]

"-L" default is 60 seconds (-L 60).

"-l" default is 30 seconds (-l 30).

=cut

EOF