mysql之二进制日志（binlog）

二进制日志（binlog） 是 MySQL 服务器层的核心日志，以二进制格式记录数据库中所有修改数据的操作（INSERT/UPDATE/DELETE、CREATE/ALTER 等），不记录查询操作（SELECT）。它是实现主从复制和数据时间点恢复的核心依赖。

binlog 的配置与启用

核心配置参数（my.cnf/my.ini）

# 1. 开启 binlog（必填，指定日志前缀，文件名将为 mysql-bin.000001、mysql-bin.000002...）
log_bin = mysql-bin

# 2. binlog 存储路径（可选，默认在 MySQL 数据目录）
log_bin_basename = /var/lib/mysql

# 3. binlog 索引文件（记录所有 binlog 文件列表，自动生成，无需修改）
log_bin_index = /var/lib/mysql/mysql-bin.index

# 4. binlog 格式（核心参数，3 种可选）
binlog_format = ROW

# 5. binlog 过期清理时间（单位：天，默认 0 表示不清理，建议设 7~15 天）
expire_logs_days = 7

# 6. 事务刷盘策略（关键参数，影响数据安全性和性能）
# sync_binlog=0：由操作系统决定何时刷盘（性能最高，风险最大）
# sync_binlog=1：每次事务提交立即刷盘（符合 ACID，数据最安全，性能略有损耗）
# sync_binlog=N：累计 N 个事务后刷盘（折中方案）
sync_binlog = 1

# 7. 服务器 ID（主从复制必须配置。主从库必须不一样，建议数字为:ip+端口，5.7.3以后版本,必须指定）
server_id = 100

配置生效与状态查看

-- 1. 临时开启 binlog（重启 MySQL 后失效，生产建议配置文件永久开启）
SET GLOBAL log_bin = ON;

-- 2. 查看 binlog 启用状态
SHOW VARIABLES LIKE '%log_bin%';

-- 3. 查看当前 binlog 格式
SHOW VARIABLES LIKE 'binlog_format';

-- 4. 查看 binlog 文件列表（含文件大小、创建时间）
SHOW BINARY LOGS;

-- 5. 查看当前正在写入的 binlog 文件及位置（主从复制核心命令）
SHOW MASTER STATUS;

binlog 的三种格式

binlog 支持三种记录格式，不同格式适用于不同场景。

格式类型	记录内容	优点	缺点	适用场景
STATEMENT（语句级）	记录完整的 SQL 语句	日志体积小，IO 压力小，主从同步带宽消耗低	1. 部分函数（NOW()/RAND()）会导致主从不一致 2. 对自定义函数、存储过程支持差	简单业务场景，无复杂函数 / 存储过程
ROW（行级）	记录每行数据的变更前后状态（如：某行 id=1 的 name 从 A 改为 B）	1. 主从一致性最高，无函数 / 存储过程兼容问题 2. 数据恢复粒度更细	日志体积大，IO 压力大，主从同步带宽消耗高	复杂业务场景（含函数 / 存储过程）、主从复制要求高一致性
MIXED（混合模式）	自动选择 STATEMENT/ROW 格式： - 普通 SQL 用 STATEMENT - 复杂 SQL（含函数）用 ROW	兼顾体积和一致性，平衡性能与可靠性	对运维人员的理解要求更高

格式切换与验证

-- 临时切换为 ROW 格式
SET GLOBAL binlog_format = 'ROW';

-- 验证格式生效
SHOW VARIABLES LIKE 'binlog_format';

-- 插入测试数据，查看 binlog 内容
INSERT INTO user(name, age) VALUES('test_binlog', 25);

binlog日志相关概念

二进制日志文件，顾名思义，它是二进制的，所以我们不能直接使用cat命令进行查看，而是需要通过一些别的命令查看其内容

1、Binlog Events（事件）

Binlog 以 “事件（Event）” 为单位，记录所有导致数据变化的操作（DDL/DML/TCL）。

核心概念

• 本质：Binlog 文件里的每一条记录，都是一个 Event。
• 内容：包含操作类型、时间戳、服务器 ID、具体数据（Row 模式）或 SQL 语句（Statement 模式）。
• 格式：每个 Event 由 Event Header（固定头部）和 Event Data（数据体）组成。

常见 Event 类型

事件类型	英文名称	作用说明
格式描述事件	FORMAT_DESCRIPTION_EVENT	Binlog 文件的第一个事件，定义文件格式版本MySQL
查询事件	QUERY_EVENT	Statement 模式下，记录原始 SQL（如 CREATE/INSERT）
事务提交事件	XID_EVENT	标记一个事务的结束（提交）
表映射事件	TABLE_MAP_EVENT	Row 模式前置，定义即将修改的表结构
行写入事件	WRITE_ROWS_EVENT	Row 模式，对应 INSERT 操作
行更新事件	UPDATE_ROWS_EVENT	Row 模式，对应 UPDATE 操作
行删除事件	DELETE_ROWS_EVENT	Row 模式，对应 DELETE 操作
日志轮转事件	ROTATE_EVENT	文件写满时，记录下一个日志文件名

