MySQL高可用之主备同步：MySQL是如何保证主备一致的

r囧r小猫 2024-04-27 15:40 75阅读 0赞

?今日学习目标：

?MySql是如何保证主备一致的  
✅创作者：林在闪闪发光  
⏰预计时间：30分钟  
?个人主页：[林在闪闪发光的个人主页][Link 1]

?林在闪闪发光的个人社区，欢迎你的加入: [林在闪闪发光的社区][Link 2]

**目录**

一 什么叫主备同步

二 主备同步的好处

三 主备同步的实现原理

四. binlog的三种格式

五. 为什么会有mixd格式的binlog？

六 常见的两种主备切换流程

M-S结构

双M结构

双M结构的循环复制问题

--------------------

## 一 什么叫主备同步 ##

主备同步，也叫主从复制，是MySQL提供的一种高可用的解决方案，保证主备数据一致性的解决方案。在生产环境中，会有很多不可控因素，例如数据库服务挂了。为了保证应用的高可用，数据库也必须要是高可用的。因此在生产环境中，都会采用主备同步。在应用的规模不大的情况下，一般会采用一主一备。

## 二 主备同步的好处 ##

除了上面提到的数据库服务挂了，能够快速切换到备库，避免应用的不可用外，采用主备同步还有以下好处：

**提升数据库的读并发性**，大多数应用都是读比写要多，采用主备同步方案，当使用规模越来越大的时候，可以扩展备库来提升读能力。

**备份**，主备同步可以得到一份实时的完整的备份数据库。

**快速恢复**，当主库出错了（比如误删表），通过备库来快速恢复数据。对于规模很大的应用，对于数据恢复速度的容忍性很低的情况，通过配置一台与主库的数据快照相隔半小时的备库，当主库误删表，就可以通过备库和binlog来快速恢复，最多等待半小时。

## 三 主备同步的实现原理 ##

如下图展示的是基本的主备切换流程

![f4de405e1465456b8d79cd9d5e9942dc.png][]

在状态1中，主库是A，备库是B，所以客户端的读写都直接方法节点A。由于节点B是节点A的备库，所以备库B只是将A的更新都同步过来，本地执行，这样可以保证节点B和节点A的数据一致性。

如果发生主备切换，就会从状态1变成状态2，节点A成为备库，节点B成为主库。

在状态1中，虽然节点B没有被客户端直接方法，但是还是**建议将节点B（备库）设置成只读（readonly）模式**，主要有以下几个理由：

避免某些服务访问了备库，造成误操作；  
防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致；  
可以用readonly状态，来判断节点的角色；  
注意：**readonly对于超级管理员是无效的，而用于同步更新的线程，就拥有超级权限，所以是可以修改备库的。**

接下来我们看下节点A到节点B的流程图：

![ba8f6367e215408d95ddfe01ac4eb010.png][]

实际上备库B和主库A之间维持了个长连接，主库A中有一个线程（dump\_thread），专门用于服务和备库B的长连接。日志同步的完整过程如下：

1.在备库B上通过change master命令，设置主库A的相关信息，以及要从哪个位置开始请求binlog；  
2.在备库B上执行start slave命令，备库会启动两个线程，即io\_thread和sql\_thread，其中io\_thread负责与主库通信；  
3.主库A校验完信息后，根据备库B转过来的位置，本地读取binlog，传递给B；  
4.备库拿到binlog后，写到本地文件，称为中转日志（relay log）；  
5.sql\_thread读取中转日志，解析出命令并执行；

## 四. [binlog][]的三种格式 ##

**binlog的格式实际上由两种格式，一种是statement，一种是row。此外还有一种mixed格式，实际上是前两种的混合。**

为了方便解释几种日志格式的区别，我们创建一个表并写入些数据。

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

然后，我们对于这个表执行[delete语句][delete]：

注意，下面这个语句包含注释，如果你用 MySQL 客户端来做这个实验的话，要记得加 -c 参数，否则客户端会自动去掉注释。

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

我们可以使用下面的命令来查看binlog中的内容：

> mysql> show binlog events in 'master.000001'

可以看到，**当binlog\_format=statement时，binlog里面记录的就是sql原文**：

![8f1ea93811ab475baacdc3327d88c337.png][]

现在，我们来看一下图 3 的输出结果。

第一行 SET @@SESSION.GTID\_NEXT='ANONYMOUS’你可以先忽略，后面文章我们会在介绍主备切换的时候再提到；  
第二行是一个 BEGIN，跟第四行的 commit 对应，表示中间是一个事务；  
第三行就是真实执行的语句了。可以看到，在真实执行的 delete 命令之前，还有一个“use ‘test’”命令。这条命令不是我们主动执行的，而是 MySQL 根据当前要操作的表所在的数据库，自行添加的。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到 test 库的表 t。  
use 'test’命令之后的 delete 语句，就是我们输入的 SQL 原文了。可以看到，binlog“忠实”地记录了 SQL 命令，甚至连注释也一并记录了。  
最后一行是一个 COMMIT。你可以看到里面写着 xid=61。  
为了说明 statement 和 row 格式的区别，我们来看一下这条 delete 命令的执行效果图：

![1c866383ca804b9babf3161d22fdfeb7.png][]

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

为什么这么说呢？这是因为 delete 带 limit，很可能会出现主备数据不一致的情况。比如上面这个例子：

如果 delete 语句使用的是索引 a，那么会根据索引 a 找到第一个满足条件的行，也就是说删除的是 a=4 这一行；  
但如果使用的是索引 t\_modified，那么删除的就是 t\_modified='2018-11-09’也就是 a=5 这一行。  
由于 statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：**在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t\_modified。因此，MySQL 认为这样写是有风险的**。

