Description

check_mysql_health is a plugin to check various parameters of a MySQL database.

Command line parameters

  • --hostname <hostname>

    The database server which should be monitored. In case of "localhost" this parameter can be omitted.

  • --username <username>

    The database user.

  • --password <password>

    Password of the database user.

  • --mode <modus>

    With the mode-parameter you tell the plugin what it should do. See the list of possible values further down.

  • --name <objektname>

    Here the check can be limited to a single object. (Momentarily this parameter is only used for mode=sql)

  • --name2 <string>

    If you use --mode=sql, then the SQL-Statement appears in the output and performance values. With the parameter name2 you're able to specify a string for this..

  • --warning <range>

    Determined values outside of this range trigger a WARNING.

  • --critical <range>

    Determined values outside of this range trigger a CRITICAL.

  • --environment <variable>=<wert>

    With this you can pass environment variables to the script. Multiple declarations are possible.

  • --method <connectmethode>

    With this parameter you tell the plugin how it should connect to the database. (dbi for using DBD::mysql (default), mysql for mysql-Tool).

  • --units <%|KB|MB|GB>

    The declaration from units serves the "beautification" of the output from mode=sql

Use the option --mode with various keywords to tell the Plugin which values it should determine and check.

Keyword Description Range
connection-time Determines how long connection establishment and login take 0..n Seconds (1, 5)
uptime Time since start of the database server (recognizes DB-Crash+Restart) 0..n Seconds (10:, 5: Minutes)
threads-connected Number of open connections 1..n (10, 20)
threadcache-hitrate Hitrate in the Thread-Cache 0%..100% (90:, 80:)
q[uery]cache-hitrate Hitrate in the Query Cache 0%..100% (90:, 80:)
q[uery]cache-lowmem-prunes Displacement out of the Query Cache due to memory shortness n/sec (1, 10)
[myisam-]keycache-hitrate Hitrate in the Myisam Key Cache 0%..100% (99:, 95:)
[innodb-]bufferpool-hitrate Hitrate in the InnoDB Buffer Pool 0%..100% (99:, 95:)
[innodb-]bufferpool-wait-free Rate of the InnoDB Buffer Pool Waits 0..n/sec (1, 10)
[innodb-]log-waits Rate of the InnoDB Log Waits 0..n/sec (1, 10)
tablecache-hitrate Hitrate in the Table-Cache 0%..100% (99:, 95:)
table-lock-contention Rate of failed table locks 0%..100% (1, 2)
index-usage Sum of the Index-Utilization (in contrast to Full Table Scans) 0%..100% (90:, 80:)
tmp-disk-tables Percent of the temporary tables that were created on the disk instead in memory 0%..100% (25, 50)
slow-queries Rate of queries that were detected as "slow" 0..n/sec (0.1, 1)
long-running-procs Sum of processes that are runnning longer than 1 minute 0..n (10, 20)
slave-lag Delay between Master and Slave 0..n Seconds
slave-io-running Checks if the IO-Thread of the Slave-DB is running  
slave-sql-running Checks if the SQL-Thread of the Slave-DB is running  
sql Result of any SQL-Statement that returns a number. The statement itself is passed over with the parameter --name. A Label for the performance data output can be passed over with the parameter --name2. The parameter --units can add units to the output (%, c, s, MB, GB,..). If the SQL-Statement includeds special characters or spaces, it can first be encoded with the mode encode. 0..n
open-files Number of open files (of upper limit) 0%..100% (80, 95)
encode Reads standard input (STDIN) and outputs an encoded string.  
cluster-ndb-running Checks if all cluster nodes are running.  

Depending on the chosen mode two labels can appear in the performance data output.

<label>= and <label_now>=

The determinded values apply to the complete runtime of the database and to the time since the last run of check_mysl_health.

Example: qcache_hitrate=71.63%;90:;80: qcache_hitrate_now=8.25%

The Hitrate of the Query-Cache is calculated from Qcache_hits / ( Qcache_hits + Com_select ). This values are continuously increased. A serious change in access behaviour affects the hitrate only slowly. To be able to recognize temporarily fluctuations in the hitrate and, for example, assign it to an application update, the value qcache_hitrate_now is printed out additionally. This value is calculated through the difference (delta) between Qcache_hits and Com_select (actual value of the variables minus the value since the last run from check_mysql_health).