事件查看示例

# 查看binlog内容（重点关注 at xxx, end_log_pos xxx, Query/Write_rows等）
mysqlbinlog -v /var/lib/mysql/mysql-bin.000001

典型输出片段

# at 1234          <-- 起始Position
#240411 10:00:00 server id 1  end_log_pos 1567  CRC32 0x12345678  Query
thread_id=2  exec_time=0  error_code=0
use `test`; INSERT INTO t1 VALUES (1,'a');  <-- Event Data

2、Binlog Position（位置）

Position 是 Binlog 文件内的字节偏移量（Byte Offset），用于精确定位某个 Event。

两个关键 Position

• Pos (起始位置)

  • 格式：at 数字（如 at 1234）
  • 含义：该 Event 第一个字节 在文件中的位置。

• End_log_pos (结束位置)

  • 格式：end_log_pos 数字（如 end_log_pos 1567）
  • 含义：该 Event 最后一个字节的下一位，即下一个 Event 的起始位置。
  • 公式：End_log_pos = Pos + Event 长度

3、 核心作用（运维必备）

主从复制（传统模式）

• 从库通过 MASTER_LOG_FILE='mysql-bin.000001' 和 MASTER_LOG_POS=1234 定位起点。
• 从库 Slave_IO_Running 线程读取主库 Position 之后的 Events。

基于时间点恢复（PITR）

• 误删数据后，截取 binlog：

mysqlbinlog --start-position=1000 --stop-position=2000 mysql-bin.000001 | mysql -u root -p

状态查看

SHOW MASTER STATUS;
-- 输出：File=mysql-bin.000001, Position=1567
-- 含义：下一个新Event将从 1567 字节开始写入。

解析 binlog（mysqlbinlog 工具）

binlog 是二进制文件，需用 mysqlbinlog 工具解析为可读的 SQL 语句，核心参数如下：

参数	作用
-v	显示行级变更的详细信息（ROW 格式必备）
-vv	显示更详细的字段类型信息
--start-datetime	解析指定开始时间的 binlog
--stop-datetime	解析指定结束时间的 binlog
--start-position	解析指定起始位置的 binlog
--stop-position	解析指定结束位置的 binlog

示例

# 1. 解析指定 binlog 文件（STATEMENT 格式可直接看到 SQL）
mysqlbinlog /var/lib/mysql/mysql-bin.000003

# 2. 解析 ROW 格式的 binlog（需加 -v 参数）
mysqlbinlog -v /var/lib/mysql/mysql-bin.000003

# 3. 按时间范围解析（恢复 2026-04-11 10:00-11:00 的数据）
mysqlbinlog --start-datetime="2026-04-11 10:00:00" --stop-datetime="2026-04-11 11:00:00" /var/lib/mysql/mysql-bin.000003 > binlog_20260411.sql

# 4. 按位置范围解析（恢复从 156 到 500 位置的操作）
mysqlbinlog --start-position=156 --stop-position=500 /var/lib/mysql/mysql-bin.000003 > binlog_position.sql

binlog 文件切换与清理

（1）手动切换 binlog

当 binlog 文件达到一定大小或需要归档时，可手动切换生成新文件：

-- 切换 binlog，生成新文件（如 mysql-bin.000004）
FLUSH BINARY LOGS;

（2）自动清理过期 binlog

通过 expire_logs_days 参数配置自动清理，也可手动清理：

-- 手动清理 2026-04-01 之前的 binlog
PURGE BINARY LOGS BEFORE '2026-04-01 00:00:00';

-- 手动清理指定文件之前的 binlog（不包含 mysql-bin.000003）
PURGE BINARY LOGS TO 'mysql-bin.000003';

注意：主从复制场景下，清理前需确保从库已同步完待清理的 binlog，否则会导致从库同步失败。

面试高频问题

1、binlog 的三种格式各有什么优缺点？

答：① STATEMENT 体积小、性能高，但主从一致性差；

② ROW 一致性最高，但体积大、性能消耗高；

③ MIXED 自动选择格式，兼顾性能和一致性

2、为什么 sync_binlog=1 是最安全的配置？

答：因为 sync_binlog=1 表示每次事务提交都会立即将 binlog 刷到磁盘，即使 MySQL 崩溃或服务器断电，也不会丢失已提交的事务 binlog，保证数据可以完整恢复。

3、主从复制中，binlog 是如何传递的？

答：主库的 dump 线程将 binlog 发送给从库的 IO 线程，从库 IO 线程将 binlog 写入中继日志，然后从库的 SQL 线程解析中继日志并重放 SQL，实现数据同步。

4、误删数据后，如何通过 binlog 恢复？

答：先恢复最近的全量备份，然后解析全量备份之后到误操作之前的 binlog，将解析后的 SQL 执行，即可实现增量恢复。