Mysql主备一致性问题

我们今天简单了解一下主备库的基本原理。理解了背后设计原理,

Mysql主备基本原理

下图就是基本的主备切换流程

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

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

在状态1中,虽然节点B没有被直接访问，但是我依然建议你把节点B,设置只读模式，这样组有以下几个好处

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

但是我们发现如果把备库设置成只读，那么主备如何同步更新呢，这个问题，是因为只读(readonly)设置对超级权限用户是无效的，而同步更新的线程，就拥有超级权限

接下来，我们看看同步的内部流程是什么样，如下图

可以看到在主库上执行的更新请求后，执行内部事务的更新逻辑，同时写binlog.备库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里面到底是什么内容，为什么备库拿过来可以直接执行

binlog的三种格式对比

我之前介绍过binglog有两种格式，一种是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');

当我们执行一条delete语句，binlog是怎么记录的，如下面sql

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

这里要注意的是上面的注释，如果我们用mysql客户端来做这个实验的话，要记得添加-c参数，否则客户端会自动去掉注释

当binlog_format=statemetn时，binlog里面记录的就是sql语句的原文，可以使用下面语句查看

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

命令查看binlog中的内容

我们在介绍一下上图的含义

1. 第一行set @@SESSION.GTID_NEXT='ANONYMOUS'你可以先忽略。后面我们介绍主备切换的时候在说
2. 第二行是一个begin,第四行的commit对应，表示中间是一个事物
3. 第三行，就是真正执行的语句，可以看到，真正执行delete之前，还有一个use test语句,这个可以保证不管线程在哪个库，都能正确的更新test库的表t,执行完之后，就会执行delete语句，这个就是我们输入的原文了,也可以看到binlog也会记录sql的注释.
4. 最后一行commit,你可以看到xid=61,XID是用来联系binlog和redo log的，比如redo log里面有一个事物是prepare状态，但是不知道是不是commit状态,那就可以用XID去binlog日志查看该事务是否提交，有提交就执行，没有提交则回滚.

我们在看看delete命令的执行效果

可以看到，运行这条delete产生了一个warning,原因是当前binlog设置的是statement格式，并且语句中有limit,所以这个命令可能是unsafe的

为什么是unsafe呢,这是因为delete的limit，很可能就出现主备数据不一致的情况，

1. 如果delete语句使用的索引a,那么会根据索引a找到第一个满足条件的行,也就是说删除的a=4这一行
2. 但是如果使用的是索引t_modified,纳闷删除就是t_modified=’2018-11-09‘也就是a=5这一行

由于statement格式下,记录到binlog里面的语句原文,因此可能出现主库上执行这条语句用的是索引a,而在备库执行这条sql语句的时候，却使用索引t_modified，因此mysql认为这是有风险的。

那么如果记录的格式是row.是不是没有这个问题呢

我们看到row格式里面没有sql原文,而替换成两个event，table_map和delete_rows。

1. table_map event,用于说明接下来要操作的表是test库的t表
2. delete_rows event,用于定义删除的行为

实际上我们一般要用mysqlbinlog工具解析binlog日志，使用下面语句，binlog是从890这个位置开始的,索引可以用start-position参数指定

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

上图我们可以看到下面这几个信息

1. sever id 1.说明这个事务是在server_id=1这个库上执行的
2. 每个event都是CRC32的值，这是因为我们把参数binlog_checksum设置成了CRC32
3. Table_map event，显示接下来要打来的表
4. 我们使用mysqlbinlog命令中，使用-vv参数是为了把内容解析出来,所以从结果里面我们看到各个子弹的值(比如@1=4,@2=4这些值)
5. binlog_row_image默认值是Full,因此delete_event里面，包含了删除的行的所有字段的值，如果把binlog_row_image设置为MINIMAL,则只会记录必要的信息，这个例子里，就只只会记录id=4这个记录
6. 最后xid event用于记录事务被正确的提交了

我们可以看到binlog_format=row的时候，binlog里面记录了真实删除行的主键id,这样在传到备库的时候，肯定会删除id=4这一行，不会有主备删除不通行的问题.

为什么会有mixed格式的binlog

那是因为前两种格式有各自的缺点，而mixed去了折中的方案

1. 如果是statement格式的binlog可能会导致主备不一致,所以要使用row格式
2. 如果使用row会占用很多空间，比如要删除10万行语句，我们既要记录都要写入binlog中，这样会导致io消耗，影响执行速度
3. 最后如果使用mixed,mysql会判断sql如果可能导致主备不一致,如果有可能，就用row格式，否则用statement,

mixed就是利用statement格式的优点同时避免了数据不一致的风险

但是越来越多的场景要求把mysql的binlog格式设置很row,这样做有很多好处，比如恢复数据

比如，即使我们执行delete语句，row格式的binlog语句与binlog里面记录所有的字段信息，这些信息保存起来，所以如果我们误删除了数据，可以直接把binlog中记录的delete转成inset,把别删除的数据插入回去就可以恢复了，其他操作同样的原理如insert,update

虽然说mixed现在场景用的不多，但是我们举个例子如下sql,说明一个问题

mysql> insert into t values(10,10, now());

如果binlog格式是mixed,上面的语句在binlog格式是row还是statement格式呢

我们发现是statement格式，但是我们还会有疑问，如果这条语句在备库上执行，会导致数据不一致,此时我们使用mysqlbinlog工具看看分析一下

可以看到binlog记录event的时候,多记录了一条命令set timestamp=1546103491,他用来预定接下来now函数的返回值,因此不管备库上什么时候执行，数据都是一样的。

我们可以发现binlog的日志有可能是依赖上下文的,因此我们不要直接把binlog的部分日志在数据库上执行，可能会导致数据不一致。

循环复制问题

文章的开头,我们看到主备的结构,是一种M-S结构，但是实际上使用的是双M结构，如下图

双M的和M-S的区别就是多了一条线，节点A和节点B总是互为主备关系，这个时候主备切换不用修改主备关系，但是双M结构还有一个问题需要解决

业务上节点A更新了一条语句，然后把生成的binlog发给节点B，节点B执行完这条更新语句后也会生成binlog,那么,如果节点A同时也是节点B的备库，此时节点B新生成的binlog,会把节点B的binlog日志拿到到节点A上执行，这不就导致死循环了吗

其实mysql是按照下面逻辑解决循环复制的问题的

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

按照上面逻辑就可以避免循环复制binlog的问题

如果对您有一丝丝帮助，麻烦点个关注，也欢迎转发，谢谢