check_mysql_slavestatus.sh

#!/bin/sh
#
# File: check_mysql_slavestatus.sh
# Release Version: 0.1.2
# Created 2012/12/10
# License: GPLv3
# Author: Takayuki Saito <github:taka3110>
# Contributor: Nico Kadel-Garcia <github.com:nkadel-skyhook>
# 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

# Default talk to localhost
OPTHOST="-h localhost"
# Default for local no password or .my.cnf setup
OPTUSER=""
# OPTPASS not used, use MYSQL_PWD instead
export MYSQL_PWD=''
# Default time limit 10 seconds
LIMIT="10"

while getopts H:u:p:L: OPT
do
    case $OPT in
	"H" )
	    OPTHOST="-h $OPTARG"
	    ;;
	"u" )
	    OPTUSER="-u$OPTARG"
	    ;;
	"p" )
	    # Does not override .my.cnf if "-u" is not used
	    MYSQL_PWD="$OPTARG"
	    export MYSQL_PWD
	    ;;
	"L" )
	    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

########################
###### 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 $OPTHOST $OPTUSER -e "select 4 + 1;" >/dev/null 2>&1
case "$?" in
    "1" )
	echo "Critical : Access denied."
	exit $STATE_CRITICAL
	;;
    "0" )
	;;
    *)
	echo "Critical : Undefined access error."
	exit $STATE_UNKNOWN
	;;
esac

########################
###### Get Rep Status
########################
SIR=`mysql $OPTHOST $OPTUSER -e "show slave status\G" | grep Slave_IO_Running | awk '{print $2}'`
SSR=`mysql $OPTHOST $OPTUSER -e "show slave status\G" | grep Slave_SQL_Running | awk '{print $2}'`
SBM=`mysql $OPTHOST $OPTUSER -e "show slave status\G" | grep Seconds_Behind_Master | awk '{print $2}'`

## Verify running processes
case "$SIR" in
    "No" )
	echo "Critical : Slave_IO_Running $SIR / Slave_SQL_Running $SSR / Seconds_Behind_Master $SBM"
	exit $STATE_CRITICAL
	;;
    "Yes" )
	;;
    * )
	echo "Critical : Unknown Slave_IO_Running response."
	exit $STATE_UNKNOWN 
	;;
esac

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

# Verify maximum lag time
if [ "$SBM" = "NULL" ]; then
    # Common to halted slave
    echo "Critical : Slave_IO_Running $SIR / Slave_SQL_Running $SSR / Seconds_Behind_Master $SBM"
    exit $STATE_CRITICAL
elif [ $SBM -lt $LIMIT ]; then
    echo "OK : Slave_IO_Running $SIR / Slave_SQL_Running $SSR / Seconds_Behind_Master $SBM"
    exit $STATE_OK
else
    echo "Critical : Unknown Seconds_Behind_Master response."
    echo "Critical : Slave_IO_Running $SIR / Slave_SQL_Running $SSR / Seconds_Behind_Master $SBM"
    exit $STATE_CRITICAL
fi

exit

Updated to allow blank command line options to check local socket accessed mysql with .my.cnf credentials, or passphrase free accounts. Also uses 'MYSQL_PWD' instead of password in command line, to avoid Percona MySQL complaining about insecure password handling and to avoid passwords showing up in 'ps' when used in command line.