MySQL慢查询邮件告警

Python

Posted by BY lingjun on May 16, 2019 本文总阅读量

MySQL慢查询邮件告警

crontab脚本每秒去捞processlist.

告警邮件

告警脚本

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Date: 2018/12/9/009
# Created by 灵均
import MySQLdb
import smtplib
from email.mime.text import MIMEText
from email.header import Header
import base64
import datetime

#自定义配置信息
dbInfoDic={'192.168.11.11':[{'receiver':'nba169@163.com,nba169@163.com','time':1,'schema':'\'testdb1\',\'testdb2\''},
                          {'receiver':'nba169@163.com','time':1,'schema':'\'testdb3\',\'testdb4\',\'testdb5\''}],
           '192.168.11.12':[{'receiver':'nba169@163.com','time':1,'schema':''}]              
           }

nowTime=str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))

#数据库连接信息
def connDatabase(dbHost):
#数据库连接信息
    mysql_db_password=base64.decodestring('**********')
    mysql_db_dbnm='information_schema'
    mysql_db_host=dbHost
    mysql_db_port=3306
    mysql_db_user='dbUser'
    mysql_db_socket='/var/lib/mysql/mysql.sock'

#连接数据库
    conn=MySQLdb.connect(host=mysql_db_host,port=mysql_db_port,user=mysql_db_user,passwd=mysql_db_password,db=mysql_db_dbnm
                         ,charset='utf8',unix_socket=mysql_db_socket)
    return conn

def getMonitorDate(dbHost,time,schema):
    if schema:
        sql="""select * from INFORMATION_SCHEMA.PROCESSLIST where  COMMAND not in ('Sleep','Binlog Dump GTID','Connect','Binlog Dump')
         and time>%d and db in (%s)"""%(time,schema)
    else:
        sql="""select * from INFORMATION_SCHEMA.PROCESSLIST where  COMMAND not in ('Sleep','Binlog Dump GTID','Connect','Binlog Dump')
         and time>%d """%(time)
    conn=connDatabase(dbHost)
    cur=conn.cursor()
    cur.execute(sql)
    title=cur.description
    tb_caption=[]
    results=[]
    for i in range(len(title)):
        tb_caption.append(title[i][0])
    values=cur.fetchall()
    for i in range(len(values)):
        results.append(values[i])
    cur.close()
    conn.close()
    return tb_caption,results

def getHtml(results,tb_caption):
    return_msg = {}
    tc = "<tr>"
    tr = ""
    # 构建表格标题
    for td in tb_caption:
        tc = tc + "<th><strong>%s</strong></th>" % (td)
    tc = tc + "</tr>"
    # 构建html表格体
    for i in results:
        tr = tr + "<tr>"
        for j in range(len(tb_caption)):
            tr = tr + "<td>%s</td>" % (i[j])
        tr = tr + "</tr>"
    html = """
    <html>
    <HEAD>
    <style type="text/css">
    table.gridtable {
            font-family: verdana,arial,sans-serif;
            font-size:11px;
            color:#333333;
            border-width: 1px;
            border-color: #666666;
            border-collapse: collapse;
    }
    table.gridtable th {
            border-width: 1px;
            padding: 8px;
            border-style: solid;
            border-color: #666666;
            background-color: #dedede;
    }
    table.gridtable td {
            border-width: 1px;
            padding: 8px;
            border-style: solid;
            border-color: #666666;
            background-color: #ffffff;
    }
    </style>
    </HEAD>
    <body>      
    <table class="gridtable">
    """ + tc + tr + """
    </table>
    </body>
    </html>
       """
    return html

#需要配置
def sendMail(html,receivers):
    theme='['+dbKey+']:Monitor SLOW SQL'
    msg = MIMEText(html, 'html', 'utf-8')
    msg['Subject'] = Header(theme, 'utf-8')
    msg['From'] = 'MonitorDatabase'
    msg['To'] = receivers
    smtp = smtplib.SMTP()
    smtp.connect('smtp.exmail.qq.com')
    smtp.login('********', '*******')
    sender='********'
    smtp.sendmail(sender, receivers.split(',') , msg.as_string())
    smtp.quit()

if __name__ == "__main__":
    for dbKey in dbInfoDic:
        for i in range(len(dbInfoDic[dbKey])):
            tb_caption,results=getMonitorDate(dbKey,dbInfoDic[dbKey][i]['time'],dbInfoDic[dbKey][i]['schema'])
            html=getHtml(results,tb_caption)
            if len(results) != 0:
                sendMail(html,dbInfoDic[dbKey][i]['receiver'])
                print nowTime+'['+dbKey+']:Slow Sql Is Send Email'
            else:
                print nowTime+'['+dbKey+':'+dbInfoDic[dbKey][i]['schema']+']:No Slow Sql'

Crontab部署

#监控数据库慢查询sql
* * * * * /usr/bin/python /data/scripts/MonitorSlowSql.py >>/data/scripts/logs/MonitorSlowSql.log