MySQL学习笔记9——binlog格式、主备基本原理、主备延迟_binlog_format=statement

---

一、MySQL 主备的基本原理

1.主备切换流程

在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行。这样可以保持节点 B 和 A 的数据是相同的。

当需要切换的时候，就切成状态 2。这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。

在状态 1 中，虽然节点 B 没有被直接访问，但是我依然建议你把节点 B（也就是备库）设置成只读（readonly）模式。这样做，有以下几个考虑：

• 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
• 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
• 可以用 readonly 状态，来判断节点的角色。

update 语句在节点 A 执行，然后同步到节点 B 的完整流程图。备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。

一个事务日志同步的完整过程是这样的：

• 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
• 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
• 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
• 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
• sql_thread 读取中转日志，解析出日志里的命令，并执行。

2.binlog 的三种格式对比

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

如果要在表中删除一行数据的话，我们来看看这个 delete 语句的 binlog 是怎么记录的。

注意，下面这个语句包含注释，如果你用 MySQL 客户端来做这个实验的话，要记得加 -c 参数，否则客户端会自动去掉注释。

delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;

binlog_format=statement

binlog 里面记录的就是 SQL 语句的原文。你可以用类似

show binlog events in 'binlog.000030';命令（文件名按自己的）看 binlog 中的内容。

• 第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’
• 第二行是一个 BEGIN，跟第四行的 commit 对应，表示中间是一个事务；
• 第三行就是真实执行的语句了。可以看到，在真实执行的 delete 命令之前，还有一个“use ‘test’”命令。这条命令不是我们主动执行的，而是 MySQL 根据当前要操作的表所在的数据库，自行添加的。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到 test 库的表 t。
  use 'test’命令之后的 delete 语句，就是我们输入的 SQL 原文了。可以看到，binlog“忠实”地记录了 SQL 命令，甚至连注释也一并记录了。
• 最后一行是一个 COMMIT。你可以看到里面写着 xid=61。

运行这条 delete 命令产生了一个 warning，原因是当前 binlog 设置的是 statement 格式，并且语句中有 limit，所以这个命令可能是 unsafe 的。

• 如果 delete 语句使用的是索引 a，那么会根据索引 a 找到第一个满足条件的行，也就是说删除的是 a=4 这一行；
• 但如果使用的是索引 t_modified，那么删除的就是 t_modified='2018-11-09’也就是 a=5 这一行。

可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。

insert into t values(10,10, now());

在statement 格式下，用 mysqlbinlog 工具来看看：

在记录 event 的时候，多记了一条命令：SET TIMESTAMP=1546103491。它用 SET TIMESTAMP 命令约定了接下来的 now() 函数的返回时间。就确保了主备数据的一致性。

用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：
mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

这个命令的意思是，将 master.000001 文件里面从第 2738 字节到第 2973 字节中间这段内容解析出来，放到 MySQL 去执行。

binlog_format=‘row’

可以看到，与 statement 格式的 binlog 相比，前后的 BEGIN 和 COMMIT 是一样的。但是，row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。

• Table_map event，用于说明接下来要操作的表是 test 库的表 t;
• Delete_rows event，用于定义删除的行为。

要借助 mysqlbinlog 工具，用mysqlbinlog -vv data/master.000001 --start-position=8900;这个命令解析和查看 binlog 中的内容。

server id 1，表示这个事务是在 server_id=1 的这个库上执行的。每个 event 都有 CRC32 的值，这是因为我把参数 binlog_checksum 设置成了 CRC32。

Table_map event 跟在图 5 中看到的相同，显示了接下来要打开的表，map 到数字 226。现在我们这条 SQL 语句只操作了一张表，如果要操作多张表呢？每个表都有一个对应的 Table_map event、都会 map 到一个单独的数字，用于区分对不同表的操作。

在 mysqlbinlog 的命令中，使用了 -vv 参数是为了把内容都解析出来，所以从结果里面可以看到各个字段的值（比如，@1=4、 @2=4 这些值）。

binlog_row_image 的默认配置是 FULL，因此 Delete_event 里面，包含了删掉的行的所有字段的值。如果把 binlog_row_image 设置为 MINIMAL，则只会记录必要的信息，在这个例子里，就是只会记录 id=4 这个信息。

最后的 Xid event，用于表示事务被正确地提交了。

当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题。

binlog_format=‘mixed’

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。

但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。

mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

尽量把 MySQL 的 binlog 格式设置成 row。最明显的好处：恢复数据。

