MySQL慢查询SQL触发阈值邮件报警

2020-08-10 10:55:18

这个只是我作为数据库和开发小白自己的一个解决思路，如果有更恰当的思路，欢迎评论或私聊呀～

一、项目背景

业务数据库被异常调用时导致慢查询量增大，影响到正常业务使用，业务只能通过nginx超时异常等来进行问题排查，增大了定位和处理问题的难度、时间，尤其商品库或者交易库可能会出现页面无法正常显示的情况。故业务需求为若主库和从库慢查询总量每分钟超过某个阈值之后则发送邮件报警。

二、开发逻辑问题及处理

1、问题梳理

  1. 由于主库和从库在不同主机上，故不能开启pt-kill的--log-dsn参数将pt-kill结果写入数据库中，否则会出现主从数据不一致，从而导致主从异常中断
  2. pt-kill在哪台主机上执行，如果pt-kill在主库、从库本身机器上执行，会将文件写入到本地，pt-kill效率的确最高，但后续如何将文件汇总到一起；而且最终需要将日志文件作为附件发送邮件，线上机器无法到外网，所以还需要分别将文件传输到中控机等可以访问外网的机器上
  3. 对应数据库的主库、从库可能会由于实例均衡发生变动，若直接在对应主机上启动pt-kill，会出现异常情况，并且还需要代码变动
  4. pt-kill日志文件时间问题，本次需求为超过1分钟的数量触发到阈值之后发送邮件报警，若所有pt-kill结果都放入一个文件，这个文件会越来越大，不仅处理时间会越来越长，而且获取1分钟内的记录计算数量可能会存在漏掉或重复计算的情况
  5. 邮件内容如何发送，虽使用附件将pt-kill日志发送，但仍需要将统计内容作为邮件正文

2、问题处理

  1. 直接在中控机上执行对应的pt-kill命令
  2. 使用pt-kill的--log参数直接将kill掉的记录写入到本地文件中
  3. 使用多进程每一分钟启动对应的pt-kill进程，并写入到对应文件中，日志文件命名为角色_端口_主机区分_时间戳_slow.log。例如：主库某个时间的文件名为：m_3306_733_20200810114116_slow.log，表示端口为3306的主库，在主机后两位为733上2020年8月10日11时41分16秒生成的kill日志文件
  4. pt-kill进程结束后使用子进程grep对应文件来获取慢SQL数量，并记录下来
  5. 由于为1min便有新的日志文件生成，时间久了之后数据量会特别大，还需要一个定时任务去清除日志文件。这里直接使用了crontab清除每10分钟清除30min之前的日志文件
  6. 邮件正文通过html写为表格，详细慢查询SQL直接通过附件发送

三、逻辑流程梳理

  1. 持续使用percona提供的pt-kill工具kill对应数据库集群(主库+从库)的超过2s的慢查询并记录到日志文件中
    1. 在管理库上获取对应集群的主库和从库主机名等信息
    2. 使用合适的pt-kill参数将每分钟kill掉的慢SQL记录到文件中
  2. 获取主库和从库总共kill的慢SQL数量
    1. 获取每个主库和从库日志文件所记录的kill慢SQL数量
    2. 获取所有主机被kill的慢SQL总数
  3. 如果超过设定的阈值，则发送邮件报警
    1. 判断是否超过设置阈值
    2. 超过则发送邮件报警

四、主要功能代码

由于markdown粘贴代码，缩进可能有部分问题，复制粘贴后需注意一下，并且这里取消了log模块，可将需要内容打印到使用的log中去

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# 公共函数
## 1. 获取数据库连接
from sqlalchemy import create_engine
def adminMySQLConn(User,Pass,Host,Port,DBName):
        try:
            adminConn="mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4".\
                    format(User,Pass,Host,Port,DBName)
            adminEngine = create_engine(adminConn)
            return adminEngine

        except Exception as err:
            self.log.error("adminMySQLConn: {}".format(err))
            #self.log.error("adminMySQLConn: {}".format(err.message))

## 2. 获取当前时间戳
from datetime import datetime
def initCurrentDateTime():
        try:
            return datetime.now().strftime('%Y%m%d%H%M%S')
        except Exception as err:
            self.log.error("initCurrentDateTime: {}".format(err.message))

