MySQL binlog

什么是MySQL binlog

MySQL 的 binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志。binlog 不会记录 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看 MySQL 执行过的所有语句。

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为 .index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为 .00000*）记录数据库所有的 DDL 和 DML(除了数据查询语句)语句事件。

MySQL binlog 以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

MySQL事务日志

说明

innodb 事务日志包括 redo log 和 undo log。

undo log 指事务开始之前，在操作任何数据之前，首先将需操作的数据备份到一个地方。redo log 指事务中操作的任何数据，将最新的数据备份到一个地方。

事务日志的目的：实例或者介质失败，事务日志文件就能派上用场。

redo log

redo log 不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo 中。具体的落盘策略可以进行配置 。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。RedoLog 是为了实现事务的持久性而出现的产物。

undo log

undo log 用来回滚行记录到某个版本。事务未提交之前，Undo 保存了未提交之前的版本数据，Undo 中的数据可作为数据旧版本快照供其他并发事务进行快照读。是为了实现事务的原子性而出现的产物,在 Mysql innodb 存储引擎中用来实现多版本并发控制。

指定 Redo log 记录在 {datadir}/ib_logfile1&ib_logfile2 可通过 innodb_log_group_home_dir 配置指定目录存储。一旦事务成功提交且数据持久化落盘之后，此时 Redo log 中的对应事务数据记录就失去了意义，所以 Redo log 的写入是日志文件循环写入的。

Undo+Redo事务的简化过程

假设有 A、B 两个数据，值分别为 1, 2，开始一个事务，事务的操作内容为：把 1 修改为 3，2 修改为 4，那么实际的记录如下（简化）：

A. 事务开始。
B. 记录 A=1 到 undo log。
C. 修改 A=3。
D. 记录 A=3 到 redo log。
E. 记录 B=2 到 undo log。
F. 修改 B=4。
G. 记录 B=4 到 redo log。
H. 将 redo log 写入磁盘。
I. 事务提交。

快照读和当前读

快照读： SQL 读取的数据是快照版本，也就是历史版本，普通的 SELECT 就是快照读 innodb 快照读，数据的读取将由 cache(原本数据) + undo(事务修改过的数据) 两部分组成。

当前读： SQL 读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改 UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE 都是当前读。

binlog格式

说明

binlog 有三种格式：

1. 基于 SQL 语句的复制（statement-based replication, SBR）
2. 基于行的复制（row-based replication, RBR）
3. 混合模式复制（mixed-based replication, MBR）

Statement

记录的是逻辑 SQL，每一条修改操作的 sql 都会记录在 binlog 中。

优点： 不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO, 提高了性能。

缺点： 无法完全保证 slave 节点与 master 节点数据完全一致。像一些特定函数的功能，slave 可与 master 上要保持一致会有很多相关问题。

Row

5.1.5 版本的 MySQL 开始支持 row level 的复制，它不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog 中可以不记录执行的 sql 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。row 格式的日志内容会非常清楚的记录下每一行数据修改的细节，不会出现某些特定情况下的存储过程。

缺点： 所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

Mixed

从 5.1.8 版本开始，MySQL 提供了 Mixed 格式，实际上就是 Statement 与 Row 的结合。在 Mixed 模式下，（默认情况下）一般的语句修改使用 statment 格式保存 binlog，下面的情况会采用 row 格式的情况包括：

1. 表的存储引擎为 NDB；
2. 使用了 UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT() 等不确定函数；
3. 使用了 INSERT DELAY 语句；
4. 使用了用户定义函数（UDF）；
5. 使用了临时表（temporary table）。

不需要死记硬背这些情况会使用 row，之所以不使用 statement 模式，就是因为简单的逻辑 SQL 无法实现数据的回放，比如使用 UUID()，这个是随机的，无法保证每次结果都一样，为了保证主从复制数据一致性，则必须是记录数据的变化信息。

binglog配置

1. max_binlog_size： 指定了单个二进制日志文件最大值，如果超过该值，则产生新的二进制日志文件后缀名+1，并记录到 .index 文件。
2. binlog_cache_size： 控制缓冲大小，默认大小 32K，基于会话的，因此每开启一个事务就分配一个 binlog_cache_size 大小的缓存，所以不能设置过大。当一个事务的记录大于 binlog_cache_size 时，MySQL 会把缓冲中的日志写入一个临时文件中，因此该值又不能设置太小（否则会频繁刷盘）。
3. sync_binlog： 表示每写缓冲多少次就要同步到磁盘。如果设置为 1，表示采用同步写磁盘的方式来写二进制日志，这时候写操作不使用操作系统的缓冲来写二进制日志。sync_binlog 的默认值为 0，如果使用 InnoDB 存储引擎进行复制，并且想得到最大的可用性，建议将该值设置为 ON（对数据库 IO 系统带来一定影响）。
4. binlog-do-db： 表示需要写入哪些库的日志，默认为空，表示需要同步所有库的日志到二进制日志。
5. binlog-ignore-db： 表示需要忽略写入哪些库的日志，默认为空，表示需要同步所有库的日志到二进制日志。
6. log-slave-update： 若当前数据库是主从复制架构中的 slave 节点，则它不会将从 master 取得并执行的 binlog 写入自己的二进制文件中。但是，如果需要搭建 master->slave->slave 这种架构的复制，则必须设置该参数。
7. binlog_format： 记录二进制日志的格式。

