MySQL如何保证主备一致？

1. MySQL主备的基本原理

如下图展示的是基本的主备切换流程：

在状态1中，主库是A，备库是B，所以客户端的读写都直接方法节点A。由于节点B是节点A的备库，所以备库B只是将A的更新都同步过来，本地执行，这样可以保证节点B和节点A的数据一致性。

如果发生主备切换，就会从状态1变成状态2，节点A成为备库，节点B成为主库。

在状态1中，虽然节点B没有被客户端直接方法，但是还是建议将节点B（备库）设置成只读（readonly）模式，主要有以下几个理由：

1. 避免某些服务访问了备库，造成误操作；
2. 防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致；
3. 可以用readonly状态，来判断节点的角色；

注意：readonly对于超级管理员是无效的，而用于同步更新的线程，就拥有超级权限，所以是可以修改备库的。

接下来我们看下节点A到节点B的流程图：

实际上备库B和主库A之间维持了个长连接，主库A中有一个线程（dump_thread），专门用于服务和备库B的长连接。日志同步的完整过程如下：

1. 在备库B上通过change master命令，设置主库A的相关信息，以及要从哪个位置开始请求binlog；
2. 在备库B上执行start slave命令，备库会启动两个线程，即io_thread和sql_thread，其中io_thread负责与主库通信；
3. 主库A校验完信息后，根据备库B转过来的位置，本地读取binlog，传递给B；
4. 备库拿到binlog后，写到本地文件，称为中转日志（relay log）；
5. sql_thread读取中转日志，解析出命令并执行；

2. binlog的三种格式

binlog的格式实际上由两种格式，一种是statement，一种是row。此外还有一种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')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

然后，我们对于这个表执行delete语句：

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

我们可以使用下面的命令来查看binlog中的内容：

mysql> show binlog events in 'master.000001'
1

可以看到，当binlog_format=statement时，binlog里面记录的就是sql原文：

为了比较statment和row的区别，我们看下这条delete语句的执行图：

从图上可以看到，运行过程中产生了一个warnings，原因是binlog设置的格式时statement，并且语句中有limit，所以时unsafe的。那为什么说是unsafe呢？

• 如果delete语句使用的是索引a，那么会根据索引a找到第一个满足条件的行，也就是a=4这一行。
• 如何delete语句使用的是索引t_modified，那么删除的就是a=5这一行。

所以使用statement可能会造成主备不一致的情况。如果在主库和备库中执行这条SQL语句，走的索引不一样，就会出现数据不一致性。

我们接下来再看binlog_format=row的情况，下面是binlog中的内容：

从图上可以看到，row格式的binlog没有写SQL语句的原文，而是替换成了两个event：

• Table_map event:说明要操作的表是test库的表t；
• Delete_rows event：定义删除哪一行

上面实际上是没有完全显示信息的，可以借助mysqlbinlog工具查看详细信息：

所以，当binlog_format=row时，binlog记录了真实删除行的主键id，这样即使在备库中，也是删除这一行，不会出现主备不一致的情况。

3. 为什么会有mixd格式的binlog？

从上面的描述中，我们可以很清楚地看到statement和row格式的优缺点：

• statement：格式节省空间，只需要记录sql语句。但是可能会出现主备不一致的情况；
• row：不会出现主备不一致的情况。但是格式十分消耗空间，需要记录所有修改的行。

而 mixed格式的意思是，MySQL会自己判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。

所以线上的场景，设置为statement格式肯定是不合理的，至少要设置成mixed格式。

实际上，现在越来越多都是使用row格式，其中一个好处就是恢复数据：

• 当执行delete语句后，发现误删了，直接将binlog中的信息，转换成insert语句插入即可
• 当执行insert语句后，发现错误插入了，直接将binlog中的信息，转换成delete语句插入即可
• 如果执行的是update语句，binlog会记录修改前后的信息，方面恢复

4. 循环复制问题

刚才介绍的是M-S结构，现在用的比较多的是双M结构，如下图：

这个和M-S结构的区别在于，节点A和节点B之间互为主备关系。这种架构有个问题：当节点A更新了数据，写入binlog_A,然后传给节点B，节点B也会执行更新，写入binlog_B。然后由于节点B更新了，节点A又会去执行节点B的更新，就造成一个死循环的情况。

为了避免这种情况，MySQL在binlog中记录了这个命令第一次执行时所在实例的server id：

1. 规定两个库的server id必须不同，如果相同，不能互为主备；
2. 一个备库接到binlog进行重放的时候，生成与原binlog的server id相同的新binlog；
3. 每个库在收到从自己的主库发过来的日志后，先判断server id，如果和自己相同，说明时自己第一次生成的，就直接丢弃这个日志。

来源：自己整理的MySQL实战45讲笔记