定期检查mysql中所有myisam表状态的perl脚本

发布时间:2019-10-03编辑:脚本学堂
perl实现的一段代码,用于自动定期检查mysql数据库中的所有myisam表的状态,有需要的朋友,可以参考下。
说明:myisam表易损坏,建议定期检查,以下脚本可以轻松实现自动定期检查,省了不少人力哦。
#!/usr/bin/perl -w
# www.jb200.com
use strict;
use DBI;

$|=1;

my $DB_HOST             =       '127.0.0.1';
my $DB_PORT             =       3306;
my $DB_LOGIN    =       'checkuser';
my $DB_PASSWD   =       'checkpwd';
my @DB_Name_all=('callcenter','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_check="CHECK TABLE $table_name";
            my $sth_check=$dbiconnect->prepare($sql_check);
            $sth_check->execute;
            my($TableName,$Op,$Msg_type,$Msg_text) =$sth_check->fetchrow_array();
            my $log=$TableName." ".$Op." ".$Msg_type." ".$Msg_text;
            &msgLog($log);
           }

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