mysql health check script

[polycom]

Hi,

I am coding a mysql health check script. The logic is to execute the commands (only once)show status,show slave status,show variables and fetch the variable name and value in a hash refer or any other fetch machanism and dynamically use the values to do calculation like the following

threhold values($uptime > 10800) && (Handler_read_rnd_next > 4000) && ((100-(((Handler_read_rnd_next + Handler_read_rnd) / (##Handler_read_rnd_next + Handler_read_rnd + Handler_read_first + Handler_read_next + Handler_read_key + Handler_read_prev))*100))
 

if any one can code using hash reference and use cloumn name to do calculation..
Show variables would result the following output.There are other 189 variables which will be used to do the above calculation.
mysql> show variables;
Variable_name | Value
-------------------------------------------------------
back_log 50
basedir /
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results latin1
character_set_server latin1
character_set_system utf8

my current code is

#!/usr/bin/perl -w
# Check the health of a mysql server.
#
use Getopt::Long;
use DBI;
#use strict;
#use warning;
 
my $Variable_name = '';
my $status = '';
# --
# Print out the usage message
# --
sub usage {
    print "usage: check_mysqlhealth.pl -H <host> -u <user> -p <password> \n";
    print "       Optional parameters:\n";
    print "       --port <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});
print "connected\n";
};
 
if ($@) {
    my $status = $@;
    print 'CRITICAL: Connect failed with reason ' . $status . "\n";
    exit 2;
}
 
&shvar;
 
sub shvar
 {
    my $sgv = $dbh->prepare("show variables");
    $sgv->execute();
   %MySQL_Variables = ();
    while (my ($name,$value) = $sgv->fetchrow_array())
{
      print "$MySQL_Variables{lc($name)} = $value\n";
 
    }
}
 

This code is listing the variables, now how can i fetch a particular variable to do computation..

Need help desperately...

[eWish]

Instead of using "Show Variable" just do a "Select" statement.

my (@rows);
 
my $select = $dbh->prepare('SELECT column1, column2, column3 FROM table_name WHERE column1 = ?');
   $select->execute($value_for_column_criteria);
 
   while (@rows = $select->fetchrow_array) {
       print join("\n", @rows);
       }       

--Kevin