Check MySQL Replication Slave Status

Last Update: December 27, 2011

check_mysql_slavestatus

I programmed this script, to check the status of the MySQL replication. It is based on the plugin 'check mysql slave sql running' by dhirajt found on MonitoringExchange. It is written in bash, so it is portable on all kind of Linux and Unix systems.

Version 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 (Victor Balada Diaz)
2008093001 Added port for MySQL server (Victor Balada Diaz)
2008093002 Added mysqldir if mysql binary is elsewhere (Victor Balada Diaz)
2008101501 Changed bindir/mysqldir to use PATH (Soren Klintrup)
2008101501 Use $() instead of `` to avoid forks (Soren Klintrup)
2008101501 Use ${} for variables to prevent problems (Soren Klintrup)
2008101501 Check if required commands exist (Soren Klintrup)
2008101501 Check if mysql connection works (Soren Klintrup)
2008101501 Exit with unknown status at script end (Soren Klintrup)
2008101501 Also display help if no option is given (Soren Klintrup)
2008101501 Add warning/critical check to delay (Soren Klintrup)
2011062200 Add perfdata (Philippe Barsalou)
2011122700 Checking Slave_IO_Running (Marc Feret)

Thanks to everyone who helped making this plugin better!

Possible parameters you can use are:

-H Hostname or IP address of server to check
-P Port of MySQL server (standard is 3306)
-u Username of user who has rights on the MySQL server
-p Password of the user declared with -u parameter
--help Help text for correct use of this script

Before you use the script, check the following variable. The 'grep', 'cut' and 'mysql' binaries have to be in one of those PATH locations. If they are located in another directory, feel free to add its directory to the PATH variable.

PATH=/usr/local/bin:/usr/bin:/bin # Set path

If you want to define the nagios check command, that's how I defined it:

# 'check_mysql_slavestatus' command definition
define command{
command_name check_mysql_slavestatus
command_line $USER1$/check_mysql_slavestatus.sh -H $HOSTADDRESS$ -P $ARG1$ -u $ARG2$ -p $ARG3$
}

And define the check-service for the host:

# Service definition
define service{
use dienste-important-service ; Name of service template to use
host_name mysqldbserver
service_description MySQL Replication Status
check_command check_mysql_slavestatus!portnumber!username!passwd
}

If you want you can also check for the time-delay between master and slave. Just modify the following lines in the script (lines 43 and 44):

warn_delay=10 # warning at this delay
crit_delay=60 # critical at this delay