Here the command line parameter --lookback is used.

  • if this is missing, than qcache_hitrate_now is calculated from the difference (delta) between Qcache_hits and Com_select since the last run from check_mysql_health. Important for the exitcode of the plugin is the long-term result qcache_hitrate (since database start). 
  • if --lookback is specified with an argument n, than qcache_hitrate_now is calculated from the difference (delta) from Qcache_hits and Com_select since the last n seconnds.

    For example: With --lookback 3600 you'll get the average hitrate of the last hour, calculated back from the last plugin execution. The exitcode now also depends on this short-term test result.

It's recommended to use --lookback but specify at least half an hour (--lookback 1800) because the now-value underlies a heavy fluctuation which would lead to frequent alarms.

Pleae note, that the thresholds must be specified according to the Nagios plug-in development Guidelines.

"10" means "Alarm, if > 10" und

"90:" means "Alarm, if < 90"

Connect to the database

Creating a database user

In order to be able to collect the needed information from the database a database user with specific privileges is required:

grant usage on *.* to 'nagios'@'nagiosserver' identified by 'nagiospassword'

Connectionstring

To connect to the database you use the parameters --username and --password. The database server which should be used can be specified more precise with --hostname and --socket or --port.

Use of environment variables

It's possible to omit --hostname, --username and --password as well as --socket and --port completely, if you provide the corresponding values in environment variables. Since Version 3.x it is possible to extend service definitions in Nagios through own attributes (custom object variables). These will appear during the exectution of the check command in the environment.

The environment variables are:

  • NAGIOS__SERVICEMYSQL_HOST (_mysql_host in the service definition)
  • NAGIOS__SERVICEMYSQL_USER (_mysql_user in the service definition)
  • NAGIOS__SERVICEMYSQL_PASS (_mysql_pass in the service definition)
  • NAGIOS__SERVICEMYSQL_PORT (_mysql_port in the service definition)
  • NAGIOS__SERVICEMYSQL_SOCK (_mysql_sock in the service definition)

Examples

nagios$ check_mysql_health --hostname mydb3 --username nagios --password nagios
--mode connection-time
OK - 0.03 seconds to connect as nagios | connection_time=0.0337s;1;5

nagios$ check_oracle_health --mode=connection-time
OK - 0.17 seconds to connect  | connection_time=0.1740;1;5

nagios$ check_mysql_health --mode querycache-hitrate
CRITICAL - query cache hitrate 70.97% | qcache_hitrate=70.97%;90:;80: qcache_hitrate_now=72.25% selects_per_sec=270.00

nagios$ check_mysql_health --mode querycache-hitrate
--warning 80: --critical 70:
WARNING - query cache hitrate 70.82% | qcache_hitrate=70.82%;80:;70: qcache_hitrate_now=62.82% selects_per_sec=420.17

nagios$ check_mysql_health --mode sql
--name 'select 111 from dual'
CRITICAL - select 111 from dual: 111 | 'select 111 from dual'=111;1;5

nagios$ echo 'select 111 from dual' |
check_mysql_health --mode encode
select%20111%20from%20dual

nagios$ check_mysql_health --mode sql
--name select%20111%20from%20dual
CRITICAL - select 111 from dual: 111 | 'select 111 from dual'=111;1;5

nagios$ check_mysql_health --mode sql
--name select%20111%20from%20dual --name2 myval
CRITICAL - myval: 111 | 'myval'=111;1;5

nagios$ check_mysql_health --mode sql
--name select%20111%20from%20dual --name2 myval --units GB
CRITICAL - myval: 111GB | 'myval'=111GB;1;5

nagios$ check_mysql_health --mode sql
--name select%20111%20from%20dual --name2 myval --units GB
--warning 100 --critical 110
CRITICAL - myval: 111GB | 'myval'=111GB;100;110

Installation

The plugin requires the installation of a mysql-client packages. The installation of the perl-modules DBI and DBD::mysql is desirable, but not mandatory.

