perl 监控mysql 事务和锁
2024-10-19 04:23:19
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 = <<CONTENT;
$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');
};
};
最新文章
- win7 去快捷箭头
- 不谈业务运维的IT主管早晚被淘汰 这里是10条干货
- JavaWeb前端基础复习笔记系列 二
- 6/14 Sprint2 看板和燃尽图
- 总结 output 用法
- nginx error_page 404 用 php header 无法跳转
- c#泛型方法重载
- 【微信公众号】将微信公众号消息里的FromUserName即OpenID转成UnionID
- leetcode_最长公共前缀
- vs2010中iostream.h出错
- tf.train.ExponentialMovingAverage
- strlen出错
- 高可用之KeepAlived(2):keepalived+lvs
- 渗透测试环境DVWA搭建
- EF Core 相关的千倍性能之差: AutoMapper ProjectTo VS Mapster ProjectToType
- Sketch 和 PS中的设计图如何实现“自动切图”?
- Docker 图形化页面管理工具使用
- Android开发-新建线程崩溃
- OpenStack之日志
- msyql int(x) 中的x