#!/usr/bin/perl
# Check the health of a mysql server.
#
use Getopt::Long;
use DBI;
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;
}

&open_report;

sub open_report
 {
   my $sgv = $dbh->prepare("show variables");
   $sgv->execute();

          while (my $gv=$sgv->fetchrow_hashref())
{
 %MySQL_Variables{@gv[0]} = @gv[1];(is there a systax err here)

Then to use a mysql item in perl use 
%MySQL_Variables{"uptime"}
             }
$sgv->finish();
}

##</code><code>##

print "********READ-WRITE RATIO********\n\n";
#The r/w ratio is of course (Com_select + Qcache_hits)/(Com_insert+Com_update+Com_delete+Com_replace)
my $cs = $dbh->prepare("show status like 'com_select'");
my $qch = $dbh->prepare("show status like 'qcache_hits'");
my $ci = $dbh->prepare("show status like 'com_insert'");
my $cu = $dbh->prepare("show status like 'com_update'");
my $cd = $dbh->prepare("show status like 'com_delete'");
my $cr = $dbh->prepare("show status like 'com_replace'");
$cs->execute();
$qch->execute();
$ci->execute();
$cu->execute();
$cd->execute();
$cr->execute();


while (my @csr = $cs->fetchrow_array())
{
while (my @qchr = $qch->fetchrow_array())
{
print "\tCom_select: $csr[1]\n";
print "\tQcahce_hits: $qchr[1]\n";
my $result = ($csr[1] + $qchr[1]);
print "The select rate is :$result\n";
}
}
$qch->finish();
$cs->finish();

while (my @cir = $ci->fetchrow_array())
{
while (my @cur = $cu->fetchrow_array())
{
while (my @cdr = $cd->fetchrow_array())
{
while (my @crr = $cr->fetchrow_array())
{
print "\tCom_Insert: $cir[1]\n"; 
print "\tCom_Update: $cur[1]\n";
print "\tCom_Delete: $cdr[1]\n";
print "\tCom_Replace: $crr[1]\n";
my $ir =($cir[1] + $cur[1] + $cdr[1] + $crr[1]);
print "The insert rate is $ir\n";
} 
}
}
}
$ci->finish();
$cu->finish();
$cd->finish();
$cr->finish();

##</code><code>##

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                                     |
| character_sets_dir              | /usr/share/mysql/charsets/               |
| collation_connection            | latin1_swedish_ci                        |
| collation_database              | latin1_swedish_ci                        |
| collation_server                | latin1_swedish_ci                        |
| concurrent_insert               | ON                                       |
| connect_timeout                 | 5                                        |
| datadir                         | /var/lib/mysql/                          |
| date_format                     | %Y-%m-%d                                 |
| datetime_format                 | %Y-%m-%d %H:%i:%s                        |
| default_week_format             | 0                                        |
| delay_key_write                 | ON                                       |
| delayed_insert_limit            | 100                                      |
| delayed_insert_timeout          | 300                                      |
| delayed_queue_size              | 1000                                     |
| expire_logs_days                | 0                                        |