MySQL实战45讲_24 | MySQL是怎么保证主备一致的？-一一网

24 | MySQL是怎么保证主备一致的？

在前面的文章中，我们提到binlog可以用来归档，也可以用来做主备同步，但它的内容是什么样的呢？为什么备库执行了binlog就可以跟主库保持一致了呢？

今天正式地和你介绍一下它。毫不夸张地说，MySQL能够成为现下最流行的开源数据库，binlog功不可没。

在最开始，MySQL是以容易学习和方便的高可用架构，被开发人员青睐的。而它的几乎所有的高可用架构，都直接依赖于binlog。虽然这些高可用架构已经呈现出越来越复杂的趋势，但都是从最基本的一主一备演化过来的。

今天这篇文章我主要为你介绍主备的基本原理。理解了背后的设计原理，你也可以从业务开发的角度，来借鉴这些设计思想。

MySQL主备的基本原理

如图所示就是基本的主备切换流程。

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

当需要切换的时候，就切成状态2。这时候客户端读写访问的都是节点B，而节点A是B的备库。在状态1中，虽然节点B没有被直接访问，但是我依然建议你把节点B（也就是备库）设置成只读（readonly）模式。这样做，有以下几个考虑：

有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致；
可以用readonly状态，来判断节点的角色。

你可能会问，我把备库设置成只读了，还怎么跟主库保持同步更新呢？
不用担心。因为readonly设置对超级(super)权限用户是无效的，而用于同步更新的线程，就拥有超级权限。
节点A到B这条线的内部流程是什么样的？图中画出的就是一个update语句在节点A执行，然后同步到节点B的完整流程图。

可以看到：主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。

备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。一个事务日志同步的完整过程是这样的：

在备库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有两种格式，一种是statement，一种是row。可能你在其他资料上还会看到有第三种格式，叫作mixed，其实它就是前两种格式的混合。
为了便于描述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');
复制代码

如果要在表中删除一行数据的话，我们来看看这个delete语句的binlog是怎么记录的。注意，下面这个语句包含注释，如果你用MySQL客户端来做这个实验的话，要记得加-c参数，否则客户端会自动去掉注释。

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

当binlog_format=statement时，binlog里面记录的就是SQL语句的原文。可以用
mysql> show binlog events in 'master.000001'命令看binlog中的内容。

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

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

为了说明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，用于定义删除的行为。

其实，我们通过图是看不到详细信息的，还需要借助mysqlbinlog工具，用下面这个命令解析和查看binlog中的内容。因为图中的信息显示，这个事务的binlog是从8900这个位置开始的，所以可以用start-position参数来指定从这个位置的日志开始解析。

mysqlbinlog -vvdata/master.000001 --start-position=8900;

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

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

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

为什么会有mixed格式的binlog？

基于上面的信息，我们来讨论一个问题：为什么会有mixed这种binlog格式的存在场景？推论过程是这样的：
因为有些statement格式的binlog可能会导致主备不一致，所以要使用row格式。但row格式的缺点是，很占空间。比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要把这10万条记录都写到binlog中。这样做，不仅会占用更大的空间，同时写binlog也要耗费IO资源，影响执行速度。

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

比如我们这个例子，设置为mixed后，就会记录为row格式；而如果执行的语句去掉limit 1，就会记录为statement格式。当然我要说的是，现在越来越多的场景要求把MySQL的binlog格式设置成row。这么做直接看出来的好处：恢复数据。

小结

mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)