Mysql如何保证主备一致

先看看mysql主备切换的流程

在状态1中，客户端的读写都直接访问节点A，而节点B是A的备库，只是将A的更新都同步过来，到本地执行。这样可以保持节点B和A的数据是相同的。当需要切换的时候，就切成状态2。这时候客户端读写访问的都是节点B，而节点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格式

binlog有以下几种格式，一种是statement，一种是row，最后是mixed，mixed是之前两种的混合。

statement 格式

我们看一条语句在binlog中的记录，语句是

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

binlog中记载的是

1. 第一行SET @@SESSION.GTID_NEXT='ANONYMOUS’先忽略。
2. 第二行是一个BEGIN，跟第四行的commit对应，表示中间是一个事务；
3. 第三行就是真实执行的语句了。可以看到，在真实执行的delete命令之前，还有一个“use test”命令。这条命令是MySQL根据当前要操作的表所在的数据库，自行添加的。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到test库的表t。use 'test’命令之后的delete 语句，就是SQL原文了。可以看到，binlog甚至连注释也一并记录了。
4. 最后一行是一个COMMIT。

看看这个delete执行的效果：

它产生了一个warning，因为delete中有limit，所以可能会导致准备数据不一致的情况。

1. 如果delete语句使用的是索引a，那么会根据索引a找到第一个满足条件的行，也就是说删除的是a=4这一行；

2. 但如果使用的是索引t_modified，那么删除的就是 _modified='2018-11-09’也就是a=5这一行。

Statement格式是记录了语句的原文，这样子，在主备执行的时候，可能选择索引会不一样，导致数据不一致。

 

row模式

还是一样的语句，delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;看row模式下的binlog是怎么记载的。

row格式的binlog里没有了SQL语句的原文，而是替换成了两个event：Table_map和Delete_rows。

1. Table_map event，用于说明接下来要操作的表是test库的表t;

2. Delete_rows event，用于定义删除的行为。

再看看详细信息：

从这个图中，我们可以看到以下几个信息：

1. server id 1，表示这个事务是在server_id=1的这个库上执行的。
2. 每个event都有CRC32的值，这是因为我把参数binlog_checksum设置成了CRC32。
3. Table_map event跟在图5中看到的相同，显示了接下来要打开的表，map到数字226。现在我们这条SQL语句只操作了一张表，如果要操作多张表呢？每个表都有一个对应的Table_mapevent、都会map到一个单独的数字，用于区分对不同表的操作。
4. binlog_row_image的默认配置是FULL，因此Delete_event里面，包含了删掉的行的所有字段的值。如果把binlog_row_image设置为MINIMAL，则只会记录必要的信息，在这个例子里，
5. 就是只会记录id=4这个信息。
6. 最后的Xid event，用于表示事务被正确地提交了。

当binlog_format使用row格式的时候，binlog里面记录了真实删除行的主键id，这样binlog传到备库去的时候，就肯定会删除id=4的行，不会有主备删除不同行的问题。

 

Mixed模式

Mix模式是以上两种模式的结合，在于：

1. 因为有些statement格式的binlog可能会导致主备不一致，所以要使用row格式。
2. 但row很占空间。比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。就要把这10万条记录都写到binlog中。

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

 

Row格式有一个好处，就是恢复数据。

1. 执行的是delete语句，row格式的binlog也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条delete语句以后，发现删错数据了，可以直接把binlog中记录的delete语句转成insert，把被错删的数据插入回去就可以恢复了。
2. 执行的是insert语句，insert语句的binlog里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把insert语句转成delete语句，删除掉这被误插入的一行数据就可以了。
3. 执行的是update语句，binlog里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了update语句的话，只需要把这个event前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

双master主备结构

Mysql还有一种结构是双master结构，他的切换如下

节点A和B之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。

为了解决一条语句只执行一次的问题，引入了server id的概念。

1. 规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系；

2. 一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog；

3. 每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。