mysql报警_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