智慧医院智能运维实训指导书-实训指南03：实现MySQL数据库日志分析

棒棒堂智能运维
693 阅读27分钟

一、实训目的

1、掌握MySQL数据库的安装与日志配置；

2、掌握基于Filebeat实现MySQL数据库日志采集；

3、掌握基于Logstash实现MySQL数据库日志的清洗；

4、掌握基于Kibana实现MySQL数据库日志的可视化分析。

二、实训学时

6 学时

三、实训类型

综合性

四、实训需求

1、硬件

每人配备计算机 1 台。

2、软件

安装Edge、Firefox、Chrome等最新版本浏览器，安装Mobaxterm软件。

3、网络

本地主机能够访问教学云计算平台，虚拟机按照配置指南配置网络。

4、工具

无。

五、实训任务

1、完成MySQL数据库的安装与日志配置；

2、完成MySQL数据库日志采集、清洗与存储；

3、完成MySQL数据库日志的可视化分析。

六、实训环境

本实训使用《实训指南02：实现Linux Syslog日志分析》中创建的虚拟机Labs-ELK-VM-102。

七、实训内容及步骤

1、安装MySQL数据库

（1）在主机Labs-ELK-VM-102上安装MySQL数据库，安装完成查看MySQL版本，参考命令如下。

# 安装MySQL
[root@Labs-ELK-VM-102 ~]# yum install -y mysql-server
# 查看MySQL版本
[root@Labs-ELK-VM-102 ~]# mysql -V
mysql  Ver 8.0.42 for Linux on x86_64 (Source distribution)

（2）启动MySQL数据库，设置MySQL数据库开机自启动，查看服务状态，参考命令如下。

# 启动MySQL
[root@Labs-ELK-VM-102 ~]# systemctl start mysqld
# 设置MySQL开机自启动
[root@Labs-ELK-VM-102 ~]# systemctl enable mysqld
# 查看MySQL服务状态
[root@Labs-ELK-VM-102 ~]# systemctl status mysqld

（3）使用mysql_secure_installation命令配置MySQL安全启动配置向导（设置验证密码组件、设置密码验证策略级别为MEDIUM、设置root的密码、删除匿名用户、禁止远程root登录、删除测试数据库），参考命令如下。