## 3. 远程/本地执行linux命令(在本地执行可以直接使用os.system或subprocess.getstatusoutput，但由于这里是个公共函数，其他程序可能需要远程执行，故这里统一使用paramiko执行)
from paramiko import SSHClient, AutoAddPolicy
def executeSSH(ip, cmds):
    try:
            client = SSHClient()
            client.set_missing_host_key_policy(AutoAddPolicy())
            system('kinit -kt /etc/krb5.keytab')
            client.connect(ip, look_for_keys=False, gss_auth=True, gss_kex=True)
            stdin, stdout, stderr = client.exec_command(cmds)
            # result = stdout.readlines()       # 获取命令执行结果,返回的数据是一个list
            _result = stdout.read().decode()  # 命令执行结果
            _status = stdout.channel.recv_exit_status()  # 命令执行状态码
            client.close()
            return _status, _result
        except Exception as e:
            # print(e)
            raise e

1、获取初始化信息

由于这里存在一个管理库，上面有所有数据库的信息，则通过pymysql去查看并获取对应信息即可。

1
2
# 输入信息，即GetHost中的info
Inputinfo = {"rsPort": 3306'}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# tb_mysql_instance为管理表，其中：rsPort为端口，rsHost为对应数据库所在主机，rsRole为数据库角色：master主库，slave从库
SQLlist = {
    "getHost": "select rsHost, rsPort, rsRole from tb_mysql_instance where rsPort = {} and rsRole in ('master', 'slave');"
}

def GetHost(**info):
    port = info['rsPort']
    insInfoList = []
    adminConn = adminMySQLConn()
    try:
        executeSQL = SQLlist['getHost'].format(port)
        insList = adminConn.execute(executeSQL).fetchall()
        if insList:
            datetime = initCurrentDateTime()
            for insInfo in insList:
                infoDict = dict(zip(insInfo.keys(), insInfo.values()))
                if infoDict['rsRole'] == 'master':
                    infoDict['logFile'] = "m_{}_{}_{}_slow.log".format(infoDict['rsPort'],''.join(infoDict['rsHost'].split('.')[2:]) , datetime)
                else:
                    infoDict['logFile'] = "s_{}_{}_{}_slow.log".format(infoDict['rsPort'],''.join(infoDict['rsHost'].split('.')[2:]) , datetime)
                infoDict['ptkillLogFile'] = "./SlowLog/{}".format(infoDict['logFile'])
                insInfoList.append(infoDict)
            msg = "Get {} pt-kill master and slave host info success.".format(port)
            return True, insInfoList
        else:
            msg = "Get {} pt-kill master and slave host info failed. SQL:{} insList: ".format(port, executeSQL), insList
            return False, msg

    except Exception as err:
        msg = "Get {} pt-kill master and slave host info err: ".format(port), err
        return False, msg
1
2
# 输出结果示例：
[{'rsHost': '1.1.1.1', 'rsPort': 3306, 'rsRole': 'master', 'logFile': 'm_3306_11_20200810084828_slow.log', 'ptkillLogFile': '/path/SlowLog/m_3306_11_20200810084828_slow.log'}, {'rsHost': '2.2.2.2', 'rsPort': 3306, 'rsRole': 'slave', 'logFile': 's_3306_22_20200810084828_slow.log', 'ptkillLogFile': '/path/SlowLog/s_3306_22_20200810084828_slow.log'}, {'rsHost': '3.3.3.3', 'rsPort': 3306, 'rsRole': 'slave', 'logFile': 's_3306_33_20200810084828_slow.log', 'ptkillLogFile': '/path/SlowLog/s_3306_33_20200810084828_slow.log'}]

2、执行pt-kill并获取慢SQL数量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
# 后面的info都一直表示获取初始化信息后的里面的字典格式
# 例如：{'rsHost': '1.1.1.1', 'rsPort': 3306, 'rsRole': 'master', 'logFile': 'm_3306_11_20200810084828_slow.log', 'ptkillLogFile': '/path/SlowLog/m_3306_11_20200810084828_slow.log'}

# 子函数
## 执行pt-kill命令，pt-kill一次只执行60s
def ExecutePtkillCmd(**info):
    try:
        ptkillCmd = "/opt/soft/percona-toolkit-2.2.14/bin/pt-kill --no-version-check " \
                    "--host {rsHost} --port {rsPort} --user 'dba' --password '5d63f33c10b8f430'" \
                    " --busy-time 2 --match-state='Sending data|Sorting result' --victim all " \
                    "--interval 1 --run-time 60 --daemonize  --kill --print --log={ptkillLogFile}".format(**info)
        status, ret = executeSSH('10.148.16.25', ptkillCmd)
        if status == 0:
            msg = "Execute {rsHost}:{rsPort} pt-kill command success.".format(**info)
            return True, msg
        else:
            msg = "Execute {rsHost}:{rsPort} pt-kill command failed, Cmd:".format(**info), ptkillCmd
            return False, msg
    except Exception as err:
        msg = "Execute {rsHost}:{rsPort} pt-kill command error.".format(**info), err
        return False, msg

## 获取慢SQL数量，并写入info中
def GetFileRegixCount(**info):
    try:
        logPwd = info['ptkillLogFile']
        process = Popen(['grep', '^# [0-9]\{4\}-[0-9]\{2\}-[0-9]\{2\}', logPwd], stdout=PIPE)
        info['slowSQLCount'] = len((process.stdout).readlines())
        msg = "Get {rsHost}:{rsPort} slow log count success.".format(**info)
        return True, info
    except Exception as err:
        msg = "Get {rsHost}:{rsPort} slow log count err:".format(**info), err
        log.error(msg)
        return False, msg
### 输出info格式类似为：
{'rsHost': '1.1.1.1', 'rsPort': 3306, 'rsRole': 'master', 'logFile': 'm_3306_11_20200810084828_slow.log', 'ptkillLogFile': '/path/SlowLog/m_3306_11_20200810084828_slow.log', 'slowSQLCount': 0}

## 每60s触发一次pt-kill的执行
from time import sleep
def killSlowSql(info):
    try:
        status, msg = ExecutePtkillCmd(**info)
        if status is False:
            return status, msg
        sleep(60) 
        return GetFileRegixCount(**info)
    except Exception as err:
        msg = "kill {rsHost}:{rsPort} {ptkillLogFile} err:".format(**info), err
        return False, msg

## 多进程执行pt-kill
from multiprocessing import Pool
def main(*HostInfoList):
    try:
        pool = Pool(8)
        res_l = []
        infoList = []
        for info in HostInfoList:
            base = pool.apply_async(killSlowSql, (info, ))
            res_l.append(base)
        pool.close()
        pool.join()
        for res in res_l:
            ret = res.get()
            infoList.append(ret[1])
        return True, infoList
    except Exception as err:
        return False, err

3、邮件正文html表格

由于python自带html表格样式有些许丑，所以参考这个小姐姐的表格前端页面来进行了修改。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
import pandas as pd
head = \
        """
        <head>
            <meta charset="utf-8">
            <STYLE TYPE="text/css" MEDIA=screen>

                table.dataframe {
                    border-collapse: collapse;
                    border: 2px solid #a19da2;
                    /*居中显示整个表格*/
                    margin: auto;
                }

                table.dataframe thead {
                    border: 2px solid #91c6e1;
                    background: #f1f1f1;
                    padding: 10px 10px 10px 10px;
                    color: #333333;
                }

                table.dataframe tbody {
                    border: 2px solid #91c6e1;
                    padding: 10px 10px 10px 10px;
                }

                table.dataframe tr {

                }

                table.dataframe th {
                    vertical-align: top;
                    font-size: 14px;
                    padding: 10px 10px 10px 10px;
                    color: #105de3;
                    font-family: arial;
                    text-align: center;
                }

                table.dataframe td {
                    text-align: center;
                    padding: 10px 10px 10px 10px;
                }

                body {
                    font-family: 宋体;
                }

                h1 {
                    color: #5db446
                }

                div.header h2 {
                    color: #0002e3;
                    font-family: 黑体;
                }

                div.content h2 {
                    text-align: center;
                    font-size: 28px;
                    text-shadow: 2px 2px 1px #de4040;
                    color: #fff;
                    font-weight: bold;
                    background-color: #008eb7;
                    line-height: 1.5;
                    margin: 20px 0;
                    box-shadow: 10px 10px 5px #888888;
                    border-radius: 5px;
                }

                h3 {
                    font-size: 22px;
                    background-color: rgba(0, 2, 227, 0.71);
                    text-shadow: 2px 2px 1px #de4040;
                    color: rgba(239, 241, 234, 0.99);
                    line-height: 1.5;
                }

                h4 {
                    color: #e10092;
                    font-family: 楷体;
                    font-size: 20px;
                    text-align: center;
                }

                td img {
                    /*width: 60px;*/
                    max-width: 300px;
                    max-height: 300px;
                }

            </STYLE>
        </head>
        """

# 转换为表格需要的输入
## 输入格式类似为result=[[1,2,3],['a','b','c']], title=['id', 'name']
def convert_to_html(result,title):
    d = {}
    index = 0
    for t in title:
        d[t] = result[index]
        index +=1
    df = pd.DataFrame(d)
    #如数据过长，可能在表格中无法显示，加上pd.set_option语句可以避免这一情况
    pd.set_option('max_colwidth',200)
    pd.set_option('colheader_justify', 'center')
    df = df [title]
    #h =df.to_html(index=False)
    h =df.to_html(col_space=30,border=1,justify='center')
    h2 = h.replace('class', 'cellspacing=\"0\" class')
    return h2

# 初始化表格
def formatHtmlTable(result, title):
    df_html = convert_to_html(result,title)
    body = \
        """
        <body>

        <div align="center" class="header">
            <!--标题部分的信息-->
            <h1 align="center">慢查询SQL邮件报警</h1>
            详情请查看附件
<!--            <h2 align="center">具体SQL详情请查看附件</h2> -->
        </div>

        <div class="content">
            <!--正文内容-->
            <h2> </h2>

            <div>
                <h4></h4>
                {df_html}

            </div>

            <p style="text-align: center">

            </p>
        </div>
        </body>
        """.format(df_html=df_html)
    html_msg = "<html>" + head + body + "</html>"
    html_msg = html_msg.replace('\n','').encode("utf-8")
    return html_msg

4、邮件发送

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
## 发送邮件，username和password为发送人邮箱的用户名和密码。注意密码为smtp的授权码
def sendMail(sender, receivers, message):
    try:
        username = '''''''xxxx@qq.com'
        password = 'xxxxxx
        smtp = smtplib.SMTP(host='xxxx.qq.com', port=25)
        smtp.login(username, password)
        rdict = smtp.sendmail(sender, receivers, message.as_string())
        smtp.quit()
        msg = "send mail success"
        return True, msg

    except smtplib.SMTPException:
        msg = "send mail failed"
        return False, msg

## 发送报警
def sendMailAlert(*infoList):
    sender ='xxx@qq.com'        # 发送人邮箱
    receivers = ['yy@163.com']  # 接收人邮箱列表，可写多个
    message = MIMEMultipart()
    message['From'] = Header("lichunliang", 'utf-8')  # 发送者别名
    message['To'] = Header("business_rds", 'utf-8')  # 接收者别名
    subject = '慢查询邮件告警测试'
    message['Subject'] = Header(subject, 'utf-8')
#    message.attach(MIMEText(mail_msg1, 'plain', 'utf-8'))
    sumCount = 0
    for info in infoList:
        sumCount += info["slowSQLCount"]
    if sumCount > 5:
        retList = []
        title = ['rsPort', 'rsRole', 'logFile', 'slowSQLCount']
        for t in title:
            ret = []
            [ret.append(info[t]) for info in infoList]
            retList.append(ret)
        for info in infoList:
            ## 发送附件
            att1 = MIMEText(open(info['ptkillLogFile'], 'rb').read(), 'base64', 'utf-8')
            att1["Content-Type"] = 'application/octet-stream'
            att1["Content-Disposition"] = 'attachment; filename={}'.format(info['ptkillLogFile'].split('/')[-1])
            message.attach(att1)
        html_msg = formatHtmlTable(retList,title)
        message.attach(MIMEText(html_msg, 'html', 'utf-8'))
        return sendMail(sender, receivers, message)
    else:
        log.info('Dont need sendmail.')

五、邮件报警效果

  1. 一、项目背景
  2. 二、开发逻辑问题及处理
    1. 1、问题梳理
    2. 2、问题处理
  3. 三、逻辑流程梳理
  4. 四、主要功能代码
    1. 1、获取初始化信息
    2. 2、执行pt-kill并获取慢SQL数量
    3. 3、邮件正文html表格
    4. 4、邮件发送
  5. 五、邮件报警效果