AWS RDS慢日志文件另存到ES并且每天发送邮件统计慢日志

1.背景：需要对aws rds慢日志文件归档到es，让开发能够随时查看。
2.需求：并且每天把最新的慢日志，过滤最慢的5条sql 发送给各个产品线的开发负责人。

3.准备：
aws ak/sk ，如果rds 在不同区域需要认证不同的ak/sk。
已经安装好的es这里不做详细展开。
安装好filebeat 用于上传日志到es。
安装mysqldumpslow 用于分析慢日志文件。

4.安装filebeat的重要文件

1):filebeat.yaml文件定义自己的慢日志索引名称

filebeat.config.modules:
  path: /usr/local/filebeat/modules.d/*.yml
  reload.enabled: true
  reload.period: 30s

setup.kibana:
  host: "10.0.139.96:5601"

filebeat.inputs:
- type: log
  enabled: true
  paths:
    - /usr/local/filebeat/logs/aurora-erp-mysql*.log
  fields:
    type: aurora-erp-mysql

- type: log
  enabled: true
  paths:
    - /usr/local/filebeat/logs/aurora-tms-mysql*.log
  fields:
    type: aurora-tms-mysql
- type: log
  enabled: true
  paths:
    - /usr/local/filebeat/logs/aurora-bi-mysql*.log
  fields:
    type: aurora-bi-mysql #类型跟下面匹配上

setup.ilm.enabled: false

output.elasticsearch:
  hosts: ["10.0.139.96:9200"]
  protocol: "http"
  indices:
    - index: "aurora-erp-mysql-%{+yyyy.MM.dd}"
      when.equals:
        fields.type: "aurora-erp-mysql"
    - index: "aurora-tms-mysql-%{+yyyy.MM.dd}"
      when.equals:
        fields.type: "aurora-tms-mysql"
    - index: "aurora-bi-mysql-%{+yyyy.MM.dd}"  #定义为自己的索引名
      when.equals:
        fields.type: "aurora-bi-mysql" #类型跟上面匹配上

2):filebeat 开启慢日志

cat   /usr/local/filebeat/modules.d/mysql.yml
# Module: mysql
# Docs: https://www.elastic.co/guide/en/beats/filebeat/8.2/filebeat-module-mysql.html

- module: mysql
  # Error logs
  error:
    enabled: false

    # Set custom paths for the log files. If left empty,
    # Filebeat will choose the paths depending on your OS.
    #var.paths:

  # Slow logs
  slowlog:
    enabled: true

    # Set custom paths for the log files. If left empty,
    # Filebeat will choose the paths depending on 

3):分析下载慢日志文件的脚本:
将最新的慢日志文件，查出最慢的5条sql保存到dbname.log文件用于每天发送邮件使用。

#!/bin/bash

cd  /usr/local/filebeat/logs 
erpmysql_name=$(ls -l aurora-erp-mysql-* | tail -1 | awk '{print $NF}')
tmsmysql_name=$(ls -l aurora-tms-mysql-* | tail -1 | awk '{print $NF}')
bimysql_name=$(ls -l aurora-bi-mysql-* | tail -1 | awk '{print $NF}')

/usr/bin/mysqldumpslow -s t -t 5  ${erpmysql_name}>/usr/local/filebeat/logs/aurora-erp-mysql.log
/usr/bin/mysqldumpslow -s t -t  5  ${tmsmysql_name}>/usr/local/filebeat/logs/aurora-tms-mysql.log
/usr/bin/mysqldumpslow -s t -t  5  ${bimysql_name}>/usr/local/filebeat/logs/aurora-bi-mysql.log

5.下载rds 慢日志文件到服务器脚本：

#!/bin/bash
source /etc/profile

export AWS_ACCESS_KEY_ID="xxxxxxx"
export AWS_SECRET_ACCESS_KEY="xxxxxx"

echo "start download aws mysql slow logs"
databases_list=(aurora-erp-mysql  aurora-tms-mysql aurora-bi-mysql)
dtime=$(date -u  +%F)
num="`expr $(date -u +%H) - 1`"
logdir="/usr/local/filebeat/logs"

#clean old logs
#cd ${logdir} &&   rm  aurora-*-mysql-*.log


for db in ${databases_list[@]};do 
  #获取循环库-每天慢查询文件名
  /usr/local/bin/aws rds describe-db-log-files --db-instance-identifier ${db}  --output text | awk '{print $3}' | sed '$d' | grep "mysql-slowquery" | tail -1>${db}.list
  #/usr/local/bin/aws rds describe-db-log-files --db-instance-identifier ${db} --output text | awk '{print $3}' | sed '$d' | grep "mysql-slowquery" | tail -n +2>${db}.list
  #aws rds describe-db-log-files --db-instance-identifier ${db}   --output text | awk '{print $3}' | sed '$d' |grep "mysql-slowquery.log">${db}.list
  #aws rds describe-db-log-files --db-instance-identifier ${db}   --output text | awk '{print $3}' | sed '$d' |grep "mysql-slowquery.log.${dtime}.${num}">${db}.list
  
  for slowfile_name in `cat ${db}.list`;do #将每个库-上一个小时生产的日志存放在本地日志中
    slow_name=$(echo "${slowfile_name}" | awk -F '.' '{print $3"."$4}')
    /usr/local/bin/aws rds download-db-log-file-portion --db-instance-identifier ${db}  --log-file-name ${slowfile_name}  --starting-token 0 --output text >${logdir}/${db}-${slow_name}.log
  done
 
done

#cut slowquery将最新的慢日志文件，查出最慢的5条sql保存到dbname.log文件用于每天发送邮件使用。
/bin/bash /srv/cut-slowlog.sh

#upload es 通过filebeat上传日志到es
/usr/bin/ps -ef | grep filebeat | awk '{print $2}'|head -1|xargs kill -9
cd /usr/local/filebeat && ./filebeat -e &

6.发送邮件python脚本

import smtplib
import datetime
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

def extract_queries(text):
    # 将文本按行分割
    lines = text.strip().split('\n')

    # 提取查询语句
    queries = []
    query = ''
    for line in lines:
        if line.startswith('Count:'):
            if query:
                queries.append(query.strip())
            query = line
        else:
            query += f'{line}'
    if query:
        queries.append(query.strip())

    return queries

def send_email(to_email, cc_email, log_file, subject):
    # 读取文本文件
    with open(log_file, 'r') as file:
        lines = file.readlines()

    # 判断行数是否大于等于2
    if len(lines) >= 4:
        # 创建HTML内容
        html_content = '<html><body>'
        html_content += '<ul>'
        
        for line in lines:
            html_content += f'<li>{line.strip().replace("<", "&lt;").replace(">", "&gt;")}</li>'
        
        html_content += '</ul>'
        html_content += '</body></html>'
    else:
        html_content = '<html><body>'
        html_content += '<p>(当前无慢日志，请等待)No logs found or the log file has less than 4 lines.</p>'
        return

    # 创建电子邮件
    msg = MIMEMultipart()
    msg['From'] = 'it_support@126.com'
    msg['To'] = ', '.join(to_email.split(','))
    msg['Cc'] = ', '.join(cc_email.split(','))
    msg['Subject'] = subject
    

    # 添加HTML内容到电子邮件
    msg.attach(MIMEText(html_content, 'html'))

    # 发送电子邮件
    with smtplib.SMTP_SSL('smtp.qiye.126.com', 465) as smtp:
        smtp.login('it_support@126.com', 'xxxxxx')
        recipients = to_email.split(',') + cc_email.split(',')
        for recipient in recipients:
            msg['To'] = recipient
            try:
                smtp.send_message(msg)
                current_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                print(f"[{current_time}] Email sent successfully to {recipient}")
            except Exception as e:
                current_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                print(f"[{current_time}] Failed to send email to {recipient}: {str(e)}")

# 发送ERP日志给指定邮箱
erp_log_file = '/usr/local/filebeat/logs/aurora-erp-mysql.log'
erp_recipient = 'wangfei@126.com,zhanghao@126.com,yubei@126.com'
cc_email = 'zhaigang@126.com,mei@126.com' #抄送指定人
erp_subject = 'ERP MySQL 【统计耗时最长的5条慢查询】'
send_email(erp_recipient, cc_email, erp_log_file, erp_subject)

# 发送TMS日志给另一个邮箱
tms_log_file = '/usr/local/filebeat/logs/aurora-tms-mysql.log'
tms_recipient = 'zhangxuewen@126.com,yangxi@126.com,wangfei@126.com'
tms_subject = 'TMS MySQL 【统计耗时最长的5条慢查询】'
send_email(tms_recipient, cc_email, tms_log_file, tms_subject)


# 发送Bi日志给另一个邮箱
tms_log_file = '/usr/local/filebeat/logs/aurora-bi-mysql.log'
tms_recipient = 'baojingyu@126.com,zhangyouhui@126.com,zhangxuewen@126.com'
tms_subject = 'Bi MySQL 【统计耗时最长的5条慢查询】'
send_email(tms_recipient, cc_email, tms_log_file, tms_subject)

7.效果图