MySQL实战问题03 mysql如何保证主备一致

MySQL 主备的基本原理

主备流程切换

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

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

M-S模式中, 为什么建议把备库设为readonly?

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

把备库设置成只读了，还怎么跟主库保持同步更新呢？

因为 readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。

节点 A 到 B 这条线的内部流程是什么样的

下图画出的就是一个 update 语句在节点 A 执行，然后同步到节点 B 的完整流程图

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接

一个事务日志同步的完整过程是这样的:

1. 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
2. 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
3. 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
4. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
5. 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');

删除一行, 分析binlog

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 语句的原文:

分析一下上图输出的结果:

• 第一行可以先忽略.
• 第二行是一个 BEGIN，跟第四行的 commit 对应，表示中间是一个事务；
• 第三行是真实的执行语句, 在delete命令前,还有一个use test命令, 是mysql自动添加的.
• 最后一行是一个 COMMIT, 包含一个xid.

如果使用statement格式, 记录到binlog 的是语句原文. 会有什么问题出现呢?

如果delete 带有 limit, 很可能出出现住主备数据库不一致的情况

在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified

当 binlog_format=row 时:

与statement相比, begin 与 commit 是一致的, 但是row格式没有记录原文, 而是替换成了两个event, 粉笔是table_map 与 delete_rows

• Table_map event，用于说明接下来要操作的表是 test 库的表 t;
• Delete_rows event，用于定义删除的行为。

使用mysqlbinlog工具分析解析binlog中内容

mysqlbinlog  -vv data/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，用于表示事务被正确地提交了。

为什么会有 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 格式。

如何解决双M结构的循环复制问题

解决两个节点间的循环复制的问题的逻辑

1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

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

1. 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；
2. 传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；
3. 再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。