自动优化mysql myisam表的perl脚本

定期优化mysql myisam 表的脚本。

#!/usr/bin/perl -w
# www.jb200.com

use strict;
use DBI;

$|=1;

my $DB_HOST             =       'localhost';
my $DB_PORT             =       3306;
my $DB_LOGIN    =       'optuser';
my $DB_PASSWD   =       'optpwd';
my @DB_Name_all=('crms','asterisk');
my @MyisamTable=();
foreach my $DB_DATABASE (@DB_Name_all){
  my $dsn = "dbi:mysql:$DB_DATABASE:$DB_HOST:$DB_PORT";
  my $dbiconnect = DBI->connect($dsn, $DB_LOGIN, $DB_PASSWD);
  my $sql="SHOW TABLE status";
  my $sth=$dbiconnect->prepare($sql);
  $sth->execute();
  my $rslt_rows=$sth->rows;
  if($rslt_rows>0)
   {
    while (my ($table_name,$table_engine)=$sth->fetchrow_array())
        {
         if($table_engine eq 'MyISAM')
           {
            my $sql_opt="OPTIMIZE NO_WRITE_TO_BINLOG TABLE $table_name";
            my $sth_opt=$dbiconnect->prepare($sql_opt);
             $sth_opt->execute;
             my($TableName,$Op,$Msg_type,$Msg_text) =$sth_opt->fetchrow_array();
             my $log=$TableName." ".$Op." ".$Msg_type." ".$Msg_text;
             &msgLog($log);
           }

        }
   }
}

$dbiconnect->disconnect;

sub msgLog() {
  my ($msg) = @_;
  open(LOG, ">>/var/log/myIsamoptimize.log");
  print LOG scalar localtime();
  print LOG " ",$msg,"n";
  close(LOG);
}