Mysql主备一致性保证

news/2024/5/2 23:41:33/文章来源:https://blog.csdn.net/weixin_46935110/article/details/130019924

大家知道 bin log 既可以用来归档，又可以用来做主备同步。有人可能会问，为什么备库执行了 bin log 就可以跟主库保持一致了呢？bin log的内容是什么样的呢？今天我们就来聊聊它。

在最开始，Mysql 是以容易学习和方便的高可用架构，备受开发人员青睐的。而它所有的高可用架构都是依赖于bin log。虽然这些高可用架构日益复杂，但都是从基本的一主一备演化而来的。今天，就来介绍下主备的基本原理，重点是学习这种设计思想。

Mysql主备基本原理

在这里插入图片描述

在状态1中，客户端的读写都是直接访问节点A，节点B是节点A的备库，只是将节点A的更新同步过来，达到与节点A数据同步。当需要切换的时候，就变成了状态2，此时节点A就成了节点B的备库。
在上述状态中，备库虽然没有被直接访问，但建议将其设置为只读（Read only）模式，考虑如下：

一些运营类的查询语句会放到备库去查，可以防止误操作；
防止主备切换过程中的双写，造成主备不一致；
可以判断节点的角色。

因为，Read only模式对Super权限用户是没用的。所以，即使备库设置为只读模式，也能执行同步主库的数据。
接下来我们来看看，节点A到节点B的内部执行的流程是什么样的。
在这里插入图片描述
流程里包含了redo log和bin log的写入流程，忘记的同学可以翻看我之前的文章查看。
备库 B 和主库 A 之间维持着一个长链接。主库 A 中有一个专门的线程，专门服务于备库B的长链接。一个事务日志的同步完整流程如下：

在备库上执行 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从主库 A 的那个哪个位置开始请求 bin log，这个位置包含 bin log 文件名和偏移量；
在备库上执行 start slave 命令，这时候备库 B上会启动两个线程，分别是图中的 io_thread 和 sql_thread。其中，io_thread 负责和主库 A建立连接。
主库 A 校验完用户名、密码后，开始从备库 B 要求的位置，从本地读取 bin log 发送给备库B；
备库B 拿到 bin log 文件后，写到本地文件，也就是图中的 relay log；
sql_thread 读取 relay log，解析出日志中的命令并执行。

说明，随着多线程复制的引入，sql_thread 被优化为多个线程，这个后面在展开讨论。分析完这个长链接的逻辑，我们接下来看看 bin log 里面有什么，为什么备库拿过去可以直接执行？

bin log 的三种格式对比

在Mysql中，bin log 有三种格式，分别为：statement、row 和 mixed。其中，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');

如果我们要删除一行数据的话，我们来看看 bin log 是怎么记录的，注意一下语句包含注释，如果你用Mysql 客户端来做这个实现的话，记得加 -c 参数，否则客户端会自动去掉注释：

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

当 binlog_format=statement 的时候，binlog里面记录的就是语句原文。可以用以下命令查看 bin log 的内容：mysql> show binlog events in ‘master.000001’;
statement 格式 bin log 示例
我们来看下bin log 里都有什么：

第一行的 SET @@SESSION.GTID_NEXT='ANONYMOUS’ 可以先忽略，后面我们将主备切换的时候再细说；
第二行的 begin 和第四行的 commit，表示中间是一个事务；
第三行就是真实执行的语句了，可以看到，在实行delete 之前，还有个use ‘test’，这条命令是Mysql 根据当前需要操作的表，自动添加的。这样做，可以保证日志在传到备库执行时候，无论工作线程在哪个库执行，都可以准确的更新到 test 库的表 t。use ‘test’之后就是，就是我们要执行的语句原文了。

为了说明 statement 和 row 格式的区别，我们来看看这条delete 语句的执行效果：
在这里插入图片描述
可以看到，这条delete 命令产生了一条 warning，原因是当前bing log 的格式为statement 并且语句中有 limit，所以这条命令是 unsafe 的。为什么这么说呢？因为delete + limit 可能造成主备数据的不一致。比如，上面这个例子：

如果这条delete 命令走的是索引a，那么会找到满足条件的第一行，也就是说删除的是 a=4 这行数据；
如果走的是索引t_modified，那么删除的就是 t_modified=‘2018-11-09’ ，也就是 a=5 这行数据。

这样的话，因为 bin log 格式是 statement，记录的是原文。在主库上，走的是索引 a，而在传到备库，走的是索引 t_modified ，就造成了数据不一致。因此，Mysql 认为这么写是有风险的。

那既然statement 格式是不安全的，如果我们改成 row 格式呢？在回答这个问题之前，我们先来看看 row 格式的bin log 长什么样吧。
在这里插入图片描述
可以看到，相较于 statement 格式的 bin log，row格式只是在 begin 和 commit 之间的记录有区别，没有原文的sql语句，变成了两个 event：Table_map 和 Delete_rows。说明：

Table_map 说明操作的是哪个库的哪个表；
Delete_rows 定义删除行为。这里还看不到删除了哪一行，需要借助 mysqlbinlog 工具。

可以用以下命令查看更详细的日志内容。因为上图显示，这个事务的binlog 是从master.000001文件且字节偏移位置为8900的位置开始的，所以命令如下：

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

在这里插入图片描述
从图中我们可以看到：

server id 1 ，表示这个事务是在 server_id=1 的库上执行的；
Table_map 和 Delete_rows 两个event 和 statement 一样，显示了接下来要打开的表，map到数字226。这条语句只涉及到了一张表，如果涉及到多张表，每一个表都会有一个对应的Table_map event，都会map到一个单独的数字，用于不同表的区分。
-vv 参数是为了看到每个字段的值
Xid event 用于表示事务被正确的提交了。

可以看到，row 格式的binlog 记录了真实的被删除的数据记录，这样传到备库执行，删除的一定是相同的行。不会有主备删除不同行的问题。

为什么会有 mixed 格式的binlog

statement 格式的binlog 可能会导致主备不一致；
row 格式的binlog 会记录操作的真实的数据记录，缺点是：占空间和占用 IO 资源。假如你要删除10w行记录，用statement 格式的 binlog 就一条语句，但是，row 格式会把这10w 条记录都写到binlog。不仅站空间，写 binlog 还会占用IO 资源，影响执行速度。
所以，Mysql 就采取了折中的方案：mixed 。 Mysql 会自己判断，当前执行的sql 是否安全，从而选用statement 还是 row 格式的binlog。