aalneyperl has asked for the wisdom of the Perl Monks concerning the following question:

Hi All, I was finally able to code Mysql Helath Check Script.All thanks to PERL MONKS..:). I get the ouput on my console, however now my requirement is, that i need to get that out put on to a browser in a formatted way.My apologies if i posted too much code.Ok i do not wish to recode the code to use cgi. I am unable to get the HTML::Template working with this code(that would also mean recode it again.) I know i sound nonsense but can anybody help me out in getting the output on to a browser with minimal change to the code. or help me understand how to use a formatting module or post a sample script or inline HTML. Somebody suggested me to do the following 
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 "<table><thead><tr>
                         <th>Slave_IO_State</th>
                         <th>Master_host</th>
                         <th>Slave_IO_Running</th>
                         <th>Slave_SQL_Running</th>
                     </tr>
             </thead>
      <tbody>
";
       # 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 "<tr><td>$val[0]</td>";
                       print "<td>$val[1]</td>";
                       print "<td>$val[10]</td>";
                       print "<td>$val[11]</td>";
                      #print "<td>$val[33]</td>";
                      print "</tr>\n"
}
print "</tbody></table>\n";

[download]
With the above code i directed the output to a html file like this.../<file name.pl> -H localhost -u mysql -p mysql > output.html and then i ran the file from the browser. This works but the code will be a mesh. I know you must be thinking "How to do want it?". I am really in a mesh....plz help.. Thanks... THIS IS MY SCRIPT 
#!/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 <host> -u <user> -p <passwor
+d> \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, $pas
+sword, {'RaiseError' => 1});
};

if ($@) {
    my $status = $@;
    print 'CRITICAL: Connect failed with reason ' . $status . "\n";
    exit 2;
}
print "<html>";
print "<head>";
print "<title> MYSQL HEALTH CHECK REPORT</title>";
print "</head>";
print "</html>\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[3
+3]\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. Th
+e FORCE option forces a repair in case of corruption. The BACKUP opti
+on causes a backup to be made so data can be restored from the old fi
+les 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_c
+ache"} != 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{"tab
+le_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 d
+efined!\n";
                    }

                      print "Table_cache_hit_rate=$table_cache_hit_rat
+e\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{'ta
+ble_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_hi
+t_rate%\n";
                print ",while $table_cache_fill% of your table cache i
+s in use You should probably increase your table_cache\n";           
+     }

        else
                {
                print "Current table_cache hit rate is $table_cache_hi
+t_rate%\n";
                print ",while $table_cache_fill% of your table cache i
+s 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{"C
+onnections"}) * 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 ca
+lculate your hit ratio by dividing the threads_created status variabl
+e by the Connections status variable:100 - ((Threads_created / Connec
+tions) * 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 threa
+ds were being created, thus creating a lot of unnecessary overhead.Th
+e cause of the problem was that 'thread_cache_size' was set to 0. Thr
+ead_cache_size determines how many threads MySQL will hold open in me
+mory to handle new connections.To determine what you should set 'thre
+ad_cache_size' to, pay close attention to the 'threads_created' statu
+s 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";
}

[download]

Replies are listed 'Best First'.
Re: Formmating Mysql Health check script output.
by Cubes (Pilgrim) on Dec 06, 2007 at 16:41 UTC
    It is not entirely clear to me what you are trying to accomplish. Do you want to run this script from your web browser -- that is, as a CGI program -- and see the output, or are you just looking for a better way to format HTML output that will be dumped into your output file to be viewed later in a web browser?

    If the former, you will need a way to provide the information to your CGI script that you're currently getting from the command line. The best way to do this is with the CGI.pm module.

    The CGI module can also help you with your output, but making it easy to print the headers and HTML that you need. Alternately, you could use a template system like HTML::Template or Template Toolkit.

    I know you said that you don't want to re-write your code, but sometimes a little re-writing is necessary to add functionality or get things working the way you want. Using a template system would also make future changes and maintenance to your code far easier and less error-prone.

    If you're really dead-set against rewriting anything, but you want to run this as a CGI program, you would need to just hard-code your command-line parameters (or use CGI.pm to get them from GET or POST parameters) and add a header output line -- minimally print "Content-type: text/html\n\n" -- somewhere before you start outputting HTML.

[reply]
[d/l]

Back to Seekers of Perl Wisdom