《MySQL实战45讲》——学习笔记24 “Master/Slave主备同步机制/binlog的三种格式“

阳光穿透心脏的1/2处 2023-09-23 23:16 53阅读 0赞

binlog可以用来归档，也可以用来做主备同步，binlog在MySQL的各种高可用方案上扮演了重要角色；

本篇主要介绍MySQL主备（M-S结构）的基本原理、不同格式binlog的优缺点和设计者的思考、MySQL双主结构和循环复制问题（双M结构）相关知识，这些点可以说是所有MySQL高可用方案的基础，在这之上演化出了诸如多节点、半同步、MySQL group replication等相对复杂的方案；在做系统开发时候，也能借鉴这些设计思想；

#### MySQL主备的基本原理 ####

![b2ab9c618bda4f81a1ba30cf1ba8f732.png][]

以上是主从结构以及主从切换的示例，客户端的读写都直接访问主库节点，而备库只是将主库的更新都同步过来，到本地执行；

此过程中，备库被设置成只读（readonly）模式，这样做有以下几个考虑：

*  备库是不允许业务写入的，将备库设置为只读可以防止误写操作；

*  可以用readonly状态，来判断节点的角色；

*  即使备库设置成readonly状态，readonly设置对超级(super)权限用户是无效的，而用于同步更新的线程就具备超级权限，不影响备库做同步更新；

**主从节点同步的细节**

![79f364c6ef6f4b2d87243fb33438e420.png][]

可以看到：

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

1.  备库B跟主库A之间维持了一个长连接；

1.  主库A内部有一个线程，专门用于服务备库B的这个长连接；

**一个事务日志同步（长连接）的完整过程**

*  设置主库属性：在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量；

*  启动备库：在备库B上执行start slave命令，这时候备库会启动两个线程，就是图中的io\_thread和sql\_thread；其中io\_thread负责与主库建立连接；

*  主库传输日志：主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B；

*  备库更新同步：备库B拿到binlog后，写到本地文件，称为中转日志（relaylog）；sql\_thread读取中转日志，解析出日志里的命令，并执行；

#### binlog的三种格式对比：statement / row / mixed ####

**例子**

为了便于描述binlog的这三种格式间的区别，用一个例子说明，下面建了一个表，并初始化几行数据；

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

执行删除语句：

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

*  当 binlog\_format=statement 时，binlog 里面记录的就是 SQL 语句的原文，包括注释

用show binlog events命令看binlog中的内容；

![87208e3a29104b988b10cab90c36e241.png][]

分析：第一行先忽略，跟主备切换有关；第二行是一个BEGIN，跟第四行的COMMIT（XID=61）对应，表示中间是一个事务；第三行就是真实执行的语句了；可以看到，binlog“忠实”地记录了SQL命令，甚至连注释也一并记录了；

*  statement格式，并且语句中有limit，这个命令可能是unsafe的

为了说明statement和row格式的区别，这里执行showwarnings命令发现运行这条delete命令产生了一个warning；

![8a84a5b34f9a42a4a200fa7464a23da5.png][]

分析：这是因为delete带limit，很可能会出现主备数据不一致的情况；

比如上面这个例子：如果delete语句使用的是索引a，那么会根据索引a找到第一个满足条件的行，也就是说删除的是a=4这一行；但如果使用的是索引t\_modified，那么删除的就是t\_modified='2018-11-09’也就是a=5这一行；

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

*  row格式，则不会出现语句中有limit可能引起的unsafe问题

把binlog的格式改为binlog\_format='row'，看看这时候binog中的内容；

![03e4200f002b435680df1d4188d51ee2.png][]

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

仅通过上图是看不到详细信息的，还需要借助 mysqlbinlog 工具，用下面这个命令解析和查看 binlog 中的内容；

![8f189af5a01d40a6982fe1de133bdca3.png][]

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

#### 为什么会有 mixed 格式的 binlog？ ####

基于上面的信息，来讨论一个问题：为什么会有mixed这种binlog格式的存在场景？

解释：

*  因为statement格式可能会导致主备不一致，所以要使用row格式；

*  但row格式的缺点是，很占空间；比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间；但如果用row格式的binlog，就要把这10万条记录都写到binlog中；

*  不仅占用更大的空间，同时写row格式的binlog也要耗费IO资源；

所以，MySQL就取了个折中方案，也就是有了mixed格式的binlog；

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

#### 建议使用row格式 ####

