查看MySql操作日志

MySQL 操作日志查看与数据恢复：从日志分析到误操作修复

当遇到数据不一致、疑似误删等问题时，MySQL 的二进制日志（binlog）是关键的排查与恢复依据。本文详细介绍如何通过 binlog 查看操作记录、定位误操作，并通过日志进行数据恢复。

二进制日志（binlog）的基本概念

二进制日志（binlog）是 MySQL 记录所有数据变更操作（INSERT/UPDATE/DELETE、CREATE/ALTER 等）的日志文件，不记录查询操作（SELECT）。其核心作用包括：

主从复制（Slave 通过 binlog 同步 Master 数据）。
数据恢复（通过回放 binlog 恢复误操作前的状态）。
审计追踪（记录所有数据变更的时间、用户、操作内容）。

查看 binlog 相关配置

确认 binlog 是否开启

-- 查看 binlog 启用状态（ON 为开启，OFF 为关闭）
show variables like 'log_bin';

若未开启（log_bin = OFF），则无法通过 binlog 追溯操作，需在 my.cnf 中配置开启（重启生效）：

[mysqld]
log_bin = mysql-bin  # 日志文件名前缀（如 mysql-bin.000001）
server-id = 1  # 必须配置，主从复制和 binlog 依赖唯一 server-id

查看当前 binlog 信息

-- 查看当前正在写入的 binlog 文件及位置
show master status;
-- 输出：File（当前日志文件名）、Position（当前写入位置）

-- 查看所有 binlog 文件列表
show binary logs;
-- 输出：Log_name（文件名）、File_size（文件大小）

查看 binlog 中的操作记录

binlog 以二进制格式存储，需通过工具解析为可读内容。

使用 `mysqlbinlog` 命令（推荐）

mysqlbinlog 是 MySQL 自带的 binlog 解析工具，支持按时间、数据库、位置等筛选操作。

基本用法：

# 解析指定 binlog 文件（全量输出）
mysqlbinlog /var/lib/mysql/mysql-bin.000329

# 筛选指定数据库的操作（如 exam_admin）
mysqlbinlog /var/lib/mysql/mysql-bin.000329 --database=exam_admin

# 按时间范围筛选（需指定时区，避免时间偏差）
mysqlbinlog /var/lib/mysql/mysql-bin.000329 \
  --start-datetime='2021-06-30 10:00:00' \
  --stop-datetime='2021-06-30 11:00:00' \
  --database=exam_admin

# 按位置范围筛选（通过 show binlog events 确定位置）
mysqlbinlog /var/lib/mysql/mysql-bin.000329 \
  --start-position=107 \
  --stop-position=1000 \
  --database=exam_admin

输出解读：

解析后的日志包含操作的详细信息，例如：

# at 154
#210630 10:30:00 server id 1  end_log_pos 388  Query  thread_id=123  exec_time=0  error_code=0
SET TIMESTAMP=1625034600/*!*/;
DELETE FROM user WHERE id = 123 /*!*/;

#210630 10:30:00：操作时间。
thread_id=123：执行操作的线程 ID（可关联 processlist 查用户）。
DELETE FROM user ...：具体操作语句。

使用 SQL 命令 `show binlog events`

通过 SQL 语句查看 binlog 内容（适合快速预览，功能不如 mysqlbinlog 全面）：

-- 查看指定 binlog 文件的事件（从位置 0 开始）
show binlog events in 'mysql-bin.000329';

-- 按位置范围查看（start_pos 起始位置，limit 限制条数）
show binlog events in 'mysql-bin.000329' from 154 limit 10;

输出字段说明：

Log_name：binlog 文件名。
Pos：事件起始位置。
Event_type：事件类型（Query 为 SQL 操作，Rotate 为日志切换）。
Server_id：执行操作的服务器 ID。
Info：具体操作内容（如 SQL 语句）。

通过 binlog 恢复误操作数据

若确认存在误操作（如误删、误更新），可通过回放 binlog 中 “误操作前的正确数据” 或 “反向执行误操作” 进行恢复。

1. 恢复步骤（以误删为例）

（1）定位误操作的时间 / 位置

通过 mysqlbinlog 找到误删语句的时间或位置，例如：

#210630 10:30:00 执行了 DELETE FROM user WHERE id = 123;

（2）导出误操作前的正确数据

导出从日志开始到误操作前的所有 SQL（即 “误操作前的正常数据变更”）：

mysqlbinlog /var/lib/mysql/mysql-bin.000329 \
  --start-position=0 \
  --stop-datetime='2021-06-30 10:29:59' \  # 早于误操作时间 1 秒
  --database=exam_admin > /tmp/restore.sql

（3）执行恢复 SQL

在目标数据库中执行导出的 SQL，回放正确的操作：

mysql -u root -p exam_admin < /tmp/restore.sql

2. 反向执行误操作（适合单条语句）

若误操作是单条 DELETE 或 UPDATE，可手动生成反向语句（如 DELETE 对应 INSERT，UPDATE 对应反向 UPDATE），直接执行即可。

例如：误删 id=123 的用户，可从 binlog 中获取该用户的原始数据，执行：

INSERT INTO user (id, name, age) VALUES (123, '张三', 25);  -- 原始数据

3. 注意事项

恢复前备份：恢复前务必备份当前数据库，避免操作失误导致二次数据丢失。
暂停业务：恢复期间建议暂停业务写入，防止新数据被覆盖。
时间 / 位置准确性：确保 --start-datetime/--stop-position 准确，避免遗漏或多包含操作。

进阶：通过 binlog 定位操作人

binlog 本身不直接记录用户名，但可通过 thread_id 关联 performance_schema 中的线程信息追溯：

从 binlog 中获取误操作的 thread_id（如 thread_id=123）。

查询该线程对应的用户：

-- 查看线程 ID 对应的用户和主机（需在操作发生时记录，或通过历史日志关联）
SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = 123;

若操作已结束，可结合general_log（通用日志，记录所有连接和语句）进一步追溯