1#!/usr/local/bin/perl 2# 3# 4# This program implements a SNMP agent for MySQL servers 5# 6# (c) Copryright 2008, 2009 - Brice Figureau 7# 8# The INNODB parsing code is originally Copyright 2008 Baron Schwartz, 9# and was released as GPL,v2. 10# 11# This program is free software: you can redistribute it and/or modify 12# it under the terms of the GNU General Public License as published by 13# the Free Software Foundation, either version 2 of the License, or 14# (at your option) any later version. 15# 16# This program is distributed in the hope that it will be useful, 17# but WITHOUT ANY WARRANTY; without even the implied warranty of 18# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 19# GNU General Public License for more details. 20# 21# You should have received a copy of the GNU General Public License 22# along with this program. If not, see <http://www.gnu.org/licenses/>. 23 24use strict; 25 26my $VERSION = "v1.2_01"; 27$VERSION = eval $VERSION; 28 29## Packages ## 30package InnoDBParser; 31 32use strict; 33use warnings; 34use Data::Dumper; 35 36# Math::BigInt reverts to perl only 37# automatically if GMP is not installed 38use Math::BigInt lib => 'GMP'; 39 40sub new { 41 bless {}, shift; 42} 43 44sub parse_innodb_status { 45 my $self = shift; 46 my $lines = shift; 47 my %status = ( 48 'current_transactions' => 0, 49 'locked_transactions' => 0, 50 'active_transactions' => 0, 51 'current_transactions' => 0, 52 'locked_transactions' => 0, 53 'active_transactions' => 0, 54 'innodb_locked_tables' => 0, 55 'innodb_tables_in_use' => 0, 56 'innodb_lock_structs' => 0, 57 'innodb_lock_wait_secs' => 0, 58 'pending_normal_aio_reads' => 0, 59 'pending_normal_aio_writes' => 0, 60 'pending_ibuf_aio_reads' => 0, 61 'pending_aio_log_ios' => 0, 62 'pending_aio_sync_ios' => 0, 63 'pending_log_flushes' => 0, 64 'pending_buf_pool_flushes' => 0, 65 'file_reads' => 0, 66 'file_writes' => 0, 67 'file_fsyncs' => 0, 68 'ibuf_inserts' => 0, 69 'ibuf_merged' => 0, 70 'ibuf_merges' => 0, 71 'log_bytes_written' => 0, 72 'unflushed_log' => 0, 73 'log_bytes_flushed' => 0, 74 'pending_log_writes' => 0, 75 'pending_chkp_writes' => 0, 76 'log_writes' => 0, 77 'pool_size' => 0, 78 'free_pages' => 0, 79 'database_pages' => 0, 80 'modified_pages' => 0, 81 'pages_read' => 0, 82 'pages_created' => 0, 83 'pages_written' => 0, 84 'queries_inside' => 0, 85 'queries_queued' => 0, 86 'read_views' => 0, 87 'rows_inserted' => 0, 88 'rows_updated' => 0, 89 'rows_deleted' => 0, 90 'rows_read' => 0, 91 'innodb_transactions' => 0, 92 'unpurged_txns' => 0, 93 'history_list' => 0, 94 'current_transactions' => 0, 95 'hash_index_cells_total' => 0, 96 'hash_index_cells_used' => 0, 97 'total_mem_alloc' => 0, 98 'additional_pool_alloc' => 0, 99 'last_checkpoint' => 0, 100 'uncheckpointed_bytes' => 0, 101 'ibuf_used_cells' => 0, 102 'ibuf_free_cells' => 0, 103 'ibuf_cell_count' => 0, 104 'adaptive_hash_memory' => 0, 105 'page_hash_memory' => 0, 106 'dictionary_cache_memory' => 0, 107 'file_system_memory' => 0, 108 'lock_system_memory' => 0, 109 'recovery_system_memory' => 0, 110 'thread_hash_memory' => 0, 111 'innodb_sem_waits' => 0, 112 'innodb_sem_wait_time_ms' => 0 113 ); 114 my $flushed_to; 115 my $innodb_lsn; 116 my $purged_to; 117 my @spin_waits; 118 my @spin_rounds; 119 my @os_waits; 120 my $txn_seen = 0; 121 my $merged_op_seen = 0; 122 my $discarded_op_seen = 0; 123 my $individual_buffer_pool_info_seen = 0; 124 125 foreach my $line (@$lines) { 126 my @row = split(/ +/, $line); 127 128 # SEMAPHORES 129 if ($line =~ m/Mutex spin waits/) { 130 push(@spin_waits, $self->tonum($row[3])); 131 push(@spin_rounds, $self->tonum($row[5])); 132 push(@os_waits, $self->tonum($row[8])); 133 } 134 elsif($line =~ m/RW-shared spins \d+, OS waits \d+; RW-excl/) { 135 # previous mysql versions had this line 136 # RW-shared spins 1956750, OS waits 199142; RW-excl spins 316353, OS waits 185734 137 push(@spin_waits, $self->tonum($row[2])); 138 push(@spin_waits, $self->tonum($row[8])); 139 push(@os_waits, $self->tonum($row[5])); 140 push(@os_waits, $self->tonum($row[11])); 141 } 142 elsif ($line =~ m/RW-shared spins \d+, rounds/) { 143 # newer mysql versions split this line in two 144 # RW-shared spins 38794, rounds 798644, OS waits 18015 145 # RW-excl spins 27732, rounds 910515, OS waits 23321 146 push(@spin_waits, $self->tonum($row[2])); 147 push(@spin_rounds, $self->tonum($row[4])); 148 push(@os_waits, $self->tonum($row[7])); 149 } 150 elsif ($line =~ m/^RW-excl spins/) { 151 # newer mysql versions split this line in two 152 # RW-shared spins 38794, rounds 798644, OS waits 18015 153 # RW-excl spins 27732, rounds 910515, OS waits 23321 154 push(@spin_waits, $self->tonum($row[2])); 155 push(@spin_rounds, $self->tonum($row[4])); 156 push(@os_waits, $self->tonum($row[7])); 157 } 158 elsif ($line =~ /seconds the semaphore:/) { 159 # --Thread 907205 has waited at handler/ha_innodb.cc line 7156 for 1.00 seconds the semaphore: 160 $status{'innodb_sem_waits'} += 1; 161 $status{'innodb_sem_wait_time_ms'} += $self->tonum($row[9]) * 1000; 162 } 163 164 # TRANSACTIONS 165 elsif ($line =~ m/Trx id counter/) { 166 # The beginning of the TRANSACTIONS section: start counting 167 # transactions 168 # Trx id counter 0 1170664159 169 # Trx id counter 861B144C 170 $status{'innodb_transactions'} = $self->make_bigint($row[3], $row[4]); 171 $txn_seen = 1; 172 } 173 elsif ($line =~ m/Purge done for trx/) { 174 # Purge done for trx's n:o < 0 1170663853 undo n:o < 0 0 175 # Purge done for trx's n:o < 861B135D undo n:o < 0 176 $purged_to = $self->make_bigint($row[6], $row[7] eq 'undo' ? undef : $row[7]); 177 $status{'unpurged_txns'} = $status{'innodb_transactions'} - $purged_to; 178 } 179 elsif ($line =~ m/History list length/) { 180 $status{'history_list'} = $self->tonum($row[3]); 181 } 182 elsif ($txn_seen && $line =~ m/---TRANSACTION/) { 183 $status{'current_transactions'} = $status{'current_transactions'} + 1; 184 if ($line =~ m/ACTIVE/) { 185 $status{'active_transactions'} = $status{'active_transactions'} + 1; 186 } 187 } 188 elsif ($txn_seen && $line =~ m/------- TRX HAS BEEN/) { 189 # ------- TRX HAS BEEN WAITING 32 SEC FOR THIS LOCK TO BE GRANTED: 190 $status{'innodb_lock_wait_secs'} = $self->tonum($row[5]); 191 } 192 elsif ($line =~ m/read views open inside/) { 193 $status{'read_views'} = $self->tonum($row[0]); 194 } 195 elsif ($line =~ m/mysql tables in use/) { 196 # mysql tables in use 2, locked 2 197 $status{'innodb_tables_in_use'} += $self->tonum($row[4]); 198 $status{'innodb_locked_tables'} += $self->tonum($row[6]); 199 } 200 elsif ($txn_seen && $line =~ m/lock struct\(s\)/) { 201 # 23 lock struct(s), heap size 3024, undo log entries 27 202 # LOCK WAIT 12 lock struct(s), heap size 3024, undo log entries 5 203 # LOCK WAIT 2 lock struct(s), heap size 368 204 if ( $line =~ m/LOCK WAIT/ ) { 205 $status{'innodb_lock_structs'} += $self->tonum($row[2]); 206 $status{'locked_transactions'} += 1; 207 } 208 else { 209 $status{'innodb_lock_structs'} += $self->tonum($row[0]); 210 } 211 } 212 # FILE I/O 213 elsif ($line =~ m/OS file reads/) { 214 $status{'file_reads'} = $self->tonum($row[0]); 215 $status{'file_writes'} = $self->tonum($row[4]); 216 $status{'file_fsyncs'} = $self->tonum($row[8]); 217 } 218 elsif ($line =~ m/Pending normal aio: \d+, aio/) { 219 # Pending normal aio reads: 0, aio writes: 0, 220 $status{'pending_normal_aio_reads'} = $self->tonum($row[4]); 221 $status{'pending_normal_aio_writes'} = $self->tonum($row[7]); 222 } 223 elsif ($line =~ m/Pending normal aio: \d+ \[\d+, \d+, \d+, \d+\]/) { 224 # Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , 225 $status{'pending_normal_aio_reads'} = $self->tonum($row[4]); 226 $status{'pending_normal_aio_writes'} = $self->tonum($row[12]); 227 } 228 elsif ($line =~ m/ibuf aio reads/) { 229 $status{'pending_ibuf_aio_reads'} = $self->tonum($row[4]); 230 $status{'pending_aio_log_ios'} = $self->tonum($row[7]); 231 $status{'pending_aio_sync_ios'} = $self->tonum($row[10]); 232 } 233 elsif ($line =~ m/Pending flushes \(fsync\)/) { 234 $status{'pending_log_flushes'} = $self->tonum($row[4]); 235 $status{'pending_buf_pool_flushes'} = $self->tonum($row[7]); 236 } 237 # INSERT BUFFER AND ADAPTIVE HASH INDEX 238 elsif ($line =~ m/^Ibuf for space 0: size /) { 239 # Older InnoDB code seemed to be ready for an ibuf per tablespace. It 240 # had two lines in the output. Newer has just one line, see below. 241 # Ibuf for space 0: size 1, free list len 887, seg size 889, is not empty 242 # Ibuf for space 0: size 1, free list len 887, seg size 889, 243 $status{'ibuf_used_cells'} = $self->tonum($row[5]); 244 $status{'ibuf_free_cells'} = $self->tonum($row[9]); 245 $status{'ibuf_cell_count'} = $self->tonum($row[12]); 246 } 247 elsif ($line =~ m/^Ibuf: size /) { 248 # Ibuf: size 1, free list len 4634, seg size 4636, 249 $status{'ibuf_used_cells'} = $self->tonum($row[2]); 250 $status{'ibuf_free_cells'} = $self->tonum($row[6]); 251 $status{'ibuf_cell_count'} = $self->tonum($row[9]); 252 if ($line =~ m/merges$/) { 253 # newer innodb plugin 254 $status{'ibuf_merges'} = $self->tonum($row[10]); 255 } 256 } 257 elsif ($line =~ m/ merged recs, /) { 258 # 19817685 inserts, 19817684 merged recs, 3552620 merges 259 $status{'ibuf_inserts'} = $self->tonum($row[0]); 260 $status{'ibuf_merged'} = $self->tonum($row[2]); 261 $status{'ibuf_merges'} = $self->tonum($row[5]); 262 } 263 elsif ($line =~ m/merged operations:/) { 264 #merged operations: 265 # insert 0, delete mark 0, delete 0 266 $merged_op_seen = 1; 267 } 268 elsif ($line =~ m/discarded operations:/) { 269 #discarded operations: 270 # insert 0, delete mark 0, delete 0 271 $discarded_op_seen = 1; 272 } 273 elsif ($merged_op_seen && !$discarded_op_seen && $line =~ m/ insert \d+, delete mark/) { 274 # we want merged operations, not discarded operations (same pattern) 275 # merged operations: 276 # insert 661027, delete mark 1093738, delete 6140 277 # discarded operations: 278 # insert 0, delete mark 0, delete 0 279 $status{'ibuf_inserts'} = $self->tonum($row[2]); 280 } 281 elsif ($line =~ m/^Hash table size /) { 282 # In some versions of InnoDB, the used cells is omitted. 283 # Hash table size 4425293, used cells 4229064, .... 284 # Hash table size 57374437, node heap has 72964 buffer(s) <-- no used cells 285 $status{'hash_index_cells_total'} = $self->tonum($row[3]); 286 $status{'hash_index_cells_used'} = $line =~ m/used cells/ ? $self->tonum($row[6]) : '0'; 287 } 288 # LOG 289 elsif ($line =~ m/ log i\/o's done, /) { #' 290 $status{'log_writes'} = $self->tonum($row[0]); 291 } 292 elsif ($line =~ m/ pending log writes, /) { 293 $status{'pending_log_writes'} = $self->tonum($row[0]); 294 $status{'pending_chkp_writes'} = $self->tonum($row[4]); 295 } 296 elsif ($line =~ m/^Log sequence number/) { 297 # This number is NOT printed in hex in InnoDB plugin. 298 # Log sequence number 13093949495856 //plugin 299 # Log sequence number 125 3934414864 //normal 300 $innodb_lsn = defined($row[4]) ? $self->make_bigint($row[3], $row[4]) : $self->tonum($row[3]); 301 } 302 elsif ($line =~ m/^Log flushed up to/) { 303 # This number is NOT printed in hex in InnoDB plugin. 304 # Log flushed up to 13093948219327 305 # Log flushed up to 125 3934414864 306 $flushed_to = defined($row[5]) ? $self->make_bigint($row[4], $row[5]) : $self->tonum($row[4]); 307 } 308 elsif ($line =~ m/^Last checkpoint at/) { 309 # Last checkpoint at 125 3934293461 310 $status{'last_checkpoint'} = defined($row[4]) ? $self->make_bigint($row[3], $row[4]) : $self->tonum($row[3]); 311 } 312 # BUFFER POOL AND MEMORY 313 elsif ($line =~ m/^INDIVIDUAL BUFFER POOL INFO/) { 314 $individual_buffer_pool_info_seen = 1; 315 } 316 elsif ($line =~ m/^Total memory allocated/) { 317 # Total memory allocated 29642194944; in additional pool allocated 0 318 $status{'total_mem_alloc'} = $self->tonum($row[3]); 319 $status{'additional_pool_alloc'} = $self->tonum($row[8]); 320 } 321 elsif($line =~ m/Adaptive hash index /) { 322 # Adaptive hash index 1538240664 (186998824 + 1351241840) 323 $status{'adaptive_hash_memory'} = $self->tonum($row[4]); 324 } 325 elsif($line =~ m/Page hash /) { 326 # Page hash 11688584 327 $status{'page_hash_memory'} = $self->tonum($row[3]); 328 } 329 elsif($line =~ m/Dictionary cache /) { 330 # Dictionary cache 145525560 (140250984 + 5274576) 331 $status{'dictionary_cache_memory'} = $self->tonum($row[3]); 332 } 333 elsif($line =~ m/File system /) { 334 # File system 313848 (82672 + 231176) 335 $status{'file_system_memory'} = $self->tonum($row[3]); 336 } 337 elsif($line =~ m/Lock system/) { 338 # Lock system 29232616 (29219368 + 13248) 339 $status{'lock_system_memory'} = $self->tonum($row[3]); 340 } 341 elsif($line =~ m/Recovery system /) { 342 # Recovery system 0 (0 + 0) 343 $status{'recovery_system_memory'} = $self->tonum($row[3]); 344 } 345 elsif($line =~ m/Threads /) { 346 # Threads 409336 (406936 + 2400) 347 $status{'thread_hash_memory'} = $self->tonum($row[2]); 348 } 349 elsif($line =~ m/innodb_io_pattern /) { 350 # innodb_io_pattern 0 (0 + 0) 351 $status{'innodb_io_pattern_memory'} = $self->tonum($row[2]); 352 } 353 elsif (!$individual_buffer_pool_info_seen && $line =~ m/Buffer pool size /) { 354 # The " " after size is necessary to avoid matching the wrong line: 355 # Buffer pool size 1769471 356 # Buffer pool size, bytes 28991012864 357 $status{'pool_size'} = $self->tonum($row[3]); 358 } 359 elsif (!$individual_buffer_pool_info_seen && $line =~ m/Free buffers/) { 360 $status{'free_pages'} = $self->tonum($row[2]); 361 } 362 elsif (!$individual_buffer_pool_info_seen && $line =~ m/^Database pages/) { 363 $status{'database_pages'} = $self->tonum($row[2]); 364 } 365 elsif (!$individual_buffer_pool_info_seen && $line =~ m/Modified db pages/) { 366 $status{'modified_pages'} = $self->tonum($row[3]); 367 } 368 elsif (!$individual_buffer_pool_info_seen && $line =~ m/Pages read \d+, created/) { 369 #Pages read 349569, created 140622, written 1614731 370 #Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s 371 $status{'pages_read'} = $self->tonum($row[2]); 372 $status{'pages_created'} = $self->tonum($row[4]); 373 $status{'pages_written'} = $self->tonum($row[6]); 374 } 375 # ROW OPERATIONS 376 elsif ($line =~ m/Number of rows inserted/) { 377 $status{'rows_inserted'} = $self->tonum($row[4]); 378 $status{'rows_updated'} = $self->tonum($row[6]); 379 $status{'rows_deleted'} = $self->tonum($row[8]); 380 $status{'rows_read'} = $self->tonum($row[10]); 381 } 382 elsif ($line =~ m/queries inside InnoDB/) { 383 $status{'queries_inside'} = $self->tonum($row[0]); 384 $status{'queries_queued'} = $self->tonum($row[4]); 385 } 386 } 387 388 # Derive some values from other values. 389 $status{'unflushed_log'} = $innodb_lsn - $flushed_to; 390 $status{'log_bytes_written'} = $innodb_lsn; 391 $status{'log_bytes_flushed'} = $flushed_to; 392 $status{'uncheckpointed_bytes'} = $status{'log_bytes_written'} - $status{'last_checkpoint'}; 393 394 my $val; 395 foreach $val (@spin_waits) { 396 $status{'spin_waits'} += $val; 397 } 398 399 foreach $val (@spin_rounds) { 400 $status{'spin_rounds'} += $val; 401 } 402 403 foreach $val (@os_waits) { 404 $status{'os_waits'} += $val; 405 } 406 return \%status; 407} 408 409# takes only numbers from a string 410sub tonum { 411 my $self = shift; 412 my $str = shift; 413 return 0 if !$str; 414 return new Math::BigInt $1 if $str =~ m/(\d+)/; 415 return 0; 416} 417 418# return a 64 bit number from either an hex encoding or 419# a hi lo representation 420sub make_bigint { 421 my ($self, $hi, $lo) = @_; 422 423 unless ($lo) { 424 $hi = new Math::BigInt '0x' . $hi; 425 return $hi; 426 } 427 428 $hi = new Math::BigInt $hi; 429 $lo = new Math::BigInt $lo; 430 return $lo->badd($hi->blsft(32)); 431} 432 433# end of package InnoDBParser 434 435package main; 436 437use strict; 438use warnings; 439use Carp; 440use Data::Dumper; 441use Unix::Syslog qw(:subs :macros); 442use Getopt::Long qw(:config auto_help auto_version no_ignore_case); 443use POSIX qw( setsid ); 444use NetSNMP::OID (':all'); 445use NetSNMP::agent(':all'); 446use NetSNMP::ASN(':all'); 447use NetSNMP::agent::default_store; 448use NetSNMP::default_store qw(:all); 449use SNMP; 450use DBI; 451use DBD::mysql; 452use Pod::Usage; 453 454my %opt = ( 455 daemon_pid => '/var/run/mysql-snmp.pid', 456 oid => '1.3.6.1.4.1.20267.200.1', 457 port => 3306, 458 refresh => 300, 459 master => 1, 460 slave => 0, 461 innodb => 1, 462 procs => 0, 463 host => 'localhost', 464 heartbeat => '' 465); 466 467my %global_status = (); 468my $global_last_refresh = 0; 469my $running = 0; 470my $error = 0; 471# this will hold a table of conversion between numerical oids and oidnames 472my %oids = (); 473my $lowestOid; 474my $highestOid; 475my @ks; 476my $regOID; 477 478# various types & definitions 479my @types = ( 480 'Counter32', 'Counter32', 'Counter32', 'Counter32', # 1 - 4 481 'Gauge32', 'Counter64', 'Gauge32', 'Gauge32', # 5 - 8 482 'Gauge32', 'Gauge32', 'Gauge32', 'Gauge32', # 9 - 12 483 'Gauge32', 'Gauge32', 'Counter32', 'Counter32', # 13 - 16 484 'Counter32', 'Counter32', 'Counter32', 'Counter32', # 17 - 20 485 'Counter32', 'Gauge32', 'Gauge32', 'Gauge32', # 21 - 24 486 'Gauge32', 'Gauge32', 'Gauge32', 'Gauge32', # 25 - 28 487 'Gauge32', 'Gauge32', 'Counter32', 'Counter32', # 29 - 32 488 'Counter32', 'Counter32', 'Counter32', 'Counter32', # 33 - 36 489 'Counter32', 'Counter32', 'Counter32', 'Counter32', # 37 - 40 490 'Counter32', 'Counter32', 'Counter32', 'Gauge32', # 41 - 44 491 'Gauge32', 'Counter32', 'Counter32', 'Counter32', # 45 - 48 492 'Counter32', 'Counter32', 'Counter32', 'Counter32', # 49 - 52 493 'Counter32', 'Gauge32', 'Gauge32', 'Counter32', # 53 - 56 494 'Gauge32', 'Gauge32', 'Gauge32', 'Counter32', # 57 - 60 495 'Gauge32', 'Gauge32', 'Counter32', 'Gauge32', # 61 - 64 496 'Gauge32', 'Gauge32', 'Gauge32', 'Counter32', # 65 - 68 497 'Counter32', 'Counter32', 'Counter32', 'Gauge32', # 69 - 72 498 'Gauge32', 'Counter32', 'Counter32', 'Counter32', # 73 - 76 499 'Counter32', 'Counter32', 'Counter32', 'Counter32', # 77 - 80 500 'Counter32', 'Counter32', 'Counter32', 'Counter32', # 81 - 84 501 'Counter32', 'Counter32', 'Counter32', 'Counter32', # 85 - 88 502 'Counter32', 'Counter32', 'Counter32', 'Counter32', # 89 - 92 503 'Counter32', 'Counter32', 'Counter32', 'Counter32', # 93 - 96 504 'Counter32', 'Counter64', 'Counter64', 'Gauge32', # 97 - 100 505 'Gauge32', 'Counter64', 'Counter64', 'Counter32', # 101 - 104 506 'Gauge32', 'Counter32', 'Counter32', 'Counter32', # 105 - 108 507 'Gauge32', 'Gauge32', 'Gauge32', 'Gauge32', # 109 - 112 508 'Gauge32', 'Gauge32', 'Gauge32', 'Gauge32', # 113 - 116 509 'Gauge32', 'Gauge32', 'Gauge32', 'Gauge32', # 117 - 120 510 'Gauge32', 'Gauge32', 'Gauge32', 'Gauge32', # 121 - 124 511 'Counter64', 'Counter64', 'Gauge32', 'Gauge32', # 125 - 128 512 'Gauge32', 'Gauge32', 'Gauge32', 'Gauge32', # 129 - 132 513 'Gauge32', 'Gauge32', 'Gauge32', 'Gauge32', # 133 - 136 514 'Counter64', 'Counter64', 'Counter64', 'Counter64', # 137 - 140 515 'Counter64', 'Counter64', 'Counter64', 'Counter64', # 141 - 144 516 'Counter64', 'Counter64', 'Counter64', 'Counter64', # 145 - 148 517 'Counter64', 'Counter64', 'String', 'Integer', # 149 - 152 518 'Integer', 'String', 'String', 'String', # 153 - 156 519 'String', 'String', 'Counter64', 'Counter64' # 157 - 160 520 521); 522 523my @newkeys = ( 524 'myKeyReadRequests', 'myKeyReads', # 1 - 2 525 'myKeyWriteRequests', 'myKeyWrites', # 3 - 4 526 'myHistoryList', 'myInnodbTransactions', # 5 - 6 527 'myReadViews', 'myCurrentTransactions', # 7 - 8 528 'myLockedTransactions', 'myActiveTransactions', # 9 - 10 529 'myPoolSize', 'myFreePages', # 11 - 12 530 'myDatabasePages', 'myModifiedPages', # 13 - 14 531 'myPagesRead', 'myPagesCreated', # 15 - 16 532 'myPagesWritten', 'myFileFsyncs', # 17 - 18 533 'myFileReads', 'myFileWrites', # 19 - 20 534 'myLogWrites', 'myPendingAIOLogIOs', # 21 - 22 535 'myPendingAIOSyncIOs', 'myPendingBufPoolFlushes', # 23 - 24 536 'myPendingChkpWrites', 'myPendingIbufAIOReads', # 25 - 26 537 'myPendingLogFlushes', 'myPendingLogWrites', # 27 - 28 538 'myPendingNormalAIOReads', 'myPendingNormalAIOWrites', # 29 - 30 539 'myIbufInserts', 'myIbufMerged', # 31 - 32 540 'myIbufMerges', 'mySpinWaits', # 33 - 34 541 'mySpinRounds', 'myOsWaits', # 35 - 36 542 'myRowsInserted', 'myRowsUpdated', # 37 - 38 543 'myRowsDeleted', 'myRowsRead', # 39 - 40 544 'myTableLocksWaited', 'myTableLocksImmediate', # 41 - 42 545 'mySlowQueries', 'myOpenFiles', # 43 - 44 546 'myOpenTables', 'myOpenedTables', # 45 - 46 547 'myInnodbOpenFiles', 'myOpenFilesLimit', # 47 - 48 548 'myTableCache', 'myAbortedClients', # 49 - 50 549 'myAbortedConnects', 'myMaxUsedConnections', # 51 - 52 550 'mySlowLaunchThreads', 'myThreadsCached', # 53 - 54 551 'myThreadsConnected', 'myThreadsCreated', # 55 - 56 552 'myThreadsRunning', 'myMaxConnections', # 57 - 58 553 'myThreadCacheSize', 'myConnections', # 59 - 60 554 'mySlaveRunning', 'mySlaveStopped', # 61 - 62 555 'mySlaveRetriedTransactions', 'mySlaveLag', # 63 - 64 556 'mySlaveOpenTempTables', 'myQcacheFreeBlocks', # 65 - 66 557 'myQcacheFreeMemory', 'myQcacheHits', # 67 - 68 558 'myQcacheInserts', 'myQcacheLowmemPrunes', # 69 - 70 559 'myQcacheNotCached', 'myQcacheQueriesInCache', # 71 - 72 560 'myQcacheTotalBlocks', 'myQueryCacheSize', # 73 - 74 561 'myQuestions', 'myComUpdate', # 75 - 76 562 'myComInsert', 'myComSelect', # 77 - 78 563 'myComDelete', 'myComReplace', # 79 - 80 564 'myComLoad', 'myComUpdateMulti', # 81 - 82 565 'myComInsertSelect', 'myComDeleteMulti', # 83 - 84 566 'myComReplaceSelect', 'mySelectFullJoin', # 85 - 86 567 'mySelectFullRangeJoin', 'mySelectRange', # 87 - 88 568 'mySelectRangeCheck', 'mySelectScan', # 89 - 90 569 'mySortMergePasses', 'mySortRange', # 91 - 92 570 'mySortRows', 'mySortScan', # 93 - 94 571 'myCreatedTmpTables', 'myCreatedTmpDiskTables', # 95 - 96 572 'myCreatedTmpFiles', 'myBytesSent', # 97 - 98 573 'myBytesReceived', 'myInnodbLogBufferSize', # 99 - 100 574 'myUnflushedLog', 'myLogBytesFlushed', # 101 - 102 575 'myLogBytesWritten', 'myRelayLogSpace', # 103 - 104 576 'myBinlogCacheSize', 'myBinlogCacheDiskUse', # 105 - 106 577 'myBinlogCacheUse', 'myBinaryLogSpace', # 107 - 108 578 'myStateClosingTables', 'myStateCopyingToTmpTable', # 109 - 110 579 'myStateEnd', 'myStateFreeingItems', # 111 - 112 580 'myStateInit', 'myStateLocked', # 113 - 114 581 'myStateLogin', 'myStatePreparing', # 115 - 116 582 'myStateReadingFromNet', 'myStateSendingData', # 117 - 118 583 'myStateSortingResult', 'myStateStatistics', # 119 - 120 584 'myStateUpdating', 'myStateWritingToNet', # 121 - 122 585 'myStateNone', 'myStateOther', # 123 - 124 586 'myAdditionalPoolAlloc', 'myTotalMemAlloc', # 125 - 126 587 'myHashIndexCellsTotal', 'myHashIndexCellsUsed', # 127 - 128 588 'myInnoDBLockStructs', 'myInnoDBLockWaitSecs', # 129 - 130 589 'myInnoDBTablesInUse', 'myInnoDBLockedTables', # 131 - 132 590 'myUncheckpointedBytes', 'myIBufCellCount', # 133 - 134 591 'myIBufUsedCells', 'myIBufFreeCells', # 135 - 136 592 'myAdaptiveHashMemory', 'myPageHashMemory', # 137 - 138 593 'myDictionaryCacheMemory', 'myFileSystemMemory', # 139 - 140 594 'myLockSystemMemory', 'myRecoverySystemMemory', # 141 - 142 595 'myThreadHashMemory', 'myInnoDBSemWaits', # 143 - 144 596 'myInnoDBSemWaitTime', 'myKeyBufBytesUnflushed', # 145 - 146 597 'myKeyBufBytesUsed', 'myKeyBufferSize', # 147 - 148 598 'myInnoDBRowLockTime', 'myInnoDBRowLockWaits', # 149 - 150 599 'myWsrepClusterStateUuid', 'myWsrepClusterConfId', # 151 - 152 600 'myWsrepClusterSize', 'myWsrepClusterStatus', # 153 - 154 601 'myWsrepReady', 'myWsrepConnected', # 155 - 156 602 'myWsrepLocalStateComment', 'myWsrepFlowControlPaused', # 157 - 158 603 'myWsrepReplicatedBytes', 'myWsrepReceivedBytes' # 159 - 160 604 605); 606 607my @oldkeys = ( 608 'Key_read_requests', 'Key_reads', # 1 - 2 609 'Key_write_requests', 'Key_writes', # 3 - 4 610 'history_list', 'innodb_transactions', # 5 - 6 611 'read_views', 'current_transactions', # 7 - 8 612 'locked_transactions', 'active_transactions', # 9 - 10 613 'pool_size', 'free_pages', # 11 - 12 614 'database_pages', 'modified_pages', # 13 - 14 615 'pages_read', 'pages_created', # 15 - 16 616 'pages_written', 'file_fsyncs', # 17 - 18 617 'file_reads', 'file_writes', # 19 - 20 618 'log_writes', 'pending_aio_log_ios', # 21 - 22 619 'pending_aio_sync_ios', 'pending_buf_pool_flushes', # 23 - 24 620 'pending_chkp_writes', 'pending_ibuf_aio_reads', # 25 - 26 621 'pending_log_flushes', 'pending_log_writes', # 27 - 28 622 'pending_normal_aio_reads', 'pending_normal_aio_writes', # 29 - 30 623 'ibuf_inserts', 'ibuf_merged', # 31 - 32 624 'ibuf_merges', 'spin_waits', # 33 - 34 625 'spin_rounds', 'os_waits', # 35 - 36 626 'rows_inserted', 'rows_updated', # 37 - 38 627 'rows_deleted', 'rows_read', # 39 - 40 628 'Table_locks_waited', 'Table_locks_immediate', # 41 - 42 629 'Slow_queries', 'Open_files', # 43 - 44 630 'Open_tables', 'Opened_tables', # 45 - 46 631 'innodb_open_files', 'open_files_limit', # 47 - 48 632 'table_cache', 'Aborted_clients', # 49 - 50 633 'Aborted_connects', 'Max_used_connections', # 51 - 52 634 'Slow_launch_threads', 'Threads_cached', # 53 - 54 635 'Threads_connected', 'Threads_created', # 55 - 56 636 'Threads_running', 'max_connections', # 57 - 58 637 'thread_cache_size', 'Connections', # 59 - 60 638 'slave_running', 'slave_stopped', # 61 - 62 639 'Slave_retried_transactions', 'slave_lag', # 63 - 64 640 'Slave_open_temp_tables', 'Qcache_free_blocks', # 65 - 66 641 'Qcache_free_memory', 'Qcache_hits', # 67 - 68 642 'Qcache_inserts', 'Qcache_lowmem_prunes', # 69 - 70 643 'Qcache_not_cached', 'Qcache_queries_in_cache', # 71 - 72 644 'Qcache_total_blocks', 'query_cache_size', # 73 - 74 645 'Questions', 'Com_update', # 75 - 76 646 'Com_insert', 'Com_select', # 77 - 78 647 'Com_delete', 'Com_replace', # 79 - 80 648 'Com_load', 'Com_update_multi', # 81 - 82 649 'Com_insert_select', 'Com_delete_multi', # 83 - 84 650 'Com_replace_select', 'Select_full_join', # 85 - 86 651 'Select_full_range_join', 'Select_range', # 87 - 88 652 'Select_range_check', 'Select_scan', # 89 - 90 653 'Sort_merge_passes', 'Sort_range', # 91 - 92 654 'Sort_rows', 'Sort_scan', # 93 - 94 655 'Created_tmp_tables', 'Created_tmp_disk_tables', # 95 - 96 656 'Created_tmp_files', 'Bytes_sent', # 97 - 98 657 'Bytes_received', 'innodb_log_buffer_size', # 99 - 100 658 'unflushed_log', 'log_bytes_flushed', # 101 - 102 659 'log_bytes_written', 'relay_log_space', # 103 - 104 660 'binlog_cache_size', 'Binlog_cache_disk_use', # 105 - 106 661 'Binlog_cache_use', 'binary_log_space', # 107 - 108 662 'State_closing_tables', 'State_copying_to_tmp_table', # 109 - 110 663 'State_end', 'State_freeing_items', # 111 - 112 664 'State_init', 'State_locked', # 113 - 114 665 'State_login', 'State_preparing', # 115 - 116 666 'State_reading_from_net', 'State_sending_data', # 117 - 118 667 'State_sorting_result', 'State_statistics', # 119 - 120 668 'State_updating', 'State_writing_to_net', # 121 - 122 669 'State_none', 'State_other', # 123 - 124 670 'additional_pool_alloc', 'total_mem_alloc', # 125 - 126 671 'hash_index_cells_total', 'hash_index_cells_used', # 127 - 128 672 'innodb_lock_structs', 'innodb_lock_wait_secs', # 129 - 130 673 'innodb_tables_in_use', 'innodb_locked_tables', # 131 - 132 674 'uncheckpointed_bytes', 'ibuf_cell_count', # 133 - 134 675 'ibuf_used_cells', 'ibuf_free_cells', # 135 - 136 676 'adaptive_hash_memory', 'page_hash_memory', # 137 - 138 677 'dictionary_cache_memory', 'file_system_memory', # 139 - 140 678 'lock_system_memory', 'recovery_system_memory', # 141 - 142 679 'thread_hash_memory', 'innodb_sem_waits', # 143 - 144 680 'innodb_sem_wait_time_ms', 'key_buf_bytes_unflushed', # 145 - 146 681 'key_buf_bytes_used', 'key_buffer_size', # 147 - 148 682 'Innodb_row_lock_time', 'Innodb_row_lock_waits', # 149 - 150 683 'wsrep_cluster_state_uuid', 'wsrep_cluster_conf_id', # 151 - 152 684 'wsrep_cluster_size', 'wsrep_cluster_status', # 153 - 154 685 'wsrep_ready', 'wsrep_connected', # 155 - 156 686 'wsrep_local_state_comment', 'wsrep_flow_control_paused', # 157 - 158 687 'wsrep_replicated_bytes', 'wsrep_received_bytes' # 159 - 160 688 689); 690 691run() unless caller(); 692 693# daemonize the program 694sub daemonize { 695 open STDIN, '/dev/null' or die "mysql-snmp: can't read /dev/null: $!"; 696 open STDOUT, '>/dev/null' or die "mysql-snmp: can't write to /dev/null: $!"; 697 defined(my $pid = fork) or die "mysql-snmp: can't fork: $!"; 698 if ($pid) { 699 # parent 700 open my $pidfile, '>', $opt{daemon_pid} 701 or croak "Couldn't open $opt{daemon_pid} for writing: $!"; 702 print {$pidfile} "$pid\n" or croak "Couldn't write pid to $opt{daemon_pid}: $!"; 703 close $pidfile or croak "Couldn't close $opt{daemon_pid}: $!"; 704 exit; 705 } 706 707 # child 708 setsid() or die "mysql-snmp: can't start a new session: $!"; 709 open STDERR, '>&STDOUT' or die "mysql-snmp: can't dup stdout: $!"; 710} 711 712sub max { 713 my ($a, $b) = @_; 714 return $a if $a > $b; 715 return $b; 716} 717 718sub bigint($) { 719 my $str = shift; 720 return Math::BigInt->bzero() if !$str; 721 return new Math::BigInt $1 if $str =~ m/(\d+)/; 722 return Math::BigInt->bzero(); 723} 724 725# This function has been translated from PHP to Perl from the 726# excellent Baron Schwartz's MySQL Cacti Templates 727sub fetch_mysql_data { 728 my ($datasource, $dbuser, $dbpass) = @_; 729 my %output; 730 eval { 731 my $dbh = DBI->connect($datasource, $dbuser, $dbpass, {RaiseError => 1, AutoCommit => 1}); 732 if (!$dbh) { 733 dolog(LOG_CRIT, "Can't connect to database: $datasource, $@"); 734 return; 735 } 736 737 my %status = ( 738 'transactions' => 0, 739 'relay_log_space' => 0, 740 'binary_log_space' => 0, 741 'slave_lag' => 0, 742 'slave_running' => 0, 743 'slave_stopped' => 0, 744 'State_closing_tables' => 0, 745 'State_copying_to_tmp_table' => 0, 746 'State_end' => 0, 747 'State_freeing_items' => 0, 748 'State_init' => 0, 749 'State_locked' => 0, 750 'State_login' => 0, 751 'State_preparing' => 0, 752 'State_reading_from_net' => 0, 753 'State_sending_data' => 0, 754 'State_sorting_result' => 0, 755 'State_statistics' => 0, 756 'State_updating' => 0, 757 'State_writing_to_net' => 0, 758 'State_none' => 0, 759 'State_other' => 0, 760 ); 761 762 my $result = $dbh->selectall_arrayref("SHOW /*!50002 GLOBAL */ STATUS"); 763 foreach my $row (@$result) { 764 $status{$row->[0]} = $row->[1]; 765 } 766 767 # Get SHOW VARIABLES and convert the name-value array into a simple 768 # associative array. 769 $result = $dbh->selectall_arrayref("SHOW VARIABLES"); 770 foreach my $row (@$result) { 771 $status{$row->[0]} = $row->[1]; 772 } 773 774 # Make table_open_cache backwards-compatible. 775 if ( defined($status{'table_open_cache'}) ) { 776 $status{'table_cache'} = $status{'table_open_cache'}; 777 } 778 779 if ($opt{slave}) { 780 $result = $dbh->selectall_arrayref("SHOW SLAVE STATUS", { Slice => {} }); 781 782 foreach my $row (@$result) 783 { 784 # Must lowercase keys because different versions have different 785 # lettercase. 786 my %newrow = map { lc($_) => $row->{$_} } keys %$row; 787 $status{'relay_log_space'} = $newrow{'relay_log_space'}; 788 $status{'slave_lag'} = $newrow{'seconds_behind_master'}; 789 790 # Check replication heartbeat, if present. 791 if ( $opt{heartbeat} ne '' ) { 792 my $row2 = $dbh->selectrow_arrayref("SELECT GREATEST(0, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) - 1) FROM $opt{heartbeat} WHERE server_id != \@\@SERVER_ID ORDER BY ts DESC LIMIT 1"); 793 $status{'slave_lag'} = $row2->[0]; 794 } 795 796 $status{'slave_running'} = ($newrow{'slave_sql_running'} eq 'Yes') ? 1 : 0; 797 $status{'slave_stopped'} = ($newrow{'slave_sql_running'} eq 'Yes') ? 0 : 1; 798 } 799 } 800 801 # Get info on master logs. 802 my @binlogs = (0); 803 if ($opt{master} && $status{'log_bin'} eq 'ON') { # See issue #8 804 $result = $dbh->selectall_arrayref( 805 "SHOW MASTER LOGS", 806 {Slice => {}} 807 ); 808 foreach my $row (@$result) { 809 my %newrow = map {lc($_) => $row->{$_}} keys %$row; 810 811 # Older versions of MySQL may not have the File_size column in the 812 # results of the command. 813 if (exists($newrow{'file_size'}) && $newrow{'file_size'} > 0) { 814 push(@binlogs, $newrow{'file_size'}); 815 } 816 else { 817 last; 818 } 819 } 820 } 821 822 if (scalar @binlogs) { 823 $status{'binary_log_space'} = 0; 824 foreach my $log (@binlogs) { 825 $status{'binary_log_space'} += $log; 826 } 827 } 828 829 # Get SHOW INNODB STATUS and extract the desired metrics from it. 830 if ($opt{innodb}) { #mysql 5.6 has no 'have_innodb' status, latest percona monitoring tool uses 'show engines' and looks for innodb 831 my $innodb_array = $dbh->selectall_arrayref("SHOW /*!50000 ENGINE*/ INNODB STATUS",{Slice => {}}); 832 my @lines = split("\n", $innodb_array->[0]{'Status'}); 833 834 my $innodb_parser = InnoDBParser->new(); 835 my $out = $innodb_parser->parse_innodb_status(\@lines); 836 837 # Override values from InnoDB parsing with values from SHOW STATUS, 838 # because InnoDB status might not have everything and the SHOW STATUS is 839 # to be preferred where possible. 840 my %overrides = ( 841 'Innodb_buffer_pool_pages_data' => 'database_pages', 842 'Innodb_buffer_pool_pages_dirty' => 'modified_pages', 843 'Innodb_buffer_pool_pages_free' => 'free_pages', 844 'Innodb_buffer_pool_pages_total' => 'pool_size', 845 'Innodb_buffer_pool_reads' => 'pages_read', 846 'Innodb_data_fsyncs' => 'file_fsyncs', 847 'Innodb_data_pending_reads' => 'pending_normal_aio_reads', 848 'Innodb_data_pending_writes' => 'pending_normal_aio_writes', 849 'Innodb_os_log_pending_fsyncs' => 'pending_log_flushes', 850 'Innodb_pages_created' => 'pages_created', 851 'Innodb_pages_read' => 'pages_read', 852 'Innodb_pages_written' => 'pages_written', 853 'Innodb_rows_deleted' => 'rows_deleted', 854 'Innodb_rows_inserted' => 'rows_inserted', 855 'Innodb_rows_read' => 'rows_read', 856 'Innodb_rows_updated' => 'rows_updated', 857 ); 858 859 # If the SHOW STATUS value exists, override... 860 foreach my $key (keys %overrides) { 861 if ( defined($status{$key}) ) { 862 $out->{$overrides{$key}} = $status{$key}; 863 } 864 } 865 866 foreach my $key (keys %$out) { 867 $status{$key} = $out->{$key}; 868 } 869 870 if (defined($status{'unflushed_log'})) { 871 # it seems that unflushed_log is sometimes not defined... 872 $status{'unflushed_log'} = max($status{'unflushed_log'}, $status{'innodb_log_buffer_size'}); 873 } 874 } 875 876 $status{'key_buf_bytes_used'} = bigint($status{'key_buffer_size'})->bsub(bigint($status{'Key_blocks_unused'})->bmul($status{'key_cache_block_size'})); 877 $status{'key_buf_bytes_unflushed'} = bigint($status{'Key_blocks_not_flushed'})->bmul(bigint($status{'key_cache_block_size'})); 878 879 # Get SHOW PROCESSLIST and aggregate it by state, then add it to the array 880 # too. 881 if ( $opt{procs} ) { 882 $result = $dbh->selectall_arrayref("SHOW PROCESSLIST",{Slice => {}}); 883 foreach my $row (@$result) { 884 my %newrow = map {lc($_) => $row->{$_}} keys %$row; 885 my $state = $newrow{'State'}; 886 if ( !defined($state) ) { 887 $state = 'NULL'; 888 } 889 if ( $state eq '' ) { 890 $state = 'none'; 891 } 892 $state = lc($state); 893 $state =~ s/ /_/; 894 if ( defined($status{"State_$state"}) ) { 895 $status{"State_$state"} += 1; 896 } 897 else { 898 $status{"State_other"} += 1; 899 } 900 } 901 } 902 903 $dbh->disconnect(); 904 905 my %trans; 906 my $i = 0; 907 foreach my $key (@oldkeys) { 908 $trans{$key} = $newkeys[$i++]; 909 } 910 911 foreach my $key (keys %status) { 912 $output{$trans{$key}} = $status{$key} if (exists($trans{$key})); 913 } 914 }; 915 if ($@) { 916 dolog(LOG_CRIT, "can't refresh data from mysql: $@\n"); 917 return (undef, undef, undef); 918 } 919 return (\@newkeys, \@types, \%output); 920} 921 922### 923### Called automatically now and then 924### Refreshes the $global_status and $global_variables 925### caches. 926### 927sub refresh_status { 928 my $startOID = shift; 929 my $dsn = shift; 930 my $now = time(); 931 932 # Check if we have been called quicker than once every $refresh 933 if (($now - $global_last_refresh) < $opt{refresh}) { 934 # if yes, do not do anything 935 dolog(LOG_DEBUG, "not refreshing: " . ($now - $global_last_refresh) . " < $opt{refresh}") if ($opt{verbose}); 936 return; 937 } 938 my ($oid, $types, $status) = fetch_mysql_data($dsn, $opt{user}, $opt{password}); 939 if ($oid) { 940 dolog(LOG_DEBUG, "Setting error to 0") if ($opt{verbose}); 941 $error = 0; 942 my $index = 0; 943 foreach my $key (@$oid) { 944 $global_status{$key}{'value'} = $status->{$key}; 945 $global_status{$key}{'type'} = $types->[$index]; 946 $index++; 947 } 948 dolog(LOG_DEBUG, "Refreshed at $now " . (time() - $now)) if ($opt{verbose}); 949 print Dumper(\%global_status) if ($opt{verbose}); 950 } 951 else { 952 dolog(LOG_DEBUG, "Setting error to 1") if ($opt{verbose}); 953 $error = 1; 954 } 955 956 $global_last_refresh = $now; 957 return; 958} 959 960sub getASNType { 961 my $type = shift; 962 if ($type eq 'Counter32') { 963 return ASN_COUNTER; 964 } 965 elsif ($type eq 'Gauge32') { 966 return ASN_GAUGE; 967 } 968 elsif ($type eq 'Counter64') { 969 return ASN_COUNTER64; 970 } 971 elsif ($type eq 'OID') { 972 return ASN_OBJECT_ID; 973 } 974 elsif ($type eq 'Integer') { 975 return ASN_INTEGER; 976 } 977 return ASN_OCTET_STR; 978} 979 980sub shut_it_down { 981 $running = 0; 982 dolog(LOG_INFO, "shutting down"); 983} 984 985sub set_value { 986 my ($request, $oid, $request_info) = @_; 987 988 if (!$error) { 989 my $oidname = $oids{$oid}->{'name'}; 990 if (!defined $oidname) { 991 if ($oid != $regOID) { 992 dolog(LOG_INFO, "OID $oid is not available"); 993 $request->setError($request_info, SNMP_ERR_NOSUCHNAME); 994 } 995 return 0; 996 } 997 998 my $value = $global_status{$oidname}{'value'}; 999 if (defined $value) { 1000 if ($opt{verbose}) { 1001 dolog(LOG_DEBUG, "$oid -> $lowestOid"); 1002 dolog(LOG_DEBUG, " -> ($oidname) $value"); 1003 } 1004 $request->setOID($oid); 1005 $request->setValue(getASNType($global_status{$oidname}{'type'}), "$value"); 1006 } 1007 else { 1008 dolog(LOG_DEBUG, "OID $oid has no value") if ($opt{verbose}); 1009 return 0; 1010 } 1011 } 1012 return 1; 1013} 1014 1015sub my_snmp_handler { 1016 my ($handler, $registration_info, $request_info, $requests) = @_; 1017 my ($request); 1018 1019 for ($request = $requests; $request; $request = $request->next()) { 1020 # Process request for $oid (e.g. mysqlUptime) 1021 my $oid = $request->getOID(); 1022 my $mode = $request_info->getMode(); 1023 my $value; 1024 my $next; 1025 1026 dolog(LOG_DEBUG, "asking for oid $oid (mode $mode)") if ($opt{verbose}); 1027 if ($error) { 1028 dolog(LOG_DEBUG, "error for oid $oid (mode $mode)") if ($opt{verbose}); 1029 $request->setError($request_info, SNMP_ERR_NOSUCHNAME); 1030 next; 1031 } 1032 1033 if ($mode == MODE_GET) { 1034 set_value($request, $oid, $request_info); 1035 } 1036 1037 if ($mode == MODE_GETNEXT) { 1038 if (NetSNMP::OID::compare($oid, $lowestOid) < 0) { 1039 set_value($request, $lowestOid, $request_info); 1040 } 1041 elsif (NetSNMP::OID::compare($oid, $highestOid) <= 0) #request is somewhere in our range, so return first one after it 1042 { 1043 my $lasti = 0; 1044 my $i = 0; 1045 my $oidToUse = undef; 1046 1047 #linear search of sorted keys array. 1048 do { 1049 $oidToUse = $ks[$i]; 1050 $i++; 1051 } while (NetSNMP::OID::compare($oid, $oidToUse) > -1 and $i < scalar @ks); 1052 1053 #got one to return 1054 if (defined $oidToUse) { 1055 if (NetSNMP::OID::compare($oid, $oidToUse) == 0) { 1056 dolog(LOG_DEBUG, "GETNEXT $oid == $oidToUse, no next, returning nothing") if ($opt{verbose}); 1057 next; 1058 } 1059 dolog(LOG_DEBUG, "Next oid to $oid is $oidToUse") if ($opt{verbose}); 1060 while (!set_value($request, $oidToUse, $request_info)) { 1061 # got not value, we need to get the next oid 1062 $oidToUse = $ks[$i]; 1063 $i++; 1064 last if $i > scalar @ks; 1065 } 1066 } 1067 } 1068 } 1069 } 1070 dolog(LOG_DEBUG, "finished processing") if ($opt{verbose}); 1071} 1072 1073sub dolog { 1074 my ($level, $msg) = @_; 1075 syslog($level, $msg); 1076 print STDERR $msg . "\n" if ($opt{verbose}); 1077} 1078 1079sub VersionMessage { 1080 print "mysql-snmp $VERSION by brice.figureau\@daysofwonder.com\n"; 1081} 1082 1083sub run 1084{ 1085 netsnmp_ds_set_boolean(NETSNMP_DS_APPLICATION_ID, NETSNMP_DS_AGENT_NO_ROOT_ACCESS, 1); 1086 my $agent = new NetSNMP::agent('Name' => 'mysql', 'AgentX' => 1); 1087 1088 GetOptions( 1089 \%opt, 1090 'host|h=s', 1091 'port|P=i', 1092 'user|u=s', 1093 'password|p=s', 1094 'config|c=s', 1095 'master|m!', 1096 'slave|s!', 1097 'innodb|i!', 1098 'oid|o=s', 1099 'procs|l|process-list!', 1100 'refresh|r=i', 1101 'daemon_pid|daemon-pid=s', 1102 'heartbeat|b=s', 1103 'no-daemon|n', 1104 'man', 1105 'usage', 1106 'verbose|v+', 1107 'version|V' => sub {VersionMessage(); exit 0;}, 1108 ) or pod2usage(-verbose => 0); 1109 1110 pod2usage(-verbose => 0) if $opt{usage}; 1111 pod2usage(-verbose => 1) if $opt{help}; 1112 pod2usage(-verbose => 2) if $opt{man}; 1113 1114 my $subagent = 0; 1115 my $dsn = 'DBI:mysql:'; 1116 if ($opt{config}) { 1117 $dsn .= "mysql_read_default_file=$opt{config}"; 1118 } 1119 else { 1120 $dsn .= join(';', "host=$opt{host}", "port=$opt{port}"); 1121 } 1122 1123 openlog("mysql-snmp", LOG_PID | LOG_PERROR, LOG_DAEMON); 1124 1125 daemonize() if !$opt{'no-daemon'}; 1126 1127 $regOID = new NetSNMP::OID($opt{oid}); 1128 $agent->register("mysql", $regOID, \&my_snmp_handler); 1129 1130 # build the oids table 1131 my $i = 1; 1132 foreach my $oidname (@newkeys) { 1133 $oids{$regOID . ".$i.0"} = { 1134 'name' => $oidname, 1135 'oid' => new NetSNMP::OID($regOID . ".$i.0") 1136 }; 1137 $i++; 1138 } 1139 1140 # this contains a lexicographycally sorted oids array 1141 @ks = sort {$a <=> $b} map {$_ = new NetSNMP::OID($_)} keys %oids; 1142 $lowestOid = $ks[0]; 1143 $highestOid = $ks[$#ks]; 1144 1145 if ($opt{verbose}) { 1146 foreach my $k (@ks) { 1147 dolog(LOG_DEBUG, "$k -> " . $oids{$k}->{'name'}); 1148 } 1149 } 1150 1151 1152 # We need to perform a loop here waiting for snmp requests. We 1153 # also check for new STATUS data. 1154 $SIG{'INT'} = \&shut_it_down; 1155 $SIG{'QUIT'} = \&shut_it_down; 1156 $SIG{'TERM'} = \&shut_it_down; 1157 $running = 1; 1158 1159 while ($running) { 1160 refresh_status($opt{oid}, $dsn); 1161 $agent->agent_check_and_process(1); # 1 = block 1162 } 1163 $agent->shutdown(); 1164 1165 dolog(LOG_INFO, "agent shutdown"); 1166} 1167 1168__END__ 1169 1170=head1 NAME 1171 1172 mysql-snmp - report mysql statistics via SNMP 1173 1174=head1 SYNOPSIS 1175 1176 mysql-snmp [options] 1177 1178 -h HOST, --host=HOST connect to MySQL DB on HOST 1179 -P PORT, --port=PORT port to connect (default 3306) 1180 -u USER, --user=USER use USER as user to connect to mysql 1181 -p PASS, --password=PASS use PASS as password to connect to mysql 1182 -c FILE, --config=FILE read mysql connection details from FILE 1183 -m, --master check master 1184 -s, --slave check slave 1185 -b, --heartbeat DB.TABLE table for checking slave lag with mk-hearbeat 1186 -i, --innodb read innodb settings 1187 -o OID, --oid=OID registering OID 1188 -l, --process-list, --procs enable the process list 1189 -r INT, --refresh=INT set refresh interval to INT (seconds) 1190 --daemon-pid=FILE write PID to FILE instead of $default{pid} 1191 -n, --no-daemon do not detach and become a daemon 1192 -v, --verbose be verbose about what you do 1193 1194 -?, --help display this help and exit 1195 --usage display detailed usage information 1196 --man display program man page 1197 -V, --version output version information and exit 1198 1199=head1 OPTIONS 1200 1201=over 8 1202 1203=item B<-h HOST, --host=HOST> 1204 1205connect to MySQL DB on HOST 1206 1207=item B<-P PORT, --port=PORT> 1208 1209port to connect (default 3306) 1210 1211=item B<-u USER, --user=USER> 1212 1213use USER as user to connect to mysql 1214 1215=item B<-p PASS, --password=PASS> 1216 1217use PASS as password to connect to mysql 1218 1219=item B<-c FILE, --config=FILE> 1220 1221read mysql connection details from file FILE. 1222 1223These should be stored in a section named [client]. Eg: 1224 1225 [client] 1226 host=dbserver 1227 port=3306 1228 user=monitor 1229 password=secret 1230 1231=item B<-m, --master> 1232 1233check master 1234 1235=item B<-s, --slave> 1236 1237check slave 1238 1239=item B<-b DB.TABLE, --heartbeat DB.TABLE> 1240 1241specifies the table containing the mk-heartbeat timestamp for computing slave lag 1242 1243=item B<-i, --innodb> 1244 1245check innodb details 1246 1247=item B<-o OID, --oid=OID> 1248 1249registering OID 1250 1251=item B<-l, --process-list, --procs> 1252 1253enable the process list 1254 1255=item B<-r INT, --refresh=INT> 1256 1257refresh interval in seconds 1258 1259=item B<--daemon-pid=FILE> 1260 1261write PID to FILE instead of $default{pid} 1262 1263=item B<-n, --no-daemon> 1264 1265do not detach and become a daemon 1266 1267=item B<-v, --verbose> 1268 1269be verbose about what you do 1270 1271=item B<--man> 1272 1273Prints the manual page and exits. 1274 1275=item B<--usage> 1276 1277Prints detailed usage information and exits. 1278 1279=item B<-?, --help> 1280 1281Print a brief help message and exits. 1282 1283=item B<-V, --version> 1284 1285output version information and exit 1286 1287=back 1288 1289=head1 DESCRIPTION 1290 1291B<mysql-snmp> is a small daemon that connects to a local snmpd daemon 1292to report statistics on a local or remote MySQL server. 1293 1294=cut 1295