MySQL如何保证主从一致

01  主从复制

主从复制一般是主库是写库，从库是读库，从数据库设置成只读（readonly）模式。这样做，有以下几个考虑：

1. 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；

2. 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；

3. 可以用 readonly 状态，来判断节点的角色。

主从复制的流程：

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

1. 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。

2. 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。

3. 其中 io_thread 负责与主库建立连接。主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。

4. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。sql_thread 读取中转日志，解析出日志里的命令，并执行。

02  binlog的三种格式

binlog 的三种格式对比：一种是 statement，一种是 row，一种是 mixed，它就是前两种格式的混合。

statement格式：

    当 binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文，这样存在的问题是：

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

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

为什么这么说呢？这是因为 delete 带 limit，很可能会出现主备数据不一致的情况。比如上面这个例子：

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

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

    由于 statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。因此，MySQL 认为这样写是有风险的。

row格式：

row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。Table_map event，用于说明接下来要操作的表是 test 库的表 t;Delete_rows event，用于定义删除的行为。

简而言之，binlog使用row模式记录日志，不是记录sql语句原文。采用的是1.记录操作哪张表；2记录对此表哪行数据进行了什么样的操作。

mixed格式：

为什么会有 mixed 这种 binlog 格式的存在场景？

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

    所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

    但是现在公司中越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的理由有很多，我来给你举一个可以直接看出来的好处：恢复数据

    接下来，我们就分别从 delete、insert 和 update 这三种 SQL 语句的角度，来看看数据恢复的问题。

    即使我执行的是 delete 语句，row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。

    如果你是执行错了 insert 语句呢？那就更直接了。row 格式下，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了。

    如果执行的是 update 语句的话，binlog 里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

03  循环复制/选错索引

循环复制问题：

1. 什么是循环复制？

假设A/B互相为双主关系（A/B都可以接受读写请求），假设A上执行了一个更新操作，会生成一个binlog，并且发送给B，B执行完这条更新语句之后会生成新的binlog，同时将该binlog发给A，A会再次执行该更新语句，同时生成新的binlog发送给B，如此循环反复，造成循环复制。

2. 如何解决循环复制？

给每条binlog加一个servier_id的字段（该字段唯一标识服务器），A执行完更新语句之后，生成binlog（server_id设为server_a_001）,发送给B，B检查binlog的server_id与自己的不一样，需要执行该语句，B执行该更新语句之后生成新的binlog（server_id为server_a_001，与A的一样），新的binlog发送给A，A 发现server_id与自己的一样，此时说明这条更新语句最初是在本服务器执行的，不需要重复执行，因此解决了循环复制的问题。

选错索引的问题：

1. 问题现象描述：在表t有索引a的前提下，select * from t where a between 10000 and 20000这个语句，没有走索引a，而是走了主索引，全表。

2. 问题原因：mysql的优化器判断使用ALL主索引全表的方案，优于使用索引a。其判断过程：估算使用索引a时(force index(a)),预计行数为3万多，加上使用索引a为二级索引，需要回表。其整体耗费高于使用ALL主索引全表。问题错误核心，在于使用了错误的索引统计信息，估算出错误的预计行数（实际只需1万，估算为3万）。

3. 选错索引的解决办法：

①由于索引统计信息不准确，导致判断行数不准确，此种情况可用 analyze table 来解决 ，更正索引统计信息

②由于临时表，排序字段，导致优化器误判，此种情况可用force index来强行指定索引，也可以通过修改语句引导优化器，还可以通过增加或者删除索引来绕过这个问题

04  小结

1. 主从复制：

    从节点有两个线程：io_thread和sql_thread.

    io_thread负责与主库建立连接，接收binlog后写到本地文件中

    sql_thread从本地文件中读取日志，解析日志并执行

2. binlog的三种形式：statement，row, mixed

 （1）statement：记录的是sql语句，存在的问题：主从选择索引错误时会导致主从不一致的问题。

（2）row：记录的是具体数据变更的记录：修改了那张表，进行了什么修改。不会存在主从不一致的问题。

           缺点：需要记录的数据太多，占用空间。

（3）mixed：结合statement和row的优点

但是实际公司使用的是row，可以解决恢复数据的问题：delete，insert，update。

3. 循环复制：A和B互为主从关系，A变更后的binlog告诉B，B变更后也会有binlog告诉A，如此循环，此时的解决办法是：A变更的binlog告诉B时增加server_id(server_a_001)，如果A收到server_id是自己时就不用变更了。

(1)如果是索引统计信息不准确，可以使用命令 analyze table来解决

（2）如果是因为临时表，排序字段导致优化器误判，可以使用force index 来强制制定索引，也可以通过修改语句引导优化器。