青岛建设网站公司,黑彩网站怎么做,济南环保行业网站开发,北京商场核酸1.背景#xff1a;需要对aws rds慢日志文件归档到es#xff0c;让开发能够随时查看。 2.需求#xff1a;并且每天把最新的慢日志#xff0c;过滤最慢的5条sql 发送给各个产品线的开发负责人。
3.准备#xff1a; aws ak/sk #xff0c;如果rds 在不同区域需要认证不同的…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:5601filebeat.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: httpindices:- 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.log5.下载rds 慢日志文件到服务器脚本
#!/bin/bash
source /etc/profileexport AWS_ACCESS_KEY_IDxxxxxxx
export AWS_SECRET_ACCESS_KEYxxxxxxecho start download aws mysql slow logs
databases_list(aurora-erp-mysql aurora-tms-mysql aurora-bi-mysql)
dtime$(date -u %F)
numexpr $(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 htmlbodyhtml_content ulfor line in lines:html_content fli{line.strip().replace(, lt;).replace(, gt;)}/lihtml_content /ulhtml_content /body/htmlelse:html_content htmlbodyhtml_content p(当前无慢日志请等待)No logs found or the log file has less than 4 lines./preturn# 创建电子邮件msg MIMEMultipart()msg[From] it_support126.commsg[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_support126.com, 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 wangfei126.com,zhanghao126.com,yubei126.com
cc_email zhaigang126.com,mei126.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 zhangxuewen126.com,yangxi126.com,wangfei126.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 baojingyu126.com,zhangyouhui126.com,zhangxuewen126.com
tms_subject Bi MySQL 【统计耗时最长的5条慢查询】
send_email(tms_recipient, cc_email, tms_log_file, tms_subject)7.效果图
