MySQL 主从保证数据一致的原理

作者: 如梦又似幻 | 来源:发表于2020-03-05 23:17 被阅读0次

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

在备库上通过 change master 命令，设置主库的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
在备库上执行 start slave 命令，这时候备库会启动两个线程，就是 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
主库校验完用户名、密码后，开始按照备库传过来的位置，从本地读取 binlog ，发给备库。
备库拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
sql_thread 读取中转日志，解析出日志里的命令并执行。

后来由于多线程复制方案的引入，sql_thread 演化成了多个线程。

statement 格式，记录的是真实的 sql 语句，但是 statement 格式在执行 delete 且带有 limit 的语句时，会有导致主备不一致的风险。
row 格式，记录的是 event ，分别记录操作的表和进行的行为。
mixed 格式

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。
但 row 格式的缺点是很占空间。比如一个删除 10 万行数据的 delete 语句，statement 格式的话就只是一条 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果是 row 格式的 binlog，就要把这 10 万行记录都写到 binlog 中。这样做不仅会占用巨大的空间，同时写 binlog 也会耗费 IO 资源，影响执行速度。
所以，MySQL 就有了 mixed 格式的 binlog。mixed 格式是指 MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

现在越来越多的场景要把 binlog 格式设置为 row。这么做的好处有很多，比如在恢复数据时：

delete 语句，row 格式的 binlog 会把删掉的行整行信息保存下来，如果删除了数据，就可以直接把 binlog 中记录的 delete 语句转成 insert来恢复数据。
insert 语句，row 格式下，insert语句的 binlog 会记录所有的字段信息，这些信息可以精确定位刚刚被插入的那一行。
update 语句，binlog 会记录修改前后的整行数据。

循环复制

主从中的 server id 就是为了解决循环复制的问题。

主备延迟，就是同一个事务在备库执行完的时间和主库执行完的时间之间的差值，包括主库事务执行完成时间和将 binlog 发送给备库，备库事务执行完成时间的差值。每个事物的 seconds_behind_master 即为延迟时间，每个事务的 binlog 里边都有一个时间字段，用于记录主库上的写入时间，备库去除当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值。
主备延迟的来源：
- 有些部署条件下，备库所在的机器性能要比主库所在的机器性能差，原因是多个备库部署在同一台机器上，大量的查询会导致 io 资源的竞争，解决的方法是配置“双1”，redo log 和 binlog 都只 write fs page cache
- 大量的查询操作在备库执行，耗费了大量的 cpu 资源，导致同步延迟。可以使用一主多从，减少备库压力。
- 大事务，如果一个大事务 dml 操作导致执行时间过长，将 binlog 发送给备库，备库执行也需要很长时间，导致主备延迟，解决方法是尽量减少大事务，比如 delete 操作可以分批删除，可以防止大事务也可以减少锁的范围。
- 大表的 DDL。这会导致主库将其 ddl binlog 发送给备库，备库解析 relay log，同步，后续的 dml binlog 发送过来，需等待 ddl 的 mdl 写锁释放，导致主备延迟。