那么，如果我把 binlog 的格式改为 binlog\_format=‘row’， 是不是就没有这个问题了呢？我们先来看看这时候 binog 中的内容吧。

![71e370c878cb41b4bd745044801e55e2.png][]

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

Table\_map event，用于说明接下来要操作的表是 test 库的表 t;  
Delete\_rows event，用于定义删除的行为。  
其实，我们通过图 5 是看不到详细信息的，还需要借助 mysqlbinlog 工具，用下面这个命令解析和查看 binlog 中的内容。因为图 5 中的信息显示，这个事务的 binlog 是从 8900 这个位置开始的，所以可以用 start-position 参数来指定从这个位置的日志开始解析。

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

![29b1184cd6c04891bc4826c1bb7de0cb.png][]

从这个图中，我们可以看到以下几个信息：

> 1.server id 1，表示这个事务是在 server\_id=1 的这个库上执行的。  
> 2.每个 event 都有 CRC32 的值，这是因为我把参数 binlog\_checksum 设置成了 CRC32。  
> 3.Table\_map event 跟在图 5 中看到的相同，显示了接下来要打开的表，map 到数字 226。现在我们这条 SQL 语句只操作了一张表，如果要操作多张表呢？每个表都有一个对应的 Table\_map event、都会 map 到一个单独的数字，用于区分对不同表的操作。  
> 我们在 mysqlbinlog 的命令中，使用了 -vv 参数是为了把内容都解析出来，所以从结果里面可以看到各个字段的值（比如，@1=4、 @2=4 这些值）。  
> 4.binlog\_row\_image 的默认配置是 FULL，因此 Delete\_event 里面，包含了删掉的行的所有字段的值。如果把 binlog\_row\_image 设置为 MINIMAL，则只会记录必要的信息，在这个例子里，就是只会记录 id=4 这个信息。  
> 5.最后的 Xid event，用于表示事务被正确地提交了。

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

## 五. 为什么会有mixd格式的binlog？ ##

从上面的描述中，我们可以很清楚地看到statement和row格式的优缺点：

> statement：格式节省空间，只需要记录sql语句。但是可能会出现主备不一致的情况；  
> row：不会出现主备不一致的情况。但是格式十分消耗空间，需要记录所有修改的行。

而 **mixed格式的意思是，MySQL会自己判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。**

所以线上的场景，设置为statement格式肯定是不合理的，至少要设置成mixed格式。

实际上，现在越来越多都是使用row格式，其中一个好处就是**恢复数据**：

> 当执行delete语句后，发现误删了，直接将binlog中的信息，转换成insert语句插入即可  
> 当执行insert语句后，发现错误插入了，直接将binlog中的信息，转换成delete语句插入即可  
> 如果执行的是update语句，binlog会记录修改前后的信息，方面恢复

## 六 常见的两种主备切换流程 ##

### M-S结构 ###

M-S结构，两个节点，一个当主库、一个当备库，不允许两个节点互换角色。

![688d866037604fa798f39eed6b61ae77.png][]

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

当需要切换的时候，就切成状态2。这时候客户端读写访问的都是节点B，而节点A是B的备库。

### 双M结构 ###

双M结构，两个节点，一个当主库，一个当备库，允许两个节点互换角色。

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

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

在实际生产使用中，多数情况是使用双M结构的。但是，双M结构还有一个问题需要解决。

业务逻辑在节点A执行更新，会生成binlog并同步到节点B。节点B同步完成后，也会生成binlog。（log\_slave\_updates设置为on，表示备库也会生成binlog）。

当节点A同时也是节点B的备库时，节点B的binlog也会发送给节点A，造成循环复制。

**解决办法：**

> 设置节点的server-id，必须不同，不然不允许设置为主备结构  
> 备库在接到binlog后重放时，会记录原记录相同的server-id，即谁产生即为谁的。  
> 每个节点在接受binlog时，会判断server-id，如果是自己的就丢掉。

**解决后的流程：**

> 业务逻辑在节点A执行更新，会生成带有节点A的server-id的binlog。  
> 节点B接受到节点A发过来的binlog，并执行完成后，会生成带有节点A的server-id的binlog。  
> 节点A接受到binlog后，发现是自己的，就丢掉。死循环就在这里断掉了。  
>

![cadb99bc048247be8a07aed122588c45.png][]

[Link 1]: https://blog.csdn.net/weixin_68829137?type=blog
[Link 2]: https://bbs.csdn.net/forums/947a8b4d170b4a8f8d6bbfbf524c1a1e?spm=1001.2014.3001.6682
[f4de405e1465456b8d79cd9d5e9942dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/5b945261e1824064baf07632bce037de.png
[ba8f6367e215408d95ddfe01ac4eb010.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/10964fe8e6604b57a0b61c914bcc0d6e.png
[binlog]: https://so.csdn.net/so/search?q=binlog&spm=1001.2101.3001.7020
[delete]: https://so.csdn.net/so/search?q=delete%E8%AF%AD%E5%8F%A5&spm=1001.2101.3001.7020
[8f1ea93811ab475baacdc3327d88c337.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/7b3c82ac8fe5413e8b5e2058db392b50.png
[1c866383ca804b9babf3161d22fdfeb7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/8da004b5fb854eb5875fb20590c97366.png
[71e370c878cb41b4bd745044801e55e2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/a1bcdd28a5b04d20aa3f3e6038d2510e.png
[29b1184cd6c04891bc4826c1bb7de0cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/c6d3b2cc11f548ab8616307f6d656fe1.png
[688d866037604fa798f39eed6b61ae77.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/9bf72f96295f4b95b68f652bcffe68ea.png
[cadb99bc048247be8a07aed122588c45.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/929f436b50b74b1184847a5b41fa6904.png