喜欢的朋友,拿走不谢,如果脚本有问题,请在下方留言处留下问题描述,以及具体问题截图和log可以发送小编邮箱:1071235258@qq.com
作者根据PT工具的逻辑思维,用perl写的一款自动kill慢查询脚本,并发送邮件报警并告诉慢查询的是辣条SQL.,例子简单粗暴很实用!
来源:http://hcymysql.blog.51cto.com/ 作者:DBA-贺春旸
#!/usr/bin/perl
############################################
=pod
Percona PT-kill精简版
多增加发送kill掉后的慢SQL邮件报警功能
By http://hcymysql.blog.51cto.com/
=cut
############################################
use DBI;
use Net::SMTP;
use Authen::SASL;
use MIME::Lite;
use Term::ANSIColor;
use Getopt::Long qw(GetOptions :config no_ignore_case);
use Proc::Daemon;
my ($username,$passwd,$host,$port,$busytime,$interval,$kill,$info,$muser,$daemon);
sub USAGE{
my $usage=<<“USAGE”;
Usage:
Options:
-u username
-p password
-h host ip
-P port
-B busytime time seconds
-I interval time seconds
–kill 如果想杀掉慢查询,后面设置为1。默认不杀只发报警邮件。
–match-info 匹配杀掉SELECT|INSERT|UPDATE语句
–match-user 匹配杀掉的用户
–daemon 开启后台守护进程
–help Help
Example :
shell> perl kill_slowsql_sendmail.pl -u admin -p 123456 -h 192.168.17.128 -P 3306 -B 10 -I 10 –kill 1 –match-info select –match-user admin –daemon 1
USAGE
print $usage;
exit;
}
GetOptions (“u=s” => $username,
“p=s” => $passwd,
“host|h=s” => $host,
“port|P=i” => $port,
“interval|I=i” => $interval,
“busytime|B=i” => $busytime,
“kill=i” => $kill,
“match-info=s” => $info,
“match-user=s” => $muser,
“daemon=i” => $daemon,
“help|?” =>&USAGE
) or &USAGE;
&USAGE unless ($username && $password && $host && $port);
####################################
if($daemon == 0){
slowsql();
exit;
}
else{
Proc::Daemon::Init;
my $continue = 1;
$SIG{TERM} = sub { $daemon = 0 };
while ($continue) {
slowsql();
sleep($interval);
next;
}
}
sub slowsql{
my $dsn = “DBI:mysql:database=test;host=$host:$port”;
my $user = “$username”;
my $password = “$passwd”;
my ($dbh,$sth,$sth_kill,@ary);
if ($info ne “”){
$dbh = DBI->connect($dsn,$user,$password);
$sth = $dbh->prepare(“SELECT ID,USER,HOST,DB,TIME,COMMAND,STATE,INFO FROM information_schema.PROCESSLIST WHERE TIME >= ‘$busytime’ AND INFO REGEXP ‘$info’;”);
$sth->execute();
}
elsif ($muser ne “”){
$dbh = DBI->connect($dsn,$user,$password);
$sth = $dbh->prepare(“SELECT ID,USER,HOST,DB,TIME,COMMAND,STATE,INFO FROM information_schema.PROCESSLIST WHERE TIME >= ‘$busytime’ AND USER REGEXP ‘$muser’;”);
$sth->execute();
}
elsif ($muser ne “” && $info ne “”){
$dbh = DBI->connect($dsn,$user,$password);
$sth = $dbh->prepare(“SELECT ID,USER,HOST,DB,TIME,COMMAND,STATE,INFO FROM information_schema.PROCESSLIST WHERE TIME >= ‘$busytime’ AND (INFO REGEXP ‘$info’ OR USER REGEXP ‘$muser’);”);
$sth->execute();
}
else{
$dbh = DBI->connect($dsn,$user,$password);
$sth = $dbh->prepare(“SELECT ID,USER,HOST,DB,TIME,COMMAND,STATE,INFO FROM information_schema.PROCESSLIST WHERE TIME >= ‘$busytime’;”);
$sth->execute();
}
$status = 0;
while(@ary = $sth->fetchrow_array()){
if($ary[0] eq “NULL” || $ary[0] eq “”){
last;}
else{
open(DATA,”+>>kill.txt”) || die “kill.txt 文件无法打开, $!”;
$result = join(“t”,@ary),”n”;
$st=localtime();
syswrite(DATA,”$stt$resultn”);
close(DATA);
print color(‘red’);
print “自动杀死执行时间超过$busytime秒的慢SQLn”;
if($kill == 1){
$sth_kill = $dbh->prepare(“KILL QUERY $ary[0];”); # 默认只杀连接中的慢SQL,保留会话连接,如果想把连接也杀掉,去掉QUERY
$sth_kill->execute();
$sth_kill->finish;
}
$status +=1;
}
}
if($status == 0){
print color(‘blue’);
print “没有慢查询n”;
}
else{
mail(); # 调用发邮件函数
}
$sth->finish;
$dbh->disconnect;
}
#定义发邮件函数
sub mail{
my $ehost=”smtp.126.com”;
my $from=’zhangtongle@126.com’;
my @to=(‘zhangtongle@126.com’,’1071235258@qq.com’);
my $subject1=”DB-$host-$ary[3] kill slowsql alert!”;
my $ctime=localtime();
my $subject=join(‘-‘,$subject1,$ctime);
my $text=`cat kill.txt`;
#my $attach = “kill.txt”;
my $user=’zhangtongle@126.com’;
my $pwd=”xxxxxxxxxxxx”;
#my $smtp=Net::SMTP->new($ehost,Hello=>’localhost’,Timeout=>120,Debug=>1);
my $smtp=Net::SMTP->new($ehost,Hello=>’localhost’,Timeout=>120);
$smtp->auth($user,$pwd);
my $msg;
my $str;
foreach my $t_mail(@to){
$msg=MIME::Lite->new(
From=>$from,
To=>$t_mail,
Cc=>’chunyang_he@139.com’,
Subject=>$subject,
Data=>$text
);
#$msg->attach(
#Type=>’auto’,
#Path=>$attach,
#Filename=>’kill.txt’,
#Disposition=>’attachment’
#);
$str=$msg->as_string() or die “$!”;
$smtp->mail($from);
$smtp->to($t_mail);
$smtp->data();
$smtp->datasend(“$str”);
$smtp->dataend();
}
$smtp->quit();
}
未经允许不得转载:同乐学堂 » Day42-MySQL自动kill慢查询并发送报警邮件
Great post however , I was wanting to know if you could
write a litte more on this topic? I’d be very thankful if you could elaborate a little bit more.
Cheers!
Just beneath, are numerous absolutely not associated websites to ours, on the other hand, they are surely really worth going over.
The information talked about within the post are a few of the ideal out there.
One of our visitors not long ago encouraged the following website.