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_si ze 32768
bulk_insert_buf fer_size 8388608
character_set_c lient latin1
character_set_c onnection latin1
character_set_d atabase latin1
character_set_r esults latin1
character_set_s erver latin1
character_set_s ystem 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