check_mysql_slavestatus

Last update: May 23, 2019

I programmed this script (starting 2008), 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.

Download

Download plugin and save it in your Nagios/Monitoring plugin folder (usually /usr/lib/nagios/plugins, depends on your distribution). Afterwards adjust the permissions (usually chmod 755).

Community contributions welcome on GitHub repo.

Version history / Changelog

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)
2012080300 Changed to use only one mysql query (Peter Lecki)
2012080301 Added warn and crit delay as optional args (Peter Lecki)
2012080302 Added standard -h option for syntax help (Peter Lecki)
2012080303 Added check for mandatory options passed in (Peter Lecki)
2012080304 Added error output from mysql (Peter Lecki)
2012080305 Changed from 'cut' to 'awk' (eliminate ws) (Peter Lecki)
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

Requirements

  • The following shell commands must exist and be executable by your Nagios user: grep, cut
  • The mysql command must be available (this command usually comes with the mysql-client package)
  • The MySQL user you want to use for this plugin needs REPLICATION CLIENT privileges.
    Here is an example how to grant the necessary privileges to a user nagios:

    GRANT REPLICATION CLIENT on *.* TO 'nagios'@'%' IDENTIFIED BY 'secret';

Definition of the parameters

Parameter Description
-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
-s Connection name (optional, with multi-source replication)
-w Warning delay of slave behind master (in seconds)
-c Critical delay of slave behind master (in seconds)
-m Moving - TBD
--help Show help/usage

* mandatory parameters

Usage / running the plugin on the command line

Usage:

./check_mysql_slavestatus.sh -H hostname -P port -u username -p password [-s connection] [-w warning delay in s] [-c critical delay in s] [-m TBD]

Examples:

./check_mysql_slavestatus.sh -H 10.0.0.100 -P 3306 -u nagios -p secret -w 60 -c 120

Command definition

Command definition in Nagios, Icinga 1.x, Shinken, Naemon

# '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$
}

Or with delay check:

# 'check_mysql_slavestatus' command definition with delay check
define command{
command_name check_mysql_slavestatus
command_line $USER1$/check_mysql_slavestatus.sh -H $HOSTADDRESS$ -P $ARG1$ -u $ARG2$ -p $ARG3$ -w $ARG4$ -c $ARG5$
}

Command definition in Icinga 2.x

object CheckCommand "check_mysql_slavestatus" {
  import "plugin-check-command"

  command = [ PluginContribDir + "/check_mysql_slavestatus" ]

  arguments = {
    "-H" = {
      value = "$mysql_slave_host$"
      description = "Hostname or IP of MySQL Slave"
    }
    "-P" = {
      value = "$mysql_slave_port$"
      description = "MySQL Listening Port"
    }
    "-u" = {
      value = "$mysql_slave_user$"
      description = "MySQL username"
    }
    "-p" = {
      value = "$mysql_slave_password$"
      description = "MySQL password"
    }
    "-w" = {
      value = "$mysql_slave_warning$"
      description = "Warning delay in seconds"
    }
    "-c" = {
      value = "$mysql_slave_critical$"
      description = "Critical delay in seconds"
    }
    "-s" = {
      value = "$mysql_slave_connection$"
      description = "Connection name when multi-source replication is used"
    }
    "-m" = {
      set_if = "$mysql_slave_moving$"
      description = "collect performance data for pnp4nagios"
    }
  }

  vars.mysql_slave_host = "$address$"
  vars.mysql_slave_port = 3306
  vars.mysql_slave_warning = 30
  vars.mysql_slave_critical = 120
}

Service definition

Service definition in Nagios, Icinga 1.x, Shinken, Naemon

Basic check without thresholds:

# check MySQL Slave Status
define service{
use generic-service ; Name of service template to use
host_name mysqlslaveserver
service_description MySQL Replication Status
check_command check_mysql_slavestatus!portnumber!username!passwd
}

Basic check with thresholds (replication delay in seconds):

# check MySQL Slave Delay
define service{
use generic-service ; Name of service template to use
host_name mysqlslaveserver
service_description MySQL Replication Delay
check_command check_mysql_slavestatus!portnumber!username!passwd!300!600
}

Service object definition Icinga 2.x

# MySQL Replication Slave Status
object Service "MySQl Replication Status" {
  import "generic-service"
  host_name "mysqlslaveserver"
  check_command = "check_mysql_slavestatus"
  vars.mysql_slave_user = "nagios"
  vars.mysql_slave_password = "secret"
  vars.mysql_slave_warning = "300"
  vars.mysql_slave_critical = "600"
}