Question

通过邮件发送统计TiDB慢SQL的Python脚本

CREATE USER `export-slowsql-user`@`192.168.1.%` IDENTIFIED BY '****';
GRANT PROCESS ON INFORMATION_SCHEMA.* TO 'export-slowsql-user'@'192.168.1.%'

#!/usr/bin/python3
# -*- coding: UTF-8 -*-
import datetime
import smtplib
import os
from email import encoders
from email.header import Header
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import mysql.connector
import xlwt

# 获取大约多少秒的慢SQL
slowtime = 2
# 获取最近多少天之前到现在的慢SQL
slow_from_day = 7
# TiDB数据库地址
DB_HOST=""
# TiDB数据库端口
DB_PORT=""
# 用于查询慢SQL的用户
DB_USER=""
DB_PASSWORD=""
# 记录慢SQL变所在的Database
DB_DATABASE="INFORMATION_SCHEMA"
# SMTP服务器的地址
SMTP_HOST=""
# SMTP用于发信的用户名
SMTP_USER=""
# SMTP用于发信的密码
SMTP_USER_PASSWORD=""
# 用于记录慢SQL的EXCEL文件名
RECORDS_EXCEL_FILENAME="tidb-slowlogs.xls"
nt = datetime.datetime.now()
lastsomeday = (nt - datetime.timedelta(days=slow_from_day)).date()


def sendEmail(attachfilename):
    sender = SMTP_USER
    # 接收者邮件地址
    receivers = ['要邮件通知的邮箱地址', '要邮件通知的邮箱地址']

    message = MIMEMultipart()
    message['From'] = Header(SMTP_USER, 'utf-8')  # 发送者
    message['To'] = Header('; '.join(str(e) for e in receivers) + '; ', 'utf-8')
    # 邮件主题内容
    message['Subject'] = Header(nt.strftime('%Y%m%d') + "TiDB最近" +
                                str(slow_from_day) + "天(" +
                                lastsomeday.strftime('%Y%m%d') + "~" + nt.strftime('%Y%m%d') +
                                ")的慢SQL", 'utf-8')
    # 邮件正文内容
    message.attach(MIMEText("TiDB最近" + str(slow_from_day) + "天("
                            + lastsomeday.strftime('%Y%m%d') + "~" +
                            nt.strftime('%Y%m%d') +
                            ")，查询时间超过" + str(slowtime) +
                            "秒的慢SQL。详情见附件Excel文件(排除了部分用户和主机客户端相关的SQL)", 'plain', 'utf-8'))
    # 附件内容
    attachment = MIMEBase('application', "octet-stream")
    attachment.set_payload(open(attachfilename, "rb").read())
    encoders.encode_base64(attachment)
    attachment.add_header('Content-Disposition',
                          'attachment; filename="' + lastsomeday.strftime('%Y%m%d') + "-" + nt.strftime(
                              '%m%d') + '-'+RECORDS_EXCEL_FILENAME+'"')
    message.attach(attachment)

    try:
        smtpObj = smtplib.SMTP(SMTP_HOST)
        smtpObj.login(SMTP_USER, SMTP_USER_PASSWORD)
        smtpObj.sendmail(sender, receivers, message.as_string())
        print("邮件发送成功")
    except smtplib.SMTPException:
        print("Error: 无法发送邮件")


def dbConnet(host, port, user, passwd, db):
    db = mysql.connector.connect(
        host=host,
        port=port,
        user=user,
        password=passwd,
        database=db
    )
    cursor = db.cursor(buffered=True, dictionary=True)
    return cursor, db


