MySQL主备复制

我们知道MySQL中的binlog 可以用来归档,同时也可以用来做主从同步,今天来详细介绍一下。

MySQL 主备原理

从上面流程可以看出,A 节点是主库,B 节点是从库。通常情况下从库我们会设置为 readonly ,这样可以防止因业务代码中的切换 BUG 造成 在从库写数据;同时我们也可以通过 readonly 的状态来判断,节点的主从状态。

下面我们看一下,一个 update 语句在 A、B 节点上面的数据流转。

上图包含了 binlog 和 redo log 的写入机制,备库 B 和 A 之间维持了一个长连接。主库 A 里面有一个线程,专门服务于备库 B 。一个事务的同步的完整过成如下:

1、在备库 B 上面通过 change master 命令,设置主库 A 的IP、端口、用户名、密码,以及需要同步主库 binlog 的位置;

2、在备库 B 上执行 start slave 命令,这个时候备库会启动 2 个线程,就是图中的 io_thread 和 sql_thread。其中 io_thread 负责和主库建立连接;

3、主库校验完成之后,开始从指定 binlog 位置读取,并传送给备库 B;

4、备库 B 拿到 binlog 后写到本地(relay log);

5、sql_thread 读取 relay log,解析并执行。

binlog 的 3 种格式

MySQL的 binlog 有以下 3 种格式:statement、row和mixed,其中 mixed 的可以认为是 statement 和 row 两种格式的混合。

statement

statement 格式下,记录到binlog 里面的是 SQL 原文。这个格式占用存储较小,但是可能会造成主从不一致。比如执行

mysql>delete from t limit 1;

此时如果 主库和从库上面的索引不同,或者执行时的时间点有问题,此时可能会造成主从不一致。

row

row 相对于 statement 格式而言,它记录了真实的数据变化。比如执行 delete 命令,此时它记录了真实的主键ID,此时备库执行时就不会删错数据了;在执行 update 语句的时候,它会记录变更前后的数据变化。

但是 row 格式的 binlog 由于记录了真实的数据变更,因此数据量会大很多。如 statement 格式下记录 delete from t order by id limit 10000,此时对于 statement 格式就是一条语句;而对于row 格式则是对应 10000 条删除的数据。

mixed

mixed 可以认为是 statement 和 row 格式的优点组合体。当 MySQL 认为执行的SQL 没有影响主从数据一致性时,它存储的就是 statement 格式;否则就是 row 格式。

但是在实际生产中,更多的场景下要求 binlog 使用的是 row 格式,因为这样可以方便的对数据恢复。

循环复制问题

上面我们介绍的主从结构是 M-S 的结构,但是在生产中使用更多的是双M 的结构,此时主备切换流程如下所示:


和上面的M-S 结构相比,双M的结构会有一个双向复制 binlog 的问题,此时解决循环复制的方案是:binlog 只记录原始的 serverid,每个节点只处理非自己生成的 binlog。


参考:《极客时间:MySQL实战》、《高性能MySQL》