perl 监控mysql 事务和锁

use DBI;use Net::SMTP;   use HTTP::Date qw(time2iso str2time time2iso time2isoz);  # mail_user should be [e-mail] send_mail{   if (@_ != 2){print "please input message and mailto";exit 1};    my $message= shift;my $CurrTime = time2iso(time());    my $to_address  = shift;     my $mail_user   = '[e-mail]';    my $mail_pwd    = 'xx';    my $mail_server = 'smtp.exmail.qq.com';    my $from    = "From: $mail_user\n";    my $subject = "Subject: zjcap info\n";    my $info = "$message";     my $message = <<CONTENT;      $infoCONTENT    my $smtp = Net::SMTP->new($mail_server);    $smtp->auth($mail_user, $mail_pwd) || die "Auth Error! $!";    $smtp->mail($mail_user);    $smtp->to($to_address);    $smtp->data();             # begin the data    $smtp->datasend($from);    # set user    $smtp->datasend($subject); # set subject    $smtp->datasend("\n\n");    $smtp->datasend("$message\n"); # set content    $smtp->dataend();    $smtp->quit();};if ( $#ARGV != 1 ){    print "input your root password and ip address"."\n"; exit(-1);  };my $message='information_schema';my $ip="$ARGV[1]";my $user="root";my $passwd="$ARGV[0]";my $dbh = DBI->connect("dbi:mysql:database=$message;host=$ip;port=3306",$user,$passwd) or die "can't connect to database ". DBI-errstr;@arr2=();##防止utf-8中文乱码$dbh->do("SET NAMES utf8");my $hostSql = qq{SELECT     NOW(),  (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,    b.id,    b.user,    b.host,    b.dbFROM    information_schema.innodb_trx a        INNER JOIN    information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id};my ($a1, $a2, $a3,$a4,$a5,$a6,$a7,$a8,$a9);my $selStmt = $dbh->prepare($hostSql);  $selStmt->execute();  $selStmt->bind_columns(undef, \$a1, \$a2, \$a3,\$a4,\$a5,\$a6);  print "$a1,$a2,$a3,$a4,$a5,$a6\n";   while( $selStmt->fetch() )        {        if ($a2 >= 20){      print "$a1,$a2,$a3,$a4,$a5,$a6\n";          print " mysq\[$ip\]  processid\[$a3\] $a4\@$a5 in db\[$a6\] hold  transaction time $a2 "."\n";       send_mail(" mysq\[$ip\]  processid\[$a3\] $a4\@$a5 in db\[$a6\] hold  transaction time $a2 ",'[e-mail]');      };      };my $hostSql = qq{SELECT     r.trx_id waiting_trx_id,    r.trx_mysql_thread_id waiting_thread,    r.trx_query waiting_query,    b.trx_id blocking_trx_id,    b.trx_mysql_thread_id blocking_thread,    b.trx_query blocking_queryFROM    information_schema.innodb_lock_waits w        INNER JOIN    information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id        INNER JOIN    information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id};my ($a1, $a2, $a3,$a4,$a5,$a6,$a7,$a8,$a9);my $selStmt = $dbh->prepare($hostSql);  $selStmt->execute();  $selStmt->bind_columns(undef, \$a1, \$a2, \$a3,\$a4,\$a5,\$a6);  while( $selStmt->fetch() )        {        if ($a1&&$a2&&$a3&&$a4&&$a5){      print "$a1,$a2,$a3,$a4,$a5,$a6\n";          print "  blocking_thread\[$5\] blocking waiting_thread\[$a2\]'s $a3"."\n";       send_mail("  blocking_thread\[$a5\] blocking waiting_thread\[$a2\]'s $a3" ,'[e-mail]');      };      };