python访问数据库日志_python处理mysql慢查询日志

#-*- coding:utf8 -*-

'''Created on 2017年1月9日

@author: qiancheng'''

importreimportosfrom email.mime.multipart importMIMEMultipartfrom email.mime.text importMIMETextfrom email.header importHeaderimportsmtplibimportsubprocessimporttimeimportsysimportthreadingclassmail_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")

message['To'] = Header("qiancheng@showjoy.com")#message['To'] = Header("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()exceptsmtplib.SMTPException:print("Error: 无法发送邮件")classanalysis_log(object):def __init__(self,_dbname,_filename,_wfilename):

self.db_name=_dbname

self.wfilename=_wfilename

self.filename=_filenamedefdo_openfile(self):ifos.path.exists(self.filename):

f=open(self.filename, 'r')

w=open(self.wfilename,'w')

start_value=0

continue_value=0whileTrue:

line=f.readline()ifline:

check_start=self.do_analysis(line)if check_start == 'success_app_success':

start_value=1

if check_start == 'success_app_fail':

start_value=0continue

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:breakf.close()

w.close()

self.mail_to_someone()else:print (self.filename+"not exists")defdo_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'

returnresultelse:#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'

defmail_to_someone(self):

mail_to('程序产生的慢查询日志，'+self.db_name+'数据库',self.wfilename)if __name__ == '__main__':for dbname insys.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。空格隔开！")