定时获取阿里云MySQL慢查询日志

项目背景：每天早上10点30分定时发送钉钉信息
软件版本：Centos 7.4， python 3.6，docker 19.03.12， docker-compose 1.25.5

创建 python 项目

安装依赖

slow_log.py

from aliyunsdkcore.client import AcsClient
from aliyunsdkcore.acs_exception.exceptions import ClientException
from aliyunsdkcore.acs_exception.exceptions import ServerException
from aliyunsdkrds.request.v20140815.DescribeSlowLogsRequest import DescribeSlowLogsRequest
import json
import openpyxl
from datetime import datetime, date, timedelta
import dingtalk

# 全局变量，保存返回符合要求的慢查询记录
total_record = []

# 全局变量，设置查询的日期
yesterday = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d")
dbtime = yesterday + 'Z'

# 阿里云 SDK 配置信息, 配置RAM，给予阿里云RDS只读权限
client = AcsClient('xxxxxxxxxxxx', 'xxxxxxxxxx', 'cn-shenzhen')

request = DescribeSlowLogsRequest()
request.set_accept_format('json')

# 慢查询日志统计的开始时间和结束时间，这里我们获取的前一天的数据
request.set_StartTime(dbtime)
request.set_EndTime(dbtime)


# 获取返回慢查询的记录数
def get_total_count(dbinstance):
    request.set_DBInstanceId(dbinstance)
    response = client.do_action_with_exception(request)
    result_json = json.loads(str(response, encoding='utf-8'))

    return result_json['TotalRecordCount']


# 获取慢查询记录，增加到全局变量：total_record 中
def get_record(dbinstance, pagenumber=1):
    request.set_DBInstanceId(dbinstance)
    request.set_PageNumber(pagenumber)
    response = client.do_action_with_exception(request)
    result_json = json.loads(str(response, encoding='utf-8'))

    for p in range(result_json['PageRecordCount']):
        # 判断的规则是 同一条SQL 的慢查询记录一天内执行超过 100次，且平均时间大于 1s
        if result_json['Items']['SQLSlowLog'][p]['MySQLTotalExecutionCounts'] > 100 and (result_json['Items']['SQLSlowLog'][p]['MySQLTotalExecutionTimes'] / result_json['Items']['SQLSlowLog'][p]['MySQLTotalExecutionCounts'] > 1):
            total_record.append(result_json['Items']['SQLSlowLog'][p])


# 生成 xlsx 文件
def generate_xlsx(total_record, dbinstance):
    if total_record:
        filename = "/export_xlsx/" + dbinstance + "_mysql_slow_sql_" + yesterday + ".xlsx"
        workbook = openpyxl.Workbook()
        sheet = workbook.active

        # 添加列标题
        data1 = list(total_record[0].keys())
        sheet.append(data1)

        # 添加值
        for i in range(len(total_record)):
            data2 = list(total_record[i].values())
            sheet.append(data2)
        
        # 保存 xlsx
        workbook.save(filename=filename)

        # 发送钉钉消息
        msg = "http://192.168.0.200:81" + filename
        dingtalk.send_msg(msg)


def main(dbinstance):
    # 每次调用 main 函数前，先清空 total_record 列表
    total_record.clear()
    # 获取总的慢查询记录数
    total_record_count = get_total_count(dbinstance)
    # 获取慢查询页码，阿里云api默认返回一页30条记录
    total_record_page = total_record_count // 30 + 1
    # 循环所有页码
    for p in range(1, total_record_page+1):
        get_record(dbinstance, p)

    # 生成 xlsx
    generate_xlsx(total_record, dbinstance)


if __name__ == '__main__':
    # 对两个 RDS实例 进行检测
    dbinstance = "rr-xxxxxxxxxxxxxxxxx"
    main(dbinstance)

    dbinstance = "rm-xxxxxxxxxxxxxxxxx"
    main(dbinstance)

dingtalk.py

import json
import requests


# 传入 msg 参数，发送钉钉消息
def send_msg(msg):
    url = 'https://oapi.dingtalk.com/robot/send?access_token=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' #  钉钉群机器人url
    parameter = {
        "msgtype": "text",
        "text": {
            "content": "云服务 -- 数据库慢查询日志已生成，下载请点击：%s ，请及时查看(该链接只可以在公司内网打开)~" % msg   # 这里我定义的关键字是 云服务，记得在 钉钉群机器人的安全设置上加上
        },
    }
    headers = {
        'Content-Type': 'application/json'
    }
    requests.post(url, data=json.dumps(parameter), headers=headers)

在 PyCharm 中 使用命令导出依赖

pip freeze > requirements.txt

把程序打包进 docker

创建一个基础镜像

mkdir /data/demo -p && cd /data/demo

vi Dockerfile
FROM python:3.6
WORKDIR /data

COPY requirements.txt ./
RUN pip install -r requirements.txt -i https://mirrors.aliyun.com/pypi/simple/

# 把 requirements.txt 上传到 /data/demo 上

docker build -t slow_sql_template:0.1 ./

创建 nginx 镜像用来提供MySQL慢日志下载服务

mkdir /data/nginx4slow_sql -p && cd /data/nginx4slow_sql

vi docker-compose.yml
version: '3.4'
services:
  nginx4slow_sql: 
    image: nginx:1.19
    ports:
      - 81:80           # 使用81端口对外提供服务
    volumes:
      - /data/export_xlsx:/usr/share/nginx/html/export_xlsx  # 把宿主机的 /data/export_xlsx 映射到容器中，提供文件下载服务

mkdir -p /data/export_xlsx

docker-compose up -d 

创建应用镜像

mkdir /data/alarm4slow_sql -p && cd /data/alarm4slow_sql

vi  Dockerfile 
FROM slow_sql_template:0.1
WORKDIR /data
COPY . .
RUN mkdir /export_xlsx
 
# 把程序 slow_log.py 和 dingtalk.py 上传到该目录

docker build -t alarm4slow_log:0.1 ./

vi docker-compose.yml
version: '3.4'
services:
  alarm4slow_sql: 
    image: alarm4slow_log:0.1
    command: python slow_log.py 
    volumes:
      - /data/export_xlsx:/export_xlsx   # 把宿主机的 /data/export_xlsx 映射到容器中的 /export_xlsx 目录中

docker-compose up -d 

运行结果：

添加 Linux 定时任务触发

crontab -e
# start alarm4slow_sql  
30 10 * * * cd /data/alarm4slow_sql && /usr/local/bin/docker-compose up -d > /dev/null 2>&1