#!/bin/bash
#########################################################################
# Script:	check_mysql_slavestatus.sh                              #
# Author:	Claudio Kuenzler www.claudiokuenzler.com                #
# Purpose:	Monitor MySQL Replication status with Nagios            #
# Description:	Connects to given MySQL hosts and checks for running    #
#		SLAVE state and delivers additional info                #
# Original:	This script is a modified version of                    #
#		check mysql slave sql running written by dhirajt        #
# Thanks to:	Victor Balada Diaz for his ideas added on 20080930      #
#		Soren Klintrup for stuff added on 20081015              #
#		Marc Feret for Slave_IO_Running check 20111227          #
#		Peter Lecki for his mods added on 20120803              #
#		Serge Victor for his mods added on 20131223             #
#               Omri Bahumi for his fix added on 20131230               #
# History:                                                              #
# 2008041700 Original Script modified                                   #
# 2008041701 Added additional info if status OK	                        #
# 2008041702 Added usage of script with params -H -u -p	                #
# 2008041703 Added bindir variable for multiple platforms               #
# 2008041704 Added help because mankind needs help                      #
# 2008093000 Using /bin/sh instead of /bin/bash                         #
# 2008093001 Added port for MySQL server                                #
# 2008093002 Added mysqldir if mysql binary is elsewhere                #
# 2008101501 Changed bindir/mysqldir to use PATH                        #
# 2008101501 Use $() instead of `` to avoid forks                       #
# 2008101501 Use ${} for variables to prevent problems                  #
# 2008101501 Check if required commands exist                           #
# 2008101501 Check if mysql connection works                            #
# 2008101501 Exit with unknown status at script end                     #
# 2008101501 Also display help if no option is given                    #
# 2008101501 Add warning/critical check to delay                        #
# 2011062200 Add perfdata                                               #
# 2011122700 Checking Slave_IO_Running                                  #
# 2012080300 Changed to use only one mysql query                        #
# 2012080301 Added warn and crit delay as optional args                 #
# 2012080302 Added standard -h option for syntax help                   #
# 2012080303 Added check for mandatory options passed in                #
# 2012080304 Added error output from mysql                              #
# 2012080305 Changed from 'cut' to 'awk' (eliminate ws)                 #
# 2012111600 Do not show password in error output                       #
# 2013042800 Changed PATH to use existing PATH, too                     #
# 2013050800 Bugfix in PATH export                                      #
# 2013092700 Bugfix in PATH export                                      #
# 2013092701 Bugfix in getopts                                          #
# 2013101600 Rewrite of threshold logic and handling                    #
# 2013101601 Optical clean up                                           #
# 2013101602 Rewrite help output                                        #
# 2013101700 Handle Slave IO in 'Connecting' state                      #
# 2013101701 Minor changes in output, handling UNKWNON situations now   #
# 2013101702 Exit CRITICAL when Slave IO in Connecting state            #
# 2013123000 Slave_SQL_Running also matched Slave_SQL_Running_State     #
#########################################################################
# Usage: ./check_mysql_slavestatus.sh -H dbhost -P port -u dbuser -p dbpass -s connection -w integer -c integer
#########################################################################
help="\ncheck_mysql_slavestatus.sh (c) 2008-2014 GNU GPLv2 licence
Usage: check_mysql_slavestatus.sh -H host -P port -u username -p password [-s connection] [-w integer] [-c integer]\n
Options:\n-H Hostname or IP of slave server\n-P Port of slave server\n-u Username of DB-user\n-p Password of DB-user\n-s Connection name (optional, with multi-source replication)\n-w Delay in seconds for Warning status (optional)\n-c Delay in seconds for Critical status (optional)\n
Attention: The DB-user you type in must have CLIENT REPLICATION rights on the DB-server. Example:\n\tGRANT REPLICATION CLIENT on *.* TO 'nagios'@'%' IDENTIFIED BY 'secret';"

STATE_OK=0			# define the exit code if status is OK
STATE_TXT_OK="OK"		# define the exit code if status is OK
STATE_WARNING=1			# define the exit code if status is Warning (not really used)
STATE_TXT_WARNING="WARNING"	# define the exit code if status is Warning (not really used)
STATE_CRITICAL=2		# define the exit code if status is Critical
STATE_TXT_CRITICAL="CRITICAL"	# define the exit code if status is Critical
STATE_UNKNOWN=3			# define the exit code if status is Unknown
STATE_TXT_UNKNOWN="UNKNOWN"	# define the exit code if status is Unknown
export PATH=$PATH:/usr/local/bin:/usr/bin:/bin # Set path
crit="No"		# what is the answer of MySQL Slave_SQL_Running for a Critical status?
ok="Yes"		# what is the answer of MySQL Slave_SQL_Running for an OK status?