binlog操作

开启

使用 vim 编辑打开 mysql 配置文件：

# 在[mysqld] 区块设置/添加 log-bin=mysql-bin
vi /usr/local/mysql/etc/my.cnf

重启 mysqld 服务使配置生效：

# /usr/local/mysql/bin/mysqld_safe --user=mysql &

也可登录 mysql 服务器，通过 mysql 的变量配置表，查看二进制日志是否已开启。

查看日志列表

mysql> show master logs;

查看binlog的目录

show global variables like "%log_bin%";

查看节点状态

查看主节点状态信息：

mysql> show master status;

查看从节点状态信息:

mysql> show slave status;

刷新log日志

mysql> flush logs;

注：每当 mysqld 服务重启时，会自动执行此命令，刷新 binlog 日志；在 mysqldump 备份数据时加 -F 选项也会刷新 binlog 日志。

清空日志

mysql> reset master;

启动复制

mysql> start slave

可以指定线程类型：IO_THREAD，SQL_THREAD，如果不指定，两个都启动。

什么时候写binlog

binlog 是二进制日志文件，用于记录 MySQL 的数据更新或者潜在更新(比如 DELETE 语句执行删除而实际并没有符合条件的数据)，在 MySQL 主从复制中就是依靠的 binlog。在 MySQL 中开启 binlog 需要设置 my.cnf 中的 log_bin 参数，另外也可以通过 binlog_do_db 指定要记录 binlog 的数据库和 binlog_ignore_db 指定不记录 binlog 的数据库。对运行中的 mysql 要启用 binlog 可以通过命令 SET SQL_LOG_BIN=1 来设置。

需要注意下 innodb 引擎中的 redo/undo log 与 mysql binlog 是完全不同的日志，它们主要有以下几个区别：

1. 层次不同： redo/undo log 是 innodb 层维护的，而 binlog 是 mysql server 层维护的，跟采用何种引擎没有关系，记录的是所有引擎的更新操作的日志记录。
2. 记录内容不同： redo/undo 日志记录的是每个页的修改情况，属于物理日志+逻辑日志结合的方式（redo log 物理到页，页内采用逻辑日志，undo log 采用的是逻辑日志），目的是保证数据的一致性。binlog 记录的都是事务操作内容，比如一条语句 DELETE FROM TABLE WHERE i > 1 之类的，不管采用的是什么引擎，当然格式是二进制的，要解析日志内容可以用这个命令 mysqlbinlog -vv BINLOG。
3. 记录时机不同： redo/undo 日志在事务执行过程中会不断的写入；而 binlog 仅仅在事务提交后才写入到日志。当然，binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

显然，我们执行 SELECT 等不涉及数据更新的语句是不会记 binlog 的，而涉及到数据更新则会记录。要注意的是，对支持事务的引擎如 innodb 而言，必须要提交了事务才会记录 binlog。

binlog 刷新到磁盘的时机跟 sync_binlog 参数相关，如果设置为 0，则表示 MySQL 不控制 binlog 的刷新，由文件系统去控制它缓存的刷新，而如果设置为不为 0 的值则表示每 sync_binlog 次事务，MySQL 调用文件系统的刷新操作刷新 binlog 到磁盘中。设为 1 是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响，一般情况下会设置为 100 或者 0，牺牲一定的一致性来获取更好的性能。

通过命令 SHOW MASTER LOGS 可以看到当前的 binlog 数目。其中第一列是 binlog 文件名，第二列是 binlog 文件大小。可以通过设置 expire_logs_days 来指定 binlog 保留时间，要手动清理 binlog 可以通过指定 binlog 名字或者指定保留的日期，命令分别是：purge master logs to BINLOGNAME 和 purge master logs before DATE。

MySQL主从复制流程

下面以 mysql 主从复制为例，讲解一个从库是如何从主库拉取 binlog，并回放其中的 event 的完整流程，mysql 主从复制的流程如下图所示：

主要分为 3 个步骤：

• 第一步： master 在每次准备提交事务完成数据更新前，将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log event，简称 event)。
• 第二步： slave 启动一个 I/O 线程来读取主库上 binary log 中的事件，并记录到 slave 自己的中继日志(relay log)中。
• 第三步： slave 还会起动一个 SQL 线程，该线程从 relay log 中读取事件并在备库执行，从而实现备库数据的更新。

binlog的应用场景

binlog 本身就像一个螺丝刀，它能发挥什么样的作用，完全取决你怎么使用。就像你可以使用螺丝刀来修电器，也可以用其来固定家具。

读写分离

最典型的场景就是通过 Mysql 主从之间通过 binlog 复制来实现横向扩展，来实现读写分离。如下图所示：

在这种场景下：

