percona-toolkit被称为MySQL DBA的“瑞士***”，其强大性不言而喻。而其中的慢查询分析工具“pt-query-digest”相比官方的“mysqldumpslow”提供了很多额外的属性，例如灵活的过滤器，基于库和表的分析排序等等。

  今天分享一例基于“pt-query-digest”和邮件自动追踪MySQL慢查询日志的小脚本，同时避免对同类型的sql语句重复提示。

  直接上脚本(只要安装了percona-toolkit，该脚本可以说是傻瓜式的，当然还是看看官方文档深入理解一下最好啦)

  首先，我们在test库（工具默认会创建percona_schema库并在其下建表，这里我们在脚本里指定test库）下创建query_history表：

这里我没让工具自动建表是因为我想在query_history表里记录‘客户host’和‘当前database’，默认表里没有这两项记录，其次，工具自动建表有好多基本用不上的列

CREATE TABLE `test`.`query_history` (

  `checksum` bigint(20) unsigned NOT NULL,

  `sample` text NOT NULL,

  `host_min` varchar(50) not null DEFAULT '',

  `db_min` varchar(30) not null DEFAULT '',

  `ts_min` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `ts_max` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `ts_cnt` float DEFAULT NULL,

  `Query_time_sum` float DEFAULT NULL,

  `Query_time_min` float DEFAULT NULL,

  `Query_time_max` float DEFAULT NULL,

  `Query_time_pct_95` float DEFAULT NULL,

  `Query_time_stddev` float DEFAULT NULL,

  `Query_time_median` float DEFAULT NULL,

  `Lock_time_sum` float DEFAULT NULL,

  `Lock_time_min` float DEFAULT NULL,

  `Lock_time_max` float DEFAULT NULL,

  `Lock_time_pct_95` float DEFAULT NULL,

  `Lock_time_stddev` float DEFAULT NULL,

  `Lock_time_median` float DEFAULT NULL,

  `Rows_sent_sum` float DEFAULT NULL,

  `Rows_sent_min` float DEFAULT NULL,

  `Rows_sent_max` float DEFAULT NULL,

  `Rows_sent_pct_95` float DEFAULT NULL,

  `Rows_sent_stddev` float DEFAULT NULL,

  `Rows_sent_median` float DEFAULT NULL,

  `Rows_examined_sum` float DEFAULT NULL,

  `Rows_examined_min` float DEFAULT NULL,

  `Rows_examined_max` float DEFAULT NULL,

  `Rows_examined_pct_95` float DEFAULT NULL,

  `Rows_examined_stddev` float DEFAULT NULL,

  `Rows_examined_median` float DEFAULT NULL,

   PRIMARY KEY (`checksum`,`ts_min`,`ts_max`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

接下来看脚本

#!/bin/sh

date_ago=`date +%Y-%m-%d\ %H:%M:%S --date='2 hours ago'`    #获取2小时之前的时间点，因为我的crontab里面每2小时执行一遍该脚本  

alias mysql_con='mysql -uuser -ppwd'

datebase=test

#以下两行操作会在test库里创建query_review表，同时将分析结果存储到两张表里

pt-query-digest --history u=user,p=pwd,D=$datebase /mysqldata/dev-env-slow.log --no-report

pt-query-digest --review u=user,p=pwd,D=$datebase /mysqldata/dev-env-slow.log --no-report

rm -f /tmp/slow_query_inc 2> /dev/null    #清空存放上次结果的文件

#从query_review表查询出'first_seen'时间在2小时以内的'checksum',并组织成  checksum1,checksum2的形式，以便后面放在sql语句的in条件里

#where条件保证只查询第一次出现的慢查询信息，避免重复提醒

check_sum=`mysql_con -N -s -e "SELECT checksum FROM percona_schema.query_review where first_seen > '$date_ago'" 2>/dev/null|awk '{printf("%s,",$0)}'|sed 's/,$//'`

#根据上面查出的checksum值从query_history表查出慢查询相关信息

if [ ! -z $check_sum ];then

mysql_con -e "

SELECT

        checksum,

    sample AS Statment,

    host_min AS Host,

    db_min AS DB,

    ts_max AS Time,

    ts_cnt as Counts,

    query_time_pct_95 AS Query_Time,

    Rows_sent_pct_95 AS Rows_Sent,

    Rows_examined_pct_95 AS Rows_Examined

FROM

    $datebase.query_history

WHERE

    checksum in ($check_sum)                               

    AND 

    ts_max in (select max(ts_max) from $datebase.query_history where checksum in ($check_sum) group by checksum)\G

" 2> /dev/null >> /tmp/slow_query_inc

if [ -s /tmp/slow_query_inc ];then    #/tmp/slow_query_inc文件有内容才发邮件

    mail -s 'slow_log in last 2 hours----from Dev' [email protected] < /tmp/slow_query_inc

fi

fi

然后在crontab里设置每2小时运行该脚本，有新的慢查询出现的话，就会收到邮件啦，如下图：

wKioL1bOqyXwxrvCAAAus1mMnBQ503.png

另有一点：sql监控这个环节最好从开发环境做起，将慢sql扼杀在萌芽阶段^_^

续：以上虽然能发邮件提醒了，但是都在邮件里终究还是略显零碎，而且不易跟踪管理。我们项目管理采用的是redmine，于是翻看redmine的API文档，最终实现了将开发环境、生产环境的慢查询都自动记录在redmine里，方便追踪统计。

  这部分只是想跟大家分享这个思路，大家所采用的项目管理软件不尽相同，所以我这部分的实现过程就不详细展开了。大致就是在上面脚本的基础上再加修改，按照redmine API的规范生成相应的xml文件，然后通过 curl调用redmine接口即可。

©著作权归作者所有：来自51CTO博客作者kai404的原创作品，如需转载，请注明出处，否则将追究法律责任

mysql慢查询mysql 慢查询追踪pt_query_digest数据库类