利用Python 發送MySQL 慢日誌郵件

利用Python 發送MySQL 慢日誌郵件

一 需求
    因為開發針對某系統做穩定性建設，需要對資料庫系統的慢日誌進行審計，檢查最佳化。和開發溝通選擇定期發送慢查詢到開發的郵箱的方式,每日匯總，然後一起評估 最佳化slow query 。

二 工具實現
mail.py  指令碼

#!/usr/bin/env python
 
# -*- coding: utf-8 -*-

# mail.py

import os.path

import time

import sys

import os

import json

import string

import random

import smtplib

import time

from datetime import date

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from email.mime.image import MIMEImage

from email.Header import Header

from email.MIMEText import MIMEText

from email.MIMEMultipart import MIMEMultipart

 

mail_user="xxx@xxxx.com";

mail_pass="xxxxx";

mail_smtp_server="smtp.xxxxx.com";

mail_smtp_port= 25

 

def sendMail(contents,subject,attach,mail_to):

    lTime=str(time.strftime('%Y%m%d_%H',time.localtime(time.time())))

    msg = MIMEMultipart('related');

    if subject=="":

        subject='test';

    msg['Subject'] = subject+" Time:"+ lTime;

    msg['From'] = mail_user;

    msg['To'] = ";".join(mail_to) ;

    html="";

    for cont in contents:

        html = html+cont[0];

    msgHtml = MIMEMultipart('alternative');

    msgHtml.set_charset('UTF-8');

    msgAtt = MIMEText(open(attach,'rb').read(),'base','gbk');

    msgAtt["Content-Type"] = 'application/octet-stream'

    msgAtt["Content-Disposition"] = 'attachment; filename="'+subject+'"'

    msg.attach(msgAtt);

 

    try:

        smtp = smtplib.SMTP();

        smtp.connect(mail_smtp_server,mail_smtp_port)

        smtp.login(mail_user,mail_pass);

        smtp.sendmail(mail_user,mail_to,msg.as_string());

        smtp.close();

    except Exception,e:

        print str(e)
 sendSlowlog.py
 

#!/usr/bin/env python
 
# -*- coding: utf-8 -*-

import mail ##使用了上述指令碼

import sys

import time

import os

import datetime

 

from datetime import date

from os.path import join, getsize

 

def sendSlowlog(subject_info,attach,mail_to):

    size = os.path.getsize(attach)

    if(size > 1):

      mail.sendMail("",subject_info,attach,mail_to)

 

 

if __name__ == '__main__':

  mail_to=["qilong.yangql@xxxx.com"]

  lDate=str(time.strftime('%Y%m%d',time.localtime(time.time())))

  lTime=str(time.strftime('%Y%m%d_%H',time.localtime(time.time())))

  subject_info="Slowlog of DBname" + lTime

  attach="/u01/my3306/log/slow_"+ lDate +"/slow.log."+lTime

  sendSlowlog(subject_info,attach,mail_to)

注
  本系統已經每小時將slow log 進行分割，關於如何切割，各位可以思考一下。

本文永久更新連結地址：