🏆今日学习目标：

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

 🍁林在闪闪发光的个人社区，欢迎你的加入: 林在闪闪发光的社区

目录

一 什么叫主备同步

二 主备同步的好处

三 主备同步的实现原理

四. binlog的三种格式

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

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

M-S结构

双M结构

双M结构的循环复制问题

---

一 什么叫主备同步

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

二 主备同步的好处

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

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

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

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

三 主备同步的实现原理

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

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

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

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

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

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

实际上备库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语句： 

注意，下面这个语句包含注释，如果你用 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原文：

 

现在，我们来看一下图 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 命令的执行效果图：

 

可以看到，运行这条 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 中的内容吧。 

可以看到，与 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; 

 

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

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结构，两个节点，一个当主库、一个当备库，不允许两个节点互换角色。

 

在状态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后，发现是自己的，就丢掉。死循环就在这里断掉了。