• 有一个主库 Master，所有的更新操作都在 master 上进行。
• 同时会有多个 Slave，每个 Slave 都连接到 Master 上，获取 binlog 在本地回放，实现数据复制。
• 在应用层面，需要对执行的 sql 进行判断。所有的更新操作都通过 Master(Insert、Update、Delete等)，而查询操作(Select等)都在 Slave 上进行。由于存在多个 slave，所以我们可以在 slave 之间做负载均衡。通常业务都会借助一些数据库中间件，如 tddl、sharding-jdbc 等来完成读写分离功能。

数据恢复

一些同学可能有误删除数据库记录的经历，或者因为误操作导致数据库存在大量脏数据的情况。如何将脏数据恢复成原来的样子？如果恢复已经被删除的记录？

这些都可以通过反解 binlog 来完成，笔者也是通过这个手段，来恢复业务方的记录。

数据最终一致性

在实际开发中，我们经常会遇到一些需求，在数据库操作成功后，需要进行一些其他操作，如：发送一条消息到 MQ 中、更新缓存或者更新搜索引擎中的索引等。

如何保证数据库操作与这些行为的一致性，就成为一个难题。以数据库与 redis 缓存的一致性为例：操作数据库成功了，可能会更新 redis 失败；反之亦然。很难保证二者的完全一致。

遇到这种看似无解的问题，最好的办法是换一种思路去解决它：不要同时去更新数据库和其他组件，只是简单的更新数据库即可。

如果数据库操作成功，必然会产生 binlog。之后，我们通过一个组件，来模拟的 mysql 的 slave，拉取并解析 binlog 中的信息。通过解析 binlog 的信息，去异步的更新缓存、索引或者发送MQ消息，保证数据库与其他组件中数据的最终一致。

在这里，我们将模拟 slave 的组件，统一称之为 binlog 同步组件。你并不需要自己编写这样的一个组件，已经有很多开源的实现，例如 linkedin 的 databus，阿里巴巴的 canal，美团点评的 puma 等。

当我们通过 binlog 同步组件完成数据一致性时，此时架构可能如下图所示：

在实际开发中，你可以简单的像上图那样，每个应用场景都模拟一个 slave，各自连接到 Mysql 上去拉取 binlog，master 会给每个连接上来的 slave 一份完整的 binlog 拷贝，业务拿到各自的 binlog 之后进行消费，彼此之间互不影响。但是这样，有一些弊端，多个 slave 会给 master 带来一些额外管理上的开销，网卡流量也将翻倍的增长。

我们可以进行一些优化，之所以不同场景模拟多个 slave 来连接 master 获取同一份 binlog，本质上要满足的是：一份 binlog 数据，同时提供给多个不同业务场景使用，彼此之间互不影响。

显然，消息中间件是一个很好的解决方案。现在很多主流的消息中间件，都支持 consumer group 的概念，如 kafka、rocketmq 等。同一个 topic 中的数据，可以由多个不同 consumer group 来消费，且不同的 consumer group 之间是相互隔离的，例如：当前消费到的位置(offset)。

因此，我们完全可以将 binlog，统一都发送到 MQ 中，不同的应用场景使用不同的 consumer group 来消费，彼此之间互不影响。此时架构如下图所示：

通过这样方式，我们巧妙的达到了一份数据多个应用场景来使用。一般，一个 Mysql 实例中可能会创建多个库(Database)，通常我们会将一个库的 binlog 放到一个对应的 MQ 中的 Topic 中。

当将 binlog 发送到 MQ 中后，我们就可以利用MQ的一些高级特性了。例如 binlog 发送到 MQ 过快，消费方来不及消费，可以利用 MQ 的消息堆积能力进行流量削峰。还可以利用 MQ 的消息回溯功能，例如一个业务需要消费历史的 binlog，此时 MQ 中如果还有保存，那么就可以直接进行回溯。

当然，有一些 binlog 同步组件可能实现了类似于 MQ 的功能，此时你就无序再单独的使用 MQ。

异地多活

一个更大的应用场景，异地多活场景下，跨数据中心之间的数据同步。这种场景的下，多个数据中心都需要写入数据，并且往对方同步。以下是一个简化的示意图：

这里有一些特殊的问题需要处理。典型的包括：

• 数据冲突： 双方同时插入了一个相同主键的值，那么往对方同步时，就会出现主键冲突的错误。
• 数据回环： 一个库 A 中插入的数据，通过 binlog 同步到另外一个库 B 中，依然会产生 binlog。此时库 B 的数据再次同步回库 A，如此反复，就形成了一个死循环。

如何解决数据冲突、数据回环，就变成了 binlog 同步组件要解决的问题。同样，业界也有了成熟的实现，比较知名的有阿里开源的 otter，以及摩拜(已经属于美团)的 DRC 等。

总结

binlog 有三种格式，各有优缺点：

statement： 基于 SQL 语句的模式，某些语句和函数如 UUID, LOAD DATA INFILE 等在复制过程可能导致数据不一致甚至出错。

row： 基于行的模式，记录的是行的变化，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。

mixed： 混合模式，根据语句来选用是 statement 还是 row 模式。