check_mysql_slavestatus

Last update: August 22, 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
2015011600 Added 'moving' check to catch possible connection issues
2015011900 Use its own threshold for replication moving check
2019082200 Add support for mysql option file (Marc Falzon)
2019082201 Improve password security (remove from mysql cli)
2019082202 Added socket parameter (-S) (Andreas Pfeiffer)
2019082203 Use default port 3306, makes -P optional
2019082204 Fix moving subcheck, improve documentation

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 Notes Description
-o 1 Path to mysql client options file containing connection credentials (e.g. /home/nagios/.my.cnf)
-H 2 Hostname or IP address of server to check
-P Port of MySQL server (standard is 3306)
-S 2 Path to local mysqld socket (e.g. /run/mysqld/mysqld.sock)
-u 3 Username of user who has rights on the MySQL server
-p 3 Password of the user declared with -u parameter
-s Connection name (optional, for multi-source replication)
-w Warning delay of slave replication behind master (in seconds)
-c Critical delay of slave replication behind master (in seconds)
-m 4 Checks whether or not the replication (exec log position to be exact) has moved in the last VALUE (in seconds)
--help Show help/usage

1: The options file can contain all communication settings and will be read by the mysql command. Using this, makes the other required connect parameters (-H or -S) optional. This is the most secure way to use the credentials.

2: Unless defined in the options file, a host/ip address to connect to (-H) or a local socket (-S) is required.

3: When using -H or -S to connect to MySQL, credentials are required.

4: This subcheck adds additional reliability to your slave replication check. It checks if the slave's log position has moved in the last N seconds. On a replication setup without many write operations, this might need to be set to a higher value.

Usage / running the plugin on the command line

Usage:

Usage: $0 (-o path|(-H string [-P int]|-S path) -u string -p string) [-s string] [-w int] [-c int] [-m int]

Example 1: Connection to a remote MySQL server using credentials. Warning when slave is 60s, critical alert when 120s behind master.

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

Example 2: Connection to local MySQL server using socket credentials. Warning when slave is 60s, critical alert when 120s behind master.

$ ./check_mysql_slavestatus.sh -S /run/mysqld/mysqld.sock -u nagios -p secret -w 60 -c 120

Example 3: Connection to a remote MySQL server using a local mysql client file. Warning when slave is 60s, critical alert when 120s behind master.

$ cat /home/nagios/.my.cnf
[mysql]
host=10.0.0.100
user=nagios
password=secret
port=3306

$ ./check_mysql_slavestatus.sh -o /home/nagios/.my.cnf -w 60 -c 120
OK: Slave SQL running: Yes Slave IO running: Yes / master: 192.168.12.11 / slave is 0 seconds behind master | delay=0s

Example 4: Connection to local MySQL server using credentials. Warning when slave is 60s, critical alert when 120s behind master. Addtionally warn when replication has not moved within 10s.

$ ./check_mysql_slavestatus.sh -S /run/mysqld/mysqld.sock -u nagios -p secret -w 60 -c 120 -m 10
WARNING: Slave replication has not moved in 10 seconds. Manual check required.

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 = {
    "-o" = {
      value = "$mysql_slave_optionfile$"
      description = "Path to mysql client options file containing connection credentials"
    }
    "-H" = {
      value = "$mysql_slave_host$"
      description = "DNS hostname or IP address of MySQL Slave"
    }
    "-S" = {
      value = "$mysql_slave_socket$"
      description = "Path to local mysqld socket"
    }
    "-P" = {
      value = "$mysql_slave_port$"
      description = "MySQL Listening Port (defaults to 3306)"
    }
    "-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 = "Additionally check for replication to keep moving (slave log position). Value in seconds."
    }
  }

  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 using credentials
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"
}

# MySQL Replication Slave Status using option file
object Service "MySQl Replication Status" {
  import "generic-service"
  host_name "mysqlslaveserver"
  check_command = "check_mysql_slavestatus"
  vars.mysql_slave_optionfile = "/home/nagios/.my.cnf"
  vars.mysql_slave_warning = "300"
  vars.mysql_slave_critical = "600"
}