# MySQL的安全性配置
[root@Labs-ELK-VM-102 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

2、配置MySQL数据库日志

（1）修改MySQL数据库配置文件/etc/my.cnf.d/mysql-server.cnf，启用MySQL数据库错误日志、慢查询日志、通用查询日志，参考命令如下。

# 编辑配置文件/etc/my.cnf.d/mysql-server.cnf
[root@Labs-ELK-VM-102 logs]# vi /etc/my.cnf.d/mysql-server.cnf
--------------------/etc/my.cnf.d/mysql-server.cnf--------------------
[mysqld]
# 在配置文件中添加以下内容：
# 配置错误日志输出格式为json
log_error_services = 'log_filter_internal; log_sink_json'
# 配置错误日志消息优先级（默认值2） 1：ERROR  2：ERROR,WARNING  3：ERROR, WARNING, INFORMATION
log_error_verbosity=3

# 启用慢查询日志
slow_query_log=1
# 设置慢查询时间
long_query_time=1
# 启用额外的慢查询日志信息
log_slow_extra=1
# 配置慢查询日志文件位置
slow_query_log_file=/var/log/mysql/slow-query.log

# 启用通用查询日志
general_log=1
# 配置通用查询日志文件位置
general_log_file=/var/log/mysql/query.log
--------------------/etc/my.cnf.d/mysql-server.cnf--------------------

# 重启MySQL数据库服务，使配置生效
[root@Labs-ELK-VM-102 logs]# systemctl restart mysqld

3、安装Filebeat日志采集器

在主机Labs-ELK-VM-102上下载Filebeat日志采集器，解压安装包到/opt目录下，并重命名解压目录，参考命令如下。

#下载rpm包
[root@Labs-ELK-VM-102 ~]# curl -L -O https://artifacts.elastic.co/downloads/beats/filebeat/filebeat-9.0.4-linux-x86_64.tar.gz
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 67.9M  100 67.9M    0     0  11.0M      0  0:00:06  0:00:06 --:--:-- 14.0M

#解压安装包到/opt目录下
[root@Labs-ELK-VM-102 ~]# tar -zxvf filebeat-9.0.4-linux-x86_64.tar.gz -C /opt

# 进入/opt目录下，重命名解压目录
[root@Labs-ELK-VM-102 ~]# cd /opt/
[root@Labs-ELK-VM-102 opt]# mv filebeat-9.0.4-linux-x86_64 filebeat-mysql

4、配置Filebeat收集MySQL数据库日志

（1）在主机Labs-ELK-VM-102上创建filebeat配置文件/opt/filebeat-mysql/filebeat-mysql.yml，配置filebeat输入内容，参考命令如下。

# 创建filebeat配置文件/opt/filebeat-mysql/filebeat-mysql.yml
[root@Labs-ELK-VM-102 opt]# vi /opt/filebeat-mysql/filebeat-mysql.yml
-------------------/opt/filebeat-mysql/filebeat-mysql.yml-------------------
# 配置Filebeat输入
filebeat.inputs:
- type: filestream
  # 唯一标识符，用于区分多个输入源
  id: mysql-error-id
  # 指定日志收集器是启用的
  enabled: true
  # 指定要收集的日志文件路径（MySQL错误日志）
  paths:
    - /var/log/mysql/mysqld.log.*.json
  # 自定义字段type，用于标识日志类型
  fields:
    type: labs-elk-mysql-error
  # 将所有定义在顶层的字段添加到根级别
  fields_under_root: true

- type: filestream
  id: mysql-slow-query-id
  enabled: true
  # 指定要收集的日志文件（MySQL慢查询日志）
  paths:
    - /var/log/mysql/slow-query.log
  exclude_lines: ['^/usr/libexec/mysqld','^Tcp','^Time']
  parsers:
    - multiline:
        pattern: '^# Time'
        negate: true
        match: after
  fields:
    type: labs-elk-mysql-slow
  fields_under_root: true

- type: filestream
  id: mysql-query-id
  enabled: true
  paths:
    - /var/log/mysql/query.log
  exclude_lines: ['^/usr/libexec/mysqld','^Tcp','^Time']
  multiline.pattern: '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}.\d+Z'
  multiline.negate: true
  multiline.match: after
  fields:
    type: labs-elk-mysql-general
  fields_under_root: true
-------------------/opt/filebeat-mysql/filebeat-mysql.yml-------------------

📌 小贴士：

Filebeat 的配置核心围绕两个关键组件运作：输入（Inputs） 和 采集器（Harvesters） 。这两个组件协同工作，实现对目标文件的监控、内容读取以及事件数据传输至指定输出。

  1. 采集器 (Harvester):

  • 职责：负责对单个文件进行内容读取。
  • 工作方式：每个被监控的文件会启动一个独立的采集器实例。采集器以逐行读取的方式处理文件内容，并将读取到的数据封装为事件发送至配置的输出端。
  • 文件管理：采集器负责其对应文件的打开与关闭操作。在采集器处于活动状态期间，该文件会保持打开状态（占用一个文件句柄）。
  • 文件处理：如果在采集器读取过程中，其监控的文件被删除（Delete） 或 重命名（Rename） ，Filebeat 会继续尝试读取该文件的最后已知路径（在类Unix系统上通常仍可访问，但在Windows上行为可能不同）。这带来的一个影响是：在采集器最终关闭之前，该文件占用的磁盘空间不会被释放。
  • 生命周期：采集器会持续读取文件，直至达到其配置的生命周期终点，例如满足 close_inactive 参数设定的非活动超时时间后才会关闭文件。

  1. 输入 (Input):

  • 职责：负责管理其下辖的所有采集器，并定位配置要求监控的文件来源（Sources） 。
  • 工作方式：输入组件根据配置（如指定的文件路径、通配符模式等）发现需要监控的日志文件。对于每个符合条件且需要被监控的文件，输入组件会启动并管理一个对应的采集器实例。输入组件是配置的核心单元，定义了监控的目标（文件路径、类型如日志、容器日志等）以及相关的采集行为参数（如 close_inactive, ignore_older, 多行模式等）。

（2）修改filebeat配置文件/opt/filebeat-mysql/filebeat-mysql.yml，配置filebeat输出，参考命令如下。

#启用 Logstash 输出
-------------------/opt/filebeat-mysql/filebeat-mysql.yml-------------------
output.logstash:
#指定Logstash服务器和Logstash配置为侦听传入的端口
  hosts: ["10.10.2.101:5045"]
-------------------/opt/filebeat-mysql/filebeat-mysql.yml-------------------

（3）在主机Labs-ELK-VM-102上创建自定义系统服务配置文件/lib/systemd/system/filebeat-mysql.service将filebaet注册为系统服务filebeat-mysql，参考命令如下。

# 将filebaet注册为系统服务
[root@Labs-ELK-VM-102 opt]# cat > /lib/systemd/system/filebeat-mysql.service <<EOF
[Unit]
Description=filebeat
Wants=network-online.target
After=network-online.target

[Service]
User=root
ExecStart=/opt/filebeat-mysql/filebeat -e -c /opt/filebeat-mysql/filebeat-mysql.yml
# 设置为掉线自动重启，进程强制杀掉后会自动重新启动
Restart=always

[Install]
WantedBy=multi-user.target
EOF

（4）启动filebeat-mysql服务（通过filebeat收集MySQL日志并推送至logstash），设置服务为开机自启动，并查看服务状态，参考命令如下。

# 启动filebeat-mysql服务，设置服务为开机自启动
[root@Labs-ELK-VM-102 opt]# systemctl enable filebeat-mysql --now

# 查看服务状态
[root@Labs-ELK-VM-102 opt]# systemctl status filebeat-mysql

5、使用Logstash进行MySQL数据库日志清洗

（1）在主机Labs-ELK-VM-101上修改容器labs-elk-logstash02的配置文件/data/docker/volumes/labs-elk-logstash02-pipeline/_data/logstash.conf配置logstash输入，使用beats输入插件接收数据，参考命令如下。

# 修改Logstash配置文件，在文件中添加如下配置内容
#修改Logstash配置文件/data/docker/volumes/labs-elk-logstash02-pipeline/_data/logstash.conf
[root@Labs-ELK-VM-101 ~]# vi /data/docker/volumes/labs-elk-logstash02-pipeline/_data/logstash.conf
----------------------logstash--------------------
# Sample Logstash configuration for creating a simple
# 创建一个从Beats到Logstash到Elasticsearch的数据管道
# Beats -> Logstash -> Elasticsearch pipeline.
input {
  # 定义输入插件Beats，允许Logstash接收来自Beats（Filebeat、Winlogbeat）的数据
  beats {
   # 指定Logstash监听的端口号,以接收来自Beats的数据
    port => 5044
  }
}
---------------------------------------------------

（2）在主机Labs-ELK-VM-101上修改容器labs-elk-logstash02的配置文件，配置过滤器清洗日志，参考命令如下。

#在“输入”下面添加以下过滤内容
---------------logstash---------------
filter {
  # 判断日志类型是否为MySQL错误日志（filebeat中定义type字段，表示日志类型）
  if [type] == "labs-elk-mysql-error" {
    # 使用 json 插件解析消息字段
    json { source => "message" }
    mutate {
      # 使用mutate插件重命名字段
      rename => { "prio" => "severity" }
      rename => { "SQL_state" => "sql_state" }
      rename => { "time" => "log_time_temp" }
      rename => { "msg" => "message" }
      rename => { "label" => "severity_label" }
    }
    # 判断是否存在 "OS_errno" 字段，如果存在则将其重命名为 "os_errno"
    if [OS_errno] {
          mutate {
        rename => { "OS_errno" => "os_errno" }
      }
    }
    # 判断是否存在 "OS_errmsg" 字段，如果存在则将其重命名为 "os_errmsg"
    if [OS_errmsg] {
          mutate {
        rename => { "OS_errmsg" => "os_errmsg" }
      }
    }
    # 使用 date 插件将"log_time_temp" 字段解析为ISO8601格式的时间，并存储到新的字段 "log_time_temp2" 中
    date {
      match => ["log_time_temp", "ISO8601"]
      target => "log_time_temp2"
    }
    # 使用ruby插件将"log_time_temp2"字段的时间戳转换为年月日时分秒，并存储到新的字段 "log_time_arr" 中
    ruby {
      code => "
        require 'time'
        timestamp = event.get('log_time_temp2').to_s
        # 将时间戳转换为 Time 对象
        time = Time.parse(timestamp) + 8*60*60
        # 获取年月日时分秒
        year = time.year
        month = time.month
        day = time.day
        hour = time.hour
        minute = time.min
        second = time.sec
        weekday = time.wday
        yearday = time.yday
        # 将结果存储到新字段中
        event.set('[log_time_arr][year]', year)
        event.set('[log_time_arr][month]', month)
        event.set('[log_time_arr][day]', day)
        event.set('[log_time_arr][hour]', hour)
        event.set('[log_time_arr][min]', minute)
        event.set('[log_time_arr][sec]', second)
        event.set('[log_time_arr][wday]', weekday)
        event.set('[log_time_arr][yday]', yearday)
      "
    }
    mutate{
      # 使用mutate插件移除原始的"log_time_temp"字段
      remove_field => ["log_time_temp"]
      # 将"log_time_temp2"字段重命名为"log_time"
      rename => { "log_time_temp2" => "log_time" }
    }
  }

  # 判断日志类型是否为MySQL慢查询日志（filebeat中定义type字段，表示日志类型）
  if [type] == "labs-elk-mysql-slow" {
    # 使用grok插件对日志消息进行匹配和解析，提取出各个字段
    grok {
      match => {
        "message" => "# Time:%{SPACE}%{TIMESTAMP_ISO8601:log_time}\n# User@Host:%{SPACE}%{USERNAME:mysql_user}\[%{HOSTNAME:mysql_host}\]%{SPACE}@%{SPACE}%{GREEDYDATA:client_host_temp}%{SPACE}Id:%{SPACE}%{NUMBER:thread_id}\n#%{SPACE}Query_time:%{SPACE}%{NUMBER:query_time:float}%{SPACE}Lock_time:%{SPACE}%{NUMBER:lock_time:float}%{SPACE}Rows_sent:%{SPACE}%{NUMBER:rows_sent:int}%{SPACE}Rows_examined:%{SPACE}%{NUMBER:rows_examined:int}%{SPACE}Thread_id:%{SPACE}%{NUMBER}%{SPACE}Errno:%{SPACE}%{NUMBER:errno:int}%{SPACE}Killed:%{SPACE}%{NUMBER:killed:int}%{SPACE}Bytes_received:%{SPACE}%{NUMBER:bytes_received:int}%{SPACE}Bytes_sent:%{SPACE}%{NUMBER:bytes_sent:int}%{SPACE}Read_first:%{SPACE}%{NUMBER:read_first:int}%{SPACE}Read_last:%{SPACE}%{NUMBER:read_last:int}%{SPACE}Read_key:%{SPACE}%{NUMBER:read_key:int}%{SPACE}Read_next:%{SPACE}%{NUMBER:read_next:int}%{SPACE}Read_prev:%{SPACE}%{NUMBER:read_prev:int}%{SPACE}Read_rnd:%{SPACE}%{NUMBER:read_rnd:int}%{SPACE}Read_rnd_next:%{SPACE}%{NUMBER:read_rnd_next:int}%{SPACE}Sort_merge_passes:%{SPACE}%{NUMBER:sort_merge_passes:int}%{SPACE}Sort_range_count:%{SPACE}%{NUMBER:sort_range_count:int}%{SPACE}Sort_rows:%{SPACE}%{NUMBER:sort_rows:int}%{SPACE}Sort_scan_count:%{SPACE}%{NUMBER:sort_scan_count:int}%{SPACE}Created_tmp_disk_tables:%{SPACE}%{NUMBER:created_tmp_disk_tables:int}%{SPACE}Created_tmp_tables:%{SPACE}%{NUMBER:created_tmp_tables:int}%{SPACE}Start:%{SPACE}%{TIMESTAMP_ISO8601:start_time}%{SPACE}End:%{SPACE}%{TIMESTAMP_ISO8601:end_time}\n%{DATA}SET timestamp=%{NUMBER};\n%{GREEDYDATA:query_sql};"
      }
    }
    # 如果客户端主机是"localhost"，则使用mutate插件添加一个新的字段"client_host"
    if "localhost" in [client_host_temp]{
      mutate {
        add_field => { "client_host" => "localhost" }
      }
    # 如果客户端主机不是"localhost"，则使用grok插件再次进行匹配，提取出客户端主机，并存储到"client_host"字段中
    } else {
      grok {
        match => {
          "message" => "@%{SPACE}[%{DATA:client_host}]"
        }
      }
    }
    # 使用mutate插件移除原始的"message"字段和临时的"client_host_temp"字段
    mutate{
      remove_field => ["message"]
      remove_field => ["client_host_temp"]
    }
    # 使用ruby插件将"start_time"字段的时间戳转换为年月日时分秒，并存储到新的字段"start_time_arr"中
    ruby {
      code => "
        require 'time'
        timestamp = event.get('start_time').to_s
        # 将时间戳转换为 Time 对象
        time = Time.parse(timestamp) + 8*60*60
        # 获取年月日时分秒
        year = time.year
        month = time.month
        day = time.day
        hour = time.hour
        minute = time.min
        second = time.sec
        weekday = time.wday
        yearday = time.yday
        # 将结果存储到新字段中
        event.set('[start_time_arr][year]', year)
        event.set('[start_time_arr][month]', month)
        event.set('[start_time_arr][day]', day)
        event.set('[start_time_arr][hour]', hour)
        event.set('[start_time_arr][min]', minute)
        event.set('[start_time_arr][sec]', second)
        event.set('[start_time_arr][wday]', weekday)
        event.set('[start_time_arr][yday]', yearday)
      "
    }
    # 使用ruby插件将"end_time"字段的时间戳转换为年月日时分秒，并存储到新的字段"end_time_arr"中
    ruby {
      code => "
        require 'time'
        timestamp = event.get('end_time').to_s
        # 将时间戳转换为 Time 对象
        time = Time.parse(timestamp) + 8*60*60
        # 获取年月日时分秒
        year = time.year
        month = time.month
        day = time.day
        hour = time.hour
        minute = time.min
        second = time.sec
        weekday = time.wday
        yearday = time.yday
        # 将结果存储到新字段中
        event.set('[end_time_arr][year]', year)
        event.set('[end_time_arr][month]', month)
        event.set('[end_time_arr][day]', day)
        event.set('[end_time_arr][hour]', hour)
        event.set('[end_time_arr][min]', minute)
        event.set('[end_time_arr][sec]', second)
        event.set('[end_time_arr][wday]', weekday)
        event.set('[end_time_arr][yday]', yearday)
      "
    }
  }

  # 判断日志类型是否为MySQL通用查询日志（filebeat中定义type字段，表示日志类型）
  if [type] == "labs-elk-mysql-general" {
    # 判断日志消息中是否包含"Execute"字符串，如果有，则直接丢弃这条日志事件
    if "Execute" in [message] {
      drop {}
    }
    # 使用grok插件对日志消息进行匹配和解析，提取出时间戳、连接ID、操作类型和操作参数等字段
    grok {
      match => {
        "message" => "%{TIMESTAMP_ISO8601:log_time}\t%{SPACE}%{NUMBER:conn_id}%{SPACE}%{DATA:opera_type}\t(?<opera_argument>(.|\r|\n)*)"
      }
    }
    # 判断操作类型是"Connect"，则再次使用grok插件对操作参数进行匹配，提取出连接用户名、连接客户端和认证方法等字段
    if [opera_type] == "Connect" {
      grok {
        match => {
          "opera_argument" => "%{USERNAME:conn_user}@%{DATA:conn_client}%{SPACE}on%{SPACE}using%{SPACE}%{GREEDYDATA:auth_method}"
        }
      }
    }
    # 使用ruby插件将"log_time"字段的时间戳转换为年月日时分秒，并存储到新的字段"log_time_arr"中
    ruby {
      code => "
        require 'time'
        timestamp = event.get('log_time').to_s
        # 将时间戳转换为 Time 对象
        time = Time.parse(timestamp) + 8*60*60
        # 获取年月日时分秒
        year = time.year
        month = time.month
        day = time.day
        hour = time.hour
        minute = time.min
        second = time.sec
        weekday = time.wday
        yearday = time.yday
        # 将结果存储到新字段中
        event.set('[log_time_arr][year]', year)
        event.set('[log_time_arr][month]', month)
        event.set('[log_time_arr][day]', day)
        event.set('[log_time_arr][hour]', hour)
        event.set('[log_time_arr][min]', minute)
        event.set('[log_time_arr][sec]', second)
        event.set('[log_time_arr][wday]', weekday)
        event.set('[log_time_arr][yday]', yearday)
      "
    }
    # 使用mutate插件移除原始的"message"字段
    mutate {
      remove_field => ["message"]
    }
  }
}
---------------logstash---------------

MySQL数据库日志清洗字段标准如表3-1、3-2、3-3所示。

表 3-1 MySQL数据库错误日志字段清洗标准

日志字段清洗字段字段说明字段格式
timelog_time事件时间戳，精度为微秒级date
log_time_arr.year时间戳-年integer
log_time_arr.month时间戳-月integer
log_time_arr.day时间戳-日integer
log_time_arr.hour时间戳-时integer
log_time_arr.min时间戳-分integer
log_time_arr.sec时间戳-秒integer
log_time_arr.yday时间戳-一年中的第几天integer
log_time_arr.wday时间戳-周几integer
log_time_arr.time_zone时间戳-时区string
prioseverity事件优先级integer
err_codeerr_code事件错误代码integer
err_symbolerr_symbol事件错误符号string
SQL_statesql_state事件SQLSTATE值string
subsystemsubsystem发生事件的子系统string
OS_errnoos_errno操作系统错误号string
OS_errmsgos_errmsg操作系统错误消息string
labelseverity_label与severity值相对应的标签string
useruser客户端用户string
hosthost客户端主机string
threadthread生成错误事件的线程IDinteger
query_idquery_id查询IDinteger
source_filesource_file发生事件的源文件string
source_linesource_line发生事件所在的源文件中的行integer
functionfunction发生事件的函数string
componentcomponent发生事件的组件或插件string
msgmessage事件消息字符串string

表3-2 MySQL数据库慢查询日志字段清洗标准

日志字段清洗字段字段说明字段格式
mysql_usermysql_user数据库用户string
mysql_hostmysql_host数据库主机名string
mysql_client_hostclient_host客户端主机string
Query_timequery_time语句执行时间（以秒为单位）integer
Lock_timelock_time获取锁的时间（以秒为单位）integer
Rows_sentrows_sent发送到客户端的行数integer
Rows_examinedrows_examined服务器层检查的行数integer
Thread_idthread_id语句线程标识符string
Errnoerrno语句错误号，没有发生错误则为 0integer
Killedkilled语句终止integer
Bytes_receivedbytes_received接收到的字节数integer
Bytes_sentbytes_sent发送的字节数integer
Read_firstread_firstinteger
Read_lastread_lastinteger
Read_keyread_keyinteger
Read_nextread_nextinteger
Read_prevread_previnteger
Read_rndread_rndinteger
Read_rnd_nextread_rnd_nextinteger
Sort_merge_passessort_merge_passes排序合并的次数integer
Sort_range_countsort_range_count使用范围查找的次数integer
Sort_rowssort_rows排序的行数integer
Sort_scan_countsort_scan_count扫描的次数integer
Created_tmp_disk_tablescreated_tmp_disk_tables创建在磁盘上临时表的次数integer
Created_tmp_tablescreated_tmp_tables创建临时表的次数integer
Startstart_time查询开始执行的时间戳date
start_time_arr.year时间戳-年integer
start_time_arr.month时间戳-月integer
start_time_arr.day时间戳-日integer
start_time_arr.hour时间戳-时integer
start_time_arr.min时间戳-分integer
start_time_arr.sec时间戳-秒integer
start_time_arr.yday时间戳-一年中的第几天integer
start_time_arr.wday时间戳-周几integer
start_time_arr.time_zone时间戳-时区string
Endend_time查询执行结束的时间戳date
end_time_arr.year时间戳-年integer
end_time_arr.month时间戳-月integer
end_time_arr.day时间戳-日integer
end_time_arr.hour时间戳-时integer
end_time_arr.min时间戳-分integer
end_time_arr.sec时间戳-秒integer
end_time_arr.yday时间戳-一年中的第几天integer
end_time_arr.wday时间戳-周几integer
end_time_arr.time_zone时间戳-时区string
query_sqlquery_sql查询语句string

表3-3 MySQL数据库通用查询日志字段清洗标准

日志字段清洗字段字段说明字段格式
Timelog_time事件时间戳，精度为微秒级date
log_time_arr.year时间戳-年integer
log_time_arr.month时间戳-月integer
log_time_arr.day时间戳-日integer
log_time_arr.hour时间戳-时integer
log_time_arr.min时间戳-分integer
log_time_arr.sec时间戳-秒integer
log_time_arr.yday时间戳-一年中的第几天integer
log_time_arr.wday时间戳-周几integer
log_time_arr.time_zone时间戳-时区string
IDconn_id数据库连接IDstring
Commandopera_type操作类型string
Argumentopera_argument操作参数string
mysql_userconn_user连接数据库用户string
mysql_client_hostconn_client客户端主机string
Authenticationauth_method认证方式string

（3）在主机Labs-ELK-VM-101上修改容器labs-elk-logstash02的配置文件，配置输出模块为Elasticsearch，参考命令如下。

#在“过滤”下面添加以下过滤内容
#修改Logstash配置文件/data/docker/volumes/labs-elk-logstash02-pipeline/_data/logstash.conf
[root@Labs-ELK-VM-101 ~]# vi /data/docker/volumes/labs-elk-logstash02-pipeline/_data/logstash.conf
----------------------logstash--------------------
#定义输出插件的配置
output {
  stdout {codec => rubydebug}
  # 判断日志类型是否为MySQL错误日志
  if [type] == "labs-elk-mysql-error" {
    #stdout {codec => rubydebug}
    elasticsearch {
      #stdout {codec => rubydebug}
      hosts => ["https://labs-elk-es-node-1:9200"]
      index => "labs-elk-mysql-error-%{+YYYY.MM.dd}"
      user => "elastic"
      password => "Labs#ELK@2025"
      ssl_certificate_authorities => ["/usr/share/logstash/config/certs/ca/ca.crt"]
    }
  }
  else if [type] == "labs-elk-mysql-slow" {
    #stdout {codec => rubydebug}
    elasticsearch {
      hosts => ["https://labs-elk-es-node-1:9200"]
      index => "labs-elk-mysql-slow-%{+YYYY.MM.dd}"
      user => "elastic"
      password => "Labs#ELK@2025"
      ssl_certificate_authorities => ["/usr/share/logstash/config/certs/ca/ca.crt"]
    }
  }
  else if [type] == "labs-elk-mysql-general" {
    #stdout {codec => rubydebug}
    elasticsearch {
      hosts => ["https://labs-elk-es-node-1:9200"]
      index => "labs-elk-mysql-general-%{+YYYY.MM.dd}"
      user => "elastic"
      password => "Labs#ELK@2025"
      ssl_certificate_authorities => ["/usr/share/logstash/config/certs/ca/ca.crt"]
    }
  }
}
--------------------------------------------------

（4）在主机Labs-ELK-VM-101上，重启容器labs-elk-logstash-2，通过docker logs命令查看日志的推送情况，验证完成后修改logstash配置文件，禁用输出到控制台，并重启容器labs-elk-logstash-2，参考命令如下。

#Logstash配置文件修改完成后，重启Logstash容器，使配置生效
[root@Labs-ELK-VM-101 ~]# docker restart labs-elk-logstash-2
#等Logstash启动后，访问网站Site生成访问日志，通过控制台实时查看Apache访问日志
[root@Labs-ELK-VM-101 ~]# docker logs labs-elk-logstash-2 --tail=100 -f
#此处省略部分日志内容
{
             "event" => {
        "original" => "2025-08-01T08:31:26.229310Z\t   11 Connect\troot@localhost on  using Sock                                                      et"
    },
               "log" => {
        "offset" => 2599,
          "file" => {
              "device_id" => "64768",
                  "inode" => "2359589",
            "fingerprint" => "9d43782d4653f979c9296296aeee05d40597b77ff47ef2b29d2eee7947f0479f",
                   "path" => "/var/log/mysql/query.log"
        }
    },
              "tags" => [
        [0] "beats_input_codec_plain_applied"
    ],
         "conn_user" => "root",
           "conn_id" => "11",
             "input" => {
        "type" => "filestream"
    },
    "opera_argument" => "root@localhost on  using Socket",
      "log_time_arr" => {
         "year" => 2025,
         "yday" => 213,
         "hour" => 16,
          "day" => 1,
          "min" => 31,
        "month" => 8,
          "sec" => 26,
         "wday" => 5
    },
       "auth_method" => "Socket",
        "@timestamp" => 2025-08-01T08:31:35.699Z,
               "ecs" => {
        "version" => "8.0.0"
    },
          "@version" => "1",
          "log_time" => "2025-08-01T08:31:26.229310Z",
              "host" => {
        "name" => "Labs-ELK-VM-102"
    },
             "agent" => {
        "ephemeral_id" => "adc01d80-2df7-4447-9bb9-db6b94781920",
                "name" => "Labs-ELK-VM-102",
                "type" => "filebeat",
                  "id" => "8f30e8ee-8865-42cb-9bf8-22a46e136757",
             "version" => "9.0.4"
    },
       "conn_client" => "localhost",
        "opera_type" => "Connect",
              "type" => "labs-elk-mysql-general"
}

#验证完成后将容器日志的输出关闭
[root@Labs-ELK-VM-101 ~]# vi /data/docker/volumes/labs-elk-logstash02-pipeline/_data/logstash.conf
----------------------logstash--------------------
#此处省略其他配置内容
  # stdout {codec => rubydebug}
--------------------------------------------------

# 重启logstash容器
[root@Labs-ELK-VM-101 ~]# docker restart labs-elk-logstash-2

6、使用Kibana检索日志

（1）使用浏览器访问Kibana地址http://10.10.2.101:5601，输入用户和密码（用户名：elastic，密码：Labs#ELK@2025）登录Kibana，如图3-1所示。

（2）进入系统界面中，单击左上角的三条横线，导航到左侧菜单，下滑选择“Management”选项卡中“Stack Management”选项，如图3-2所示。

（3）在“Stack Management”界面，选择“Kibana”选项卡中“Data Views”选项，创建和管理数据视图，如图3-3所示。

（4）单击【Data Views】，在数据视图界面中单击【Create data view】，创建MySQL通用查询日志的数据视图，如图3-4所示。

（5）输入视图名称为“labs-elk-mysql-general”，索引模式为“labs-elk-mysql-general-*”，Timestamp字段选择“log_time”，单击【Save data view to Kibana】，保存视图配置，如图3-5、3-6所示。

📌

小贴士：在显示高级设置（Show advanced settings）中可以设置“显示隐藏索引和系统索引”，指定自定义的数据视图名称。

（6）单击左上角导航栏，选择“Analytics”选项卡中“Discover”选项，进入日志分析视图界面。在左上角选择“labs-elk-mysql-general”的数据视图，单击【Search entire time range】检索MySQL通用查询日志，如图3-7所示。

（7）查看左侧字段列表，单击左侧Available fields中【＋】，可对MySQL通用查询日志中的字段日志进行检索查看，如图3-8所示。

（8）参照上述步骤（4）-（7）创建MySQL错误日志数据视图labs-elk-mysql-error、慢查询日志数据视图labs-elk-mysql-slow，并检索日志，如图3-9所示。

7、使用Kibana配置告警规则与通知

（1）在Kibana的Home界面，单击左上角的三条横线，导航到左侧菜单，下滑选择“Management”选项卡中“Stack Management”选项，选择“Alerts and Insights”菜单下的“Connectors”，配置与其他系统（如电子邮件、Slack、ServiceNow、Webhook等）的连接，用于告警通知或数据交互，如图3-10所示。

（2）在Connectors页面，单击【Create connector】创建连接器，选择“Email”连接器，如图3-11所示。

（3）在“Email”连接器配置页面，配置邮件服务器信息，如图3-12所示。

📌

Email connector配置说明

Connector name：连接器名称（可自定义）

Connector settings

  • Sender：发件人（通常该地址必须与SMTP服务器上认证的用户名相同）
  • Service：邮件服务商（本指南使用QQ邮件服务商，此处选择Other）
  • Host：SMTP邮件服务器地址
  • Port：SMTP邮件服务器端口

Authentication

  • Username：SMTP邮件服务器用户名，通常是完整的邮箱地址。
  • Password：SMTP邮件服务器授权码，通常需登录邮箱后台开启SMTP服务并获取授权码

（4）配置完成，单击【Save & test】，填写接收消息的邮箱地址，以及Subject和Message测试内容，单击【Run】进行邮件服务器发送消息测试，如图3-13所示。

（5）测试返回结果“Test was successful”表示邮件服务器配置正确，单击【Close】关闭页面，登录接收信息的邮箱查看是否接收到测试邮件，如图3-14、3-15所示。

（6）在“Management”导航菜单下选择“Alerts and Insights”菜单下的“Rules”，单击【Create rule】创建告警规则，如图3-16所示。

（7）在“Select rule type”页面，选择并单击“Elasticsearch query”，选择告警规则类型为弹性搜素查询，如图3-17所示。

（8）在“Elasticsearch query rule”配置页面，配置使用Elasticsearch SQL查询MySQL慢查询日志，并将慢查询日志通过“Email”连接器发送给指定的邮箱地址，设置规则名称和标签，单击【Create rule】创建规则，如图3-18、3-19、3-20所示。

Elasticsearch query rule配置内容如表3-4所示。

表 3-4 Elasticsearch query rule配置

配置项配置内容配置说明
ES/QLES/QL：FROM labs-elk-mysql-slow-*Select a time field：@timestampSet the time window：1minutesRule schedule：1minutesRule scope：Stack Rules使用Elasticsearch SQL查询最近1分钟内的MySQL慢查询日志，设置检查警报条件的频率为1分钟
ActionsAdd action：邮件告警推送To：673259194@qq.comSubject：MySQL慢查询告警通知Message：数据库主机：{{context.hits.0._source.agent.name.keyword}}客户端主机：{{context.hits.0._source.client_host.keyword}}数据库用户：{{context.hits.0._source.mysql_user.keyword}}慢查询语句：{{context.hits.0._source.query_sql.keyword}}慢查询耗时：{{context.hits.0._source.query_time}}秒配置触发告警规则的活动为：推送告警邮件，设置邮件接收人、主题和邮件内容
Rule detailsRule name：MySQL慢查询告警通知Tags：MySQL配置告警规则名称和标签

（9）在主机Labs-ELK-VM-102上，使用root用户本地连接MySQL数据库，执行SQL语句模拟慢查询，参考命令如下。

# 连接MySQL数据库
[root@Labs-ELK-VM-102 ~]# mysql -uroot -p

# 执行模拟慢查询SQL语句
mysql> select sleep(1.41);

（10）查询结束等待1分钟左右，在“Management”导航菜单下选择“Alerts and Insights”菜单下的“Rules”，单击“MySQL慢查询告警通知”，在Alerts中可查看到已触发告警，如图3-21，3-22所示。

（11）登录邮件接收人邮箱后台，查看收件箱已接收到主题为“MySQL慢查询告警通知”的告警邮件，如图3-23所示。

8、MySQL日志可视化分析

（1）通过 MySQL数据库日志字段清洗标准建立日志分析模型，实现MySQL数据库日志可视化分析。MySQL数据库日志分析模型如表3-5、3-6、3-7所示。

表3-5 MySQL数据库错误日志分析模型

分析模型图表类型分析模型用途
日志总量数字展示MySQL数据库错误日志总量
系统日志量数字展示事件优先级为System的日志量
异常日志量数字展示事件优先级为Warning、Error的日志量
消息日志量数字展示事件优先级为Note/Information的日志量
日志趋势折线图展示日志量随时间的变化趋势
24小时日志统计柱状图展示每小时的日志量
子系统日志排行表格展示不同子系统的日志量排行
组件日志排行表格展示不同组件的日志量排行
事件函数日志排行表格展示不同函数的日志量排行
错误符号标签云标签云展示日志事件错误符号标签云
日志等级占比饼状图展示不同等级日志占比
日志严重性变化趋势折线图展示不同等级日志随时间的变化趋势

表3-6 MySQL数据库慢查询日志分析模型

分析模型图表类型分析模型用途
日志总量数字展示MySQL数据库慢查询日志总量（关于慢查询）
发送字节数数字展示发送字节总数（关于慢查询）
接收字节数数字展示接收字节总数（关于慢查询）
日志趋势折线图展示日志量随时间的变化趋势（关于慢查询）
24小时日志统计柱状图展示每小时的日志量（关于慢查询）
客户端数数字展示连接数据库的客户端数（关于慢查询的客户端数，并不是客户端总数）
客户端慢查询排行表格展示不同客户端慢查询次数的排行（关于慢查询的客户端，并不是所有客户端）
数据库用户数数字展示连接数据库的用户数（关于慢查询的用户数，并不是用户总数）
数据库用户慢查询排行表格展示不同数据库用户慢查询次数的排行（关于慢查询的用户，并不是所有用户）
执行语句耗时排行柱状图展示不同执行语句的耗时排行
获取锁耗时排行柱状图展示不同执行语句获取锁的耗时排行
执行语句排序合并次数排行表格展示不同执行语句排序合并的次数排行
执行语句使用范围查找次数排行表格展示不同执行语句使用范围查找的次数排行
执行语句创建临时表次数排行表格展示不同执行语句创建临时表的次数排行
执行语句在磁盘上创建临时表次数排行表格展示不同执行语句在磁盘上创建临时表的次数排行

表3-7 MySQL数据库通用查询日志分析模型

分析模型图表类型分析模型用途
日志总量数字展示MySQL数据库通用查询日志总量
客户端数数字展示连接数据库的客户端数
数据库用户数数字展示连接数据库的用户数
认证方式排行表格展示用户连接数据库不同认证方式的次数排行
认证方式标签云标签云展示用户连接数据库的认证方式标签云
操作类型占比柱状图展示不同操作类型日志占比
日志趋势折线图展示日志量随时间的变化趋势（关于通用查询）
24小时日志统计柱状图展示每小时的日志量（关于通用查询）

（2）通过Kibana平台的可视化图表功能，绘制上述日志分析模型，如图5-9所示。

avatar
文章
阅读
粉丝