尽管上面解释了mixed格式存在的原因，现在越来越多的场景要求把MySQL的binlog格式设置成row；这么做的理由有很多，其中重要的一个好处就是：**恢复数据**；

通过上面的分析可知，row格式的binlog会把被修改的行的整行信息保存起来；下面从delete、insert和update这3种SQL语句的角度，来分析row格式下数据恢复的思路：

*  恢复delete：如果在执行完一条delete语句以后，发现删错数据了，可以直接把binlog中记录的delete语句转成insert，把被错删的数据插入回去就可以恢复了；

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

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

#### 双M结构下的循环复制问题 ####

上面介绍的是M-S结构，但实际生产上使用比较多的是双M结构，它的一个好处就是切换M的时候就不用再修改主备关系；

![19a02a8b3f3a4307805006b52a1587f7.png][]

要注意，一般说双M是仅仅是将AB之间设置为互为主备，任何时刻只会有一个节点在接受client的更新请求；

**双M结构还有一个问题需要解决——循环复制问题**

循环复制问题：业务逻辑在节点A上更新了一条语句，然后再把生成的binlog发给节点B，节点B执行完这条更新语句后也会生成binlog；双M结构下，节点A同时是节点B的备库，相当于又会把节点B新生成的binlog拿过来执行了一次，然后节点A和B间，会不断地循环执行这个更新语句，也就是循环复制了；

**如何解决？**

实际上，MySQL在binlog中记录了这个命令第一次执行时所在实例的serverid；用下面的逻辑来解决两个节点间的循环复制的问题：

*  规定两个库的serverid必须不同，如果相同，则它们之间不能设定为主备关系；

*  一个备库接到binlog并在重放的过程中，生成与原binlog的serverid相同的新的binlog；

*  每个库在收到从自己的主库发过来的日志后，先判断serverid，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志；

按照这个逻辑，如果设置了双M结构，日志的执行流就会变成这样：

*  从节点A更新的事务，binlog里面记的serverid=A；

*  传到节点B执行一次以后，节点B生成的binlog的serverid也是A；

*  再传回给节点A，A判断到这个serverid=A与自己的相同，就不会再处理这个日志；所以，死循环在这里就断掉了；

**但是，循环复制偶尔还是会发生的**

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

case2：做数据库迁移时，节点复制的场景可能出现循环复制；如下：

![4bcbcb1392814ba882fb4c2d4500c1a9.png][]

**解决**：让某个节点临时忽略某个server\_id；然后再恢复；

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

stop slave;
    CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);
    start slave;

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

stop slave;
    CHANGE MASTER TO IGNORE_SERVER_IDS=();
    start slave;

下篇文章：[《MySQL实战45讲》——学习笔记25 “主备延迟“][MySQL_45_25]

本章参考：[24 | MySQL是怎么保证主备一致的？][24 _ MySQL]

[b2ab9c618bda4f81a1ba30cf1ba8f732.png]: https://img-blog.csdnimg.cn/img_convert/b2ab9c618bda4f81a1ba30cf1ba8f732.png
[79f364c6ef6f4b2d87243fb33438e420.png]: https://img-blog.csdnimg.cn/img_convert/79f364c6ef6f4b2d87243fb33438e420.png
[87208e3a29104b988b10cab90c36e241.png]: https://img-blog.csdnimg.cn/img_convert/87208e3a29104b988b10cab90c36e241.png
[8a84a5b34f9a42a4a200fa7464a23da5.png]: https://img-blog.csdnimg.cn/img_convert/8a84a5b34f9a42a4a200fa7464a23da5.png
[03e4200f002b435680df1d4188d51ee2.png]: https://img-blog.csdnimg.cn/img_convert/03e4200f002b435680df1d4188d51ee2.png
[8f189af5a01d40a6982fe1de133bdca3.png]: https://img-blog.csdnimg.cn/img_convert/8f189af5a01d40a6982fe1de133bdca3.png
[19a02a8b3f3a4307805006b52a1587f7.png]: https://img-blog.csdnimg.cn/img_convert/19a02a8b3f3a4307805006b52a1587f7.png
[4bcbcb1392814ba882fb4c2d4500c1a9.png]: https://img-blog.csdnimg.cn/img_convert/4bcbcb1392814ba882fb4c2d4500c1a9.png
[MySQL_45_25]: https://blog.csdn.net/minghao0508/article/details/128851747
[24 _ MySQL]: https://time.geekbang.org/column/article/76446