Mysql: Bin log原理以及三种格式

目录

一、什么是 Binlog？

二、Binlog 的应用场景与案例

1. 数据恢复 (Point-in-Time Recovery) 

2. 主从复制 (Master-Slave Replication)

3. 数据审计

 三、Binlog 的三种格式

1. STATEMENT 模式 (Statement-Based Logging - SBL)

2. ROW 模式 (Row-Based Logging - RBL)

3. MIXED 模式 (Mixed-Based Logging - MBL)

一、什么是 Binlog？

        Binlog（Binary Log）是 MySQL 数据库中一种二进制日志文件，它记录了所有对数据库的更改操作，包括数据的插入、更新、删除，以及表结构（DDL）的修改等。Binlog 以事件（event）的形式记录这些操作，并且是逻辑性的，也就是说它记录的是SQL语句的逻辑，而不是数据页的物理变化。

        它是 MySQL 数据库服务层提供的，与具体的存储引擎无关。无论你用的是 InnoDB、MyISAM 还是其他存储引擎，只要是对数据库有更改的操作，Server 层都会将它记录到 Binlog 中。

        Binlog 的主要目的是为了记录数据库发生改变的事件，并且它是一个追加写入（append-only）的文件，一旦写入就不会被修改。

二、Binlog 的应用场景与案例

1. 数据恢复 (Point-in-Time Recovery) 

场景： 假设你的电商网站数据库在今天下午 2 点，因为运维人员误执行了一条 ：

DELETE FROM products; 

导致商品表所有数据丢失。你昨天晚上 12 点对数据库做了全量备份。

解决方案：

1. 恢复全量备份： 首先，将昨天晚上 12 点的全量备份恢复到数据库中，这样数据就恢复到了昨天晚上 12 点的状态。

2. 应用 Binlog： 然后，你需要找到从昨天晚上 12 点备份开始到今天下午 2 点之间（误删除之前）的所有 Binlog 文件。使用 mysqlbinlog 工具解析这些 Binlog，并将其中的 SQL 语句应用到恢复后的数据库上。这样，数据库就会从昨天晚上 12 点逐步恢复到今天下午 2 点误删除之前的状态。

案例分析：

        通过这种方式，即使发生了灾难性的数据丢失，只要有定期备份和完整的 Binlog，就可以将数据库恢复到任意一个精确的时间点，最大限度地减少数据损失。这是 Binlog 在数据安全中最核心的价值之一。

2. 主从复制 (Master-Slave Replication)

场景： 为了提高数据库的读取性能和可用性，你希望搭建一个 MySQL 主从复制集群，主库负责写入操作，从库负责读取操作。这也是Binlog的核心功能，我们来重点讲解一下：

什么是 MySQL 主从复制？

        MySQL 主从复制是一种将数据从一个 MySQL 数据库服务器（主库 Master）自动同步到一个或多个其他 MySQL 数据库服务器（从库 Slave）的过程。主库处理所有的写入操作，而从库则接收并应用主库的更新。

这个过程就像是：

• 主库 负责记录所有最新的变化（比如账本上的每一笔交易）。

• 从库 则实时地复制这份账本，保证自己的数据和主库保持一致。

Binlog 在 MySQL 主从复制中的实现与底层原理：

        MySQL 的主从复制是一个异步（或半同步）的数据同步过程，其核心就是通过 Binlog 来实现的。整个过程可以概括为三个主要步骤：写入 Binlog、传输 Binlog 和 重放 Binlog。

主从复制实现原理图：

1. 主库：写入 Binlog (Binary Log)

• 原理： 当主库执行任何数据修改操作（INSERT、UPDATE、DELETE、DDL 等）时，在事务提交之前，这些操作会被记录到 Binlog 中。

• 实现细节：

  • 事件记录： MySQL 服务器会将这些操作封装成 Binlog 事件。每个事件都包含了操作的类型、作用的数据库和表、以及具体的数据变更内容（取决于 Binlog 格式，这个我们之后会提到：SQL 语句或行数据）。

  • 写入文件： 这些事件会按照顺序写入到当前活跃的 Binlog 文件中。Binlog 文件是追加写入的，当一个文件达到最大大小时，MySQL 会自动创建一个新的 Binlog 文件。

  • 索引文件： MySQL 还会维护一个 Binlog 索引文件（通常是 .index 后缀），记录了所有 Binlog 文件的名称，以便快速查找。

  • 刷盘机制： 为了保证数据安全，Binlog 事件通常会定期或在每次事务提交时被刷写（fsync）到磁盘上，这由 sync_binlog 参数控制。设置为 1 表示每次事务提交都刷盘，是最安全的，但性能开销最大。

• 案例：

  • 你连接到主库，执行：

    CREATE DATABASE my_shop;
    USE my_shop;
    CREATE TABLE products (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100),
        price DECIMAL(10, 2)
    );
    INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);
    UPDATE products SET price = 1250.00 WHERE id = 1;
    

  • 这些操作会依次被记录到主库的 Binlog 文件中（例如 mysql-bin.000001）。如果 Binlog 格式是 ROW，那么 INSERT 会记录插入的行数据，UPDATE 会记录更新前后的行数据。

