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 osdef 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 datadef 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)
