一 需求
因为开发针对某系统做稳定性建设,需要对数据库系统的慢日志进行审计,检查优化。和开发沟通选择定期发送慢查询到开发的邮箱的方式,每日汇总,然后一起评估 优化 slow query 。
二 工具实现
mail.py 脚本
sendSlowlog.py
注
本系统已经每小时将slow log 进行分割,关于如何切割,各位可以思考一下。
因为开发针对某系统做稳定性建设,需要对数据库系统的慢日志进行审计,检查优化。和开发沟通选择定期发送慢查询到开发的邮箱的方式,每日汇总,然后一起评估 优化 slow query 。
二 工具实现
mail.py 脚本
- #!/usr/bin/env python
- # -*- coding: utf-8 -*-
- # mail.py
- import os.path
- import time
- import sys
- import os
- import json
- import string
- import random
- import smtplib
- import time
- from datetime import date
- from email.mime.multipart import MIMEMultipart
- from email.mime.text import MIMEText
- from email.mime.image import MIMEImage
- from email.Header import Header
- from email.MIMEText import MIMEText
- from email.MIMEMultipart import MIMEMultipart
-
- mail_user="xxx@xxxx.com";
- mail_pass="xxxxx";
- mail_smtp_server="smtp.xxxxx.com";
- mail_smtp_port= 25
-
- def sendMail(contents,subject,attach,mail_to):
- lTime=str(time.strftime('%Y%m%d_%H',time.localtime(time.time())))
- msg = MIMEMultipart('related');
- if subject=="":
- subject='test';
- msg['Subject'] = subject+" Time:"+ lTime;
- msg['From'] = mail_user;
- msg['To'] = ";".join(mail_to) ;
- html="";
- for cont in contents:
- html = html+cont[0];
- msgHtml = MIMEMultipart('alternative');
- msgHtml.set_charset('UTF-8');
- msgAtt = MIMEText(open(attach,'rb').read(),'base','gbk');
- msgAtt["Content-Type"] = 'application/octet-stream'
- msgAtt["Content-Disposition"] = 'attachment; filename="'+subject+'"'
- msg.attach(msgAtt);
-
- try:
- smtp = smtplib.SMTP();
- smtp.connect(mail_smtp_server,mail_smtp_port)
- smtp.login(mail_user,mail_pass);
- smtp.sendmail(mail_user,mail_to,msg.as_string());
- smtp.close();
- except Exception,e:
- print str(e)
- #!/usr/bin/env python
- # -*- coding: utf-8 -*-
- import mail ##使用了上述脚本
- import sys
- import time
- import os
- import datetime
-
- from datetime import date
- from os.path import join, getsize
-
- def sendSlowlog(subject_info,attach,mail_to):
- size = os.path.getsize(attach)
- if(size > 1):
- mail.sendMail("",subject_info,attach,mail_to)
-
-
- if __name__ == '__main__':
- mail_to=["qilong.yangql@xxxx.com"]
- lDate=str(time.strftime('%Y%m%d',time.localtime(time.time())))
- lTime=str(time.strftime('%Y%m%d_%H',time.localtime(time.time())))
- subject_info="Slowlog of DBname" + lTime
- attach="/u01/my3306/log/slow_"+ lDate +"/slow.log."+lTime
- sendSlowlog(subject_info,attach,mail_to)
注
本系统已经每小时将slow log 进行分割,关于如何切割,各位可以思考一下。