#!/usr/bin/perl # Check the health of a mysql server. use strict; use warnings; use Getopt::Long; use DBI; use HTML::Template; my %variables = (); my %status = (); my $resl = (); # -- # Print out the usage message # -- sub usage { print "usage: check_mysqlhealth.pl -H -u -p \n"; print " Optional parameters:\n"; print " --port \n"; } $|=1; # -- # Parse arguments and read Configuration # -- my ($host, $user, $password, $port); GetOptions ( 'host=s' => \$host, 'H=s' => \$host, 'user=s' => \$user, 'u=s' => \$user, 'password=s' => \$password, 'p:s' => \$password, 'port=i' => \$port, ); if (!$host || !$user) { usage(); exit(1); } if (!$port) { $port = 3306; } my $totalTime = time(); # -- # Establish connection # -- my $state = "OK"; my $dbh; eval { $dbh = DBI->connect("DBI:mysql:host=$host;port=$port", $user, $password, {'RaiseError' => 1}); }; if ($@) { my $status = $@; print 'CRITICAL: Connect failed with reason ' . $status . "\n"; exit 2; } #open the html template my $template=HTML::Template->new(filename => '/root/cookbook/perl/MysqlHC.tmpl'); &var(); &stat(); &repl(); sub var { my $sgv = $dbh->prepare("show variables"); $sgv->execute(); #my %variables = (); while (my ($keyword, $value) = $sgv->fetchrow_array()) { $variables{$keyword} = $value; } $sgv->finish(); } sub stat { my $sth = $dbh->prepare("show status"); $sth->execute(); #my %status= (); while (my ($keyword,$value) = $sth->fetchrow_array()) { $status{$keyword} = $value; } $sth->finish(); } sub repl { my $rs = $dbh->prepare("show status like 'slave_running'"); my $rrdbs = $dbh->prepare("show variables like 'read_rnd_buffer_size'"); $rs->execute(); $rrdbs->execute(); while(my @vall = $rs->fetchrow_array()) { $template->param(Replication => $vall[1]); if($vall[1] eq 'ON') { print "The system is configured as Mysql Replication SLAVE\n"; print " The following is the Slave Status\n"; my $mas_hos = $dbh->prepare("show slave status"); $mas_hos->execute(); while (my @val = $mas_hos->fetchrow_array()) { print "Slave_IO_State: $val[0]\n"; print "Master_host: $val[1]\n"; print "Slave_IO_Running: $val[10]\n"; print "Slave_SQL_Running: $val[11]\n\n"; #print "Seconds_Behind_master: $val[33]\n"; } $mas_hos->finish(); } } } # send the obligatory Content-Type and print the template output print "Content-Type: text/html\n\n", $template->output; #### Mysql Health Check Script Replication Slave Status < TMPL_VAR NAME=Replication>