MySQL的binlog

上一篇Mysql的redolog：MySQL的redo log-优快云博客

redo log是物理日志，记录的是“在某个数据页做了什么修改”，属于Innodb存储引擎。

Binlog 是 MySQL Server 层提供的一种日志功能，它是 逻辑日志，记录了所有对数据库进行更改的 SQL语句（如 INSERT、UPDATE、DELETE 等）以及一些其他元数据信息。

 binlog 的主要用途

1. 数据恢复：通过 mysqlbinlog 工具将 binlog 日志解析成可执行的 SQL，恢复数据库到特定时间点。
2. 主从复制：主数据库将 binlog 发送到从数据库，从数据库通过重放 binlog 达到数据同步的目的。
3. 数据审计：通过解析 binlog，可以审查数据变更的历史记录。

binlog日志的格式

binlog日志有三种格式，可以通过binlog_format参数设置：

1.语句模式（Statement-Based Logging，SBL）：

        记录的是执行的 SQL 语句本身，例如：

UPDATE users SET age = age + 1 WHERE id = 100;

优点：占用日志空间较小。

缺点：某些情况下会有 非确定性问题，如使用 NOW()、RAND() 等函数，可能导致主从数据不一致，从机执行这个SQL的结果可能与主机不同。

2.行模式（Row-Based Logging，RBL）：

记录的是数据变更的 前后值，例如：

Before image: {id: 100, name: "Alice", age: 25}
After image:  {id: 100, name: "Alice", age: 26}

优点：精确记录了每行数据的变化，主从数据一致性更强。

缺点：日志文件较大，尤其是批量操作时。

3.混合模式（Mixed-Based Logging，MBL）：（建议使用）

• 根据具体的 SQL 语句自动选择语句模式或行模式。
• 例如，对于简单的 INSERT、DELETE 使用语句模式；对于复杂的操作（如使用函数）使用行模式。

Binlog的存储机制

• Binlog 是以二进制文件的形式存储在磁盘上的，默认目录为 MySQL 数据目录下的 binlog.* 文件。
• 每个 binlog 文件都有一个唯一的编号，例如 binlog.000001，binlog.000002。
• 每次重启 MySQL 或手动执行 FLUSH LOGS，都会切换到新的 binlog 文件。
• MySQL 会同时维护一个 binlog.index 文件，记录了所有 binlog 文件的文件名，便于管理和查找。

binlog的特性

Binlog 属于 MySQL Server 层，与具体的存储引擎无关。这意味着，不论是 InnoDB、MyISAM 还是其他存储引擎，只要发生了数据更改，都会生成 binlog 日志。

和redolog一样，有一个参数来指定binlog的刷盘策略：sync_binlog，可以配置成0、1、N(N>1)。

值	行为描述	特点
0	Binlog 写入操作由操作系统自行决定，可能会批量写入，也可能延迟刷盘。(只调用write写到操作系统的page cache,什么时候fsync同步到磁盘文件有操作系统决定)	性能最好，但崩溃时可能丢失较多 Binlog 数据。
1	每次事务提交时，立即调用 fsync() 将 Binlog 写入磁盘。	数据最安全，但性能开销较高。
>1	每 N 次事务提交后，调用一次 fsync() 将 Binlog 写入磁盘。例如 sync_binlog=1000 表示每 1000 次事务刷盘。	性能和数据安全性之间的折中。

binlog与redolog的对比

特性	Binlog	Redo Log
作用层级	MySQL Server 层	InnoDB 存储引擎
日志类型	逻辑日志，记录 SQL 语句或行变化	物理日志，记录页级别的物理变更
作用	主从复制、数据恢复、审计	崩溃恢复、事务一致性
刷盘策略	追加写入，由系统控制	可配置（如每秒一次、每事务一次等）
与存储引擎关系	无关，适用于所有存储引擎	仅适用于 InnoDB 存储引擎

还有一个问题：两种日志的写入顺序可能导致不一致

Redo Log 先写：

• 如果事务已写入 Redo Log，但 Binlog 还未写入磁盘，系统崩溃后，Binlog 丢失，导致主从复制的数据不一致。

Binlog 先写：

• 如果事务已写入 Binlog，但 Redo Log 未写入，崩溃恢复后，事务数据不完整。

需要MySQL 中的两阶段来解决