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

#!/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();

}