Mysql集群慢日志分析（mysql集群调优）

使用脚本前，请先安装pt-query-digest工具。

#!/usr/bin/python
#-*- coding:utf-8 -*-
# ******************************************************
# Author       : mayi 
# Last modified: 2020-12-31
# Filename     : analysis_mysql_slow_report.py
# phone        : 17729842980
# Description  : pt-query-digest解析数据库慢日志，并通过邮件以附件形式发送给相关研发,使用paramiko方式收集多mysql日志
# ******************************************************
import os
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
import paramiko


#标题
title="【生产环境】Mysql慢日志分析报告"

#分析最近几小时内的查询,默认一周
times=168

# 第三方 SMTP 服务
mail_host="smtp.mxhichina.com" 
mail_user="mayi@devops.com"    
mail_pass="123456"
    
sender = 'mayi@devops.com'
To=['mayi@163.com']
Cc=['mayi@test.com']
#To=['403182580@qq.com']
#Cc=['dengyoucheng@ailecheng.com']

reciver = To+Cc

hosts_list=[
            {"hostname":"master","IP":"127.0.0.1","user":"root","password":"Mayi@123","mysql_slow":"/data/log/mysql/mysqld1_slowquery.log","slow_tmp":"/tmp/master_slow.log","slow_report_tmp":"Master_slow_report.log"},
            {"hostname":"slave01","IP":"192.168.0.25","user":"root","password":"Mayi@123","mysql_slow":"/data/log/mysql/mysqld02_slowquery.log","slow_tmp":"/tmp/slave01_slow.log","slow_report_tmp":"Slave01_slow_report.log"},
            {"hostname":"slave02","IP":"192.168.0.26","user":"root","password":"Mayi@123","mysql_slow":"/data/log/mysql/mysqld03_slowquery.log","slow_tmp":"/tmp/slave02_slow.log","slow_report_tmp":"Slave02_slow_report.log"}
        ]

#安装工具
#yum -y install epel-release percona-toolkit

def paramiko_slow_command(ip,user,password,cmd):
    client = paramiko.SSHClient()
    client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    try:
        # 连接的服务器
        client.connect(
            hostname=ip,
            username=user,
            password=password
        )

    except NoValidConnectionsError as e:
        return '主机%s连接失败' % (hostname)
    except AuthenticationException as e:
        return '主机%s密码错误' % (hostname)
    except Exception as e:
        return '未知错误：', e

    # 执行操作
    stdin, stdout, stderr = client.exec_command(cmd)
    # 结果放到stdout中，如果有错误将放到stderr中
    print(stdout.read().decode())
    
    # 关闭连接
    client.close()

def paramiko_get(ip,user,password,mysql_slow,slow_tmp):
    transport = paramiko.Transport((ip, 22))
    transport.connect(username=user, password=password)
    sftp = paramiko.SFTPClient.from_transport(transport)
    sftp.get(slow_tmp, slow_tmp)
    transport.close()


def sendmail():
    message = MIMEMultipart()
    message["Subject"] = Header(title, "utf-8")  # 标题
    #收件人
    message['To'] = ",".join(To)
    message['Cc'] = ','.join(Cc) 

    # 邮件正文
    context='Mysql慢日志分析报告如附件,请各位研发同学关注.'
    message.attach(payload=MIMEText(_text=context, _subtype="plain", _charset="utf-8"))

    for list in hosts_list:
        #以附件形式发送邮件
        list["hostname"] = MIMEText(open("/tmp/"+list["slow_report_tmp"], "rb").read(), _subtype="base64", _charset="utf-8")
        list["hostname"].add_header('Content-Disposition', 'attachment', filename=list["slow_report_tmp"])
        message.attach(list["hostname"])

    try:
        smtpObj = smtplib.SMTP() 
        smtpObj.connect(mail_host, 80)
        smtpObj.login(mail_user,mail_pass)  
        smtpObj.sendmail(sender, reciver, message.as_string())
        print ("邮件发送成功")
    except smtplib.SMTPException:
        print ("Error: 无法发送邮件")


if __name__ == "__main__":
    for list in hosts_list:
        ip=list["IP"]
        user=list["user"]
        password=list["password"]
        mysql_slow=list["mysql_slow"]
        slow_tmp=list["slow_tmp"]
        slow_report_tmp=list["slow_report_tmp"]
        cmd="/usr/bin/pt-query-digest --since=%sh %s > %s" %(times,mysql_slow,slow_tmp)
        if ip == "127.0.0.1" or ip =="localhost":
            os.system(cmd)
        else: 
            paramiko_slow_command(ip,user,password,cmd)
            paramiko_get(ip,user,password,mysql_slow,slow_tmp)

        #清理文件中的空行
        infopen = open(slow_tmp, 'r')
        outfopen = open("/tmp/"+slow_report_tmp, 'w')
  
        lines = infopen.readlines()
        for line in lines:
            if line.split():
                outfopen.writelines(line)
            else:
                outfopen.writelines("")
        infopen.close()
        outfopen.close()

    sendmail()

hosts_list列表说明：

1. hostname：Mysql主机名称
2. IP：mysql主机IP地址
3. user：ssh连接用户
4. password：ssh用户密码
5. mysql_slow：主机上mysql存放目录
6. slow_tmp：pt-query-digest分析后，临时生产的报告
7. slow_report_tmp：最终生成报告文件。