【工具】利用python 发送MySQL 慢日志邮件

一 需求
    因为开发针对某系统做稳定性建设，需要对数据库系统的慢日志进行审计，检查优化。和开发沟通选择定期发送慢查询到开发的邮箱的方式,每日汇总，然后一起评估 优化 slow query 。

二 工具实现
mail.py   脚本

1. #!/usr/bin/env python
   
2. # -*- coding: utf-8 -*-
   
3. # mail.py
   
4. import os.path
   
5. import time
   
6. import sys
   
7. import os
   
8. import json
   
9. import string
   
10. import random
    
11. import smtplib
    
12. import time
    
13. from datetime import date
    
14. from email.mime.multipart import MIMEMultipart
    
15. from email.mime.text import MIMEText
    
16. from email.mime.image import MIMEImage
    
17. from email.Header import Header
    
18. from email.MIMEText import MIMEText
    
19. from email.MIMEMultipart import MIMEMultipart
    
20. 
    
21. mail_user="xxx@xxxx.com";
    
22. mail_pass="xxxxx";
    
23. mail_smtp_server="smtp.xxxxx.com";
    
24. mail_smtp_port= 25
    
25. 
    
26. def sendMail(contents,subject,attach,mail_to):
    
27.     lTime=str(time.strftime('%Y%m%d_%H',time.localtime(time.time())))
    
28.     msg = MIMEMultipart('related');
    
29.     if subject=="":
    
30.         subject='test';
    
31.     msg['Subject'] = subject+" Time:"+ lTime;
    
32.     msg['From'] = mail_user;
    
33.     msg['To'] = ";".join(mail_to) ;
    
34.     html="";
    
35.     for cont in contents:
    
36.         html = html+cont[0];
    
37.     msgHtml = MIMEMultipart('alternative');
    
38.     msgHtml.set_charset('UTF-8');
    
39.     msgAtt = MIMEText(open(attach,'rb').read(),'base','gbk');
    
40.     msgAtt["Content-Type"] = 'application/octet-stream'
    
41.     msgAtt["Content-Disposition"] = 'attachment; filename="'+subject+'"'
    
42.     msg.attach(msgAtt);
    
43. 
    
44.     try:
    
45.         smtp = smtplib.SMTP();
    
46.         smtp.connect(mail_smtp_server,mail_smtp_port)
    
47.         smtp.login(mail_user,mail_pass);
    
48.         smtp.sendmail(mail_user,mail_to,msg.as_string());
    
49.         smtp.close();
    
50.     except Exception,e:
    
51.         print str(e)

 sendSlowlog.py
 

1. #!/usr/bin/env python
   
2. # -*- coding: utf-8 -*-
   
3. import mail ##使用了上述脚本
   
4. import sys
   
5. import time
   
6. import os
   
7. import datetime
   
8. 
   
9. from datetime import date
   
10. from os.path import join, getsize
    
11. 
    
12. def sendSlowlog(subject_info,attach,mail_to):
    
13.     size = os.path.getsize(attach)
    
14.     if(size > 1):
    
15.        mail.sendMail("",subject_info,attach,mail_to)
    
16. 
    
17. 
    
18. if __name__ == '__main__':
    
19.    mail_to=["qilong.yangql@xxxx.com"]
    
20.    lDate=str(time.strftime('%Y%m%d',time.localtime(time.time())))
    
21.    lTime=str(time.strftime('%Y%m%d_%H',time.localtime(time.time())))
    
22.    subject_info="Slowlog of DBname" + lTime
    
23.    attach="/u01/my3306/log/slow_"+ lDate +"/slow.log."+lTime
    
24.    sendSlowlog(subject_info,attach,mail_to)

注
   本系统已经每小时将slow log 进行分割，关于如何切割，各位可以思考一下。