mysql监控报警工具

#!/usr/bin/env python
# coding:utf-8

import MySQLdb
import requests, json
import time

url = "http://api.sendcloud.net/apiv2/mail/send"

key = {}
params = {}
#目标用户的邮箱
to_email = {'root': ["xxxx@xxx.com ", ], 'tianyi': ["x@qq.com", "x@sina.com", ]}

#对发送邮件的格式的配置
def sendmail(mail_list, sql):
    for number in range(len(mail_list)):
        params['apiUser'] = "xxx"
        params['apiKey'] = "0Mxb0ag"
        params['from'] = "x@foxmail.com"
        params['fromName'] = "bill"
        params['subject'] = "warning"
        params['html'] = "your sqlserver is dangerous: " + sql
        params['to'] = mail_list[number]
        r = requests.post(url, files={}, data=params)
        print r.text
        time.sleep(1)


if __name__ == "__main__":
    while True:
        #配置数据库，root用户可以看见所有用户的线程
        conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='x')
        cur = conn.cursor()
        reCount = cur.execute('show processlist;')

        for i in cur.fetchall():
            print i

            # 判断对用户的操作
            number = 0
            #i[4]对应的是Command,i[5]是Time,当sql语句处于执行的状态，并且时间大于1秒的时候
            if i[4] == 'Query' and i[5] >= 1:
                while number < i[5]:
                    # 发送邮件
                    sendmail(to_email[i[1]], str(i[7]))
                    number = number + 1
                    # 如果此时大于5秒就杀死进程
                    if number == 4:
                        id = str(i[0])
                        print id
                        sql = 'kill ' + id + ';'
                        cur.execute(sql)
                        break
                    time.sleep(1)

        cur.close()
        conn.close()
        time.sleep(1)


#配置说明
#修改所处mysql服务器的连接信息： conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='x')
#所拥有用户及其对应的邮箱地址：to_email = {'root': ["x@xx.com ", ], 'tianyi': ["x@qq.com", "x@sina.com", ]}
#使用的相关的sendcloud api配置：sendmail()

 

 

参考资料：https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

　　　　　http://www.linuxidc.com/Linux/2016-02/128558.htm