MySQL45讲 第二十四讲 MySQL是怎么保证主备一致的？——阅读总结

MySQL45讲 第二十四讲 MySQL是怎么保证主备一致的？——阅读总结

在 MySQL 数据库的世界里，主备一致性是确保数据可靠性和高可用性的关键。就深入探讨 MySQL 是如何保证主备一致的，这涉及到 binlog 的多种格式、主备复制的详细流程以及双 M 结构下的循环复制问题等诸多重要知识点。

---

一、MySQL 主备基本原理

（一）主备切换流程

1. 状态 1

   ：如图 1 所示，客户端的读写直接访问节点 A，节点 B 作为 A 的备库，通过同步 A 的更新来保持数据一致。此时，建议将备库 B 设置为只读（readonly）模式，原因如下：

   • 防止运营类查询语句在备库上的误操作。
   • 避免切换逻辑出现 bug，如双写导致主备不一致。
   • 可依据 readonly 状态判断节点角色。
   • 设置为 readonly，那主库怎么和备库进行同步？同步更新的线程拥有超级（super）权限，仍可进行数据同步。

2. 状态 2：当需要切换时，客户端读写访问切换到节点 B，节点 A 成为 B 的备库。

（二）主备数据同步流程

在这里插入图片描述

1. 备库设置：在备库 B 上通过 change master 命令设置主库 A 的相关信息，包括 IP、端口、用户名、密码以及 binlog 的起始位置（包含文件名和日志偏移量），然后执行 start slave 命令启动图中 io_thread 和 sql_thread 两个线程。其中 io_thread 负责与主库建立连接。
2. 主库操作：主库 A 校验完用户名和密码后，按照备库 B 的请求位置读取 binlog，并将其发送给 B。
3. 备库接收与执行：备库 B 收到 binlog 后，写入本地中转日志（relay log），sql_thread 读取 relay log，解析并执行其中的命令。

---

二、binlog 格式及相关问题

（一）binlog 的三种格式

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

mysql> CREATE TABLE `t` (
`id` int(11) NOTNULL,
`a` int(11) DEFAULTNULL,
`t_modified` timestamp NOTNULL DEFAULTCURRENT_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. statement 格式：记录 SQL 语句原文，例如执行delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1语句时，binlog 中会如实记录该 SQL 语句，包括自动添加的use 'test'命令（确保在备库能正确更新到指定库的表），同时还会记录注释等信息。然而，这种格式可能导致主备数据不一致，如上述 delete 语句，若主库和备库使用不同索引执行，可能删除不同行。

   

2. row 格式：不记录 SQL 语句原文，而是通过 Table_map 和 Delete_rows 等事件来定义操作。例如上述 delete 语句，会记录操作的表信息（Table_map event）以及删除行为（Delete_rows event），且会记录真实删除行的主键 id，保证备库执行时删除正确的行。此外，对于 insert 和 update 语句，row 格式的 binlog 也会记录足够信息以便数据恢复，如 insert 语句会记录所有字段信息，update 语句会记录修改前和修改后的整行数据。

   

   该事务从8900开始，通过mysqlbinlog -vvdata/master.000001 --start-position=8900;指令，查看

   

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

   为什么会有mixed格式的binlog？

   有些statement格式的binlog虽然简单但可能会导致主备不一致，所以要使用row格式。但row格式又比较占用空间，所以算是一种折中的办法。mixed格式可以利用statment格式的优点，同时又避免了数据不一致的风险。

3. mixed 格式：MySQL 会判断 SQL 语句是否可能引起主备不一致，若有可能则使用 row 格式，否则使用 statement 格式，是一种折中的方案。但在某些情况下，如执行insert into t values(10,10, now())时，虽可能存在主备不一致风险，但仍会记录为 statement 格式，不过会通过 SET TIMESTAMP 命令约定 now () 函数的返回时间，确保主备数据一致性。

   

（二）格式对比与选择

1. 数据恢复优势：row 格式在数据恢复方面具有明显优势，可通过反转操作恢复误执行的 delete、insert 或 update 语句，而 statement 格式相对较难实现精确恢复。
2. 空间占用与性能：row 格式占用空间较大，写 binlog 时耗费 IO 资源，影响执行速度；statement 格式占用空间小，但可能存在数据不一致风险。因此，越来越多的场景推荐使用 row 格式的 binlog，不过也可根据实际情况选择 mixed 格式，而一般不建议使用单纯的 statement 格式。

---

三、循环复制问题及解决

（一）循环复制现象

在双 M 结构中，业务逻辑在节点 A 更新语句后生成 binlog 发给节点 B，B 执行后也生成 binlog，若节点 A 同时是节点 B 的备库，可能会导致循环执行更新语句，即循环复制。

（二）解决逻辑

1. server id 设置：规定两个库的 server id 必须不同，否则不能设定为主备关系。
2. binlog 生成规则：备库在重放 binlog 过程中生成与原 binlog 的 server id 相同的新 binlog。
3. 日志接收判断：每个库收到主库发来的日志后，先判断 server id，若与自己相同则直接丢弃，从而避免循环复制。例如，节点 A 更新事务的 binlog 记有 A 的 server id，传给节点 B 执行后，B 生成的 binlog 的 server id 也是 A 的 server id，再传回 A 时，A 会因 server id 相同而不处理该日志。

---

四、总结与思考

MySQL 通过 binlog 实现主备同步，binlog 的三种格式各有优劣，在保证主备一致性方面发挥着关键作用。主备复制流程涉及多个步骤和线程协作，双 M 结构虽有循环复制问题，但可通过 server id 机制解决。