5
use Getopt::Long qw(:config no_ignore_case);
8
use Nagios::DBD::MySQL::Server;
9
use Nagios::DBD::MySQL::Cluster;
12
my %ERRORS=( OK => 0, WARNING => 1, CRITICAL => 2, UNKNOWN => 3 );
13
my %ERRORCODES=( 0 => 'OK', 1 => 'WARNING', 2 => 'CRITICAL', 3 => 'UNKNOWN' );
15
use vars qw ($PROGNAME $REVISION $CONTACT $TIMEOUT $STATEFILESDIR $needs_restart %commandline);
17
$PROGNAME = "check_mysql_health";
18
$REVISION = '$Revision: #PACKAGE_VERSION# $';
19
$CONTACT = 'gerhard.lausser@consol.de';
21
$STATEFILESDIR = '#STATEFILES_DIR#';
25
['server::connectiontime',
26
'connection-time', undef,
27
'Time to connect to the server' ],
30
'Time the server is running' ],
31
['server::instance::connectedthreads',
32
'threads-connected', undef,
33
'Number of currently open connections' ],
34
['server::instance::threadcachehitrate',
35
'threadcache-hitrate', undef,
36
'Hit rate of the thread-cache' ],
37
['server::instance::createdthreads',
38
'threads-created', undef,
39
'Number of threads created per sec' ],
40
['server::instance::runningthreads',
41
'threads-running', undef,
42
'Number of currently running threads' ],
43
['server::instance::cachedthreads',
44
'threads-cached', undef,
45
'Number of currently cached threads' ],
46
['server::instance::abortedconnects',
47
'connects-aborted', undef,
48
'Number of aborted connections per sec' ],
49
['server::instance::abortedclients',
50
'clients-aborted', undef,
51
'Number of aborted connections (because the client died) per sec' ],
52
['server::instance::replication::slavelag',
53
'slave-lag', ['replication-slave-lag'],
54
'Seconds behind master' ],
55
['server::instance::replication::slaveiorunning',
56
'slave-io-running', ['replication-slave-io-running'],
57
'Slave io running: Yes' ],
58
['server::instance::replication::slavesqlrunning',
59
'slave-sql-running', ['replication-slave-sql-running'],
60
'Slave sql running: Yes' ],
61
['server::instance::querycachehitrate',
62
'qcache-hitrate', ['querycache-hitrate'],
63
'Query cache hitrate' ],
64
['server::instance::querycachelowmemprunes',
65
'qcache-lowmem-prunes', ['querycache-lowmem-prunes'],
66
'Query cache entries pruned because of low memory' ],
67
['server::instance::myisam::keycache::hitrate',
68
'keycache-hitrate', ['myisam-keycache-hitrate'],
69
'MyISAM key cache hitrate' ],
70
['server::instance::innodb::bufferpool::hitrate',
71
'bufferpool-hitrate', ['innodb-bufferpool-hitrate'],
72
'InnoDB buffer pool hitrate' ],
73
['server::instance::innodb::bufferpool::waitfree',
74
'bufferpool-wait-free', ['innodb-bufferpool-wait-free'],
75
'InnoDB buffer pool waits for clean page available' ],
76
['server::instance::innodb::logwaits',
77
'log-waits', ['innodb-log-waits'],
78
'InnoDB log waits because of a too small log buffer' ],
79
['server::instance::tablecachehitrate',
80
'tablecache-hitrate', undef,
81
'Table cache hitrate' ],
82
['server::instance::tablelockcontention',
83
'table-lock-contention', undef,
84
'Table lock contention' ],
85
['server::instance::tableindexusage',
88
['server::instance::tabletmpondisk',
89
'tmp-disk-tables', undef,
90
'Percent of temp tables created on disk' ],
91
['server::instance::needoptimize',
92
'table-fragmentation', undef,
93
'Show tables which should be optimized' ],
94
['server::instance::openfiles',
96
'Percent of opened files' ],
97
['server::instance::slowqueries',
98
'slow-queries', undef,
100
['server::instance::longprocs',
101
'long-running-procs', undef,
102
'long running processes' ],
103
['cluster::ndbdrunning',
104
'cluster-ndbd-running', undef,
105
'ndnd nodes are up and running' ],
108
'any sql command returning a single number' ],
111
# rrd data store names are limited to 19 characters
113
bufferpool_hitrate => {
114
groundwork => 'bp_hitrate',
116
bufferpool_hitrate_now => {
117
groundwork => 'bp_hitrate_now',
119
bufferpool_free_waits_rate => {
120
groundwork => 'bp_freewaits',
122
innodb_log_waits_rate => {
123
groundwork => 'inno_log_waits',
125
keycache_hitrate => {
126
groundwork => 'kc_hitrate',
128
keycache_hitrate_now => {
129
groundwork => 'kc_hitrate_now',
131
threads_created_per_sec => {
132
groundwork => 'thrds_creat_per_s',
134
connects_aborted_per_sec => {
135
groundwork => 'conn_abrt_per_s',
137
clients_aborted_per_sec => {
138
groundwork => 'clnt_abrt_per_s',
140
thread_cache_hitrate => {
141
groundwork => 'tc_hitrate',
143
thread_cache_hitrate_now => {
144
groundwork => 'tc_hitrate_now',
146
qcache_lowmem_prunes_rate => {
147
groundwork => 'qc_lowm_prnsrate',
149
slow_queries_rate => {
150
groundwork => 'slow_q_rate',
152
tablecache_hitrate => {
153
groundwork => 'tac_hitrate',
155
tablecache_fillrate => {
156
groundwork => 'tac_fillrate',
158
tablelock_contention => {
159
groundwork => 'tl_contention',
161
tablelock_contention_now => {
162
groundwork => 'tl_contention_now',
164
pct_tmp_table_on_disk => {
165
groundwork => 'tmptab_on_disk',
167
pct_tmp_table_on_disk_now => {
168
groundwork => 'tmptab_on_disk_now',
175
$PROGNAME [-v] [-t <timeout>] [[--hostname <hostname>]
176
[--port <port> | --socket <socket>]
177
--username <username> --password <password>] --mode <mode>
179
$PROGNAME [-h | --help]
180
$PROGNAME [-V | --version]
184
the database server's hostname
186
the database's port. (default: 3306)
188
the database's unix socket.
192
the mysql db user's password
194
the database's name. (default: information_schema)
200
the mode of the plugin. select one of the following keywords:
202
my $longest = length ((reverse sort {length $a <=> length $b} map { $_->[1] } @modes)[0]);
204
(length ((reverse sort {length $a <=> length $b} map { $_->[1] } @modes)[0])).
207
printf $format, $_->[1], $_->[3];
212
the name of something that needs to be further specified,
213
currently only used for sql statements
215
if name is a sql statement, this statement would appear in
216
the output and the performance data. This can be ugly, so
217
name2 can be used to appear instead.
219
if this parameter is used, name will be interpreted as a
222
one of %, KB, MB, GB. This is used for a better output of mode=sql
223
and for specifying thresholds for mode=tablespace-free
225
one of pnp4nagios (which is the default) or groundwork.
226
It is used to shorten performance data labels to 19 characters.
228
In mode sql you can url-encode the statement so you will not have to mess
229
around with special characters in your Nagios service definitions.
231
--name="select count(*) from v\$session where status = 'ACTIVE'"
233
--name=select%20count%28%2A%29%20from%20v%24session%20where%20status%20%3D%20%27ACTIVE%27
234
For your convenience you can call check_mysql_health with the --mode encode
235
option and it will encode the standard input.
237
You can find the full documentation at
238
http://www.consol.de/opensource/nagios/check-mysql-health
245
print "Copyright (c) 2009 Gerhard Lausser\n\n";
247
print " Check various parameters of MySQL databases \n";
254
sub print_revision ($$) {
255
my $commandName = shift;
256
my $pluginRevision = shift;
257
$pluginRevision =~ s/^\$Revision: //;
258
$pluginRevision =~ s/ \$\s*$//;
259
print "$commandName ($pluginRevision)\n";
260
print "This nagios plugin comes with ABSOLUTELY NO WARRANTY. You may redistribute\ncopies of this plugin under the terms of the GNU General Public License.\n";
264
my $support='Send email to gerhard.lausser@consol.de if you have questions\nregarding use of this software. \nPlease include version information with all correspondence (when possible,\nuse output from the --version option of the plugin itself).\n';
265
$support =~ s/@/\@/g;
266
$support =~ s/\\n/\n/g;
270
sub contact_author ($$) {
272
my $strangepattern = shift;
273
if ($commandline{verbose}) {
275
"++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n".
276
"You found a line which is not recognized by %s\n".
277
"This means, certain components of your system cannot be checked.\n".
278
"Please contact the author %s and\nsend him the following output:\n\n".
279
"%s /%s/\n\nThank you!\n".
280
"++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n",
281
$PROGNAME, $CONTACT, $item, $strangepattern;
320
"with-mymodules-dyn-dir=s",
325
if (! GetOptions(\%commandline, @params)) {
327
exit $ERRORS{UNKNOWN};
330
if (exists $commandline{'extra-opts'}) {
331
# read the extra file and overwrite other parameters
332
my $extras = Extraopts->new(file => $commandline{'extra-opts'}, commandline =>
334
if (! $extras->is_valid()) {
335
printf "extra-opts are not valid: %s\n", $extras->{errors};
336
exit $ERRORS{UNKNOWN};
338
$extras->overwrite();
342
if (exists $commandline{version}) {
343
print_revision($PROGNAME, $REVISION);
347
if (exists $commandline{help}) {
350
} elsif (! exists $commandline{mode}) {
351
printf "Please select a mode\n";
356
if ($commandline{mode} eq "encode") {
359
$input =~ s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;
360
printf "%s\n", $input;
364
if (exists $commandline{3}) {
365
$ENV{NRPE_MULTILINESUPPORT} = 1;
368
if (exists $commandline{timeout}) {
369
$TIMEOUT = $commandline{timeout};
372
if (exists $commandline{verbose}) {
373
$DBD::MySQL::Server::verbose = exists $commandline{verbose};
376
if (exists $commandline{scream}) {
377
# $DBD::MySQL::Server::hysterical = exists $commandline{scream};
380
if (exists $commandline{method}) {
381
# snmp or mysql cmdline
383
$commandline{method} = "dbi";
386
if (exists $commandline{report}) {
389
$commandline{report} = "long";
392
if (exists $commandline{labelformat}) {
395
$commandline{labelformat} = "pnp4nagios";
398
if (exists $commandline{'with-mymodules-dyn-dir'}) {
399
$DBD::MySQL::Server::my_modules_dyn_dir = $commandline{'with-mymodules-dyn-dir'};
401
$DBD::MySQL::Server::my_modules_dyn_dir = '#MYMODULES_DYN_DIR#';
404
if (exists $commandline{environment}) {
405
# if the desired environment variable values are different from
406
# the environment of this running script, then a restart is necessary.
407
# because setting $ENV does _not_ change the environment of the running script.
408
foreach (keys %{$commandline{environment}}) {
409
if ((! $ENV{$_}) || ($ENV{$_} ne $commandline{environment}->{$_})) {
411
$ENV{$_} = $commandline{environment}->{$_};
412
printf STDERR "new %s=%s forces restart\n", $_, $ENV{$_}
413
if $DBD::MySQL::Server::verbose;
416
# e.g. called with --runas dbnagio. shlib_path environment variable is stripped
418
# so the perl interpreter starts without a shlib_path. but --runas cares for
419
# a --environment shlib_path=...
420
# so setting the environment variable in the code above and restarting the
421
# perl interpreter will help it find shared libs
424
if (exists $commandline{runas}) {
425
# remove the runas parameter
426
# exec sudo $0 ... the remaining parameters
428
# if the calling script has a path for shared libs and there is no --environment
429
# parameter then the called script surely needs the variable too.
430
foreach my $important_env (qw(LD_LIBRARY_PATH SHLIB_PATH
431
ORACLE_HOME TNS_ADMIN ORA_NLS ORA_NLS33 ORA_NLS10)) {
432
if ($ENV{$important_env} && ! scalar(grep { /^$important_env=/ }
433
keys %{$commandline{environment}})) {
434
$commandline{environment}->{$important_env} = $ENV{$important_env};
435
printf STDERR "add important --environment %s=%s\n",
436
$important_env, $ENV{$important_env} if $DBD::MySQL::Server::verbose;
441
if ($needs_restart) {
444
if (exists $commandline{runas}) {
445
$runas = $commandline{runas};
446
delete $commandline{runas};
448
foreach my $option (keys %commandline) {
449
if (grep { /^$option/ && /=/ } @params) {
450
if (ref ($commandline{$option}) eq "HASH") {
451
foreach (keys %{$commandline{$option}}) {
452
push(@newargv, sprintf "--%s", $option);
453
push(@newargv, sprintf "%s=%s", $_, $commandline{$option}->{$_});
456
push(@newargv, sprintf "--%s", $option);
457
push(@newargv, sprintf "%s", $commandline{$option});
460
push(@newargv, sprintf "--%s", $option);
464
exec "sudo", "-S", "-u", $runas, $0, @newargv;
467
# this makes sure that even a SHLIB or LD_LIBRARY_PATH are set correctly
468
# when the perl interpreter starts. Setting them during runtime does not
469
# help loading e.g. libclntsh.so
474
if (exists $commandline{shell}) {
475
# forget what you see here.
479
if (! exists $commandline{statefilesdir}) {
480
if (exists $ENV{OMD_ROOT}) {
481
$commandline{statefilesdir} = $ENV{OMD_ROOT}."/var/tmp/check_mysql_health";
483
$commandline{statefilesdir} = $STATEFILESDIR;
487
if (exists $commandline{name}) {
488
if ($^O =~ /MSWin/ && $commandline{name} =~ /^'(.*)'$/) {
489
# putting arguments in single ticks under Windows CMD leaves the ' intact
491
$commandline{name} = $1;
493
# objects can be encoded like an url
494
# with s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;
495
if (($commandline{mode} ne "sql") ||
496
(($commandline{mode} eq "sql") &&
497
($commandline{name} =~ /select%20/i))) { # protect ... like '%cac%' ... from decoding
498
$commandline{name} =~ s/\%([A-Fa-f0-9]{2})/pack('C', hex($1))/seg;
500
if ($commandline{name} =~ /^0$/) {
501
# without this, $params{selectname} would be treated like undef
502
$commandline{name} = "00";
507
printf "UNKNOWN - %s timed out after %d seconds\n", $PROGNAME, $TIMEOUT;
508
exit $ERRORS{UNKNOWN};
512
my $nagios_level = $ERRORS{UNKNOWN};
513
my $nagios_message = "";
515
if ($commandline{mode} =~ /^my-([^\-.]+)/) {
516
my $param = $commandline{mode};
518
push(@modes, [$param, $commandline{mode}, undef, 'my extension']);
519
} elsif ((! grep { $commandline{mode} eq $_ } map { $_->[1] } @modes) &&
520
(! grep { $commandline{mode} eq $_ } map { defined $_->[2] ? @{$_->[2]} : () } @modes)) {
521
printf "UNKNOWN - mode %s\n", $commandline{mode};
530
($commandline{mode} eq $_->[1]) ||
531
( defined $_->[2] && grep { $commandline{mode} eq $_ } @{$_->[2]})
534
cmdlinemode => $commandline{mode},
535
method => $commandline{method} ||
536
$ENV{NAGIOS__SERVICEMYSQL_METH} ||
537
$ENV{NAGIOS__HOSTMYSQL_METH} || 'dbi',
538
hostname => $commandline{hostname} ||
539
$ENV{NAGIOS__SERVICEMYSQL_HOST} ||
540
$ENV{NAGIOS__HOSTMYSQL_HOST} || 'localhost',
541
database => $commandline{database} ||
542
$ENV{NAGIOS__SERVICEMYSQL_DATABASE} ||
543
$ENV{NAGIOS__HOSTMYSQL_DATABASE} || 'information_schema',
544
port => $commandline{port} || (($commandline{mode} =~ /^cluster/) ?
545
($ENV{NAGIOS__SERVICENDBMGM_PORT} || $ENV{NAGIOS__HOSTNDBMGM_PORT} || 1186) :
546
($ENV{NAGIOS__SERVICEMYSQL_PORT} || $ENV{NAGIOS__HOSTMYSQL_PORT} || 3306)),
547
socket => $commandline{socket} ||
548
$ENV{NAGIOS__SERVICEMYSQL_SOCKET} ||
549
$ENV{NAGIOS__HOSTMYSQL_SOCKET},
550
username => $commandline{username} ||
551
$ENV{NAGIOS__SERVICEMYSQL_USER} ||
552
$ENV{NAGIOS__HOSTMYSQL_USER},
553
password => $commandline{password} ||
554
$ENV{NAGIOS__SERVICEMYSQL_PASS} ||
555
$ENV{NAGIOS__HOSTMYSQL_PASS},
556
mycnf => $commandline{mycnf} ||
557
$ENV{NAGIOS__SERVICEMYSQL_MYCNF} ||
558
$ENV{NAGIOS__HOSTMYSQL_MYCNF},
559
mycnfgroup => $commandline{mycnfgroup} ||
560
$ENV{NAGIOS__SERVICEMYSQL_MYCNFGROUP} ||
561
$ENV{NAGIOS__HOSTMYSQL_MYCNFGROUP},
562
warningrange => $commandline{warning},
563
criticalrange => $commandline{critical},
564
dbthresholds => $commandline{dbthresholds},
565
absolute => $commandline{absolute},
566
lookback => $commandline{lookback},
567
selectname => $commandline{name} || $commandline{tablespace} || $commandline{datafile},
568
regexp => $commandline{regexp},
569
name => $commandline{name},
570
name2 => $commandline{name2} || $commandline{name},
571
units => $commandline{units},
572
lookback => $commandline{lookback} || 0,
573
eyecandy => $commandline{eyecandy},
574
statefilesdir => $commandline{statefilesdir},
575
verbose => $commandline{verbose},
576
report => $commandline{report},
577
labelformat => $commandline{labelformat},
583
if ($params{mode} =~ /^(server|my)/) {
584
$server = DBD::MySQL::Server->new(%params);
585
$server->nagios(%params);
586
$server->calculate_result(\%labels);
587
$nagios_message = $server->{nagios_message};
588
$nagios_level = $server->{nagios_level};
589
$perfdata = $server->{perfdata};
590
} elsif ($params{mode} =~ /^cluster/) {
591
$cluster = DBD::MySQL::Cluster->new(%params);
592
$cluster->nagios(%params);
593
$cluster->calculate_result(\%labels);
594
$nagios_message = $cluster->{nagios_message};
595
$nagios_level = $cluster->{nagios_level};
596
$perfdata = $cluster->{perfdata};
599
printf "%s - %s", $ERRORCODES{$nagios_level}, $nagios_message;
600
printf " | %s", $perfdata if $perfdata;