sub repl
{
#print "********REPLICATION STATUS********\n\n";
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())
{print "
| Slave_IO_State |
Master_host |
Slave_IO_Running |
Slave_SQL_Running |
";
# print "Replication Slave Status: $vall[1]\n";
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 "| $val[0] | ";
print "$val[1] | ";
print "$val[10] | ";
print "$val[11] | ";
#print "$val[33] | ";
print "
\n"
}
print "
\n";
####
#!/usr/bin/perl
# Check the health of a mysql server.
use strict;
use warnings;
use Getopt::Long;
use DBI;
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;
}
print "";
print "";
print " MYSQL HEALTH CHECK REPORT";
print "";
print "\n";
&var();
&stat();
&repl();
&mr();
&tac();
&tc();
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
{
print "#########################################\n";
print "# REPLICATION STATUS #\n";
print "#########################################\n";
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())
{
print "Replication Slave Status: $vall[1]\n";
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();
}
}
}
sub mr
{
print "#########################################\n";
print "# MYISAM RECOVER OPTION #\n";
print "#########################################\n";
print "Myisam_recover_options:$variables{'myisam_recover_options'}\n";
if ( $variables{"myisam_recover_options"} eq 'BACKUP,FORCE')
{
print"MyISAM-recover option is enabled.This is good";
}
else
{
print "MyISAM-recover option is NOT enabled.\n";
}
print "Problem Description:
The myisam-recover option enables automatic crash recovery of corrupted MyISAM tables. If this option is not set, then a table will be Marked as crashed if it becomes corrupt, and no sessions will be able to SELECT from it, or perform any sort of DML against it.
Advice:
Set myisam-recover=FORCE,BACKUP in your my.cnf/my.ini file. The FORCE option forces a repair in case of corruption. The BACKUP option causes a backup to be made so data can be restored from the old files in case the repair process lost some data.
Recommended Action
Set myisam-recover=FORCE,BACKUP\n\n\n"
}
sub tac
{
print "#########################################\n";
print "# TABLE CACHE #\n";
print "#########################################\n";
my $table_cache_hit_rate;
my $table_cache_fill;
my $table_open_cache;
my $table_cache;
my $Open_tables;
my $Opened_tables;
print "Table_cache:$variables{'table_cache'}\n";
print "Open_tables:$status{'Open_tables'}\n";
print "Opened_tables:$status{'Opened_tables'}\n";
if ( $status{"Opened_tables"} != 0 and $variables{"table_cache"} != 0 )
{
$table_cache_hit_rate = $status{"Open_tables"} * 100 / $status{"Opened_tables"};
$table_cache_fill = int $status{"Open_tables"} * 100 / $variables{"table_cache"};
}
elsif ( $status{"Opened_tables"} == 0 and $variables{"table_cache"} != 0 )
{
$table_cache_hit_rate=100;
$table_cache_fill= int $status{"Open_tables"} * 100 / $variables{"table_cache"};
}
else
{
print "Error.NO table_cache OR Table Cache Not defined!\n";
}
print "Table_cache_hit_rate=$table_cache_hit_rate\n";
print "Table_cache_fill=$table_cache_fill\n";
print "\n";
if ( $variables{"table_cache"} and !$table_open_cache )
{
print "Current table_cache value = $variables{'table_cache'}\n";
}
if ( $table_cache_fill < 95 )
{
print "You have $status{'Open_tables'} open tables\n";
print "The table_cache value seems to be fine\n";
}
elsif ( $table_cache_hit_rate < 85 and $table_cache_fill > 95 )
{
print "You have $status{'Open_tables'} open tables.\n";
print "Current table_cache hit rate is $table_cache_hit_rate%\n";
print ",while $table_cache_fill% of your table cache is in use You should probably increase your table_cache\n"; }
else
{
print "Current table_cache hit rate is $table_cache_hit_rate%\n";
print ",while $table_cache_fill% of your table cache is in use\n";
print "The table cache value seems to be fine\n";
}
}
sub tc
{
print "#########################################\n";
print "# THREAD CACHE #\n";
print "#########################################\n";
my $thread_cache_hit_rate;
print "Threads_created:$status{'Threads_created'}\n";
print "Connections:$status{'Connections'}\n";
print "Thread_cache_size:$variables{'thread_cache_size'}\n";
print "Current Thread Cache Size:$variables{'thread_cache_size'}\n";
$thread_cache_hit_rate = 100 - (($status{"Threads_created"}/$status{"Connections"}) * 100);
print "Thread Cache Hit Rate:$thread_cache_hit_rate\n";
print "Description:\n";
print "The hit rate should be as close to 100% as possible. You can calculate your hit ratio by dividing the threads_created status variable by the Connections status variable:100 - ((Threads_created / Connections) * 100).If hitrate is very low like 1% then almost every mysql connection was causing a new thread to be created, and a lot of threads were being created, thus creating a lot of unnecessary overhead.The cause of the problem was that 'thread_cache_size' was set to 0. Thread_cache_size determines how many threads MySQL will hold open in memory to handle new connections.To determine what you should set 'thread_cache_size' to, pay close attention to the 'threads_created' status variable. If it keeps going up it means your 'thread_cache_size' is set too low. Just keep bumping up 'thread_cache_size' until 'threads_created' no longer increments.\n";
}