def createExcel(data, filename):
    # 创建excel工作表
    workbook = xlwt.Workbook(encoding='utf-8')
    worksheet = workbook.add_sheet('sheet1')
    # 设置表头
    worksheet.write(0, 0, label='UID')
    worksheet.write(0, 1, label='Count')
    worksheet.write(0, 2, label='数据库')
    worksheet.write(0, 3, label='执行耗时')
    worksheet.write(0, 4, label='扫描总Key个数')
    worksheet.write(0, 5, label='执行时间')
    worksheet.write(0, 6, label='执行用户')
    worksheet.write(0, 7, label='主机')
    worksheet.write(0, 8, label='返回结果行数')
    worksheet.write(0, 9, label='内存')
    worksheet.write(0, 10, label='Cop_time')
    worksheet.write(0, 11, label='Write_sql_response_total')
    worksheet.write(0, 12, label='Total_keys')
    worksheet.write(0, 13, label='Process_keys')
    worksheet.write(0, 14, label='SQL语句')
    ordered_list = ["UID",
                    "Count",
                    "数据库",
                    "执行耗时",
                    "扫描总Key个数",
                    "执行时间",
                    "执行用户",
                    "主机",
                    "返回结果行数",
                    "内存",
                    "Cop_time",
                    "Write_sql_response_total",
                    "Total_keys",
                    "Process_keys",
                    "SQL语句",
                    ]
    row = 1
    for player in data:
        for _key, _value in player.items():
            col = ordered_list.index(_key)
            worksheet.write(row, col, _value)
        row += 1
    workbook.save(filename)


def execute_sql(sql):
    mycursor, mydb = dbConnet(DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_DATABASE)
    mycursor.execute(sql)
    sqlresults = mycursor.fetchall()
    mydb.commit()
    mycursor.close()
    mydb.close()
    if sqlresults:
        filename = 'prod-tidb-slowlogs.xls'
        createExcel(sqlresults, filename)
        sendEmail(filename)
        if os.path.exists(RECORDS_EXCEL_FILENAME):
            os.remove(RECORDS_EXCEL_FILENAME)
            print("已删除临时文件")
        else:
            print("临时文件不存在!")
    else:
        print(lastsomeday.strftime('%Y%m%d') + "至" + nt.strftime('%Y%m%d') + "没有相关的慢SQL")


def main():
    print("====" + nt.strftime('%Y%m%d %H:%M:%s') + "====")
    query = ("SELECT "
             "Digest AS 'UID',"
             "count( 1 ) AS 'Count',"
             "DB AS '数据库',"
             "CONCAT( CAST(( Query_time ) AS CHAR ( 4 )), 's' ) AS '执行耗时',"
             "Total_keys AS '扫描总Key个数',"
             "CONCAT('\\'',Time) AS '执行时间', "
             "User AS '执行用户',"
             "Host AS '主机',"
             "Result_rows AS '返回结果行数',"
             "CONCAT( CAST(( Mem_max / 1024 / 1024 ) AS CHAR ( 4 )), 'MB' ) AS '内存',"
             "CONCAT( CAST(( Cop_time ) AS CHAR ( 4 )), 's' ) AS 'Cop_time',"
             "CONCAT( CAST(( Write_sql_response_total ) AS CHAR ( 4 )), 's' ) AS 'Write_sql_response_total',"
             "Total_keys,"
             "Process_keys,"
             "Query AS 'SQL语句'"
             "FROM    INFORMATION_SCHEMA.CLUSTER_SLOW_QUERY "
             "WHERE "
             "Query_time >= '%s' "
             "AND Time >= DATE_SUB( '%s' , INTERVAL '%s' DAY )  "
             "AND db not in  ('','要排除的DB')"
             "AND host not in  ('要排除的客户端IP地址','要排除的客户端IP地址') "
             "AND user NOT IN ( '要排除的客户端用户名' , '要排除的客户端用户名', '要排除的客户端用户名')"
             "GROUP BY Digest "
             "ORDER BY Query_time DESC , Result_rows DESC, Count DESC "
             "LIMIT 1000;" % (slowtime, nt, slow_from_day)
             )
    execute_sql(query)


if __name__ == "__main__":
    main()