/[nagios-plugins-perl]/trunk/plugins/check_mysql.pl
ViewVC logotype

Contents of /trunk/plugins/check_mysql.pl

Parent Directory Parent Directory | Revision Log Revision Log


Revision 193 - (show annotations) (download)
Mon Dec 18 14:45:38 2017 UTC (2 years, 11 months ago) by racvision
File MIME type: text/plain
File size: 10114 byte(s)
Remise en ordre XH 201712
1 #!/usr/bin/perl -w
2 #
3 # Copyright (c) 2011 - St├ęphane Urbanovski <stephane.urbanovski@ac-nancy-metz.fr>
4 #
5 # This program is free software; you can redistribute it and/or
6 # modify it under the terms of the GNU General Public License
7 # as published by the Free Software Foundation; either version 2
8 # of the License, or (at your option) any later version.
9 #
10 # This program is distributed in the hope that it will be useful,
11 # but WITHOUT ANY WARRANTY; without even the implied warranty
12 # of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 # GNU General Public License for more details.
14 #
15 # you should have received a copy of the GNU General Public License
16 # along with this program (or with Nagios); if not, write to the
17 # Free Software Foundation, Inc., 59 Temple Place - Suite 330,
18 # Boston, MA 02111-1307, USA
19 #
20 # $Id: check_db2.pl,v 1.7 2009/04/24 13:32:42 uid5000 Exp $
21
22 use strict;
23 use warnings;
24
25 use Nagios::Plugin ;
26
27 use DBI;
28
29 use File::Basename;
30 use Locale::gettext;
31 use Getopt::Long;
32
33 use POSIX qw(:signal_h floor setlocale);
34 use Time::HiRes; # get microtime
35 use DateTime;
36
37 use Data::Dumper;
38
39
40 my $PROGNAME = basename($0);
41 '$Revision: 1.0 $' =~ /^.*(\d+.\d+) \$$/; # Use The Revision from RCS/CVS/SVN
42 my $VERSION = $1;
43
44 my $DEBUG = 0;
45 my $TIMEOUT = 9;
46
47 # i18n :
48 setlocale(LC_MESSAGES, '');
49 textdomain('nagios-plugins');
50
51 # Retention files path (save previous values) :
52 # FIXME: Make this configurable
53 my $TempPath = '/tmp/';
54
55
56 my $np = Nagios::Plugin->new(
57 version => $VERSION,
58 blurb => _gt('Plugin to check MySQL database'),
59 usage => "Usage: %s [ -v|--verbose ] -H <host> [-p <port>] [-t <timeout>] [ -c|--critical=<threshold> ] [ -w|--warning=<threshold> ]",
60 timeout => $TIMEOUT+1
61 );
62
63
64 # Parse command line arguments :
65
66 $np->add_arg (
67 spec => 'debug|d',
68 help => _gt('Debug level'),
69 default => 0,
70 required => 0,
71 );
72
73 $np->add_arg (
74 spec => 'hostname|H=s',
75 help => _gt('Hostname'),
76 required => 1,
77 );
78
79 $np->add_arg (
80 spec => 'port|p=i',
81 help => _gt('Database listen port'),
82 default => 3306,
83 required => 0,
84 );
85
86 $np->add_arg (
87 spec => 'base|b=s',
88 help => _gt('Database name'),
89 default => 'mysql',
90 );
91 $np->add_arg (
92 spec => 'user|u=s',
93 help => _gt('Connection user name'),
94 required => 1,
95 );
96 $np->add_arg (
97 spec => 'password=s',
98 help => _gt('Connection user password'),
99 required => 1,
100 );
101
102
103 $np->add_arg (
104 spec => 'wt=f',
105 help => _gt('Warning request time threshold (in seconds)'),
106 default => 2,
107 required => 0,
108 label => 'FLOAT'
109 );
110
111
112 $np->getopts;
113
114 $DEBUG = $np->opts->get('debug');
115
116 my $hostname = $np->opts->get('hostname');
117 my $port = $np->opts->get('port');
118 my $db = $np->opts->get('base');
119
120 my $user = $np->opts->get('user');
121 my $pass = $np->opts->get('password');
122
123
124 # Thresholds :
125 # time
126 my $warn_t = $np->opts->get('wt');
127 my $crit_t = $np->opts->get('ct');
128
129
130
131 # db params
132 my $dsn = "DBI:mysql:host=$hostname";
133
134
135 my $mask = POSIX::SigSet->new( SIGALRM );
136 my $action = POSIX::SigAction->new(\&timeoutExit,$mask);
137 my $oldaction = POSIX::SigAction->new();
138
139 sigaction( SIGALRM, $action, $oldaction );
140
141 debug( "Seting Alarm timeout to $TIMEOUT");
142 my $startTime = Time::HiRes::time();
143 alarm($TIMEOUT);
144
145
146 my $dbh = DBI->connect($dsn, $user, $pass, {
147 'PrintError' => $DEBUG,
148 'PrintWarn' => $DEBUG,
149 'RaiseError' => 0,
150 'FetchHashKeyName' => 'NAME_lc',
151 });
152
153 if ( !$dbh ) {
154 $np->nagios_exit(CRITICAL, _gt("Query failed (connect): ").$DBI::errstr );
155 }
156
157 debug( 'Connected !');
158 my $out = '';
159
160 my $sql = 'SHOW VARIABLES LIKE "version%";';
161 my $rowVers = queryStatus($dbh,$sql);
162
163 if ( !defined($rowVers) ) {
164 $np->nagios_exit(CRITICAL, 'Error while querying version : '.$DBI::errstr );
165
166 }
167
168 $sql = 'SHOW /*!50002 GLOBAL */ STATUS;';
169 my $rowStatus = queryStatus($dbh,$sql);
170
171 if ( !defined($rowStatus) ) {
172 $np->nagios_exit(CRITICAL, 'Error while querying SERVER STATUS : '.$DBI::errstr );
173
174 }
175
176 my $rowSlave = undef;
177
178 if ( defined($rowStatus->{'slave_running'}) && $rowStatus->{'slave_running'} eq 'ON') {
179 # Check master/slave status
180
181 $sql = 'SHOW SLAVE STATUS;';
182 $rowSlave = queryFirstRow($dbh,$sql);
183
184 if ( !defined($rowSlave) ) {
185 $np->nagios_exit(CRITICAL, 'Error while querying SLAVE STATUS : '.$DBI::errstr );
186
187 }
188 }
189
190
191 $dbh->disconnect();
192
193 alarm(0);
194
195
196 # Check timer value
197
198 my $timer = Time::HiRes::time() - $startTime;
199
200 my $status_t = $np->check_threshold(
201 'check' => $timer,
202 'warning' => $warn_t,
203 'critical' => $crit_t,
204 );
205
206 $np->add_perfdata(
207 'label' => 't',
208 'value' => sprintf('%.6f',$timer),
209 'min' => 0,
210 'uom' => 's',
211 'threshold' => $np->threshold()
212 );
213
214
215 # Get version informations
216
217 # print Dumper($rowVers);
218
219 my $version = _gt("Unknown MySQL version");
220 if ( defined ($rowVers->{'version'}) ) {
221 $version = $rowVers->{'version'};
222 }
223 if ( defined ($rowVers->{'version_compile_machine'}) ) {
224 $version .= '/'.$rowVers->{'version_compile_machine'};
225 }
226
227
228 # check master/slave status
229
230 if ( defined($rowSlave) ) {
231
232 $version .= sprintf(_gt(' (replicated from %s)'),$rowSlave->{'master_host'});
233
234 if ( defined($rowSlave->{'slave_sql_running'}) && $rowSlave->{'slave_sql_running'} ne 'Yes' ) {
235 $np->add_message(CRITICAL, _gt("Slave SQL thread not running !") );
236 }
237 if ( defined($rowSlave->{'slave_io_running'}) && $rowSlave->{'slave_io_running'} ne 'Yes' ) {
238 $np->add_message(CRITICAL, _gt("Slave SQL IO not running !") );
239 }
240
241 if ( defined($rowSlave->{'seconds_behind_master'}) ) {
242 $np->add_perfdata(
243 'label' => 's_behind_master',
244 'value' => $rowSlave->{'seconds_behind_master'},
245 'uom' => 's',
246 );
247 }
248 }
249
250
251 # add perfdata for metrics
252 foreach my $counter ('threads_connected') {
253 if ( defined($rowStatus->{$counter}) ) {
254 $np->add_perfdata(
255 'label' => $counter,
256 'value' => $rowStatus->{$counter},
257 );
258 }
259 }
260
261
262
263
264
265 # Read previous retention data ( get rate from counter values)
266
267 my $TempFile = $TempPath.'check_mysql_'.$hostname.'_on_'.$db;
268
269 my %previousValues = ();
270 if ( (-e $TempFile) && (-r $TempFile) && (-w $TempFile) ) {
271 if ( !open (RETENTION_FILE, '<',$TempFile) ) {
272 # $np->nagios_exit(CRITICAL, _gt("Error while trying to read $TempFile !") );
273 }
274 debug("Retention file : $TempFile");
275 while ( my $l = <RETENTION_FILE> ) {
276 chomp($l);
277 if ( $l =~ /^(\w+) ([\d\.]+)$/ ) {
278 $previousValues{$1} = $2;
279 debug("Retention data : '$1' = $2");
280 } else {
281 debug("Bad retention data : $l");
282 }
283 }
284 close (RETENTION_FILE);
285
286 } else {
287 debug("Retention file '$TempFile' not found");
288 }
289
290
291 # Save retention data
292 if ( !open (RETENTION_FILE, '>',$TempFile) ) {
293 $np->nagios_exit(CRITICAL, sprintf(_gt("Error while trying to read %s !",$TempFile)) );
294 }
295 print RETENTION_FILE 'TIMESTAMP '.$startTime."\n";
296 foreach my $counter ('select_scan','select_full_join','questions','slow_queries','com_select','com_insert','com_update','com_delete','qcache_hits') {
297 if ( defined($rowStatus->{$counter}) ) {
298 print RETENTION_FILE $counter.' '.$rowStatus->{$counter}."\n";
299 }
300 }
301 close (RETENTION_FILE);
302
303
304 my %delta = ();
305 # my %rate = ();
306
307 my $dt = 0;
308
309 if ( defined($previousValues{'TIMESTAMP'}) ) {
310 $dt = $startTime - $previousValues{'TIMESTAMP'};
311
312 if ( $dt > 0 && $dt <= 600 ) {
313 #time increase (but not obsolete : >10mn)
314
315 # compute deltas for counters
316 foreach my $counter ('select_scan','select_full_join','questions','slow_queries','com_select','com_insert','com_update','com_delete','qcache_hits') {
317 if ( defined($rowStatus->{$counter}) && defined($previousValues{$counter}) ) {
318
319 if ( $rowStatus->{$counter} >= $previousValues{$counter} ) {
320 # counter increase
321 $delta{$counter} = $rowStatus->{$counter} - $previousValues{$counter};
322 }
323 }
324 }
325
326
327 # add perfdata for some rates
328 foreach my $counter ('select_scan','select_full_join','questions','slow_queries','com_select','com_insert','com_update','com_delete','qcache_hits') {
329 if ( defined($delta{$counter}) ) {
330 # The number of SQL SELECT statements par second that were executed.
331 my $rate = $delta{$counter} / $dt;
332 $np->add_perfdata(
333 'label' => $counter.'_RATE',
334 'value' => $rate,
335 'uom' => 'req/s'
336 );
337 }
338 }
339
340 }
341 }
342
343
344
345
346 my ($status, $message) = $np->check_messages();
347
348 $np->nagios_exit($status, $version.' '.$message );
349
350 exit;
351
352 sub timeoutExit {
353 print _gt("DB2 - Connection timeout !\n");
354 exit CRITICAL;
355 }
356
357 sub queryStatus {
358 my ($dbh,$sql) = @_;
359 my $timer = Time::HiRes::time();
360 my $sth = $dbh->prepare($sql);
361
362 debug ("SQL: $sql");
363
364 if ( !$sth ) {
365 $np->nagios_exit(CRITICAL, _gt("Query failed (prepare): ").$sth->errstr );
366 return undef;
367 }
368
369 if ( $sth->execute() ) {
370
371 $timer = Time::HiRes::time()-$timer;
372 my $data = {};
373
374 debug ( sprintf ("------- Sql results (time=%fms)", $timer*1000));
375 while ( my $row = $sth->fetchrow_hashref() ) {
376
377 if ( $dbh->err ) {
378 debug ('Fetch error : '.$dbh->err.' '.$dbh->errstr);
379 return undef;
380 }
381
382 my $key = lc($row->{'variable_name'});
383 my $value = $row->{'value'};
384
385 if ($DEBUG) {
386 debug ( sprintf ("%40s = %s", $key,defined($value)?$value:'NULL'));
387 }
388 $data->{$key} = $value;
389 }
390
391 debug ( '---------');
392 $sth->finish();
393 return $data;
394
395 } else {
396 $np->nagios_exit(CRITICAL, _gt("Query failed (execute):").$sth->errstr );
397 }
398 }
399
400 sub queryFirstRow {
401 my ($dbh,$sql) = @_;
402 my $timer = Time::HiRes::time();
403 my $sth = $dbh->prepare($sql);
404
405 debug ("SQL: $sql");
406
407 if ( !$sth ) {
408 $np->nagios_exit(CRITICAL, _gt("Query failed (prepare): ").$sth->errstr );
409 return undef;
410 }
411
412 if ( $sth->execute() ) {
413
414 $timer = Time::HiRes::time()-$timer;
415 my $row = $sth->fetchrow_hashref();
416
417 if ( $dbh->err ) {
418 debug ('Fetch error : '.$dbh->err.' '.$dbh->errstr);
419 return undef;
420 }
421 if ($DEBUG) {
422 debug ( sprintf ("------- Sql results (time=%fms)", $timer*1000));
423 foreach my $attr ( keys(%{$row}) ) {
424 debug ( sprintf ("%40s = %s", $attr,defined($row->{$attr})?$row->{$attr}:'NULL'));
425 }
426 debug ( '---------');
427 }
428 $sth->finish();
429
430 return $row;
431
432 } else {
433 $np->nagios_exit(CRITICAL, _gt("Query failed (execute):").$sth->errstr );
434 }
435 }
436
437
438 # Print debug information if $DEBUG > 0
439 sub debug {
440 print STDERR "[DEBUG] ".$_[0]."\n" if $DEBUG;
441 }
442
443 # Gettext wrapper
444 sub _gt {
445 return gettext($_[0]);
446 }
447
448

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.8