Graylog采集MySQL慢日志实战

---

前言

在完整的日志体系中，数据库的慢日志是采集中必不可少的一部分,当数据库开启mysql慢日志记录时,研发人员可以通过graylog-sidecar采集到的慢日志进行查看分析，更好的优化mysql语句，进步服从。

---

一、MySQL慢日志

数据库部署不再过多形貌,感爱好的可以查看我数据库专栏文章
0. 慢查询相干语句

SQL含义备注show variables like ‘slow_query_log’;是否开启慢查询记录ON 表示开启；OFF 表示关闭show variables like ‘slow_query_log_file’;慢查询存储位置默认位置/自定义位置show variables like ‘long_query_time’;慢查询阈值默认值是 10s，使用慢查询则一定需要更改show variables like ‘log_queries_not_using_indexes’;未使用索引的查询也被记录到慢查询日志中OFF 表示关闭，通常不会被开启show variables like ‘log_output’;慢查询日志存储方式默认值是 FILE，表示将日志存入文件；
修改为 TABLE，表示将日志存入数据库（mysql.slow_log 表）show global status like ‘Slow_queries’;查看有多少条慢查询记录/ 1. 检查MySQL是否开启慢日志及慢查询生存位置

当slow_query_log字段的值为ON时,则表示开启了慢日志

1. # 查看慢日志记录途径
2. mysql> show variables  like 'log_output';
3. +---------------+-------+
4. | Variable_name | Value |
5. +---------------+-------+
6. | log_output    | FILE  |
7. +---------------+-------+
8. 1 row in set (0.00 sec)
10. mysql> show variables like 'slow%';
11. +---------------------+-------------------------------------------------+
12. | Variable_name       | Value                                           |
13. +---------------------+-------------------------------------------------+
14. | slow_launch_time    | 2                                               |
15. | slow_query_log      | ON                                              |
16. | slow_query_log_file | /export/servers/data/my3306/log/mysqld-slow.log |
17. +---------------------+-------------------------------------------------+
18. 3 rows in set (0.01 sec)

复制代码

2. 检查慢查询阈值

超过2秒则记录到慢日志中

1. mysql> show variables like 'long%';
2. +-----------------+----------+
3. | Variable_name   | Value    |
4. +-----------------+----------+
5. | long_query_time | 2.000000 |
6. +-----------------+----------+
7. 1 row in set (0.00 sec)

复制代码

3. 未使用索引是否开启记录慢查询日志

如果需要则开启,默认是关闭状态

1. mysql> show variables like 'log_queries_not_using_indexes';
2. +-------------------------------+-------+
3. | Variable_name                 | Value |
4. +-------------------------------+-------+
5. | log_queries_not_using_indexes | OFF   |
6. +-------------------------------+-------+
7. 1 row in set (0.00 sec)

复制代码

4. 查看mysql.slow_log表布局及字段含义

1. mysql> SELECT COLUMN_NAME                                          AS '字段名',
2.     ->        DATA_TYPE                                            AS `数据类型`,
3.     ->        CHARACTER_MAXIMUM_LENGTH                             AS `字符长度`,
4.     ->        NUMERIC_PRECISION                                    AS `数字长度`,
5.     ->        NUMERIC_SCALE                                        AS `小数位数`,
6.     ->        IS_NULLABLE                                          AS `是否允许非空`,
7.     ->        CASE WHEN EXTRA = 'auto_increment' THEN 1 ELSE 0 END AS `是否自增`,
8.     ->        COLUMN_DEFAULT                                       AS `默认值`,
9.     ->        COLUMN_COMMENT                                       AS `备注`
10.     -> FROM information_schema.COLUMNS
11.     -> WHERE TABLE_SCHEMA = 'mysql'
12.     ->   AND TABLE_NAME = 'slow_log';
13. +----------------+--------------+--------------+--------------+--------------+--------------------+--------------+----------------------+--------+
14. | 字段名         | 数据类型     | 字符长度     | 数字长度     | 小数位数     | 是否允许非空       | 是否自增     | 默认值               | 备注   |
15. +----------------+--------------+--------------+--------------+--------------+--------------------+--------------+----------------------+--------+
16. | start_time     | timestamp    |         NULL |         NULL |         NULL | NO                 |            0 | CURRENT_TIMESTAMP(6) |        |
17. | user_host      | mediumtext   |     16777215 |         NULL |         NULL | NO                 |            0 | NULL                 |        |
18. | query_time     | time         |         NULL |         NULL |         NULL | NO                 |            0 | NULL                 |        |
19. | lock_time      | time         |         NULL |         NULL |         NULL | NO                 |            0 | NULL                 |        |
20. | rows_sent      | int          |         NULL |           10 |            0 | NO                 |            0 | NULL                 |        |
21. | rows_examined  | int          |         NULL |           10 |            0 | NO                 |            0 | NULL                 |        |
22. | db             | varchar      |          512 |         NULL |         NULL | NO                 |            0 | NULL                 |        |
23. | last_insert_id | int          |         NULL |           10 |            0 | NO                 |            0 | NULL                 |        |
24. | insert_id      | int          |         NULL |           10 |            0 | NO                 |            0 | NULL                 |        |
25. | server_id      | int          |         NULL |           10 |            0 | NO                 |            0 | NULL                 |        |
26. | sql_text       | mediumblob   |     16777215 |         NULL |         NULL | NO                 |            0 | NULL                 |        |
27. | thread_id      | bigint       |         NULL |           20 |            0 | NO                 |            0 | NULL                 |        |
28. +----------------+--------------+--------------+--------------+--------------+--------------------+--------------+----------------------+--------+
29. 12 rows in set (0.00 sec)

