python MySQL慢查询监控

MySQL慢查询会话监控

#!/usr/bin/python
# -*- coding: UTF-8 -*-

from email.mime.text import MIMEText
from email.utils import formataddr
from email.mime.multipart import MIMEMultipart
import MySQLdb
import smtplib
import datetime
import logging
import logging.handlers
import os


def handlemysql(sql):
    """
    执行sql
    """
    db = MySQLdb.connect(host, user, passwd, dbname)
    cursor = db.cursor()
    monitor_log(sql)
    cursor.execute(sql)
    data = cursor.fetchall()
    # 关闭数据库连接
    db.close()
    return data


def execsql(num):
    """
    查询进程id
    :return:
    """
    num = int(num[0][1])

    process_id_sql = "SELECT id FROM information_schema.processlist WHERE command not in ('Connect','Sleep','Binlog Dump') and time >= %d and user not in ('wedba','replicator','root','system user')"
    # process_id_sql = "SELECT id FROM information_schema.processlist WHERE time >= %d and user not in ('wedba','replicator','system user')"
    if 50 < num < 100:
        sql = process_id_sql % 60
        ret = handlemysql(sql)
        return ret
    elif 100 < num < 200:
        sql = process_id_sql % 30
        ret = handlemysql(sql)
        return ret
    elif 200 < num:
        sql = process_id_sql % 10
        ret = handlemysql(sql)
        return ret
    else:
        loginfo = "Not need to kill threads"
        monitor_log(loginfo)
        exit()


def killid(pid):
    """
     kill 进程id
    :param arg:
    :return:
    """
    sendsql = "select user,host,db,command,time,info from information_schema.processlist where command not in ('sleep') and user not in ('wedba') order by time desc"
    # sendsql = "select user,host,db,command,time,info from information_schema.processlist"
    if pid:
        content = handlemysql(sendsql)
        for i in pid:
            i = long(i[0])
            killsql = "kill %d" % i
            handlemysql(killsql)
        msg = ""
        for item in content:
            if item:
                msg = msg + "<p>" + ",".join(str(line) for line in item) + "</p>\n"
        with open("/tmp/info.html", 'w+') as f:
             f.write(msg)
        monitor_log("write /tmp/info.html success")


def senmail(emailuser, emailpwd, tousers):
    """
    发邮件功能
    :param name:
    :param mailaddr:
    :param title:
    :param content:
    :return:
    """
    msg = MIMEMultipart()
    msg['From'] = formataddr(["monitor", emailuser])
    msg['To'] = formataddr([tousers[0], tousers[0]])
    msg['Subject'] = "业务从库数据库会话监控"
    mail_msg = "业务从库数据库kill会话sql详情"
    msg.attach(MIMEText(mail_msg, 'plain', 'utf-8'))

    # ---这是附件部分---
    att1 = MIMEText(open('/tmp/info.html', 'rb').read(), 'base64', 'utf-8')
    att1["Content-Type"] = 'application/octet-stream'
    att1["Content-Disposition"] = 'attachment; filename="info.html"'
    msg.attach(att1)

    try:
        # 创建SMTP对象
        server = smtplib.SMTP_SSL("smtp.exmail.qq.com", 465)
        # server.set_debuglevel(1)
        server.login(emailuser, emailpwd)
        server.sendmail(emailuser, tousers, msg.as_string())
        server.quit()
        os.rename('/tmp/info.html','/tmp/info.html%s' % datetime.datetime.now())
        print u"%s\t邮件发送成功!" % datetime.datetime.now()
        monitor_log("Send mail success!")
    except smtplib.SMTPException:
        print u"Error: 无法发送邮件"
        monitor_log("Error: Send mail faild")


def monitor_log(loginfo, logfile="/tmp/monitor_mysql.log"):
    """
    log记录
    :return:
    """
    LOG_FILE = logfile
    handler = logging.handlers.RotatingFileHandler(LOG_FILE, maxBytes=10 * 1024 * 1024, backupCount=5)
    fmt = '%(asctime)s - %(filename)s:%(lineno)s - %(name)s - %(message)s'
    formatter = logging.Formatter(fmt)
    handler.setFormatter(formatter)
    logger = logging.getLogger('monitor-mysql')
    if not len(logger.handlers):
        logger.addHandler(handler)
    logger.setLevel(logging.INFO)
    logger.info(loginfo)


if __name__ == '__main__':
    host = "192.168.1.100"
    user = "root"
    passwd = "admin123"
    dbname = "information_schema"
    emailuser = "shuke@163.com"
    emailpwd = "123456"
    tousers = ['Tome@163.com']

    select_threads_sql = "show global status like 'Threads_running'"
    num = handlemysql(select_threads_sql)
    pid = execsql(num)
    if pid:
        centent = killid(pid)
        senmail(emailuser, emailpwd, tousers)