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

MySQL 慢查询日志与 Binlog 启用与故障排查实录

news 2025/6/24 13:14:43

🚀MySQL 慢查询日志与 Binlog 启用与故障排查实录

📌 背景介绍

🔧 一、慢查询日志开启与排查

✅ 目标配置

✅ 问题现象

✅ 排查步骤

✅ 优化建议：添加缺失索引

🧨 二、Binlog 配置失败与崩溃排查

✅ 原始配置

❌ 问题症状

✅ 原因分析

✅ 解决方法

🧩 配置文件加载确认方法

✅ 最佳实践配置模板（合并）

📘 结语

🚀MySQL 慢查询日志与 Binlog 启用与故障排查实录

本文记录了在配置 MySQL 慢查询日志与二进制日志（Binlog）过程中遇到的问题、排查方法与最终解决方案，适合 DBA 与开发人员参考学习。

📌 背景介绍

在性能调优与数据审计场景中，我们常常需要启用 MySQL 的以下两个日志功能：

慢查询日志（Slow Query Log）：定位执行缓慢或未使用索引的 SQL。
二进制日志（Binary Log）：支持数据恢复、主从复制、审计功能。

但配置中常常因权限、目录、配置冲突等导致日志不生效甚至服务器崩溃。以下是实际案例中的解决过程。

🔧 一、慢查询日志开启与排查

✅ 目标配置

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_output = FILE

✅ 问题现象

配置生效后未生成 /var/log/mysql-slow.log
查询耗时明显未超过 2 秒，却仍被记录为“慢查询”

✅ 排查步骤

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';

发现返回为：

slow_query_log = OFF

说明配置未生效。

手动开启：

SET GLOBAL slow_query_log = 1;

⚠️ 这只对新连接生效。

sudo touch /var/log/mysql-slow.log
sudo chown mysql:mysql /var/log/mysql-slow.log

确保 MySQL 用户有写权限。

日志中发现如下记录：

Query_time: 0.096918  Rows_examined: 45981

虽然查询耗时小于 2 秒，但由于：

log_queries_not_using_indexes = 1

未命中索引，也会被记录为“慢查询”。

✅ 优化建议：添加缺失索引

以字段 actual_owner_name 为例，其字段为 varchar(2000)，为了提高查询效率建议添加如下索引：

CREATE INDEX idx_actual_owner_name
ON t_task_process_instance_detail (actual_owner_name(255));

如果使用 utf8mb4 编码，应限定为 191 字符长度。

🧨 二、Binlog 配置失败与崩溃排查

✅ 原始配置

log_bin = /newfile/mysql-bin
server_id = 3
binlog_format = row
expire_logs_days = 7

❌ 问题症状

MySQL 启动后无任何 binlog 文件
启动过程中出现严重崩溃，日志提示：

MYSQL_BIN_LOG::handle_binlog_flush_or_sync_error

✅ 原因分析

/newfile/ 目录不存在或 MySQL 无权限写入
磁盘满、文件系统错误或不支持 fsync()
配置文件未被正确加载

✅ 解决方法

# 创建目录并授权
sudo mkdir -p /newfile
sudo chown -R mysql:mysql /newfile

推荐将 binlog 写入数据目录，例如：

log_bin = /home/mysql/mysql-bin

确认是否开启成功：

SHOW VARIABLES LIKE 'log_bin';

查看是否为 ON。

🧩 配置文件加载确认方法

mysql --help | grep -A 1 "Default options"

确认实际加载的是哪个配置文件（常见位置 /etc/my.cnf）。

✅ 最佳实践配置模板（合并）

[mysqld]
# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_output = FILE# 二进制日志
log_bin = /home/mysql/mysql-bin
server_id = 3
binlog_format = ROW
expire_logs_days = 7# 基础配置
datadir=/home/mysql/mysql
socket=/home/mysql/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid