MySQL 慢查询日志过滤和告警

按时间段过滤截取MySQL慢查询日志内容，上传截取内容到腾讯云COS，并通发送告警到钉钉群。开发和运维人员可以通过告警内容中的COS链接地址查看慢查询详细信息。

慢查询原日志内容

# Time: 2022-10-20T03:21:02.257596Z
# User@Host: appuser_prod_rw[appuser_prod_rw] @  [172.22.0.111]  Id: 7691255
# Query_time: 1.159538  Lock_time: 0.000079 Rows_sent: 0  Rows_examined: 1804396
SET timestamp=1666236062;
SELECT  xxxxxxxxxxxxxxxxxxxxxxx  FROM xxxxxx
 
 WHERE (xxx = 4317404);
# Time: 2022-10-20T03:21:03.933240Z
# User@Host: appuser_prod_rw[appuser_prod_rw] @  [172.22.0.111]  Id: 7691437
# Query_time: 2.058303  Lock_time: 0.000111 Rows_sent: 13  Rows_examined: 1620175
SET timestamp=1666236063;
SELECT  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  FROM xxxxxx 
 WHERE  xxx=0

AND (sync_status IN (0,1)) LIMIT 100;
# Time: 2022-10-20T03:21:05.534173Z
# User@Host: appuser_prod_rw[appuser_prod_rw] @  [172.22.0.142]  Id: 7691167
# Query_time: 1.554506  Lock_time: 0.000065 Rows_sent: 0  Rows_examined: 1657680
use xxx;
SET timestamp=1666236065;
SELECT * FROM `xxxxxx` WHERE xxx = xxx AND xxx = 'xxx' AND xxx in  (   'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  ,  'xxxxxxxxxxxxxxxxx'  );
# Time: 2022-10-20T03:21:26.819932Z
# User@Host: appuser_prod_rw[appuser_prod_rw] @  [172.22.0.111]  Id: 7691255
# Query_time: 1.149886  Lock_time: 0.000068 Rows_sent: 0  Rows_examined: 1804397
use xxx;
SET timestamp=1666236086;
SELECT  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  FROM xxxxxx 
 
 WHERE (xxx 4317405);

慢SQL截取

按时间段截取慢查询日志

这里，比如我们需要截取 2022/10/20 11:10:00 ~ 2022/10/20 11:20:59 时间段内慢查询日志。

由于慢查询日志中时间是UTC格式，所以截取时需要 -8 小时。

方式一：

awk -F'[TZ]' 'BEGIN{RS="# Time: 2022-10-20T"} $1>"03:10:00" && $1<"03:20:59"{print RS,$0}' mysql-slowlog.txt | sed '/# Time:/i######################################################################################' > new-slowlog.txt

方式二：

awk -F'[TZ]' '/^# Time: 2022-10-20/{slow=( $3>"03:05:00" && $3 <"03:15:59")}slow' mysql-slowlog.txt | sed '/# Time:/i######################################################################################' > new-slowlog.txt

awk -F'[TZ]' '/^# Time: 2022-10-20/{slow=( $(NF-1)>"03:05:00" && $(NF-1) <"03:15:59")}slow' mysql-slowlog.txt | sed '/# Time:/i######################################################################################' > new-slowlog.txt

截取效果：

######################################################################################
# Time: 2022-10-20 T03:10:28.856114Z
# User@Host: appuser_prod_rw[appuser_prod_rw] @  [172.22.0.157]  Id: 7690900
# Query_time: 1.137763  Lock_time: 0.000117 Rows_sent: 0  Rows_examined: 1804294
SET timestamp=1666235428;
SELECT  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  FROM xxxxxx 
 
 WHERE (xxx = 4317302);

######################################################################################
# Time: 2022-10-20 T03:10:30.406647Z
# User@Host: appuser_prod_rw[appuser_prod_rw] @  [172.22.0.157]  Id: 7690900
# Query_time: 1.140221  Lock_time: 0.000057 Rows_sent: 0  Rows_examined: 1804295
SET timestamp=1666235430;
SELECT  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  FROM xxxxxx 
 
 WHERE (xxx = 4317303);

######################################################################################
# Time: 2022-10-20 T03:10:52.206813Z
# User@Host: appuser_prod_rw[appuser_prod_rw] @  [172.22.0.157]  Id: 7690900
# Query_time: 1.136964  Lock_time: 0.000069 Rows_sent: 0  Rows_examined: 1804296
SET timestamp=1666235452;
SELECT  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  FROM xxxxxx 
 
 WHERE (xxx = 4317304);

######################################################################################
# Time: 2022-10-20 T03:10:53.785344Z
# User@Host: appuser_prod_rw[appuser_prod_rw] @  [172.22.0.111]  Id: 7691021

获取时间慢查询最长时间

awk '/^# Query_time:/{print $3}' new-slowlog.txt | sort | uniq -c | sort -nk2 | tail -1 | awk '{print $NF}'

慢查询日志告警脚本

告警脚本内容

脚本名：monitor-mysql-slowlog.sh

#!/bin/bash
# Author: wanhebin
# UpdateTime: 2022/10/26
# Description: MySQL慢查询监控告警脚本

# 钉钉告警函数
FUN_DINGDING() {
        local DINGDING_TEXT="**MySQL 数据库慢查询告警** \n\n --- \n\n **告警级别:** WARNING  \n\n **运行环境:** XXXX生产环境 \n\n **事件信息:** \n > - 告警内容：MySQL 数据库慢查询次数(${QueryThreshold}s以上) > $1 次 \n > - 最大耗时: $2 \n > - 详细信息: [慢查询语句]($COS_URL) \n\n **事件标签:** \n > - HOSTNAME: $HOSTNAME \n > - Instance: $INSTANCEIP \n\n ###### $NOW_DATE | FROM: $HOST_IP"
        ## 发送告警到钉钉告警群
        curl 'https://oapi.dingtalk.com/robot/send?access_token=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' -H 'Content-Type: application/json' -d '{"msgtype":"markdown","markdown":{"title":"报警","text":'"'$DINGDING_TEXT'"' },"at":{"atMobiles":[],"isAtAll":true}}'
}

# 脚本相关变量
INSTANCEIP="192.168.10.157:3306"
HOST_IP=`/sbin/ifconfig eth0 | awk 'NR==2{print $2}'`
NOW_DATE=`date +"%Y-%m-%dT%H:%M:%S"`
NOW_TIME_YMDHMS=`date +"%Y%m%d%H%M%S"`
NOW_TIME_YMD=`date -u +%Y-%m-%d`
NOW_TIME_HM=`date -u +%H:%M`
PRE_TIME_HM=`date -u +%H:%M -d "+30 minutes ago"`

SlowLog="/data/server/mysql/data/mysql-slow.log"
SCRIPTS_DIR="$( cd "$( dirname "$0" )" && pwd )"
Tmp_Slowlog=$SCRIPTS_DIR/tmp/mysql-slowlogs-tmp-${NOW_TIME_YMDHMS}.log
COS_NAME="prod-monitor-xxxxxxxxxx"
COS_DIR="/prod-monitor/mysql/slow-query"
COS_URL="https://${COS_NAME}.cos.ap-guangzhou.myqcloud.com${COS_DIR}/${Tmp_Slowlog##*/}"
QueryThreshold=1

[ -d $SCRIPTS_DIR/tmp ] || mkdir -p $SCRIPTS_DIR/tmp

# 按时间截取慢查询日志
awk -F'[TZ]' -v NOW_TIME_YMD="$NOW_TIME_YMD" -v NOW_TIME_HM="${NOW_TIME_HM}" -v PRE_TIME_HM="$PRE_TIME_HM" 'BEGIN{RS="# Time: "NOW_TIME_YMD"T"} $1 > PRE_TIME_HM && $1 < NOW_TIME_HM {print RS,$0}' $SlowLog | sed '/# Time: /i######################################################################################'> $Tmp_Slowlog

# 统计所有慢SQL中最长耗时，并统计慢查询数量
Slow_Max_Time=`awk '/^# Query_time:/{print $3}' $Tmp_Slowlog | sort | uniq -c | sort -nk2 | tail -1 | awk '{print $NF}'`
Slow_Num=`awk '/^# Query_time:/{print $3}' $Tmp_Slowlog | wc -l`

# 触发钉钉报警
if [ $Slow_Num -ge 10 ]; then
        /usr/local/bin/coscmd -b $COS_NAME upload -r -s $Tmp_Slowlog $COS_DIR/
        FUN_DINGDING $Slow_Num $Slow_Max_Time
else
        exit 0
fi