基于Python实现自动慢查询分析，邮件自动发送

rr322w

由两个程序文件组成，第一个程序实现慢查询分析，文件上传

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

#!/usr/local/python27/bin/python2.7 from fabric.api import * from fabric.context_managers import * from fabric.contrib.console import confirm import sys import os import time import datetime sdir = '/alidata/server/mysql/data/' ddir = '/root/' env.user='root' env.roledefs = {     'master_db':['192.168.1.2'],     'slave_db':['192.168.1.3'],     'quliao_db':['192.168.1.4'] } env.passwords = {     'root@192.168.1.2:22':'123456',     'root@192.168.1.3:22':'123456',     'root@192.168.1.4:22':'123456' } #慢查询分析函数，这个函数会调用服务端本地的工具分析慢查询，之后会清空慢查询文件。 def my_slow(ori_log,save_log,result_log):     run('cp '+sdir+ori_log+' '+save_log)     run('/usr/local/bin/mysqlsla -lt slow '+ddir+save_log+' -sort c_sum -top 10 >'+'/root/'+result_log)     run('echo '+''+'>'+sdir+ori_log) #用于下载服务端分析好的慢查询文件保存到本地新建的目录中 def my_get(fname):     with lcd('/alidata/slow_log'):         dname = time.strftime('%Y%m%d',time.localtime(time.time()))         with cd('/root/'):             get(fname,dname+'/') #定义文件删除函数，接受两个参数，用于删除拷贝出来的慢查询文件和分析之后的慢查询文件。 def remove(f1,f2):     with cd('/root'):         run('rm -f '+f1+' '+f2 ) #在客户端机器创建一个以当前日期为名称的文件夹，用于保存分析好的慢查询文件 @runs_once def mk_dir():     with lcd('/alidata/slow_log'):         dname = time.strftime('%Y%m%d',time.localtime(time.time()))         local('mkdir '+dname) @roles('slave_db') def sdb():     print ("run slave db pro")     run("ls /alidata/server/mysql/data")     my_slow('slow.log','mysql-r-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log','mysql-read-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     my_get('mysql-read-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     remove('mysql-r-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log','mysql-read-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log') @roles('master_db') def mdb():     print ('run master db pro')     run("ls /alidata/server/mysql/data")     my_slow('AY131008162509536ef1Z-slow.log','mysql-w-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log','mysql-write-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     my_get('mysql-write-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     remove('mysql-w-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log','mysql-write-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log') @roles('quliao_db') def ldb():     print ('run quliao db pro')     run("ls /alidata/server/mysql/data")     my_slow('slow.log','mysql-q-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log','mysql-quliao-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     my_get('mysql-quliao-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     remove('mysql-q-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log','mysql-quliao-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log') def deploy():     mk_dir()     execute(sdb)     execute(mdb)     execute(ldb) #最后这里是调用客户端本地的邮件发送程序把刚刚抓回来的慢查询日志以邮件附件的形式发送给管理员。     local('/root/tuchao/sendma.py')

第二个程序负责把分析好的日志以邮件附件的形式发送给管理员

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

#!/usr/local/python27/bin/python2.7 # coding=utf8 import smtplib import time import datetime from email.mime.text import MIMEText from email import encoders from email.header import Header from email.utils import parseaddr, formataddr from email.MIMEMultipart import MIMEMultipart from email.MIMEBase import MIMEBase from_addr = 'tuchao@mail.admin.net' password = '111111' smtp_server = '114.234.116.2' to_addr = ['123456@qq.com','1234567@qq.com'] slow_log_path = '/alidata/slow_log/'+time.strftime('%Y%m%d',time.localtime(time.time()))+'/' def _format_addr(s):     name,addr = parseaddr(s)     return formataddr((Header(name,'utf-8').encode(),addr.encode('utf-8') if isinstance(addr, unicode) else addr)) #需要将多个对象组合起来，在这里构建一个MIMEMultipart对象。 msg = MIMEMultipart() msg['From'] = _format_addr(u'来自运维技术XX <%s>' % from_addr) msg['To'] = _format_addr(u'管理员 <%s>' % to_addr) msg['Subject'] = Header(u'MySQL慢查询日志', 'utf-8').encode() msg.attach(MIMEText('<html><body><h3>mysql-read:从库读慢查询</h3>' +     '<p><h3>mysql-write:主库读写慢查询</h3></p>'     '<p><h3>mysql-quliao:外部程序库读写慢查询</h3></p>'     '<p><img src="cid:1"></p>' +     '</body></html>', 'html', 'utf-8')) with open(slow_log_path+'mysql-read-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log','rb') as f:     mime = MIMEBase('text/plain','txt',filename='mysql-read-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     mime.add_header('Content-Disposition','attachment',filename='mysql-read-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     mime.add_header('Content-ID','<0>')     mime.add_header('X-Attachment-Id','0')     mime.set_payload(f.read())     encoders.encode_base64(mime)     msg.attach(mime) with open(slow_log_path+'mysql-write-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log','rb') as f:     mime = MIMEBase('text/plain','txt',filename='mysql-write-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     mime.add_header('Content-Disposition','attachment',filename='mysql-write-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     mime.add_header('Content-ID','<0>')     mime.add_header('X-Attachment-Id','0')     mime.set_payload(f.read())     encoders.encode_base64(mime)     msg.attach(mime) with open(slow_log_path+'mysql-quliao-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log','rb') as f:     mime = MIMEBase('text/plain','txt',filename='mysql-quliao-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     mime.add_header('Content-Disposition','attachment',filename='mysql-quliao-'+time.strftime('%Y%m%d',time.localtime(time.time()))+'.log')     mime.add_header('Content-ID','<0>')     mime.add_header('X-Attachment-Id','0')     mime.set_payload(f.read())     encoders.encode_base64(mime)     msg.attach(mime) with open('/alidata/slow_log/aamy.jpg','rb') as f:     mime = MIMEBase('image','jpg',filename='aamy.jpg')     mime.add_header('Content-Disposition','attachment',filename='aamy.jpg')     mime.add_header('Content-ID','<1>')     mime.add_header('X-Attachment-Id','0')     mime.set_payload(f.read())     encoders.encode_base64(mime)     msg.attach(mime) server = smtplib.SMTP(smtp_server,25) server.set_debuglevel(0) server.login(from_addr,password) server.sendmail(from_addr,to_addr,msg.as_string()) server.quit()

构造一个邮件对象就是一个Messag对象
构造一个MIMEText对象，就表示一个文本邮件对象
构造一个MIMEImage对象，就表示一个作为附件的图片
要把多个对象组合起来，就用MIMEMultipart对象
MIMEBase可以表示任何对象