for cmd in mysql awk grep [
do
 if ! `which ${cmd} &>/dev/null`
 then
  echo "UNKNOWN: This script requires the command '${cmd}' but it does not exist; please check if command exists and PATH is correct"
  exit ${STATE_UNKNOWN}
 fi
done

# Check for people who need help - aren't we all nice ;-)
#########################################################################
#if [ "${1}" = "--help" -o "${#}" = "0" ];
if [ "${1}" = "--help" ];
	then
	echo -e "${help}";
	exit 1;
fi

# Important given variables for the DB-Connect
#########################################################################
while getopts "H:P:u:p:s:w:c:h" Input;
do
	case ${Input} in
	H)	host=${OPTARG};;
	P)	port=${OPTARG};;
	u)	user=${OPTARG};;
	p)	password=${OPTARG};;
	s)	connection=\"${OPTARG}\";;
	w)      warn_delay=${OPTARG};;
	c)      crit_delay=${OPTARG};;
	h)      echo -e "${help}"; exit 1;;
	\?)	echo "Wrong option given. Please use options -H for host, -P for port, -u for user and -p for password"
		exit 1
		;;
	esac
done


#####  Biodec Patch (auto prametrization) ##############################
maintenance_file=/etc/mysql/debian.cnf 
warn_delay=5
crit_delay=10
min_delay=0
max_delay=60
if [ -e $maintenance_file ]; then
	user=$(sed -n '0,/user/ s/.*user *= *\([^ ]*.*\)/\1/p' < $maintenance_file) 
	password=$(sed -n '0,/password/ s/.*password *= *\([^ ]*.*\)/\1/p' < $maintenance_file)
	port=$(netstat -4tnlp | awk '/\/mysqld / {print $4}' |awk -F ":" '{print $2}')
	host=$(netstat -4tnlp | awk '/\/mysqld / {print $4}' |awk -F ":" '{print $1}')
	if [ "${host}" = "0.0.0.0" ]; then
		host="127.0.0.1"
	fi
fi
##Exit silently if any of the variable is empty""
if [ "${user}" = "" ] || [ "${password}" = "" ] || [ "${port}" = "" ] || [ "${host}" = "" ]; then
     exit 0;
fi

output_replica_string='echo $STATE $CHECK_NAME - $STATE_TXT $GENERIC_OUTPUT'
output_replica_delay_string='echo $STATE $CHECK_NAME delay=${delay}\;${warn_delay}\;${crit_delay}\;${min_delay}\;${max_delay} $STATE_TXT $GENERIC_OUTPUT'

#########################################################################

# Connect to the DB server and check for informations
#########################################################################
# Check whether all required arguments were passed in
if [ -z "${host}" -o -z "${port}" -o -z "${user}" -o -z "${password}" ];then
	STATE=$STATE_UNKNOWN
	STATE_TXT=$STATE_TXT_UNKNOWN
	CHECK_NAME="mysql_replica_check"
	delay="unknown"
	GENERIC_OUTPUT="Not all arguments are available host=${host}, port=${port}, user=${user}, and given password"
	echo $(eval $output_replica_string)
	##yes exit, no connection, no further check
	exit 0
	#exit ${STATE_UNKNOWN}
fi
# Connect to the DB server and store output in vars
ConnectionResult=`mysql -h ${host} -P ${port} -u ${user} --password=${password} -e "show slave ${connection} status\G" 2>&1`
if [ -z "`echo "${ConnectionResult}" |grep Slave_IO_State`" ]; then
	STATE=$STATE_UNKNOWN
        STATE_TXT=$STATE_TXT_UNKNOWN
	CHECK_NAME="mysql_replica_check"
        delay="unknown"
        GENERIC_OUTPUT="Unable to connect to server host=${host}:port=${port} with user=${user} and given password"
	echo $(eval $output_replica_string)
	##yes exit, no connection, no further check
	exit 0
	#exit ${STATE_CRITICAL}
fi
#echo ${ConnectionResult}
check=`echo "${ConnectionResult}" |grep Slave_SQL_Running: | awk '{print $2}'`
checkio=`echo "${ConnectionResult}" |grep Slave_IO_Running: | awk '{print $2}'`
masterinfo=`echo "${ConnectionResult}" |grep  Master_Host: | awk '{print $2}'`
delayinfo=`echo "${ConnectionResult}" |grep Seconds_Behind_Master: | awk '{print $2}'`

# Output of different exit states
#########################################################################

if [ ${check} = "NULL" ]; then
	STATE=$STATE_UNKNOWN
        STATE_TXT=$STATE_TXT_UNKNOWN
	CHECK_NAME="mysql_replica_check"
        GENERIC_OUTPUT="Slave SQL Server host=${host}:port=${port} with user=${user} and given password is answering NULL (maybe not a Slave Server)"
	echo $(eval $output_replica_string)
	##yes exit, no connection, no further check
	exit 0
	#exit ${STATE_CRITICAL};
fi