After unpacking the archive ./configure is called. With ./configure --help some options can be printed which show some default values for compiling the plugin.

  • --prefix=BASEDIRECTORY

    Specify a directory in which check_mysql_health should be stored. (default: /usr/local/nagios)

  • --with-nagios-user=SOMEUSER

    This User will be the owner of the check_mysql_health file. (default: nagios)

  • --with-nagios-group=SOMEGROUP

    The group of the check_mysql_health plugin. (default: nagios)

  • --with-perl=PATHTOPERL

    Specify the path to the perl interpreter you wish to use. (default: perl in PATH)

Download

check_mysql_health-2.1.8.2.tar.gz

check_mysql_health-2.1.8.2.shar.gz

Manche tar-Versionen haben Probleme wegen der langen Dateinamen. In diesem Fall entpacken sie bitte das shar-Paket mit

cat check_mysql_health-xxx.shar.gz | gzip -d | sh

Changelog

  • 2.1.8.2 2012-08-08
    - bugfix in querycache-hitrate (div by 0 after db restart). (Thanks Gianluca Varisco)
  • 2.1.8.1 2012-01-21
    - bugfix in timeout-alarm handling under windows
    - fix warnings for newest perl versions
  • 2.1.8 2011-09-29
    - new parameters --mycnf and --mycnfgroup
    - single ticks around the --name argument under Windows CMD will be removed automatically
  • 2011-08-23 2.1.7
    innodb modes now detect problems with the innodb engine
  • 2011-08-12 2.1.6
    fix a bug with statefilesdir and capital letters

    add --labelformat so that groundwork no longer complains (max label length is 19 characters)

  • 2011-01-03 2.1.5.1
    bugfix in mode sql (numerical vs. regexp output)
  • 2010-12-20 2.1.5
    fixed a division by zero bug in index-usage (Thanks Wiltmut Gerdes)

    fixed a severe bug when loading dynamic extensions (Thanks Ralph Schneider)

    added mode table-fragmentation

    fixed a bug in table-lock-contention (thanks mayukmok00)

    mode sql can now have a non-numerical output which is compared to a string/regexp

    new parameter --dbthresholds

    new mode report can be used to output only the bad news (short,long,html)

  • 2010-10-02 2.1.4
    added modes threads-created, threads-running, threads-cached

    added connects-aborted, clients-aborted

  • 2010-10-01 2.1.3
    added mode open-files

    fix a bug in the pnp template

    add extra-opts

  • 2010-06-10 2.1.2 Changed some statements for better 4.x compatibility. (Thanks Florian)
  • 2010-03-30 2.1.1 More tracing (touch /tmp/check_mysql_health.trace to watch), fixed a bug in master-slave modes, so it outputs a more meaningful error message (Thanks Will Oberman), fixed a typo (Thanks Larsen)
  • 2009-10-02 2.1 New parameter --lookback
  • 2009-09-20 2.0.5 Bugfix in master-slave modes. (Danke Thomas Mueller). Bugfix in bufferpool-wait-free. (Danke Matthias Flacke). Bugfix im PNP template. (Danke Matthias Flacke). Mode slave-lag bemerkt failed io threads. (Danke Greg)
  • 2009-04-02 2.0.4 Bugfix bei mode cluster-ndb-running, Bugfix im Master/Slave-Code. (Danke Arkadiusz Miskiewicz)
  • 2009-03-18 2.0.3 Bugfix wg. warning=0, Bugfix in long-running-procs (betr. MySQL < 5.1) (Danke Bodo Schulz)
  • 2009-03-11 2.0.1 Störende Uninitialized-Meldungen beseitigt (Danke John Alberts & Thomas Borger). Passwortloses login an localhost ist jetzt möglich.
  • 2009-03-06 2.0 erste öffentliche Version

Copyright

Gerhard Laußer

Check_mysql_health is published under the GNU General Public License. GPL

Author

Gerhard Laußer (gerhard.lausser@consol.de) gladly answers questions to this plugin.

Translation

Thanks to Christian Lauf there is finally an english translation of this page :-)