快乐学习
前程无忧、中华英才非你莫属!

Day42-MySQL自动kill慢查询并发送报警邮件

喜欢的朋友,拿走不谢,如果脚本有问题,请在下方留言处留下问题描述,以及具体问题截图和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();
}
打赏
赞(0) 打赏
未经允许不得转载:同乐学堂 » Day42-MySQL自动kill慢查询并发送报警邮件

特别的技术,给特别的你!

联系QQ:1071235258QQ群:710045715

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

error: Sorry,暂时内容不可复制!