mysql 慢查询统计分析脚本

#!/usr/bin/perl

use List::Util qw(first);
use Time::Local;

$minutes = @ARGV ? shift @ARGV : 2;

open MYSQL_CONF, "</etc/my.cnf" or die "cant find my.conf";

$_ = first { /^\s*log-slow-queries\s*=\s*([\w.\/]+)/ } <MYSQL_CONF>
                or die "cant find log-slow-qureis";
/=\s*([\w.\/]+)/;
my $slow_log = $1;

my $from_time = time() - $minutes * 60;

my $mtime = (stat("$slow_log"))[9];
print "$mtime vs from_time=$from_time\n";

if ($mtime < $from_time)
{
 print "no slow log in recent $minutes minutes\n";
 exit;
}


`tail -5000 $slow_log > /tmp/slow_log.txt`;

open SELECT_LOG, ">/tmp/.last.txt" or die "cant open last log txt";
open TMP_LOG, "</tmp/slow_log.txt" or die "open tmp log failed";

select SELECT_LOG;
my $new_log = 0;
while (<TMP_LOG>)
{
 if ($new_log) {
        print;
        next;
 }
 if (/^# Time: (\d\d)(\d\d)(\d\d) (\d\d):(\d\d):(\d\d)$/) {
        $time_s = timelocal($6, $5, $4, $3, $2-1, 2000+$1);
        if ($time_s >= $from_time) {
                $new_log = 1;
                print;
        }
 }
}

exit if $new_log == 0;

select STDOUT;

`/usr/local/mysql/bin/mysqldumpslow  -s t /tmp/.last.txt > /tmp/.sloww`;

open SELECT_LOG, "</tmp/.sloww" or die "cant open slow log";
undef$/;
$content = <SELECT_LOG>;
print $content;

&send_mail("mysql slow log monitor", $content);

sub send_mail {
        my ($subject, $message) = @_;
}

使用：

加入crontab 即可：

*/2 * * * * /usr/local/bin/mysql_slow_monitor.perl 2 > /tmp/mysql_slow_monitor.log 2>&1 &

 

转载：http://blog.chinaunix.net/uid-26443921-id-3219655.html