MySQL深入21-主备一致的保证

主从复制的核心就是binlog，本文对binlog是如何保证主备一致的进行展开。

MySQL主备的基本原理

MySQL主备切换流程如下：

• 状态1中：客户端的读写都是访问节点A，而节点B是节点A的备库，只是将A的更新都同步过来，到本地执行，这样就可以保证节点B和A的数据是同步的。
• 主备切换之后，就是状态2，这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。

无论在那个状态中，从库节点都是没有直接被访问的，但是仍然建议把从库节点设置成只读（readonly）模式：

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

虽然从库设置成readonly了，但是这个设置对超级权限用户是无效的，而用于同步更新的线程，就拥有超级权限，

所有从库仍然能够进行数据同步。

接下来我们就对主从库两个节点内部流程进行深入：

主库接受到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。

备库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 读取中转日志，解析出日志里的命令，并执行。

binlog的三种格式对比

我们对主备的基本原理进行了了解，下面我们再来对binlog内部进行深入。

binlog有三种格式：statement、row、mixed（前两种模式的混合），首先来对前两种格式进行分析。

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语句的binlog是如何记录的。

delete from t /*comment*/  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命令之后，就是我们原封不动的SQL原文。

最后一行是一个 COMMIT。你可以看到里面写着 xid=61。这个XID是用来联系bin log和redo log的。

我们再来看一下这条delete命令执行效果：

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

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

由于statement格式下，记录到binlog里的语句原文，因此就可能出现主从库执行SQL语句的时候使用索引不一样，比如主库使用索引a，而备库使用索引t_modified，因此MySQL认为这样写是有风险的。

接下来我们使用row格式的binlog来进行分析一下：

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

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

相当于这个row格式的binlog做了两件事情：

1. 记录操作哪张表；
2. 记录对此表哪行数据进行了什么样的操作。

借助mysqlbinlog工具，我们来查看一下row格式binlog的详情信息：

• server id 1，表示这个事务是在 server_id=1 的这个库上执行的。
• Table_map event 显示了接下来要打开的表，map 到数字 226。现在我们这条 SQL 语句只操作了一张表，如果要操作多张表呢？每个表都有一个对应的 Table_map event、都会 map 到一个单独的数字，用于区分对不同表的操作。
• 我们在 mysqlbinlog 的命令中，使用了 -vv 参数是为了把内容都解析出来，所以从结果里面可以看到各个字段的值（比如，@1=4、 @2=4 这些值）。

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

mixed格式的binlog

首先我们要分析一下statement格式和row格式的优缺点：

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

所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

但是为了便于恢复数据越来越多的场景把MySQL的binlog格式设置成row。下面分别从delete、insert和update这三种SQL语句的角度来看看数据恢复的问题。

delete

即使执行的是 delete 语句，row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。

insert

row 格式下，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了。

update

binlog 里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

循环复制问题

实际生产上使用比较多的是双M结构：

和之前的区别在于节点A和节点B总是互为主备关系，这样在切换的时候就不用再修改主备关系。

业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。

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

MySQL在binlog中记录了这个命令第一次执行时所在实例的server id来解决这个问题：

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

那么现在双M结构，日志的执行流程如下：

1. 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；
2. 传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；
3. 再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

参考

MySQL 实战 45 讲-极客时间 (opens new window)