mysql慢查询日志处理

  1 # -*- coding:utf8 -*-
  2 ‘‘‘
  3 Created on 2017年1月9日
  4 
  5 @author: qiancheng
  6 ‘‘‘
  7 
  8 import re
  9 import os
 10 from email.mime.multipart import MIMEMultipart
 11 from email.mime.text import MIMEText
 12 from email.header import Header
 13 import smtplib
 14 import subprocess
 15 
 16 class mail_to:
 17     def __init__(self,_message,_file):
 18         self.msg=_message
 19         mail_host="115.231.109.86"  #smtp-server
 20         mail_user="qiancheng"    #username
 21         mail_pass="cocacola123"  #password
 22         sender = ‘qiancheng@showjoy.com‘
 23         receivers = [‘xxxxxx@showjoy.com‘]
 24         message = MIMEMultipart()
 25         message[‘From‘] = Header("qiancheng@showjoy.com<qiancheng@showjoy.com>")
 26         message[‘To‘] =  Header("xxxxxx@showjoy.com<yunwei@showjoy.com>")
 27         #邮件标题
 28         subject = ‘本周慢查询日志‘
 29         message[‘Subject‘] = Header(subject,‘utf-8‘)
 30         #邮件正文
 31         message.attach(MIMEText(self.msg,‘plain‘,‘utf-8‘))
 32         #txt附件
 33         mail_file=open(_file,‘rb‘)
 34         load=MIMEText(mail_file.read(),‘base64‘,‘utf-8‘)
 35         load[‘Content-Type‘]=‘application/octet-stream‘
 36         load["Content-Disposition"] = ‘attachment; filename="shop-slow.log.txt"‘
 37         message.attach(load)
 38         
 39         try:
 40             smtpObj=smtplib.SMTP()
 41             smtpObj.connect(mail_host)
 42             smtpObj.login(mail_user,mail_pass)
 43             smtpObj.sendmail(sender, receivers, message.as_string())
 44             smtpObj.close()
　　             mail_file.close()
 45         except smtplib.SMTPException:
 46             print("Error: 无法发送邮件")
 47 
 48 class analysis_log(object):
 49     def __init__(self,_filename,_wfilename):
 50         self.wfilename = _wfilename
 51         self.filename = _filename
 52     def do_openfile(self):
 53         if os.path.exists(self.filename):
 54             f=open(self.filename, ‘r‘)
 55             w=open(self.wfilename,‘w‘)
 56             start_value=0
 57             continue_value=0
 58             while True:
 59                 line=f.readline()
 60                 if line:
 61                     check_start = self.do_analysis(line)
 62                     if check_start == ‘success_app_success‘:
 63                         start_value=1
 64                     if check_start == ‘success_app_fail‘:
 65                         start_value=0
 66                         continue
 67                     if check_start == ‘fail‘:
 68                         continue_value=1
 69                     if start_value == 1 or (start_value == 1 and continue_value == 1):
 70                         print line
 71                         w.write(line)
 72                     else:
 73                         
 74                         continue
 75                 else:
 76                     break
 77             f.close()
 78             w.close()
 79         else:
 80             print (self.filename+"not exists")
 81     def do_analysis(self,_line):
 82         if re.search(‘User@Host‘,_line) == None:
 83             result=‘fail‘
 84         else:
 85             result=‘success‘
 86         if re.search(‘shop\[shop\]‘,_line) == None:
 87             result= result+‘_‘+‘app_fail‘
 88         else:
 89             result= result+‘_‘+‘app_success‘
 90         
 91         return result
 92             
 93 if __name__ == ‘__main__‘:
 94     subprocess.call(‘scp root@s-c12:/usr/local/mysql/data/dbdata_3310/S-C12-slow.log /tmp/S-C12-slow.log‘,shell=True)
 95     subprocess.call(‘ssh root@s-c12 \‘echo "" > /usr/local/mysql/data/dbdata_3310/S-C12-slow.log\‘‘,shell=True)
 96     file_name=‘/tmp/S-C12-slow.log‘
 97     wfile_name=‘/tmp/S-C12-slow.log.txt‘
 98     analysis=analysis_log(file_name,wfile_name)
 99     analysis.do_openfile()
100     mail_to(‘程序产生的慢查询日志‘,wfile_name)