###### SQL ##########
if [ ${check} = ${crit} ] ; then
        STATE=$STATE_CRITICAL
        STATE_TXT=$STATE_TXT_CRITICAL
	CHECK_NAME="mysql_replica_check_${masterinfo}"
        GENERIC_OUTPUT="Slave SQL Server host=${host}:port=${port} with user=${user} and given password is not replicating"
        echo $(eval $output_replica_string)
	# DO not exit. Wait for IO check.
        #exit 0
	#echo "CRITICAL: ${host}:${port} Slave_SQL_Running: ${check}"; exit ${STATE_CRITICAL};
elif [ ${check} = ${ok} ] ; then
        STATE=$STATE_OK
        STATE_TXT=$STATE_TXT_OK
        CHECK_NAME="mysql_replica_check_${masterinfo}"
        GENERIC_OUTPUT="Slave SQL Server host=${host}:port=${port} with user=${user} and given password is replicating"
        echo $(eval $output_replica_string)
        # DO not exit. Wait for IO check and for delay
        #exit 0
        #echo "CRITICAL: ${host}:${port} Slave_SQL_Running: ${check}"; exit ${STATE_CRITICAL};
fi

###### IO ###########
if [ ${checkio} = ${crit} ] ; then
	STATE=$STATE_CRITICAL
        STATE_TXT=$STATE_TXT_CRITICAL
        CHECK_NAME="mysql_replica_checkio_${masterinfo}"
        GENERIC_OUTPUT="Slave IO Server host=${host}:port=${port} with user=${user} and given password is not replicating"
        echo $(eval $output_replica_string)
	# Do not Exit. It shouldn't have any sense to check delay if it is not replicating, but we could loose the delay check (no output for an inventorized check)
        #exit 0
	#echo "CRITICAL: ${host} Slave_IO_Running: ${checkio}"; exit ${STATE_CRITICAL};
elif [ ${checkio} = "Connecting" ]; then
	STATE=$STATE_CRITICAL
        STATE_TXT=$STATE_TXT_CRITICAL
        CHECK_NAME="mysql_replica_checkio_${masterinfo}"
        GENERIC_OUTPUT="Slave IO Server host=${host}:port=${port} with user=${user} and given password is in state 'Connecting' (not yet replicating)"
        echo $(eval $output_replica_string)
	# Do not Exit. It shouldn't have any sense to check delay if it is not replicating, but we could loose the delay check (no output for an inventorized check)
        #exit 0
	#echo "CRITICAL: ${host} Slave_IO_Running: ${checkio}"; exit ${STATE_CRITICAL};
elif [ ${checkio} = ${ok} ]; then
	STATE=$STATE_OK
        STATE_TXT=$STATE_TXT_OK
        CHECK_NAME="mysql_replica_checkio_${masterinfo}"
        GENERIC_OUTPUT="Slave IO Server host=${host}:port=${port} with user=${user} and given password is replicating"
        echo $(eval $output_replica_string)
        # Do not exit, check delay.
        #exit 0
        #echo "CRITICAL: ${host} Slave_IO_Running: ${checkio}"; exit ${STATE_CRITICAL};
fi

###### Delay ###########
## If one of the above is crit, maybe the delay is undetermined. Just print a critical, with the value of the delay (is not syncins sto the delay is whatever).
if [ ${check} = ${crit} ] || [ ${checkio} = ${crit} ]; then
	STATE=$STATE_CRITICAL
        STATE_TXT=$STATE_TXT_CRITICAL
        CHECK_NAME="mysql_replica_delay_${masterinfo}"
        GENERIC_OUTPUT="Slave Server host=${host}:port=${port} with user=${user} and given password is not replicating, so it is in delay"
	delay=$delayinfo
        echo $(eval $output_replica_delay_string)
elif [[ ${delayinfo} -ge ${crit_delay} ]]; then
	STATE=$STATE_CRITICAL
        STATE_TXT=$STATE_TXT_CRITICAL
        CHECK_NAME="mysql_replica_delay_${masterinfo}"
        GENERIC_OUTPUT="Slave Server host=${host}:port=${port} with user=${user} and given password is replicating, but the delay (of ${delayinfo} behind master) is greater then the critical threshold of ${crit_delay}s"
        delay=$delayinfo
        echo $(eval $output_replica_delay_string) 
elif [[ ${delayinfo} -ge ${warn_delay} ]]; then
	STATE=$STATE_WARNING
        STATE_TXT=$STATE_TXT_WARNING
        CHECK_NAME="mysql_replica_delay_${masterinfo}"
        GENERIC_OUTPUT="Slave Server host=${host}:port=${port} with user=${user} and given password is replicating, but the delay (of ${delayinfo} behind master) is greater then the warning threshold of ${warn_delay}s"
        delay=$delayinfo
        echo $(eval $output_replica_delay_string)
else
	STATE=$STATE_OK
        STATE_TXT=$STATE_TXT_OK
        CHECK_NAME="mysql_replica_delay_${masterinfo}"
        GENERIC_OUTPUT="Slave Server host=${host}:port=${port} with user=${user} and given password is replicating fine (0s delay)"
        delay=$delayinfo
        echo $(eval $output_replica_delay_string)
fi
##########################	
