当前位置： 首页 > article >正文

Graylog采集MySQL慢日志实战

article 2025/1/16 10:50:31

文章目录

  • 前言
  • 一、MySQL慢日志
    • 0. 慢查询相关语句
    • 1. 检查MySQL是否开启慢日志及慢查询保存位置
    • 2. 检查慢查询阈值
    • 3. 未使用索引是否开启记录慢查询日志
    • 4. 查看mysql.slow_log表结构及字段含义
    • 5. 慢查询记录两种情况示例
  • 二、graylog采集慢查询日志
    • 1. 采集思路
    • 2. 创建Sidecar配置
    • 3. 绑定该配置到对应机器的sidecar并重启sidecar
    • 4. 查看采集到的日志并解析关键字段

前言

在完整的日志系统中，数据库的慢日志是采集中必不可少的一部分,当数据库开启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时,则表示开启了慢日志

# 查看慢日志记录途径
mysql> show variables  like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'slow%';
+---------------------+-------------------------------------------------+
| Variable_name       | Value                                           |
+---------------------+-------------------------------------------------+
| slow_launch_time    | 2                                               |
| slow_query_log      | ON                                              |
| slow_query_log_file | /export/servers/data/my3306/log/mysqld-slow.log |
+---------------------+-------------------------------------------------+
3 rows in set (0.01 sec)

2. 检查慢查询阈值

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

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

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

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

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

4. 查看mysql.slow_log表结构及字段含义

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

慢日志记录字段含义

字段含义
start_time开始时间
user_host用户名ip
query_time执行时间
lock_time执行获取锁时间
rows_sent获取结果行数
rows_examined扫描数据行数
db数据库实例
last_insert_id最近一次插入操作生成的自动增长ID
insert_id插入的ID，用于记录插入操作所生成的ID，一般用于AUTO_INCREMENT字段
server_id服务器ID，表示执行查询的数据库服务器的标识符
sql_text具体sql
thread_id线程id

5. 慢查询记录两种情况示例

打开慢查询记录即可看到

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

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

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

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

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

二、graylog采集慢查询日志

1. 采集思路

如下（示例）：

根据上边的日志格式可知道一般情况下日志有5-6行的数据，关于use database这一行不一定有。

1、MySQL 的慢查询日志多行构成了一条完整的日志，日志收集时要把这些行拼装成一条日志传输与存储，即需要实现多行合并。

2、# Time 开头的行可以确定是一定存在的，可以根据该行来处理多行合并问题，不是以# Time开头的都合并到一行。

3、一条完整的慢查询日志最终将以# Time开始的行，以SQL语句结尾的行合并为一条完整的慢日志语句。

4、因为use database这一行不是所有慢sql都存在，所以不能通过这个来确定SQL对应的数据库名称，慢日志中也没有字段记录DB，因此这里建议创建数据库账号时以数据库名称命名，这样看到账号名就知道是哪个DB了。

2. 创建Sidecar配置

在这里插入图片描述
在这里插入图片描述

配置如下（示例）：

# Needed for Graylog
fields_under_root: true
fields.collector_node_id: ${sidecar.nodeName}
fields.gl2_source_collector: ${sidecar.nodeId}
fields.localIp: ${sidecar.nodeName}
filebeat.registry.flush: 60s
filebeat.shutdown_timeout: 10s 
max_procs: 1

filebeat.inputs:
- input_type: log
  paths:
    - /export/servers/data/my3306/log/mysqld-slow.log
  multiline.pattern: '^# Time'
  multiline.negate: true
  multiline.match: after
  fields:
   app_name: mysql_slow_query_log
   environment: pro
   log_type: MySQL
  type: log
output.logstash:
   hosts: ["192.168.56.160:5044"]
path:
  data: /var/lib/graylog-sidecar/collectors/filebeat/data
  logs: /var/lib/graylog-sidecar/collectors/filebeat/log

3. 绑定该配置到对应机器的sidecar并重启sidecar

在这里插入图片描述

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

在这里插入图片描述
#grok pattern方式配置提取器

#%{SPACE}Time:%{SPACE}%{TIMESTAMP_ISO8601:time}(?:\+%{INT:timezone})?\
#%{SPACE}User@Host:%{SPACE}%{DATA:user}%{SPACE}@%{SPACE}%{DATA:host}%{SPACE}Id:%{SPACE}%{NOTSPACE:thread_id}\
#%{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};|())\
SET%{SPACE} timestamp=%{BASE10NUM:exec_timestamp;long};
%{DATA:sql}$

在这里插入图片描述
在这里插入图片描述
最终获取到如下结果
在这里插入图片描述


http://www.kler.cn/a/504894.html

相关文章：

  • 汽车免拆诊断案例 | 2007 款法拉利 599 GTB 车发动机故障灯异常点亮
  • excel 整理表格，分割一列变成多列数据
  • 查看APK的公钥，MD5信息
  • 使用WebdriverIO和Appium测试App
  • 图解Git——分支开发工作流《Pro Git》
  • Python爬虫-汽车之家各车系周销量榜数据
  • 源码编译安装httpd 2.4，提供系统服务管理脚本并测试
  • jenkins docker 遇到 /var/run/docker.sock: permission denied 解决方案
  • 【JavaEE进阶】SpringMVC 响应
  • 代码随想录算法训练营第三十四天-动态规划-63. 不同路径II
  • vue运用uniapp框架开发企业微信小程序中常用的一些基础方法
  • C#，入门教程(27)——应用程序（Application）的基础知识
  • JavaScript：模版字符串
  • scons通用构建_生成方法Command
  • 向量数据库如何助力Text2SQL处理高基数类别数据
  • 更新用户密码功能
  • 解决nginx: [emerg] unknown directive “stream“ in /etc/nginx/nginx.conf问题 --九五小庞
  • LeetCode | 解锁数组与字符串的秘密：经典题型详解与高效解法
  • 20250113面试鸭特训营第21天
  • STLG_01_12_程序设计C语言 - 联合体和枚举类型
  • 【AIGC-ChatGPT进阶提示词指令】智慧母婴：打造基于成长树的儿童发展引导系统
  • 【网络云SRE运维开发】2025第3周-每日【2025/01/14】小测-【第13章ospf路由协议】理论和实操解析
  • PPPLib源码阅读
  • 「蓝桥杯题解」数字接龙
  • 石化煤矿智能化转型“硬通货”，遨游防爆手机如何面面俱到？
  • Vue2+OpenLayers实现车辆开始、暂停、重置行驶轨迹动画（提供Gitee源码）