nagios监控mysql插件：

http://labs.consol.de/lang/en/nagios/check_mysql_health/

check_mysql_health

Posted on July 6th, 2009 by admin

Description

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

Command line parameters

• –hostname
  
  The database server which should be monitored. In case of "localhost" this parameter can be omitted.
• –username
  
  The database user.
• –password
  
  Password of the database user.
• –mode
  
  With the mode-parameter you tell the plugin what it should do. See the list of possible values further down.
• –name
  
  Here the check can be limited to a single object. (Momentarily this parameter is only used for mode=sql)
• –name2
  
  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
  
  Determined values outside of this range trigger a WARNING.
• –critical
  
  Determined values outside of this range trigger a CRITICAL.
• –environment =
  
  With this you can pass environment variables to the script. Multiple declarations are possible.
• –method
  
  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.

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

= and =

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.7.tar.gz

check_mysql_health-2.1.7.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