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/*.ymlreload.enabled: truereload.period: 30ssetup.kibana:host: "10.0.139.96:5601"filebeat.inputs:
- type: logenabled: truepaths:- /usr/local/filebeat/logs/aurora-erp-mysql*.logfields:type: aurora-erp-mysql- type: logenabled: truepaths:- /usr/local/filebeat/logs/aurora-tms-mysql*.logfields:type: aurora-tms-mysql
- type: logenabled: truepaths:- /usr/local/filebeat/logs/aurora-bi-mysql*.logfields:type: aurora-bi-mysql #类型跟下面匹配上setup.ilm.enabled: falseoutput.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 logserror:enabled: false# Set custom paths for the log files. If left empty,# Filebeat will choose the paths depending on your OS.#var.paths:# Slow logsslowlog: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/bashcd  /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/profileexport 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-*.logfor 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}.listfor 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}.logdonedone#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 MIMETextdef 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 = lineelse:query += f'{line}'if query:queries.append(query.strip())return queriesdef send_email(to_email, cc_email, log_file, subject):# 读取文本文件with open(log_file, 'r') as file:lines = file.readlines()# 判断行数是否大于等于2if 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'] = '[e-mail]'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('[e-mail]', 'xxxxxx')recipients = to_email.split(',') + cc_email.split(',')for recipient in recipients:msg['To'] = recipienttry: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 = '[e-mail],[e-mail],[e-mail]'
cc_email = '[e-mail],[e-mail]' #抄送指定人
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 = '[e-mail],[e-mail],[e-mail]'
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 = '[e-mail],[e-mail],[e-mail]'
tms_subject = 'Bi MySQL 【统计耗时最长的5条慢查询】'
send_email(tms_recipient, cc_email, tms_log_file, tms_subject)

7.效果图