定时获取阿里云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