MySQL 是怎么保证主备一致的？
MySQL 主备的基本原理
状态 1 中，客户端读写都是直接访问节点 A，节点 B 只是将节点 A 的更新同步过来在本地执行。当需要主备切换时，就变成状态 2。
状态 1 中没有直接访问节点 B，但仍然建议把节点 B 设置成只读模式，原因如下：
有时一些运营类的查询语句会被放到备库上查看，设置为只读可以防止误操作；
防止切换逻辑出现双写等 bug，造成主备不一致；
可以用 readonly 状态来判断节点角色。
把备库设置成只读，还怎么跟主库保持同步更新呢？这是由于 readonly 设置对超级权限的用户是无效的，而对于同步更新的线程，就拥有超级权限。
而一个 update 语句在节点 A 执行，然后同步到节点 B 的完整流程如下：
主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写 binlog。备库和主库之间维持了一个长连接，主库内部有一个线程专门服务该长连接。一个事务日志同步的完整过程为：
备库 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 读取中转日志，解析出日志里的命令并执行。
由于多线程复制方案的引入，sql_thread 后来实际上演化成为了多个线程。
分析完长连接的逻辑，再来看一个问题：binlog 里面到底是什么内容，为什么备库拿过去可以直接执行。
binlog 的三种格式对比
为了方便描述 binlog 的三种格式，以如下数据为例：
假设要在表中删除一行：
可以用下面的命令查看 binlog 中的内容：
当binlog_format=statement，binlog 里记录的就是 SQL 语句的原文。statement 格式的 binlog 内容：
第二行的 begin 与第四行的 commit 对应，表示中间是一个事务；
第三行是真实执行的语句。在真实执行的 delete 命令前，MySQL 根据当前要操作的表所在的数据库，自行添加了一个命令，这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能正确更新到 test 库的表 t。
这条 delete 命令的执行效果图：
产生了一个 warning，原因是当前 binlog 设置的是 statement 格式，且语句中有 limit，因此该命令可能是 unsafe 的。具体来说，delete 带 limit，可能出现主备数据不一致情况，在上面的例子中：
如果 delete 用的是索引 a，那么会根据索引 a 找到第一个满足条件的行
a=4；如果 delete 用的是索引 t_modified，那么删除的就是
t_modified='2018-11-09'，是a=5。
由于 statement 格式下 binlog 记录语句原文，因此可能出现：主库用的是索引 a，备库使用的是索引 t_modified，所以 MySQL 认为这样写是有风险的。
如果将 binlog 格式改为 row，此时 binlog 内容：
这时没有了 sql 原文，而是替换成了两个 event：
Table_map event：说明接下来要操作的表是 test 库的表 t；
Delete_rows event：用于定义删除的行为。
row 格式下，通过 binlog 还看不到详细信息，还需要借助 mysqlbinlog 工具解析和查看 binlog 中的内容。从上面的 binlog 中可以看出，事务的 binlog 从 8900 位置开始，因此可以用 start-position 参数指定从该位置的日志开始解析：
解析出的 binlog 详细内容：
对于解析结果：
server id 1，表示事务是在
server_id=1库上执行的；Table_map event 显示了接下来要打开的表，map 到数字 226。当前 SQL 语句只操作了一张表，如果要操作多张表，那么每个表都有一个对应的 Table_map event，都会 map 到一个单独的数字，用于区分对不同表的操作；
@1=4, @2=4这些表示各个字段的值，即id=4,a=4；Xid event，用于表示事务被正确提交。
可以看到，binlog 使用 row 格式时，会记录真实删除行的主键 id，这样 binlog 传到备库时，肯定会删除id=4的行，不会有主备删除不同行的问题。
为什么会有 mixed 格式的 binlog？
首先可以根据上面的分析来推一下：
因为某些 statement 格式的 binlog 可能导致主备不一致，所以要使用 row 格式；
row 格式缺点是很占空间，比如 delete 语句删除 10 万行数据，用 statement 就是一个 SQL 语句，而 row 格式需要把 10 万条记录都写到 binlog，这样会占用更大的空间，同时写 binlog 会耗费 IO 资源；
因此 MySQL 取折中方案，即 mixed 格式，MySQL 自己判断 SQL 语句是否可能引起主备不一致，如果可能就用 row，否则用 statement。
可以看出，如果 MySQL 设置 binlog 为 statement 格式，基本可以认为是一个不合理的设置，至少可以设置为 mixed。不过现在越来越多的场景要求把 MySQL 的 binlog 设置为 row，理由有很多，其中有一点就是恢复数据。
从增删改来看数据恢复的问题：
delete 语句，row 格式的 binlog 会把删掉的行的整行信息保存起来，如果发现删错数据，可以直接把 binlog 中记录的 delete 语句转成 insert，将错删的数据插入；
insert 语句，row 格式的 binlog 会记录所有字段信息，可以转成 delete 删掉误插入的数据；
update 语句，row 格式的 binlog 会记录修改前和修改后的整行数据，如果误执行，只需要把前后两行消息对调一下，再去数据库里执行。
循环复制问题
最开始的图中 MySQL 主备切换流程是 M-S 结构，实际生产上使用比较多的是双 M 结构：
对比两图，可以发现双 M 结构和 M-S 结构区别只是多了一条线，即节点 A 和 B 之间总是互为主备关系，这样切换时就不用再修改主备关系。
双 M 结构有一个问题。如果业务逻辑在节点 A 上更新一条语句，再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog，会发送给节点 A，相当于节点 A 又把节点 B 新生成的 binlog 拿过来执行了一次，然后两个节点会不断循环执行这个更新语句，即循环复制。
循环复制的解决：
规定两个库的 server id 必须不同，如果相同则它们之间不能设为主备关系；
一个备库在重放 binlog 的过程中，会生成与原 binlog 的 server id 相同的新 binlog；
每个库在收到从自己的主库发过来的日志后，先判断 server id，如果和自己的相同，表示该日志由自己生成，直接丢弃。
按这个逻辑，如果设置了双 M 结构，日志的执行流就为：
从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；
传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；
再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志，死循环在这里就断掉了。
文章转载自：叁沐
还未添加个人签名 2023-06-19 加入
还未添加个人简介
评论