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. 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.2.tar.gz Changelog - 2.2 - 2015-04-23
add rfc3986-encoded passwords - 2.1.9.2 2014-12-22
bugfix in InnoDB initialization & versions > 5.6.1 (Thanks Jorg Veit) - 2.1.9.1 2014-06-12
add connections_aborted, open_files to the pnp template (Thanks Simon Meggle) - 2.1.9 2014-06-12
bugfix in pnp template (Thanks Simon Meggle)
bugfix in qcache calculation (Thanks lermit) - 2.1.8.4 2014-04-01
implement –negate old_level=new_level
allow floating point numbers in thresholds - 2.1.8.3 2012-10-15
output also ok-messages for my-modes - 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 auto
matically - 2.1.7 2011-08-23
innodb modes now detect problems with the innodb engine - 2.1.6 2011-08-12
fix a bug with statefilesdir and capital letters
add –labelformat so that groundwork no longer complains (max label length is 19 characters) - 2.1.5.2 2011-06-03
sites in an OMD (http://) environment have now private statefile directories - 2.1.5.1 2011-01-03
bugfix in –mode sql (numeric vs. regexp result) - 2.1.5 2010-12-20
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) - 2.1.4 2010-10-02
added modes threads-created, threads-running, threads-cached
added connects-aborted, clients-aborted - 2.1.3 2010-09-29
added mode open-files
fix a bug in the pnp template
add extra-opts - 2.1.2 2010-06-10
changed statements for 4.x compatibility (show variables like) (Thanks Florian) - 2.1.1 2010-03-30
added 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) - 2.1
parameter –lookback uses custom intervals for _now-values - 2.0.5 2009-09-21
fixed another bug in master-slave modes. (Thanks Thomas Mueller)
fixed a bug in bufferpool-wait-free. (Thanks Matthias Flacke)
fixed a bug in the PNP template. (Thanks Matthias Flacke)
slave-lag now handles failed io threads. (Thanks Greg)
fixed a bug in connections with non-standard sockets (Thanks Stephan Huiser) - 2.0.4
fixed a bug in –mode cluster-ndbd-running where dead api nodes were overseen
fixed a bug in master-slave modes. (Thanks Arkadiusz Miskiewicz) - 2.0.3
fixed a bug with 0 warning/critical
fixed a bug in long-running-procs (affects only mysql 5.0 and below). (Thanks Bodo Schulz) - 2.0.2
$NAGIOS__HOSTMYSQL_HOST etc. is now possible - 2.0.1 2009-03-09
fixed a (harmless) bug which caused uninitialized-messages. (Thanks John Alberts & Thomas Borger)
enabled password-less login to localhost. - 2.0 2009-03-06
This is the first release of the new plugin check_mysql_health
It replaces check_mysql_perf which is a nightmare to install
It is written in Perl
It can use either DBD::mysql, the mysql command or DBD::SQLrelay
It can monitor mysql clusters (the ndb stuff)
It can execute custom sql statements Copyright Gerhard Lau?er Check_mysql_health is published under the GNU General Public License. GPL Author Gerhard Lau?er (gerhard.lausser@) gladly answers questions to this plugin. Translation Thanks to Christian Lauf there is finally an english translation of this page :-)
|
