python处理mysql慢查询日志

# -*- 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和其他人工的语句。

转载于:https://www.cnblogs.com/qianchengprogram/p/6265451.html