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 <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 :-)

101 Responses to “check_mysql_health”

  1. Jozef Fulop Says:
    November 12th, 2009 at 18:35

    To check replication related parameters (slave-lag, slave-io-running, etc.) you need also these privileges: grant super,replication client on . to nagios@foo

    lausser Reply:

    Yes, that’s true. Thank you for pointing this out. And another common pitfall…you need to run check_mysql_health against the slave when checking replication.

  2. Larsen Says:
    December 3rd, 2009 at 14:02

    Hi,

    checking MySQL 4 servers doesn´t work with check_mysql_health: “CRITICAL – cannot connect to information_schema”. It used to work with check_mysql_perf 1.3.

    Is there a workaround?

    lausser Reply:

    Please try it with this testscript:

    use DBI;</p>
     
    <h1>please change these settings if necessary</h1>
     
    <p>$self->{hostname} = 'mydb3';
    $self->{username} = 'nagios';
    $self->{password} = 'nagios';
    $self->{port} = 3306;
    $self->{database} = 'information_schema';
    #
    $self->{dsn} = "DBI:mysql:";
    $self->{dsn} .= sprintf "database=%s", $self->{database};
    $self->{dsn} .= sprintf ";host=%s", $self->{hostname};
    $self->{dsn} .= sprintf ";port=%s", $self->{port};
    eval {
      if ($self->{handle} = DBI->connect(
          $self->{dsn},
          $self->{username},
          $self->{password},
          { RaiseError => 1, AutoCommit => 0, PrintError => 1 })) {
        printf "connected&#92;n";
        $self->{handle}->disconnect();
      } else {
        printf "%s&#92;n", DBI::errstr();
      }
    };
    if ($@) {
      printf "%s&#92;n%s&#92;n", $@, DBI::errstr();
    }

    Jens Rantil Reply:

    @lausser,

    I am experiencing the same issue as Larsen. However, your script above works perfectly. Any hint?

    Jens

    Jens Rantil Reply:

    It seems the error might be related to the fact that I am connecting with a user that has no password. Is anyone able to recreate this?

    Jens Rantil Reply:

    Finally, I was able to fix it by applying a small patch: http://pastebin.com/m4067bf7e

    The script required a password. This should not be a requirement (unless the user has a password, of course).

  3. Andreas Says:
    December 16th, 2009 at 12:03

    Hallo. Erstmal: tolles plugin!!!

    Ich habe eine Frage zu: tmp-disk-tables.

    Es wird bei mir dieser Wert angezeigt: “WARNING – 32.07% of 1059 tables were created on disk”

    Aufruf mit: –method mysql –mode tmp-disk-tables

    Die DB sagt mir aber (show status) das hier: | Created_tmp_disk_tables 0 | Created_tmp_files 5 | Created_tmp_tables 1

    Wie kann hier dieser Wert dann zusammenkommen?

    Viele Grüße und Danke!

    Andreas

    lausser Reply:

    Mit SHOW STATUS bekommst du nur die Werte deiner aktuellen Session. Das Plugin ruft aber SHOW GLOBAL STATUS auf. Damit bekommt man die Summe aller Sessions und dann sieht’s gleich ganz anders aus. Gerhard

    Andreas Reply:

    @lausser, Hallo Gerhard. Besten Dank für die Antwort. Dann werde ich gleich mal die GLOBALE Sicht nehmen … :-)

    Schön, dass Du deiner Arbeit zur Verfügung stellst.

    Danke.

    Andreas.

  4. Mandy Says:
    December 24th, 2009 at 13:37

    Hey,

    Thanks for such a cool plugin.

    One thing that I couldn’t find here was to check for queries_per_second.

    Is it something that’s easily available through –mode ?

    Please let me know.

    -Mandy.

    lausser Reply:

    queries (selects) per second are part of –mode querycache-hitrate

  5. Will Oberman Says:
    January 15th, 2010 at 16:32

    The “slave-lag” test was failing with the following error:

    Can’t locate object method “errstr” via package “DBD::MySQL::Server::Connection::Dbi” at /usr/local/nagios/libexec/check_mysql_health line 368.

    I’m 90% sure the bug is trying to use $self->{handle}->errstr() verses DBI::errstr(). I changed line 368 to:

    $self->add_nagios_critical(sprintf “unable to get replication info%s”, DBI::errstr());

    And now I get a meaningful error:

    you need the SUPER,REPLICATION CLIENT privilege for this operation

    Which lines up with the first comment above (though, it’s easier to figure that out with a good error message) ;-)

    lausser Reply:

    Good catch! errstr is an attribute, not a method here. Can you please try $self->{handle}->{errstr} instead of DBI::errstr()?

  6. John McLear Says:
    January 19th, 2010 at 0:20

    Great plugin, using it on a few boxes :) good work!

  7. John McLear Says:
    January 19th, 2010 at 19:11

    ./check_mysql_health –hostname myhostname –username myuser –password mypass –warning=10 –critical=20 –mode slave-lag Can’t locate object method “errstr” via package “DBD::MySQL::Server::Connection::Dbi” at ./check_mysql_health line 368.

    Any idea on this? This check was working fine, it appears that maybe its returning NULL because there is no slave-lag?

    Plugin is working, see below:

    ./check_mysql_health –hostname myhostname –username myuser –password mypass –warning=10 –critical=20 –mode qcache-hitrate OK – query cache hitrate 9.45% | qcache_hitrate=9.45%;10;20 qcache_hitrate_now=9.45% selects_per_sec=0.00

  8. John McLear Says:
    January 19th, 2010 at 19:12

    oh I just read above, heh. weird how google didn’t pick up this page when i googled the error :/

    lausser Reply:

    i’ll release a buxfix-version soon. :-)

  9. Mark Reynolds Says:
    January 28th, 2010 at 12:35

    Hi,

    Thanks for a great plug in! I am monitoring thread cache hit rate and I would have thought having a high hit rate is a good thing, however the warning and critical values appear to be treated as a greater than threshold rather than a less than e.g.

    ./check_mysql_health –hostname server –username nagios –password “#BK79o6&” –mode threadcache-hitrate –warning 60 –critical 40

    Gives

    CRITICAL – thread cache hitrate 95.52% | thread_cache_hitrate=95.52%;60;40 thread_cache_hitrate_now=100.00% connections_per_sec=0.03

    I would have thought that should return OK not CRITICAL.

    Is there anyway to invert this?

  10. Mark Reynolds Says:
    January 28th, 2010 at 13:32

    Sorry to reply to my own post – I have just found the documentation that states adding a : to my value means it will do a less than rather than greater than.

  11. Tarak Ranjan Says:
    February 12th, 2010 at 8:49

    Hi ,

    I have used check_mysql_health in my nagios server. When i am running the checks from the command line , it works fine.

    but when it’s displaying the output on Nagios frontend , that time i’m getting the below error….

    ” **ePN failed to compile /usr/local/nagios/libexec/check_mysql_health: “Missing right curly or square bracket at (eval 23) line 3116, at end of line “

    Please help

    Christian Reply:

    @Tarak Ranjan, Hi Tarak, its a bit late to answer this but maybe it’ll help someone other.

    Your error message seems to indicate ePN has a Problem executing the plugin. Try to disable ePN in the nagios.cfg (Change: enable_embedded_perl=1 to enable_embedded_perl=0), restart Nagios an try again.

  12. Felipe Ferreira Says:
    March 15th, 2010 at 16:29

    Great Plugin! Thanks for the good work. Is there a simple way to check a table or a DB size? Also when deadlocks are a happening?

    thanks

    lausser Reply:

    I’ll put deadlocks on my list. It’s surely not be done with a few lines of code, so it may take a while. The db/tablesize is something you should be able to implement yourself, if you can manage to get the size with a single sql statement (–mode sql). You can also look into the contrib subdirectory to see how more complicated statements can be used to add functionality.

  13. Larsen Says:
    March 30th, 2010 at 16:06

    Hi, the errstr problem also exists in other lines and there is a small typo in line 366 “…io thead is not…”

    lausser Reply:

    Thanks, i forgot to publish the fixed version. You can now download 2.1.1 with the corrected errstr and typo.

  14. Pat Bastien Says:
    April 26th, 2010 at 18:20

    I am having the same issue with the Nagios embedded perl ePN compiler running the check_mysql_health plugin that Tarak Ranjan is having. [ **ePN failed to compile /usr/lib/nagios/plugins/check_mysql_health: Missing right curly or square bracket at (eval 12) line 3190, at end of line] I have to run the plugin using the external perl to get it to work. I’d like to use several of the measures in your great plugin but can’t if I have to load perl every measure/server. I tried determining why ePN is complaining by running the plugin with perl -c and using strict mode (which you already use) as recommended by Nagios group, but it doesn’t return any complaints. Note that I am running Nagios 2.7 — could that be the issue? Thanks.

    lausser Reply:

    Hi, i don’t think check_mysql_health is running with ePN at all.

  15. kim Says:
    May 23rd, 2010 at 11:16

    thks a lot

  16. seteqsystems Says:
    June 3rd, 2010 at 13:32

    Hi,

    On line 864 you should replace SHOW VARIABLES WHERE Variable_name = ‘version'; with SHOW VARIABLES LIKE ‘version';

    Because MySQL4 does not support WHERE with SHOW VARIABLES :)

    best regards Florian

    lausser Reply:

    Ah, thx. Can you please confirm, that this patch also works correctly on MySQL 5.x?

  17. wangjun Says:
    June 6th, 2010 at 12:43

    Could you offer the explanation of English version for your plugin. All in German.

    Thank you

    lausser Reply:

    Sorry, i didn’t have the time yet. In the meantime you might try google translate. The output is not too bad imho.

  18. Ed Says:
    June 18th, 2010 at 15:12

    Great plugin. One of the best I’ve seen for Nagios so far.

  19. Dennis Says:
    June 22nd, 2010 at 10:15

    Hi,

    a wrote a mysql-performance plugin in the past and queries/second has been a really useful indicator. It think, even if it is a part of the query-cachehit output, the data itself has its value.

    Far more important is the observation, that in mode “index-usage” as well as “querycache-hitrate” the critical value is assumed to be bigger, than the warning value, which may not be correct, in these to modes.

    I would appreciate any help.

    lausser Reply:

    Hi, did you notice the “:”? (according to the plugin developer guidelines where ‘:number’ is ‘less than number’) Default:

    nagios$ check_mysql_health --mode querycache-hitrate 
    CRITICAL - query cache hitrate 70.97% | qcache_hitrate=70.97%;90:;80:
    90: = less than 90, 80: = less than 80. So when you specify your own thresholds, you must use the ‘:’ too.
    nagios$ check_mysql_health --mode querycache-hitrate \ 
    --warning 80: --critical 70: 
    WARNING - query cache hitrate 70.82% | qcache_hitrate=70.82%;80:;70:

    Dennis Reply:

    Oh Sorry, actually i didnt. Sorry about that and thanks for your quick help.

  20. Tobias Says:
    July 5th, 2010 at 16:29

    Hallo!

    Ich muss mich anschließen: das Plugin ist super, tolle Arbeit, danke fürs freigeben!

    In den Zeilen 1244, 1937 und 2512 der Version 2.1.2 wird die open-Funktion verwendet ohne zu prüfen, ob diese erfolgreich war. Man bekommt als Fehlermeldung, falls was mit dem Schreiben nicht hinhaute ein “Could not write to closed filehandle” anstelle eines möglichen “Could not write to file xyz”. Meiner Meinung nach sollten Operationen auf dem dem Dateisystem, o.ä. immer kontrolliert werden. Das ist Geschmackssache, kein Fehler, klar.

    Viele Grüße nach München Tobias

    lausser Reply:

    Ja, das könnte man verbessern. Üblicherweise krachts aber nur, wenn mal wieder so ein Spezialist das Plugin als root aufgerufen hat (pfui, pfui und nochmals pfui) und die erzeugte temporäre Datei somit root gehört. Wenn nachher der Nagios-Prozess die Plugin-Ausführung übernimmt, kann die Datei nicht mehr überschrieben werden und die fehlgeschlagene open-Funktion reisst den ganzen Prozess runter. Zur Strafe sollte es eigentlich den ganzen Rechner zerlegen :-) Das Blöde ist, daß es von der Programmierung her nicht leicht ist, einen Fehler im open “nach Oben” durchzureichen. Ich mag es gar nicht, mitten im Programm mit einem die oder exit rauszugehen, aber hier wird wohl nichts anderes übrig bleiben. Ich bin grad im Urlaub und schau es mir nächste Woche an.

  21. edvard.pohl Says:
    July 8th, 2010 at 11:26

    For all with mysql 4.x . I also had a problem with geting it work with 4.x mysql and it’s because this proggi use ” information_schema” as default db which don’t exists in 4.x mysql. There is a –database option which you could use.

    lausser Reply:

    I don’t have a 4.x db at hand. Is there an equivalent to information_schema in 4.x?

  22. uvdevnull Says:
    July 15th, 2010 at 0:53

    Hi lausser, The english page (http://labs.consol.de/lang/en/nagios/check_mysql_health/) doesn’t actually show in english, still german. And for some reason, google is also unable to translate it. Any clues?

    Christian Reply:

    @uvdevnull, by now it should be translated :)

  23. box2 Says:
    August 3rd, 2010 at 0:28

    Let me start by saying I love this plugin! Very effective.

    Using check_mysql_health with ‘–mode bufferpool-hitrate’ makes pnp4nagios choke trying to process perfdata into pretty RRD graphs (template: Innodb buffer pool hitrate). It gives me the red screen of death :( (ERROR: invalid rpn expression in: …)

    All the other modes work great using the templates that come in /usr/local/pnp4nagios/share/templates.dist/check_mysql_health.php

    check_mysql_health version: 2.1.2 pnp4nagios version: 0.6.5

    I don’t know if you or Joerg @ pnp can/should solve this, but I don’t know how, so I will use default template until I hear it works :(

    lausser Reply:

    There was a bug in the template. I informed Joerg to package a corrected version with pnp. Meanwhile you can get the template from http://github.com/lausser/check_mysql_health/blob/2.1.2.1/contrib/check_mysql_health.php

  24. box2 Says:
    August 3rd, 2010 at 17:00

    You’re awesome, thank you!

  25. Aaron Says:
    August 11th, 2010 at 1:00

    Hi,

    First off, let me thank you for this great plugin. Best MySQL plugin IMO.:D

    Anyway, I’m having issues when I use the plugin to check for remote hosts, might be a stupid error but I’m a bit new with Linux so, yeah. :p

    Basically, I tested it with the local MySQL where Nagios is also running and I get a ” is not allowed to connect to this MySQL server” but when I use the loopback IP 127.0.0.1, I get the intended reply. :-/

    Help please!

    [root@XXXX libexec]# ./check_mysql_health –hostname XXXX –username root –password XXXX –mode connection-time CRITICAL – cannot connect to information_schema. Host ‘XXXX.com’ is not allowed to connect to this MySQL server [root@XXXX libexec]#

    If I use localhost address 127.0.0.1, the plugin works

    [root@xxxx libexec]# ./check_mysql_health –hostname 127.0.0.1 –username root –password xxxx –mode connection-time OK – 0.04 seconds to connect as root | connection_time=0.0434s;1;5 [root@xxxx libexec]#

    Thanks & Regards,

    Aaron

    lausser Reply:

    you need another user. Mysql does not only look at the username, but a username-hostname-combination.

    GRANT ALL privileges TO 'root'@'XXXX' ....

    Aaron Reply:

    Thanks again!

  26. Jorge G. Says:
    September 24th, 2010 at 17:53

    When using the -mode sql and a query returns NULL, the plugin exits with UNKNOWN. Can this be changed to return 0 and then exit properly based-on the thresholds?

    Thank you for all your work on this.

    lausser Reply:

    So you mean the sql statement usually returns a numerical value, only in some cases a NULL value? Did you try NVL?

    SELECT nvl(column_sometimes_null, 0) FROM TABLE
  27. Christian Says:
    October 19th, 2010 at 22:22

    hello,

    i tried the template which lausser wrote down above. In a few checks, i get a red failure and 2 of get following messages: Template /opt/pnp4nagios/share/templates/check_mysql_health.php does not provide array $def[]. Read FAQ online

    Four or Five are workign fine. Can someone please help me?

    Thanks in advance

  28. N Britz Says:
    October 26th, 2010 at 14:54

    Hi,

    ich hätte da einen klitzekleinen Feature-Request. Wäre schön, wenn threads_running. threads created und threads_cached auch noch abgefragt würden. Bevor ich das selber reinbastel, wäre es irgendwie netter, das als offzielle Erweiterung zu haben. :-) Bestünde hier die Possibilität?

    Grüße vom Stiglmaierplatz N

    lausser Reply:

    Kann ich machen. Wenn du mir die SQL-Statements mailst, geht’s schneller, da ich grad etwas unter Wasser bin (bzw. zu faul, um selber nachzuschauen)

    Grüße aus dem ICE 625

  29. Christophe Says:
    October 28th, 2010 at 11:24

    Thx for this great plugins.

    Do you think it is easy to modify your plugins for doing several mode in one check and return allthe perfs data on a same line ?

    Thx

    lausser Reply:

    Multiple modes was never intended and is not possible. It would require a complete rewrite of the plugin. But you can easily combine several instances of check_mysql_health by running them in parallel with check_multi: http://my-plugin.de/wiki/projects/check_multi/start

  30. OMD Version 0.44 erschienen » klimmbimm Says:
    November 15th, 2010 at 17:56

    [...] … dienen der Abfrage von Parametern der bekannten Datenbanksysteme. Mehr Infos auf http://labs.consol.de/lang/de/nagios/check_oracle_health/ und http://labs.consol.de/lang/de/nagios/check_mysql_health/ [...]

  31. Mike Hathaway Says:
    November 17th, 2010 at 20:40

    Does anyone have examples from the commands.cfg of how they use the script and then the actual call from what I guess would be the mysql.cfg file in nagios? I am having trouble creating these from scratch. I do have the command line plug in working and have checked a few boxes from the command.

    Thanks

    jnicol Reply:

    @Mike Hathaway, Hi Mike, I like to tune on a host-by-host basis, so all I have for the command_line is this: $USER1$/check_mysql_health –lookback 300 –username $ARG1$ –password $ARG2$ –mode $ARG3$ –warning $ARG4$ –critical $ARG5$ Then some of the check_commands look like this: check_mysql_health!user!password!slow-queries!0.5!2 check_mysql_health!user!password!bufferpool-hitrate!98:!95: check_mysql_health!user!password!threads-connected!120!135

  32. jnicol Says:
    November 18th, 2010 at 22:32

    Hi, thanks for the great plugin!

    I’m actually having some trouble getting warning/critical thresholds working with slave-lag

    We have a slave that purposely lags behind (using mk-slave-delay). It’s set to remain several hours behind, so I want to alarm both if its too caught up or too far behind. I’m not sure if I’m misunderstanding the options or if this is a bug?

    Normal usage works as expected (critical if > 16000): check_mysql_health –critical 16000 CRITICAL – Slave is 18599 seconds behind master

    This doesn’t (critical if < 14000 or > 16000) check_mysql_health –critical 14000:16000 OK – Slave is 18619 seconds behind master

    Neither does this (critical if < 20000) check_mysql_health –critical 20000: OK – Slave is 18676 seconds behind master

    lausser Reply:

    Well, normal usage is: warning/critical if lag is bigger than a threshold. Everything else is exotic and will be not implemented. So, it is intended behavior.

    jnicol Reply:

    @lausser,

    Understood, thanks much for the reply. I’ve since realized this wouldn’t work for me anyway. mk-slave-delay stops the slave’s sql thread to let it fall behind, and the slave lag can’t be read while it’s stopped.

  33. Mike Hathaway Says:
    November 19th, 2010 at 18:37

    I have it up and running! Configuring this plug in forced me to learn a lot more about how nagios works, which is always a good thing. I am posting my mysql.cfg file as a starting point for other people. If you have better ideas let me know, I am not an expert with mysql. http://mikehathaway.com/content/nagios-and-mysql

    This is a great plugin, thanks guys.

  34. box2 Says:
    November 22nd, 2010 at 20:42

    Any plans for adding pnp templates for the slave_io / slave_sql / slavelag checks?

    lausser Reply:

    No, but if you’re willing to write the templates, we will gladly integrate them.

    hec Reply:

    @box2

    hi, slave io and slave sql doesn`t create perfdata output, i think…

    so why just copy on of the existing entrys and edit them like this:

    if(preg_match(‘/^slave_lag$/’, $NAME[$i])) { $ds_name[$defcnt] = “Slave lag”; $opt[$defcnt] = “–vertical-label \”sec\” –title \”Slave $hostname behind master\” “; $def[$defcnt] = “”; $def[$defcnt] .= “DEF:slag=$RRDFILE[$i]:$DS[$i]:AVERAGE:reduce=LAST ” ; $def[$defcnt] .= “AREA:slag#111111 “; $def[$defcnt] .= “VDEF:vslag=slag,LAST ” ; $def[$defcnt] .= “GPRINT:vslag:\”currently %.0lf s Slave lag \” ” ; $defcnt++; }

    hope thats ok…

  35. Matrak Says:
    November 29th, 2010 at 16:03

    Hello all,

    Is it possible with this plugin to monitor multiple database ?

    Example : ./check_mysql_health –database ‘list of several sql dabatse’

    Thanks for your help

    lausser Reply:

    No, you can’t do that. Have a look at check_multi http://my-plugin.de/wiki/projects/check_multi/discussion

    Matrak Reply:

    @lausser,

    Arf, thanks for the link, i want to test this solution :)

  36. mayukmok00 Says:
    December 15th, 2010 at 17:14

    Hi,

    This is a great nagios plugin. Have installed in on nagios core 3.2.3 compliled from source. Most of the script worked but when it comes to table lock contention I experience this error “Use of uninitialized value in sprintf at /usr/local/nagios/libexec/check_mysql_health line 777. OK – table lock contention 0.00% (uptime < 10800) | tablelock_contention=0.00%;1;2 tablelock_contention_now=0.00%”.

    Any idea what might caused this?

    Thank you

    lausser Reply:

    You found a bug :-( In line 779 please change the $self->{refkey} to $self->{$refkey}

    Will be corrected in the next release.

  37. micah Says:
    December 27th, 2010 at 0:33

    I noticed you added the option –dbthresholds, but I cannot find any documentation of this option. Its not listed in –help as an option either.

    lausser Reply:

    It’s documented in the source.

  38. 17 Nagios-Fliegen mit einer Klappe: OMD 0.44 | KenntWas.de - Technische Tips Says:
    January 7th, 2011 at 22:49

    [...] Plugin check_mysql_health aus den Labs der Fa. Consol überprüft Mysql [...]

  39. jerdna Says:
    February 7th, 2011 at 8:56

    Hello.

    Please help. I monitoring MySQL server. In report print whith line:

    printf() on closed filehandle STATE at /usr/local/icinga/libexec/check_mysql_health line 1521.

    Where resolve problem?

    lausser Reply:

    DO NOT RUN PLUGINS UNDER THE ROOT-ACCOUNT! NEVER!! NEVER!! NEVER!! Now delete or chown the directory /var/tmp/check_mysql_health.

  40. Marcus Padovani Says:
    February 9th, 2011 at 11:56

    Hallo,

    leider bekomme ich das Plugin nicht kompiliert.

    Making all in plugins-scripts make[1]: Betrete Verzeichnis ‘/home/elpado/Downloads/check_mysql_health-2.1.5/plugins-scripts’ /bin/echo “#! #PERL# -w” | /usr/bin/gawk -f ./subst > check_mysql_health /bin/echo “# nagios: -epn” >> check_mysql_health /bin/echo >> check_mysql_health /bin/echo “my %ERRORS=( OK => 0, WARNING => 1, CRITICAL => 2, UNKNOWN => 3 );” >> check_mysql_health /bin/echo “my %ERRORCODES=( 0 => ‘OK’, 1 => ‘WARNING’, 2 => ‘CRITICAL’, 3 => ‘UNKNOWN’ );” >> check_mysql_health for m in Nagios/DBD/MySQL/Server/Instance/Innodb.pm Nagios/DBD/MySQL/Server/Instance/Myisam.pm Nagios/DBD/MySQL/Server/Instance/Replication.pm Nagios/DBD/MySQL/Server/Instance.pm Nagios/DBD/MySQL/Server.pm Nagios/DBD/MySQL/Cluster.pm Nagios/Extraopts.pm; do \ /bin/sed -e ‘s/^1;//g’ < $m | /usr/bin/gawk -f ./subst | /bin/grep -v “my %ERROR” >> check_mysql_health; \ done if [ -d "/usr/local/nagios/libexec" ]; then \ for m in /usr/local/nagios/libexec/CheckMySQLHealthExt*.pm; do \ if [ -f $m ]; then \ /bin/echo found $m; \ /bin/sed -e ‘s/^1;//g’ < $m | /usr/bin/gawk -f ./subst | /bin/grep -v “my %ERROR” >> check_mysql_health; \ fi \ done \ fi /bin/cat check_mysql_health.pl | /bin/grep -v “^use Nagios” | /bin/grep -v “^my %ERROR” | /usr/bin/gawk -f ./subst >> check_mysql_health chmod +x check_mysql_health make[1]: Verlasse Verzeichnis ‘/home/elpado/Downloads/check_mysql_health-2.1.5/plugins-scripts’ Making all in t make[1]: Betrete Verzeichnis ‘/home/elpado/Downloads/check_mysql_health-2.1.5/t’ make[1]: Für das Ziel »all« ist nichts zu tun. make[1]: Verlasse Verzeichnis ‘/home/elpado/Downloads/check_mysql_health-2.1.5/t’ make[1]: Betrete Verzeichnis ‘/home/elpado/Downloads/check_mysql_health-2.1.5′ make[1]: Für das Ziel »all-am« ist nichts zu tun. make[1]: Verlasse Verzeichnis ‘/home/elpado/Downloads/check_mysql_health-2.1.5′

    Jemand eine Idee woran es liegen könnte?

    Viele Grüße, Marcus Padovani

    Marcus Padovani Reply:

    @Marcus Padovani,

    Hallo,

    ich habe wohl die Ausgabe falsch verstanden. Das Plugin wurde offensichtlich korrekt compiliert und installiert.

    Alles schön :)

    Viele Grüße, Marcus Padovani

  41. John Says:
    February 16th, 2011 at 0:42

    Hi, Thanks for this plugin.

    I get this error due to missing DBD::mysql. I couldn’t install the perl module due to conflicts with my version of MySQL server. I have MySQL-client installed. How do i go around without installing the perl mod?

    THANKS.

    CRITICAL – cannot connect to TestDB. install_driver(mysql) failed: Can’t locate DBD/mysql.pm in @INC (@INC contains: . /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8) at (eval 13) line 3. Perhaps the DBD::mysql perl module hasn’t been fully installed, or perhaps the capitalisation of ‘mysql’ isn’t right. Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge. at ./check_mysql_health line 1784

    lausser Reply:

    Install the mysql client software, which means: make sure, you have the mysql command in your $PATH. Then simply add ‘–method mysql’ to the argument list of the plugin. Instead of using DBD::Mysql to communicate with the database, it will use the mysql command.

  42. micah Says:
    March 19th, 2011 at 16:15

    I’m having difficulty understanding the warning/error threasholds for the threads-cached check. Why are more cached threads treated like its more of a problem than less? I get quite a few warnings/criticals for threads-cached and am trying to understand what I can tune in my applications, in mysql configurations as well as the check_mysql_health plugin. Thanks for any tips!

  43. Andre Kapp Says:
    March 22nd, 2011 at 11:19

    Good day.

    I’m running the following: OS: 64 Bit CentOS 5.5 MySQL: 5.5.9

    I have downloaded the plugin, run a make and then a make install. The executable was placed in the /usr/local/nagios/libexec folder. I change the permissions to be correct. I also create the database user for nagios.

    I then run the following command: ./check_mysql_health –host 127.0.0.1 –username nagios –password xxxxxx –mode threads-connected –warning 90 –critical 100 OK – 7 client connection threads | threads_connected=7;90;100

    The output seems fine …..

    Now the two problems I am experiencing: 1. When using the documented notation of 90:, I get the following error ./check_mysql_health –host 127.0.0.1 –username nagios –password xxxxxxxx –mode threads-connected –warning 90: –critical 100 Argument “90:” isn’t numeric in sprintf at ./check_mysql_health line 749. OK – 7 client connection threads | threads_connected=7;90;100

    It seems to be confused by the “:” character.

    Problem 2: I have the following command defined in commands.cfg #

    Define command so we can use it to check various mysql paramaters

    # define command{ command_name check_mysql_health command_line $USER1$/check_mysql_health –host $HOSTADDRESS$ –username $ARG1$ –password $ARG2$ –mode $ARG3$ –warning $ARG4$ –critical $ARG5$ }

    I have the following service defined: #

    Define services to check various mysql parameters

    command_line $USER1$/check_mysql_health –host $HOSTADDRESS$ –username $ARG1$ –password $ARG2$ –mode $ARG3$ –warning $ARG4$ –critical $ARG5$

    # # define service { use local-service host_name localhost service_description MYSQL ThreadsConnected check_command check_mysqld!nagios!xxxxxxxx!threads_connected!20!50 }

    This validation process is happy with my config and nagios starts well. However- the following is displayed on the gui page:

    MYSQL ThreadsConnected

    UNKNOWN     03-22-2011 11:04:03     0d 0h 59m 3s    4/4     UNKNOWN - mode threads_connected 
    

    I also get the following email errors:

    ***** Nagios *****

    Notification Type: PROBLEM

    Service: MYSQL ThreadsConnected Host: localhost Address: 127.0.0.1 State: UNKNOWN

    Date/Time: Tue Mar 22 10:37:29 SAST 2011

    Additional Info:

    UNKNOWN – mode threads_connected

    Any idea what I am doing wrong here. Running from command line all seems fine, but running as a service it is not happy ?

    Thank you Andre

    Andre Kapp Reply:

    @Andre Kapp, Sorry – missed this bit – I’m running Nagios 3.2.3

    w1 Reply:

    Further testing: slow-queries. slave-lag works fine with the “:” character as well as when defined as a service…

    open-files also fails when using the “:” character

    lausser Reply:

    open-files and threads-connected are not supposed to be used with ‘less than’-thresholds. That’s why the plugin only handles numerical thresholds here. I see no reason to check if the number of open files is less than a certain percentage of max.

    lausser Reply:

    in your config you write threads_connected, but the correct name is threads-connected. (dash, not underscore)

    Andre Reply:

    Tks a lot lausser. This is what happens when you do way too many hours per day. Works like a dream now! Stunning plugin!

  44. tbrinkmann Says:
    April 7th, 2011 at 11:10

    Hi…. ist der mysql-community-server-client auch notwendig wenn auf dem Nagios Serversystem ein MySQL Server läuft ?

    Danke *T

    lausser Reply:

    Wenn das Plugin mit ‘–method mysql’ aufgerufen wird, ja. Denn dann werden die SQL-Statements erst an das mysql-Binray übergeben und von diesem an den DB-Server. mysql ist Bestandteil des client-RPMs. Alternativ (‘–method dbi’ oder gar kein –method) braucht man ein perl-DBD-mysql, dann läuft die Kommunikation zwischen Plugin und DB-Server über ein Perl-Modul.

  45. MrCleanX Says:
    April 7th, 2011 at 23:46

    CRITICAL – index usage 0.09% | index_usage=0.09%;90:;80: index_usage_now=0.00%

    Critical at 0.09% doesn’t seem right.

    lausser Reply:

    As long as you don’t provide details (execute the sql statement behind index-usega manually, creating a tracefile etc., 0.09% is perfectly correct)

  46. Norbert Says:
    April 11th, 2011 at 10:21

    The “Installation” section on this page could use some extra information. Also, may add a couple of links to that section, to other webpages that explain how to use check_mysql_health with Nagios. Like: http://www.it-slav.net/blogs/2009/04/06/mysql-performance-monitoring-with-nagios-or-op5-monitor/

  47. Norbert Says:
    April 11th, 2011 at 10:50

    Another useful webpage for Nagios users: http://kedar.nitty-witty.com/blog/10-steps-mysql-monitoring-nagios-installation-configuration

  48. Ian Struthers Says:
    April 12th, 2011 at 16:28

    Hi – re Andre Kapp’s post 11:19 22Mar2011 I’ve just installed check_mysql_health plugin on Fedora / Nagios 2.0b3 with remote MySQL 5.1.3

    I get exact same problem – the plugin works fine from the command line, but fails when running as a service check in Nagios.

    $ /usr/local/nagios/libexec/check_mysql_health -H x.x.x.x –user nagios –password **** -mode uptime OK – database is up since 981 minutes | uptime=58898s

    but in Nagios GUI

    UNKNOWN – mode uptime$

    Its like the mode parameter is being processed by the plugin as $ instead of uptime Does anyone know what the fix is for this?

    Thanks, Ian

    Ian Struthers Reply:

    @Ian Struthers, Sorry – this was entirely my fault! I’d coded commands.cfg line as define command{ command_name check_mysql_uptime command_line $USER1$/check_mysql_health –hostname $HOSTADDRESS –user $ARG1$ –password $ARG2$ –mode $ARG3$ }

    missing the trailing $ off $HOSTADDRESS$

    Debug tip: temporarily added the following line to check_mysql_health plugin print “Called with host=X$commandline{hostname}X user=X$commandline{username}X pass=X$commandline{password}X mode=X$commandline{mode}X \n”; just before check on commandline{mode) at line 3535 and this displayed on GUI “Called with host=X–userX user=XX pass=X$X mode=X$X” which was the hint I needed.

    Thanks for an excellent plugin!

  49. gsnerf Says:
    April 12th, 2011 at 17:49

    Hallo,

    zunächst einmal vielen Dank für dieses großartige Plugin :)

    Ich muss aber zugeben, dass ich ein paar kleine Probleme mit dem sql mode habe. Ich habe Version 2.1.5 des Plugins mit einem MySQL Server 5.1 im Betrieb. Wenn ich das folgende Teststament aufrufe funktioniert zunächst alles Prima: ./check_mysql_health –username nagios –password XXXXX –mode sql –name “select count() from information_schema.schemata” OK – select count() from information_schema.schemata: 1 | ‘select’=1;1;5

    nun wollte ich per –name2 einen namen vergeben wie in der doku beschrieben, erhalte aber einen Fehler: ./check_mysql_health –username nagios –password XXXXX –mode sql –name “select count(*) from information_schema.schemata” –name2 myval CRITICAL – output 1 not found

    Was mache ich an der Stelle falsch?

    PS: mir ist durchaus bewusst, dass die Abfrage inhaltlich keinen Sinn ergibt, es geht mir hierbei zunächst nur darum den modus zu testen und verstehen :)

    lausser Reply:

    Das Korrekturrelese liegt seit Wichen hier rum, hab’ vergessen, es hochzuladen. Mit der 2.1.5.1 sollte es klappen

  50. カジュアルに MySQL を監視する | Carpe Diem Says:
    December 18th, 2011 at 17:51

    [...] check_mysql_helath プラグインは、さきほど解説した check_mysql と check_mysql_query を組み合わせて、さらに多くの監視ができるプラグインです。公式のマニュアルは、ここ(英語)で公開されています。 具体的には、次のことができます。 [...]

  51. Installer shinken ? c’est facile ! | Communauté Francophone de la Supervision Libre Says:
    February 2nd, 2012 at 14:13

    [...] check_mysql_health [...]

  52. Tech » Monitor MySQL Server with Nagios 3.4 Says:
    March 26th, 2013 at 10:28

    [...] first. yum install perl* asciidoc Download the latest MySQL Health Check plugin from the Nagios website, Also you can issue the following command to download in terminal. [...]

  53. ItZgeek » Monitor MySQL Server with Nagios 3.4 Says:
    March 29th, 2013 at 9:21

    [...] first. yum install perl* asciidoc Download the latest MySQL Health Check plugin from the Nagios website, Also you can issue the following command to download in terminal. [...]