MySQL 每天自行统计慢日志 脚本

前言：

 MySQL中提供了一个慢查询的日志记录功能 (有点 类似 oracle的 v$session_longops  ) ，可以把查询SQL语句时间大于多少秒的语句写入慢查询日志，日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在；

 可以通过mysqldumpslow、mysqlsla、pt-query-digest ..等工具进行分析慢日志,  mysqldumpslow、mysqlsla文本查看，而 pt-query-digest 工具可导入数据库，方便查看；

 可参考之前博客  MySQL slow query [慢查询] 资料整理   ；

本文主要通过pt -query-digest工具将所需内容导入到数据库(sql按照发起sql者区分),最终将各个用户下所有信息导入到一张总表.再进行分析..

   

脚本内容：

#!/bin/bash

# Descirption:mysql slow_log Statistics
# DATE: 2015年7月1日
# Author: 毛海晴 

#脚本目的：
#每天将慢查询sql及信息导入到mysql数据库表作分析（需要信息：执行sql用户{database}、sql文本、执行次数、总执行时间、平均时间、查询行数、返回行数）

#脚本编写思路

#pt-query-digest未发现直接按照database过滤自带参数 （问题 当提供慢sql时需要知道sql在哪个库执行的 的情况该怎么办）  
#不过可以考虑使用pt-query-digest自带用户限制参数--filter '($event->{user} || "") =~ m/^用户/i' 可以简单用用户充当database再结合mysql.user再分析')
#通过pt-query-digest user 限制后导入数据库，但导入后并没有某字段记录 database_name 信息

#所以考虑循环限制并导入database+date命名数据表后手动加database_name字段，默认值为 相应sql执行的用户名
#最终会生成多表（例如：slow_log_his_2015_07_01_retail_uc、slow_log_his_2015_07_01_retail_pms...）第一个字段是 database_name 和相应的执行sql用户名

#表整理：将多表追加到一张slow_log_his_2015_07_01表，再通过mysql sql 取出我们需要字段。

# *********************************************步骤********************************************
## 变量设置 （注释：变量定义=等号前后不能出现空格，否则会被系统理解成命令）
#登陆名
user='root' 
#登陆密码 

slow_root='root'

#日期设置， 生成慢查询表命令YYYY_MM_DD  ' train_public '后缀名避免多库导入同一远程数据库表名重复，数据覆盖问题。

pt_table=slow_log_his_`date +%Y_%m_%d`_train_public 

#IP获取

# ip=`ifconfig |grep "Bcast"|awk -F ':' '{print $2}'|awk -F ' ' '{print $1}'`
echo '------------- '`date +%Y-%m-%d' '%H:%M:%S`' 开始 "'`hostname`'"(IP...) 慢查询统计 -------------'

slow_name=`mysql -u$slow_root -p$slow_password -e "show variables like 'slow_query_log_file'"|grep log|awk '{print $2}'`

#--> 一、整合表'slow_log_his_'$date配置配置

# 表数据要保证为空，避免“ERROR 1062 (23000) at line 1: Duplicate entry 'XX' for key 'PRIMARY'”
#--》1 本次伪例限制--filter '($event->{user} || "") =~ m/^mhq/i'导入空表 mhq用户并不存在
#--》或2 方案，在insert之前先 delete from 表；  
#（1）删除已有并创建
mysql -h${slow_host} -u${slow_root} -p${slow_password} -D${slow_dbname} -B -N  -e " drop table if exists ${pt_table};"

pt-query-digest  --user=${slow_root} --password=${slow_password} --no-report  --history h=${slow_host},D=${slow_dbname},t=${pt_table} --create-history-table --filter '($event->{user} || "") =~ m/^mhq/i' $slow_name.`date +%Y%m%d`

#（2）加 database_name  字段，保证各个表结构相同

mysql  -h${slow_host} -u${slow_root} -p${slow_password} -D${slow_dbname} -B -N  -e  " alter table  ${pt_table}  add  COLUMN  database_name  varchar(255) NOT NULL FIRST ;  "   

#--> 二、查找 database_name ，开始循环导入database_name+date命名 数据库表