• 执行 delete 语句，row 格式的 binlog 也会把被删掉的行的整行信息保存起来。可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。
• 执行 insert 语句，**直接把 insert 转成 delete **，删除掉这被误插入的一行数据就可以了。
• 执行 update 语句，binlog 里面会记录修改前整行的数据和修改后的整行数据。如果误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

3.循环复制问题

实际生产上使用比较多的是双 M 结构，区别只是多了一条线，即：节点 A 和 B 之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。

业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（我建议你把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。

那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。

这时，就需要用 server id 来解决。

• 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
• 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
• 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

---

二、MySQL的高可用

1.主备延迟的概念

正常情况下，只要主库执行更新生成的所有 binlog，都可以传到备库并被正确地执行，备库就能达到跟主库一致的状态，这就是最终一致性。

但是，MySQL 要提供高可用能力，只有最终一致性是不够的。

同步延迟。与数据同步有关的时间点主要包括以下三个：

• 主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;
• 之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;
• 备库 B 执行完成这个事务，我们把这个时刻记为 T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。

你可以在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒。

在网络正常的时候，日志从主库传给备库所需的时间是很短的，即 T2-T1 的值是非常小的。也就是说，网络正常情况下，主备延迟的主要来源是备库接收完 binlog 和执行完这个事务之间的时间差。

所以说，主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。

2.主备延迟的来源

1、常见于备库的压力大。出自于备库上的大量查询。

一般可以这么处理：

• 一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。
• 通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。

2、大事务。

因为主库上必须等事务执行完成才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟。

常见大事务场景：

• 一次性地用 delete 语句删除太多数据
• 大表 DDL

3、备库的并行复制能力

通常客户端写入主库，比备库上 sql_thread 执行中转日志（relay log）的并行度要高得多。

在主库上，影响并发度的原因就是各种锁了。而日志在备库上的执行，如果是用单线程的话，就会导致备库应用日志不够快，造成主备延迟。

所有的多线程复制机制，都符合下面的这个模型：

事务是不能按照轮询的方式分发给各个 worker，因为，事务被分发给 worker 以后，不同的 worker 就独立执行了。但是，由于 CPU 的调度策略，很可能第二个事务最终比第一个事务先执行。而如果这时候刚好这两个事务更新的是同一行，也就意味着，同一行上的两个事务，在主库和备库上的执行顺序相反，会导致主备不一致的问题。

同时，同一个事务的多个更新语句，不能分给不同的 worker 。因为其中一个worker完成事务之后，如果备库上有一个查询，就会看到这个事务更新了一半的结果，破坏了事务逻辑的隔离性。

所以，coordinator 在分发的时候，需要满足以下这两个基本要求：

• 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。
• 同一个事务不能被拆开，必须放到同一个 worker 中。

每个事务在分发的时候，跟所有 worker 的冲突关系包括以下三种情况：

• 如果跟所有 worker 都不冲突，coordinator 线程就会把这个事务分配给最空闲的 woker;
• 如果跟多于一个 worker 冲突，coordinator 线程就进入等待状态，直到和这个事务存在冲突关系的 worker 只剩下 1 个；
• 如果只跟一个 worker 冲突，coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker。

MySQL5.6 版本，支持了并行复制，只是支持的粒度是按库并行。每个 worker 线程对应一个 hash 表，用于保存当前正在这个 worker 的“执行队列”里的事务所涉及的库。key 就是数据库名。value是数字，表示队列中有多少个事务修改这个库。

MySQL5.7 版本，由参数 slave-parallel-type 来控制并行复制策略：

• 配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；
• 配置为 LOGICAL_CLOCK，同时处于 prepare 状态的事务，在备库执行时是可以并行的；
  处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的。

MySQL 5.7.22 增加了一个新的并行复制策略，基于 WRITESET 的并行复制。

相应地，新增了一个参数 binlog-transaction-dependency-tracking，用来控制是否启用这个新策略。这个参数的可选值有以下三种。

• COMMIT_ORDER，表示的就是前面介绍的，根据同时进入 prepare 和 commit 来判断是否可以并行的策略。
• WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行。
• WRITESET_SESSION，是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。

当然为了唯一标识，这个 hash 值是通过“库名 + 表名 + 索引名 + 值”计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert 语句对应的 writeset 就要多增加一个 hash 值。

writeset 是在主库生成后直接写入到 binlog 里面的，这样在备库执行的时候，不需要解析 binlog 内容（event 里的行数据），节省了很多计算量；

不需要把整个事务的 binlog 都扫一遍才能决定分发到哪个 worker，更省内存；

由于备库的分发策略不依赖于 binlog 内容，所以 binlog 是 statement 格式也是可以的。