2. 从库：I/O 线程 (IO Thread) - 传输 Binlog

• 原理： 从库上有一个专门的 I/O 线程，它负责连接到主库，并请求主库发送 Binlog 事件流。

• 实现细节：

  • 连接主库： 从库的 I/O 线程使用在从库配置中设置的主库 IP 地址、端口、复制用户和密码来连接主库。

  • 发送请求： 从库会告诉主库它已经接收并处理到的 Binlog 文件名和位置（这是通过 CHANGE MASTER TO 命令设定的）。

  • 主库 Dump 线程： 主库会为此从库启动一个Dump 线程 (Binlog Dump Thread)。这个 Dump 线程会读取主库的 Binlog 文件，并将其发送给从库的 I/O 线程。

  • 写入 Relay Log： 从库的 I/O 线程接收到 Binlog 事件后，会将这些事件顺序写入到自己的中继日志 (Relay Log) 中。中继日志也是一系列文件，与 Binlog 结构类似。同时，I/O 线程会更新 master.info 文件（或 mysql.slave_master_info 表），记录当前已接收到的 Binlog 文件名和位置，以便下次断开重连时可以从上次中断的地方继续。

3. 从库：SQL 线程 (SQL Thread) - 重放 Binlog

• 原理： 从库的 SQL 线程负责读取中继日志中的事件，并在从库上逐个执行这些事件，从而使从库的数据与主库保持同步。

• 实现细节：

  • 读取 Relay Log： SQL 线程会从 Relay Log 中读取事件。

  • 解析并执行： 对于每个事件，SQL 线程会解析其内容，并将其作为 SQL 语句（或行数据变更）在从库上执行。

  • 事务处理： 如果主库的 Binlog 中是一个事务，那么从库的 SQL 线程也会将这个事务作为一个整体进行提交，确保事务的原子性。

  • 进度记录： SQL 线程会实时更新 relay-log.info 文件（或 mysql.slave_relay_log_info 表），记录当前已经执行到的 Relay Log 文件名和位置，以及对应的主库 Binlog 文件名和位置，这被称为复制位点。

3. 数据审计

场景： 公司的安全规定要求记录所有对敏感数据的修改历史，以便在出现问题时进行追溯。

解决方案：

1. 启用 Binlog： 确保数据库启用了 Binlog。

2. 解析 Binlog： 定期或实时地使用 mysqlbinlog 工具解析 Binlog 文件。mysqlbinlog 可以将二进制格式的 Binlog 转换为可读的 SQL 语句。

3. 审计分析： 将解析出来的 SQL 语句导入到日志分析系统或专门的审计工具中，进行进一步的分析、过滤和存储。这样就可以清晰地看到谁在什么时候对哪个表做了什么操作。

案例分析：

通过解析 Binlog，你可以发现：

• 非法操作： 哪些用户执行了非授权的数据修改。

• 数据变更溯源： 某个字段的值是在何时被谁修改的，修改前后的值是什么。

• 性能分析： 某些高频操作对数据库的性能影响。

 三、Binlog 的三种格式

MySQL 的 Binlog 记录格式由 binlog_format 参数控制，主要有三种：STATEMENT、ROW 和 MIXED。

1. STATEMENT 模式 (Statement-Based Logging - SBL)

• 记录内容： 这种模式记录的是导致数据改变的 SQL 语句本身。例如，如果你执行 UPDATE users SET age = age + 1 WHERE id = 1;，Binlog 中记录的就是这条 SQL 语句。

• 优点：

  • 日志文件小： 只需要记录执行的 SQL 语句，而不是每条语句影响到的每一行数据的变化，因此 Binlog 文件通常比较小。这在网络传输和存储方面有优势。

  • 易于阅读： 对于人来说，直接查看 SQL 语句更容易理解数据库在特定时间点做了什么操作。

• 缺点：

  • 可能导致主从数据不一致： 这是 STATEMENT 模式最大的问题。某些不确定性函数（如 UUID()、NOW()、RAND()）、存储过程、触发器等，在主库和从库上执行时可能产生不同的结果，从而导致主从数据不一致。

  • 对复杂操作支持不好： 对于像 LOAD DATA INFILE 或包含子查询的复杂 UPDATE 语句，有时难以准确地在从库上重放。

案例分析：

假设主库和从库的当前时间不同（在复制中这很常见）。

你执行以下 SQL 语句：

INSERT INTO orders (order_time) VALUES (NOW());

• 主库： 在主库上执行时，NOW() 函数会获取主库当前时间，比如 2025-07-09 14:30:00。Binlog 记录的也是 INSERT INTO orders (order_time) VALUES (NOW()); 这条语句。

• 从库： 从库收到这条 Binlog 后，会重放这条 SQL 语句。如果从库的时间比主库快或慢，或者因为网络延迟等原因导致从库执行时间点和主库不同，那么从库上的 NOW() 函数获取的时间可能与主库不同（例如 2025-07-09 14:30:05），从而导致主从 order_time 字段的值不一致。