#（1）查找所有 database_name  
for username in `mysql -uroot -hlocalhost -p123456  -N -B -e "select distinct user from mysql.user;"` 
do    
#（2）使用pt工具将通过--filter 过滤 database_name 循环导入 database_name +date命名 数据库表(存在先删除..)

mysql -h${slow_host} -u${slow_root} -p${slow_password} -D${slow_dbname} -B -N  -e "drop table if exists ${pt_table}_${f};"

# 由于后续sql过滤只用到history表，所以未加“--review   h=$host,D=$dbname,t='slow_log_'$date'_'$ username    --create-review-table”参数

#--> 三、处理循环导入的 database_name +date命名表 

mysql -h${slow_host} -u${slow_root} -p${slow_password} -D${slow_dbname} -B -N  -e "

#（1）向每张表加字段，默认值为 database_name ，用来查询使用

alter table ${pt_table}_${f} add  COLUMN database_name varchar(255) NOT NULL default '$username'  FIRST ;

#（2）循环将 database_name +date命名表数据读到'slow_log_his_'$date表.

insert into ${pt_table} select * from  ${pt_table}_${f};
#（3）清理 database_name +date命名表

drop table ${pt_table}_${f};
" 
## -e " 冒号需要写在同一行，否则会提示“mysql: option '-e' requires an argument”

#结束循环
done 

echo '------------- '`date +%Y-%m-%d' '%H:%M:%S`' 结束 "'`hostname`'"(IP...) 慢查询统计 -------------'

# pt-query-digest用到的参数注释：
# --user  mysql用户名
#--password  mysql用户密码
#--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同,则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化
# --no-report  不打印分析log
# --host  mysql服务器地址
# h  要导入mysql服务器地址
# D  要导入到的database
# t  要导入到的table
#--create-history-table     当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
# --filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
#--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
# 其他参数
#--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
#--until 截止时间，配合—since可以分析一段时间内的慢查询。  

---

 

# 查询SQL 

---

#未使用表自带字段“*_*_pct_95”平均值，是因为 sum / ts_cnt  并不相等于*_pct_95字段
SELECT
     his.*
FROM
     ( SELECT
               database_name AS "sql发起者",
               sample sql_text,
               ts_cnt AS "执行次数",
               round(Query_time_sum, 2) AS "执行时间(总)",
               round(Query_time_sum / ts_cnt, 2) AS "执行时间(每)",
               round(Lock_time_sum, 3) AS "锁时间(总)",
               round(Lock_time_sum / ts_cnt, 3) AS "锁时间 (每)",
               round(Rows_examined_min / ts_cnt) AS "参加运算的记录平均行数",
               round(Rows_sent_sum / ts_cnt) AS "平均返回记录数"
          FROM
               slow_log_his_2015_07_03_train_public t
          #WHERE database_name LIKE 'retail_%' #database_name not LIKE 'retail_%' 
          ORDER BY
               4 DESC
     ) his
LIMIT 10;

显示结果:


注意:

1 、需要在慢启动日志切割后执行。

2 、 /*!40001 SQL_NO_CACHE */   表示 :   不把查询结果存储在查询缓存中   ( 类似 oracle   的 direct path read) ...本环境是由mysqldump引起 /*!40001 SQL_NO_CACHE */

3 、 pt-query-   digest 小瑕疵：

选中部分 SQL   SELECT /*!40001 SQL_NO_CACHE */ * FROM `item_sku`   执行次数   =9   ；

而实际上是 select * from   三个分别三次不同的表   ... (review 和 history   表都记录到一条数据，执行次数为   9)

       而 mysqldumpslow   工具结果如下：

     Count: 3  Time=9.89s (29s)  Lock=0.00s (0s)  Rows=2453000.0 (7359000), root[root]@localhost

       SELECT /*!N SQL_NO_CACHE */ * FROM `item_sku`

     Count: 3  Time=6.68s (20s)  Lock=0.00s (0s)  Rows=1545142.0 (4635426), root[root]@localhost

       SELECT /*!N SQL_NO_CACHE */ * FROM `item_extension`

     Count: 3  Time=4.01s (12s)  Lock=0.00s (0s)  Rows=328886.0 (986658), root[root]@localhost

       SELECT /*!N SQL_NO_CACHE */ * FROM `item`

MYSQL慢日志分析参数的涵义  

---

---