24 | MySQL是怎么保证主备一致的？

主备切换流程

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

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

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

疑问：备库设置成只读，还怎么跟主库保持同步更新？
因为 readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。

一个 update 语句在节点 A 执行，然后同步到节点 B 的完整流程：

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。

一个事务日志同步的完整过程如下：

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

说明：后来由于多线程复制方案的引入，sql_thread 演化成为了多个线程。

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');

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

当 binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文。

show binlog events in 'master.000001';

第三行是真实执行的语句。在真实执行的 delete 命令之前，还有一个“use ‘test’”命令，这条命令并不是主动执行的，而是 MySQL 根据当前要操作的表所在的数据库，自行添加。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到 test 库的表 t。use 'test’命令之后的 delete 语句，就是输入的 SQL 原文。

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

delete 带 limit，很可能会出现主备数据不一致的情况。

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

由于 statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。因此，MySQL 认为这样写是有风险的。

将 binlog 的格式改为 binlog_format=row，如下如：

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

通过上图看不到详细信息，还需要借助 mysqlbinlog 工具，用下面这个命令解析和查看 binlog 中的内容。根据上图的信息显示，这个事务的 binlog 从 8900 这个位置开始，所以可以用 start-position 参数来指定从这个位置的日志开始解析。

mysqlbinlog  -vv data/master.000001 --start-position=8900;

1. server id 1，表示这个事务是在 server_id=1 的这个库上执行。
2. 每个 event 都有 CRC32 的值，因为参数 binlog_checksum 被设置成 CRC32。
3. Table_map event 显示接下来要打开的表，map 到数字 226。如果要操作多张表，每个表都有一个对应的 Table_map event 且 map 到一个单独的数字，用于区分对不同表的操作。
4. -vv 参数作用是把所有内容都解析出来，从结果里面可以看到各个字段的值（比如，@1=4、 @2=4）。
5. binlog_row_image 的默认配置是 FULL，因此 Delete_event 里面，包含了删掉的行的所有字段的值。如果把 binlog_row_image 设置为 MINIMAL，则只会记录必要的信息，在这个例子里，就是只会记录 id=4 这个信息。
6. Xid event，用于表示事务被正确地提交。

若 binlog_format=row，binlog 里面记录了真实删除行的主键 id，当 binlog 传到备库去的时候，就肯定会删除正确的行，不会有主备删除不同行的问题。

statement 格式的 binlog，很可能会导致主备不一致。

假设用一个 delete 语句删掉 10 万行数据，statement 格式 binlog 只是记录一个 SQL 语句，占用几十个字节的空间。而 row 格式的 binlog，则要记录这 10 万条记录。因此，row 格式的 binlog 不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。

线上 MySQL 设置的 binlog 格式是 statement 被认为是一个不合理的设置，至少应该把 binlog 的格式设置为 mixed。

现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row，其中一个好处：恢复数据。如果 delete、insert 和 update 语句执行错误，可以 根据 binlog 中记录很方便地恢复。

实际生产上主备切换流程使用比较多的是双 M 结构，如下图：

节点 A 和 B 之间总是互为主备关系，在切换的时候不用修改主备关系。

业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（建议把参数 log_slave_updates 设置为 on，表示备库在执行 relay log 后生成 binlog），因为节点 A 同时又是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制。

如何解决循环复制的问题？

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