再比如：

DELETE FROM products LIMIT 1;

• 这条语句没有 ORDER BY 子句，删除哪一行是不确定的。

• 主库： 可能删除了 id = 10 的产品。

• 从库： 从库收到语句后重放，可能删除了 id = 20 的产品，导致主从数据不一致。 

2. ROW 模式 (Row-Based Logging - RBL)

• 记录内容： 这种模式记录的是 行级别的数据变更，包括哪些行被修改了，以及修改前和修改后的行数据。它不记录具体的 SQL 语句。

• 优点：

  • 主从数据一致性高： 这是最主要的优点。因为它记录的是最终的数据变更结果，所以无论 SQL 语句有多复杂或是否包含不确定性函数，从库都能准确地复制主库的变更，保证数据一致性。

  • 更安全： 避免了 STATEMENT 模式可能导致的主从不一致问题，尤其适用于复杂的数据库操作。

  • 可以进行精确恢复： 在数据恢复时，可以精确到行的变更。

• 缺点：

  • 日志文件可能非常大： 特别是对于 UPDATE 或 DELETE 大量行的操作。例如，一个 UPDATE 语句更新了 100 万行数据，Binlog 中就会记录 100 万行数据的变更，导致 Binlog 文件急剧膨胀，占用大量磁盘空间，并增加网络传输负担。

  • 不易于阅读： Binlog 中记录的是二进制的行数据变化，不直观，无法直接像 SQL 语句那样阅读和理解。

案例分析：

依然是刚才的 INSERT INTO orders (order_time) VALUES (NOW()); 语句。

• 主库： 执行 INSERT 后，主库会将新插入行的所有列的值（包括 order_time 的具体值，例如 2025-07-09 14:30:00）记录到 Binlog 中。

• 从库： 从库收到 Binlog 后，直接按照 Binlog 中记录的数据值插入一行，而不会重新计算 NOW()。因此，主从数据库中的 order_time 字段会完全一致。

再比如：

UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

• 主库： 如果有 1000 行 category = 'electronics' 的产品，Binlog 会记录这 1000 行的 每一行 的变更（旧值和新值）。

• 从库： 从库会根据 Binlog 中记录的 1000 行的变更信息，直接更新相应的行，而不是再次执行 UPDATE 语句。这样保证了主从数据的一致性。

3. MIXED 模式 (Mixed-Based Logging - MBL)

• 记录内容： MIXED 模式是 STATEMENT 和 ROW 模式的混合体。MySQL 会根据执行的 SQL 语句，自动判断 应该使用 STATEMENT 模式还是 ROW 模式来记录 Binlog。

  • 对于那些不会导致主从不一致的 SQL 语句（例如简单的 SELECT、INSERT INTO ... VALUES、DELETE FROM ... WHERE id = X），MySQL 会使用 STATEMENT 模式，以保持 Binlog 文件较小。

  • 对于那些可能导致主从不一致的语句（例如包含 NOW()、RAND() 的语句，或者没有 ORDER BY 的 LIMIT 语句等），MySQL 会自动切换到 ROW 模式来记录。

• 优点：

  • 兼顾一致性和性能： 在保证数据一致性的前提下，尽量减小 Binlog 文件的大小。这是目前推荐的 Binlog 格式，平衡了两种模式的优缺点。

  • 智能切换： 由 MySQL 自动判断，无需 DBA 手动干预。

• 缺点：

  • 理解复杂性： 对于分析 Binlog 的人来说，Binlog 中可能同时包含 STATEMENT 和 ROW 事件，需要更复杂的解析工具来处理。

案例分析：

• 执行 INSERT INTO users (name) VALUES ('Alice');：这是一条简单且确定性的 SQL 语句，MySQL 会以 STATEMENT 模式记录。

• 执行 INSERT INTO orders (order_time) VALUES (NOW());：由于 NOW() 函数的不确定性，MySQL 会自动切换到 ROW 模式记录，确保 order_time 在主从之间完全一致。

• 执行 DELETE FROM temp_logs LIMIT 100;：如果没有 ORDER BY 子句，删除哪些行是不确定的，MySQL 会以 ROW 模式记录。如果加上 ORDER BY 子句（例如 DELETE FROM temp_logs ORDER BY log_id LIMIT 100;），则可以以 STATEMENT 模式记录，因为删除的顺序是确定的。

总结：

• STATEMENT： 最古老的模式，Binlog 文件小，但存在主从不一致的风险。不推荐在新环境中使用。

• ROW： 最安全、最可靠的模式，确保主从数据一致，但 Binlog 文件可能很大。在对数据一致性要求极高，且不介意 Binlog 文件大小的场景下使用。

• MIXED： 目前最推荐的模式。它智能地平衡了安全性和效率，既保证了数据一致性，又在可能的情况下减小了 Binlog 文件。

在实际生产环境中，MIXED 模式通常是最佳选择，因为它在保证数据一致性的同时，能够有效地管理 Binlog 文件的大小。