复制代码

慢日志记录字段含义
字段含义start_time开始时间user_host用户名ipquery_time实行时间lock_time实行获取锁时间rows_sent获取结果行数rows_examined扫描数据行数db数据库实例last_insert_id近来一次插入操作生成的自动增长IDinsert_id插入的ID，用于记录插入操作所生成的ID，一般用于AUTO_INCREMENT字段server_id服务器ID，表示实行查询的数据库服务器的标识符sql_text具体sqlthread_id线程id 5. 慢查询记录两种情况示例

打开慢查询记录即可看到
第一种：慢查询记录中没有use database

1. # Time: 2025-01-15T15:30:35.791555+08:00
2. # User@Host: root[root] @ localhost []  Id:     5
3. # Query_time: 20.008015  Lock_time: 0.000099 Rows_sent: 4  Rows_examined: 4
4. SET timestamp=1736926235;
5. SELECT SLEEP(5), id, name,email FROM users WHERE id > 2;

复制代码

第二种：慢查询记录中有use database

1. # Time: 2025-01-15T15:36:17.669885+08:00
2. # User@Host: xx_fast[xxx_fast] @  [192.168.56.101]  Id: 607
3. # Query_time: 10.622605  Lock_time: 0.000106 Rows_sent: 0  Rows_examined: 19680
4. use xx_fast;
5. SET timestamp=1736926577;
6. SELECT t.id
7.                 FROM xxl_job_log AS t
8.                 WHERE t.trigger_code = 200
9.                         and t.handle_code = 0
10.                         and t.trigger_time   <=   '2025-01-15 15:26:07.047'
11.                         and t.executor_address not in (
12.                                 SELECT t2.registry_value
13.                                 FROM xxl_job_registry AS t2
14.                         );

复制代码

1. 日志格式说明:
2.         第一行:
3.                 记录时间
4.         第二行:
5.                 用户名 、用户的IP信息、线程ID号
6.         第三行:
7.                 执行花费的时间【单位：毫秒】、执行获得锁的时间、获得的结果行数、扫描的数据行数
8.         第四行:
9.                 这SQL执行的时间戳
10.         第五行:
11.                 具体的SQL语句
12. 补充:
13.         有时还会多出一行 数据库名称记录

复制代码

二、graylog采集慢查询日志

1. 采集思绪

   如下（示例）：

1. 根据上边的日志格式可知道一般情况下日志有5-6行的数据，关于use database这一行不一定有。
3. 1、MySQL 的慢查询日志多行构成了一条完整的日志，日志收集时要把这些行拼装成一条日志传输与存储，即需要实现多行合并。
5. 2、# Time 开头的行可以确定是一定存在的，可以根据该行来处理多行合并问题，不是以# Time开头的都合并到一行。
7. 3、一条完整的慢查询日志最终将以# Time开始的行，以SQL语句结尾的行合并为一条完整的慢日志语句。
9. 4、因为use database这一行不是所有慢sql都存在，所以不能通过这个来确定SQL对应的数据库名称，慢日志中也没有字段记录DB，因此这里建议创建数据库账号时以数据库名称命名，这样看到账号名就知道是哪个DB了。

复制代码

2. 创建Sidecar设置

   设置如下（示例）：

1. # Needed for Graylog
2. fields_under_root: true
3. fields.collector_node_id: ${sidecar.nodeName}
4. fields.gl2_source_collector: ${sidecar.nodeId}
5. fields.localIp: ${sidecar.nodeName}
6. filebeat.registry.flush: 60s
7. filebeat.shutdown_timeout: 10s
8. max_procs: 1
10. filebeat.inputs:
11. - input_type: log
12.   paths:
13.     - /export/servers/data/my3306/log/mysqld-slow.log
14.   multiline.pattern: '^# Time'
15.   multiline.negate: true
16.   multiline.match: after
17.   fields:
18.    app_name: mysql_slow_query_log
19.    environment: pro
20.    log_type: MySQL
21.   type: log
22. output.logstash:
23.    hosts: ["192.168.56.160:5044"]
24. path:
25.   data: /var/lib/graylog-sidecar/collectors/filebeat/data
26.   logs: /var/lib/graylog-sidecar/collectors/filebeat/log

复制代码

3. 绑定该设置到对应呆板的sidecar并重启sidecar

4. 查看采集到的日志并解析关键字段

#grok pattern方式设置提取器

1. #%{SPACE}Time:%{SPACE}%{TIMESTAMP_ISO8601:time}(?:\+%{INT:timezone})?\
2. #%{SPACE}User@Host:%{SPACE}%{DATA:user}%{SPACE}@%{SPACE}%{DATA:host}%{SPACE}Id:%{SPACE}%{NOTSPACE:thread_id}\
3. #%{SPACE} Query_time:%{SPACE}%{BASE10NUM:query_time;float}%{SPACE} Lock_time:%{SPACE}%{BASE10NUM:lock_time;float}%{SPACE}Rows_sent:%{SPACE}%{BASE10NUM:rows_sent;long}%{SPACE}Rows_examined:%{SPACE}%{BASE10NUM:rows_examined;long}(?:\nuse %{DATA:db};|())\
4. SET%{SPACE} timestamp=%{BASE10NUM:exec_timestamp;long};
5. %{DATA:sql}$

复制代码

最终获取到如下结果

---

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。