# -*- coding:utf8 -*- ''' Created on 2017年1月9日 @author: qiancheng ''' import re import os from email.mime.multipart import MIMEMultipart from email.mime.text import MIMEText from email.header import Header import smtplib import subprocess import time import sys import threading class mail_to: def __init__(self,_message,_file): self.msg=_message mail_host="x.x.x.x" #smtp-server mail_user="qiancheng" #username mail_pass="xxxxxxx" #password sender = 'qiancheng@showjoy.com' receivers = ['qiancheng@showjoy.com'] #receivers = ['qiancheng@showjoy.com'] message = MIMEMultipart() message['From'] = Header("qiancheng@showjoy.com<qiancheng@showjoy.com>") message['To'] = Header("qiancheng@showjoy.com<qiancheng@showjoy.com>") #message['To'] = Header("qiancheng@showjoy.com<qiancheng@showjoy.com>") #邮件标题 subject = '本周慢查询日志' message['Subject'] = Header(subject,'utf-8') #邮件正文 message.attach(MIMEText(self.msg,'plain','utf-8')) #txt附件 mail_file=open(_file,'rb') load=MIMEText(mail_file.read(),'base64','utf-8') load['Content-Type']='application/octet-stream' load["Content-Disposition"] = 'attachment; filename="slow.log.txt"' message.attach(load) try: smtpObj=smtplib.SMTP() smtpObj.connect(mail_host) smtpObj.login(mail_user,mail_pass) smtpObj.sendmail(sender, receivers, message.as_string()) smtpObj.close() mail_file.close() except smtplib.SMTPException: print("Error: 无法发送邮件") class analysis_log(object): def __init__(self,_dbname,_filename,_wfilename): self.db_name=_dbname self.wfilename = _wfilename self.filename = _filename def do_openfile(self): if os.path.exists(self.filename): f=open(self.filename, 'r') w=open(self.wfilename,'w') start_value=0 continue_value=0 while True: line=f.readline() if line: check_start = self.do_analysis(line) if check_start == 'success_app_success': start_value=1 if check_start == 'success_app_fail': start_value=0 continue if check_start == 'fail': continue_value=1 if re.search('timedate',check_start) != None and start_value == 1 and continue_value == 0: w.write(check_start) continue if start_value == 1 or (start_value == 1 and continue_value == 1): #print check_start w.write(line) else: continue else: break f.close() w.close() self.mail_to_someone() else: print (self.filename+"not exists") def do_analysis(self,_line): if re.search('SET timestamp',_line) == None: if re.search('User@Host',_line) == None: result='fail' else: result='success' if re.search(self.db_name+'\['+self.db_name+'\]',_line) == None: result= result+'_'+'app_fail' else: result= result+'_'+'app_success' return result else: #timestamp=_line.split('=')[-1] timestamp=float(re.findall('\d+',_line)[-1]) _time = time.localtime(timestamp) d=str(time.strftime("%Y-%m-%d %H:%M:%S",_time)) return 'SET timedate='+d+'\n' def mail_to_someone(self): mail_to('程序产生的慢查询日志,'+self.db_name+'数据库',self.wfilename) if __name__ == '__main__': for dbname in sys.argv: if dbname == 'shop': file_name='C:\\work\\showjoy\\S-C12-slow.log' wfile_name='C:\\work\\showjoy\\S-C12-slow.log.txt' #subprocess.call('scp root@s-c12:/usr/local/mysql/data/dbdata_3310/S-C12-slow.log /tmp/S-C12-slow.log',shell=True) #subprocess.call('ssh root@s-c12 \'echo "" > /usr/local/mysql/data/dbdata_3310/S-C12-slow.log\'',shell=True) analysis1=analysis_log(dbname,file_name,wfile_name) t1=threading.Thread(target=analysis1.do_openfile()) elif dbname == 'trade': file_name='C:\\work\\showjoy\\S-C9-slow.log' wfile_name='C:\\work\\showjoy\\S-C9-slow.log.txt' #subprocess.call('scp root@s-c12:/usr/local/mysql/data/dbdata_3310/S-C12-slow.log /tmp/S-C12-slow.log',shell=True) #subprocess.call('ssh root@s-c12 \'echo "" > /usr/local/mysql/data/dbdata_3310/S-C12-slow.log\'',shell=True) analysis2=analysis_log(dbname,file_name,wfile_name) t2=threading.Thread(target=analysis2.do_openfile()) else: print ("请输入数据库名字,如shop,trade。空格隔开!")
日志处理,timstamp 转换成datetime,把提取shop,这种程序跑出来的sql,去除root和其他人工的语句。