获取昨天的慢查询SQL自动创建 Jira 的子任务
在获取前一天 MySQL 慢查的情况下,进一步做告警(https://www.cnblogs.com/klvchen/articles/13677698.html)
通过 xlsx 获取到 sqlhash,SQL 等字段。
创建路径
mkdir -p /data/slow_sqlhash4jira/
cd /data/slow_sqlhash4jira/
mkdir base_image images
代码
cd /data/slow_sqlhash4jira/images
app.py
from openpyxl import load_workbook
import datetime
import os
import dingtalk
import jira_util
# 需要修改的配置文件
#xls_patch = "d:\\Documents\\xls\\"
xls_patch = "/export_xlsx/"
oneday = datetime.timedelta(days=1)
today = datetime.date.today()
yesterday = today - oneday
parent_key = 'FCYP-9086'
def get_xls_filename():
filename = xls_patch + "rm-wz9lzm065d7c4b4a0_mysql_slow_sql_" + str(yesterday) + ".xlsx"
# print(filename)
return filename
def get_xls_info(filename):
if os.path.exists(filename):
workbook = load_workbook(filename)
sheet = workbook.active
row_length = sheet.max_row
sum = 0
for i in range(2, row_length + 1):
sql_cell = sheet['F' + str(i)].value
sql_hash_cell = sheet['I' + str(i)].value
sql_db_name = sheet['O' + str(i)].value
sql_max_execute_time = sheet['H' + str(i)].value
sql_execute_count = sheet['M' + str(i)].value
sql_max_parse_count = sheet['G' + str(i)].value
sql_max_return_count = sheet['E' + str(i)].value
description = "库名: {} \n最大执行时间: {} 秒 \n解析SQL最大行数: {}\n返回SQL总行数: {}\n执行次数: {}\nSQL: \n{}".format(sql_db_name, sql_max_execute_time, sql_max_parse_count, sql_max_return_count, sql_execute_count, sql_cell)
result = jira_util.search_and_create_task(sql_hash_cell, description, parent_key)
if result:
sum += 1
if sum > 0:
dingtalk.send_msg(sum)
else:
print("xlsx 不存在")
if __name__ == '__main__':
# get_redis()
filename = get_xls_filename()
get_xls_info(filename)
dingtalk.py
import json
import requests
# 需要自己替换 token
url = 'https://oapi.dingtalk.com/robot/send?access_token=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxd464'
def send_msg(msg):
parameter = {
"msgtype": "text",
"text": {
"content": "云服务 -- 昨天新增线上MySQL慢查数量 %s ,已添加到 Jira ~" % msg
},
}
headers = {
'Content-Type': 'application/json'
}
requests.post(url, data=json.dumps(parameter), headers=headers)
jira_util.py
from jira import JIRA
# 通过jira域名和账户密码登录,需要自己替换用户名和账号
jira = JIRA('http://jira.bokevip.com/', basic_auth=('xxxxxxxx', 'xxxxxxxx'))
# 通过 JQL 查找
# 标签为 "SQL慢查询",
# 解决结果:未解决
# 经办人: None
def search_task():
temp_list = []
mysql_slow = jira.search_issues('project=FCYP and Labels=SQL慢查询 and resolution = null and assignee = null')
for issue in mysql_slow:
temp_list.append(issue.fields.summary)
return temp_list
# 创建子任务 issue
def create_sub_task(summary, description, parent_key):
issue_dict = {
'project': {'key': 'FCYP'},
'summary': summary,
'description': description,
'labels': ['SQL慢查询'],
'issuetype': {'name': 'Sub-task'},
'parent': {'key': parent_key}
}
new_issue = jira.create_issue(fields=issue_dict)
# 判断是否需要创建子任务
def search_and_create_task(summary, description, parent_key):
slow_task_list = search_task()
if summary not in slow_task_list:
create_sub_task(summary, description, parent_key)
return 1
return 0
if __name__ == '__main__':
# summary = '74b7f594f61f6f2dd14a13d71c997ed6'
# description = 'Look into this one'
# parent_key = 'FCYP-9086'
#create_sub_task(summary, description, parent_key)
slow_task_list = search_task()
print(slow_task_list)
创建基础镜像
cd base_image
# 配置项目依赖
vi requirements.txt
certifi==2020.12.5
chardet==4.0.0
et-xmlfile==1.0.1
idna==2.10
jdcal==1.4.1
openpyxl==3.0.5
redis==3.5.3
requests==2.25.1
urllib3==1.26.2
jira==2.0.0
# 创建 Dockerfile
vi Dockerfile
FROM python:3.6
WORKDIR /data
RUN echo "Asia/Shanghai" > /etc/timezone
RUN cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
COPY requirements.txt ./
RUN pip install -r requirements.txt -i https://mirrors.aliyun.com/pypi/simple/
docker buid -t slow_sqlhash_base4jira:20210119.1 .
创建业务镜像
cd /data/slow_sqlhash4jira/images
vi Dockerfile
FROM slow_sqlhash_base4jira:20210119.1
WORKDIR /data
COPY . ./
docker build -t slow_sqlhash4jira:0.1 .
创建 docker-compose
cd /data/slow_sqlhash4jira
vi docker-compose.yml
version: '3.4'
services:
slow_sqlhash4jira:
image: slow_sqlhash4jira:0.4
command: python app.py
#command: sleep 3600
volumes:
- /data/export_xlsx:/export_xlsx
# 启动
docker-compose up -d
效果: