MySQL 慢查询日志分析(Filebeat+Elasticsearch+DataEase)

先看成果，本仪表板使用开源数据可视化分析工具 DataEase 制作，可以通过上方的搜索框过滤查询语句，也可以过滤日志产生的日期范围。

1. Filebeat 介绍

Filebeat 是 Elastic 官方提供的一个轻量级的日志采集器，主要用于文件的数据采集。基于 Golang 开发，可以安装到想要日志的服务器或者主机上来读取对应的数据，并发送到 Elasticsearch 或者 Logstash 上，数据量大时，还可以先输出到 Kafka、Redis等中间件上。

根据官方文档的解释，Filebeat 主要包含两个主要组件：input 和 harvesters。

harvester: 用于按行读取单个文件的内容。每个文件都会启动一个 harvester，harvester 负责打开和关闭文件。Filebeat 使用 registry file 记录文件的偏移量，即上一次读取的位置，下一次打开文件时会从 registry file 读取偏移量然后继续读取数据。

input：负责管理 harvester 并且找到所有符合读取条件的文件。如果输入类型为 log，则 input 会在驱动器上找到与定义的路径符合的文件，并会给每个文件都启动一个harvester。

其次再配置 Output 组件将获取的数据进行输出。

Filebeat 中支持多种服务的数据采集，包括但不仅限于Mysql，MongoDB，Nginx，Redis，ActiveMQ，PostgreSQL，RabbitMQ，Tomcat 等等。更多可查看官方文档。

2. DataEase 介绍

DataEase 是开源的数据可视化分析工具，帮助用户快速分析数据并洞察业务趋势，从而实现业务的改进与优化。DataEase 支持丰富的数据源连接，能够通过拖拉拽方式快速制作图表，并可以方便地与他人分享。

3. 配置 MySQL 慢查询日志

在 my.cnf 中添加如下配置

slow_query_log=on
slow_query_log_file=/var/log/mysql/slow_query.log
long_query_time=2

4. 安装 Elasticsearch、Kibana、Filebeat

1）添加 elastic 的 yum 源

使用如下内容创建文件 /etc/yum.repos.d/elastic.repo

[elastic-7.x]
name=Elastic repository for 7.x packages
baseurl=https://artifacts.elastic.co/packages/7.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md

2）使用 yum 命令安装组件

可以使用 yum search elastic --show-duplicates 查看所有可用的组件及版本，此处以安装 7.17.2 版本为例，需保证各组件版本一致。

yum -y install elasticsearch-7.17.2
yum -y install kibana-7.17.2
yum -y install filebeat-7.17.2

5. 配置 Kibana

修改 /etc/kibana/kibana.yml 中的如下内容：

# 允许外部访问
server.host: "0.0.0.0"
# 服务访问地址
server.publicBaseUrl: "http://<你的服务请求地址>"
# ElasticSearch 的用户密码
elasticsearch.username: "elastic"
elasticsearch.password: "password"
# 使用中文界面
i18n.locale: "zh-CN"

6. 配置 Filebeat

1）启用 MySQL 模块

filebeat modules enable mysql

2）修改 MySQL 日志文件

修改文件 /etc/filebeat/modules.d/mysql.yml 中的日志路径

# Module: mysql
# Docs: https://www.elastic.co/guide/en/beats/filebeat/7.16/filebeat-module-mysql.html

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

    # Set custom paths for the log files. If left empty,
    # Filebeat will choose the paths depending on your OS.
    var.paths: ["/var/log/mysql/mysql.err"]

  # Slow logs
  slowlog:
    enabled: true

    # Set custom paths for the log files. If left empty,
    # Filebeat will choose the paths depending on your OS.
    var.paths: ["/var/log/mysql/slow_query.log"]

3）修改 Filebeat 配置文件

在 /etc/filebeat/filebeat.yml 中配置如下内容，此处未列出内容不做修改即可。

filebeat.config.modules:
  # Glob pattern for configuration loading
  path: ${path.config}/modules.d/*.yml
  # Set to true to enable config reloading
  reload.enabled: true
  # Period on which files under path should be checked for changes
  reload.period: 2s

setup.kibana:
  host: "10.1.1.123:5601"

output.elasticsearch:
  # elasticsearch 访问地址
  hosts: ["10.1.1.123:9200"]
  # Protocol - either `http` (default) or `https`.
  protocol: "http"
  # elasticsearch 用户密码
  username: "elastic"
  password: "password"

4）设置初始环境

如果此步骤报错，需解决报错

filebeat setup -e

7. 启动服务

全部配置好后，可以使用 systemctl enable 命令配置开机自启

systemctl enable elasticsearch
systemctl enable kibana
systemctl enable filebeat

使用 systemctl start 命令来启动服务

systemctl start elasticsearch
systemctl start kibana
systemctl start filebeat

8. 验证

全部服务启动完成后，可查看 Elasticsearch 中的索引。

Filebeat 默认会生成如下格式的索引，可在配置文件中自行修改，具体修改方式请自行百度。

filebeat-<版本号>-<年>.<月>.<日>

9. 接入 DataEase 并制作图表

在 DataEase 中创建 Elasticsearch 数据源，并使用 SQL 创建慢查询分析数据集，SQL 如下：

select DATETIME_FORMAT("@timestamp",'yyyy-MM-dd HH:mm:ss')  as time
        ,REPLACE(UCASE(substring("mysql.slowlog.query",0,locate(' ',"mysql.slowlog.query",0))), CHAR(10) ,'') operation
        ,"agent.ephemeral_id"
        ,"agent.hostname"
        ,"agent.id"
        ,"agent.name"
        ,"agent.type"
        ,"agent.version"
        ,"container.id"
        ,"ecs.version"
        ,"event.category"
        ,"event.dataset"
        ,"event.duration"
        ,"event.ingested"
        ,"event.kind"
        ,"event.module"
        ,"event.type"
        ,"fileset.name"
        ,"host.architecture"
        ,"host.containerized"
        ,"host.hostname"
        ,"host.id"
        ,"host.ip"
        ,"host.mac"
        ,"host.name"
        ,"host.os.codename"
        ,"host.os.family"
        ,"host.os.kernel"
        ,"host.os.name"
        ,"host.os.name.text"
        ,"host.os.platform"
        ,"host.os.type"
        ,"host.os.version"
        ,"input.type"
        ,"log.file.path"
        ,"log.flags"
        ,"log.offset"
        ,"mysql.slowlog.current_user"
        ,"mysql.slowlog.lock_time.sec"
        ,"mysql.slowlog.query"
        ,"mysql.slowlog.rows_examined"
        ,"mysql.slowlog.rows_sent"
        ,"mysql.thread_id"
        ,"service.type"
        ,"source.ip"
        ,"user.name"
        ,"user.name.text" from "filebeat-7.16.2-2022.12.15-000001"

创建完成后导入模板并切换数据集即可，操作流程可参考视频：

上传视频封面

好的标题可以获得更多的推荐及关注者

模板自取链接：

链接: pan.baidu.com/s/1FDqipvIT… 提取码: q59e