mysql 监控事务锁_perl 监控mysql 事务和锁

use DBI;

use Net::SMTP;

use HTTP::Date qw(time2iso str2time time2iso time2isoz);

# mail_user should be your_mail@163.com

sub 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 = 'yjzhao@podinns.com';

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 = <

$info

CONTENT

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.db

FROM

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 ",'yjzhao@podinns.com');

};

};

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_query

FROM

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" ,'yjzhao@podinns.com');

};

};