3.可靠性优先策略

由于主备延迟的存在，所以在主备切换的时候，就相应的有不同的策略。

双 M 结构下，切换的详细过程是这样的：

1. 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
2. 把主库 A 改成只读状态，即把 readonly 设置为 true；
3. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
4. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
5. 把业务请求切到备库 B。

这个切换流程中是有不可用时间的。因为在步骤 2 之后，主库 A 和备库 B 都处于 readonly 状态，也就是说这时系统处于不可写状态，直到步骤 5 完成后才能恢复。

在这个不可用状态中，比较耗费时间的是步骤 3，可能需要耗费好几秒的时间。这也是为什么需要在步骤 1 先做判断，确保 seconds_behind_master 的值足够小。

4.可用性优先策略

如果把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了。

这个切换流程的代价，就是可能出现数据不一致的情况。

CREATE TABLE `t` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `c` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into t(c) values(1),(2),(3);
1
2
3
4
5
6
7

这个表定义了一个自增主键 id，初始化数据后，主库和备库上都是 3 行数据。接下来，业务人员要继续在表 t 上执行两条插入语句的命令，依次是：

insert into t(c) values(4);
insert into t(c) values(5);
1
2

假设，现在主库上其他的数据表有大量的更新，导致主备延迟达到 5 秒。在插入一条 c=4 的语句后，发起了主备切换。

当 binlog_format=mixed 时：

• 主库 A 执行完 insert 语句，插入了一行数据（4,4），之后开始进行主备切换。
• 步骤 3 中，由于主备之间有 5 秒的延迟，所以备库 B 还没来得及应用“插入 c=4”这个中转日志，就开始接收客户端“插入 c=5”的命令。
• 步骤 4 中，备库 B 插入了一行数据（4,5），并且把这个 binlog 发给主库 A。
• 步骤 5 中，备库 B 执行“插入 c=4”这个中转日志，插入了一行数据（5,4）。而直接在备库 B 执行的“插入 c=5”这个语句，传到主库 A，就插入了一行新数据（5,5）。

最后的结果就是，主库 A 和备库 B 上出现了两行不一致的数据。

当 binlog_format=row 时：

因为 row 格式在记录 binlog 的时候，会记录新插入的行的所有字段值，所以最后只会有一行不一致。而且，两边的主备同步的应用线程会报错 duplicate key error 并停止。

所以，在满足数据可靠性的前提下，MySQL 高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。

---

---

思考题

1、说到循环复制问题的时候，我们说 MySQL 通过判断 server id 的方式，断掉死循环。但是，这个机制其实并不完备，在某些场景下，还是有可能出现死循环。你能构造出一个这样的场景吗？又应该怎么解决呢？

答：一种场景是，在一个主库更新事务后，用命令 set global server_id=x 修改了 server_id。等日志再传回来的时候，发现 server_id 跟自己的 server_id 不同，就只能执行了。

另一种场景是，有三个节点的时候，如图 7 所示，trx1 是在节点 B 执行的，因此 binlog 上的 server_id 就是 B，binlog 传给节点 A，然后 A 和 A’搭建了双 M 结构，就会出现循环复制。

这种三节点复制的场景，做数据库迁移的时候会出现。

如果出现了循环复制，可以在 A 或者 A’上，执行如下命令：

stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);
start slave;
1
2
3

这样这个节点收到日志后就不会再执行。过一段时间后，再执行下面的命令把这个值改回来。

stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=();
start slave;
1
2
3

---

2、假设，现在你看到你维护的一个备库，它的延迟监控的图像是一个 45°斜向上的线段，你觉得可能是什么原因导致呢？你又会怎么去确认这个原因呢？

答：一种是大事务（包括大表 DDL、一个事务操作很多行）；

还有一种情况比较隐蔽，就是备库起了一个长事务，这时候主库对表 t 做了一个加字段操作，即使这个表很小，这个 DDL 在备库应用的时候也会被堵住。

---

3、假设一个 MySQL 5.7.22 版本的主库，单线程插入了很多数据，过了 3 个小时后，我们要给这个主库搭建一个相同版本的备库。这时候，你为了更快地让备库追上主库，要开并行复制。在 binlog-transaction-dependency-tracking 参数的 COMMIT_ORDER、WRITESET 和 WRITE_SESSION 这三个取值中，你会选择哪一个呢？你选择的原因是什么？如果设置另外两个参数，你认为会出现什么现象呢？

答：用writeset，因为主库单线程，所以每次插入的commit id 都不同，用commit_order和write_session会退化成单线程。

---

参考资料：林晓斌——MySQL实战45讲