读mysql45讲-主备一致

在最开始，MySQL是以容易学习和方便的高可用架构，被开发人员青睐的。而它的几乎所有的 高可用架构，都直接依赖于binlog。虽然这些高可用架构已经呈现出越来越复杂的趋势，但都是 从最基本的一主一备演化过来的。

在状态1的时候，客户端读取数据都是从A数据库读取，节点B是数据库A的备用库；在A中执行的更新语句都会同步到备用库B中，以此保证两个数据库的数据是一致的。

在状态2中，当需要切换的时候，客户端就从数据库B读取数据，数据库A就变为数据库B的备用库；

在状态1和状态2中，备用库都是readonly,也就是只读状态。

• 这样可以防止当有些执行语句被分配到从库的时候发生误操作
• 防止切换逻辑出现bug，比如切换过程中出现双写，造成主备的数据不一致
• 可以用来区分节点的角色，读写就是主库，只读就是从库。

在主库中发生的更新语句都会同步备用库中，虽然备用库是只读的，但是对超级管理员来说是不生效的，用于同步数据的线程就是拥有超级管理员权限。

在masterA节点中的过程就是之前有记录的更新操作记录到redo log和binlog的过程。

备用库和主库之间有一个长连接，主库A内部有一个线程，专门服务于备用库B的连接。

1. 在备库B中执行change master 命令，设置主库A的ip，port，username，password，以及从哪个位置开始请求binlog，这个位置包含binlog文件名和偏移量。
2. 在备库B上执行start slave,这个时候备用库B会启动两个线程，就是上图中的io-thread，sql-thread；io-thread负责和主库的连接
3. 主库在验证完用户名，密码等等之后，按照备库B指定的binlog的文件名和偏移量，从本地读取binlog文件，返回给备库B。
4. 备库B拿到binlog日志之后，写到本地文件，称为中转日志(relay log)
5. sql_thread读取relay log，解析其中记载的命令，开始执行。

binlog的三种格式

binlog有三种格式，一种是statement，另一种是row，还有一种是minxed(也就是前两中的混合)。

当binlog_format=statement的时候，binlog里面记录的就是SQL语句的原文。

• 可以看到第二行有个begin，末尾行是个commit，表示是开启了一个事务。
• 第三行就是SQL语句的原文了
• 在SQL语句原文的前面，有个use test; 指定了数据库，这个命令不是主动加的，而是binlog自己添加的，这样就可以保证不会在其他库里面执行错语句。

运行这条delete命令会产生一个warning
原因是当前binlog设置的是statement格 式，并且语句中有limit，所以这个命令可能是unsafe的。
为什么这么说呢？这是因为delete 带limit，很可能会出现主备数据不一致的情况。比如上面这个例子：

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

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

binlog_format=row；

• 也是一个事务
• 并没有记载sql语句原文，而是记载了两个event
• Table_map： 记载的是哪个库的哪个表，table_id： 226(test.t)
• Delete_rows： 用来定义删除的行为

借助mysqlbinlog工具，用下面这个命令解析和 查看binlog中的内容。因为图5中的信息显示，这个事务的binlog是从8900这个位置开始的，所以 可以用start-position参数来指定从这个位置的日志开始解析。（复制过来的，并没有去用mysqlbinlog工具）

mysqlbinlog -vvdata/master.000001 --start-position=8900;

• server id 1，表示是在server_id = 1这个库上执行的
• Table_map： test.t mapped to number 226和上上图中是一样的，每个表都有一个对应的Table_map event、都会map到一个单独的数字，用于区分对不同表的操作。Delete_rows也是类似的。
• 我们在mysqlbinlog的命令中，使用了-vv参数是为了把内容都解析出来，所以从结果里面可以 看到各个字段的值（比如，@1=4、 @2=4这些值）(好像1，2这些数字就是表结构字段的顺序，1就是id)
• binlog_row_image的默认配置是FULL，因此Delete_event里面，包含了删掉的行的所有字段 的值。如果把binlog_row_image设置为MINIMAL，则只会记录必要的信息，在这个例子里， 就是只会记录id=4这个信息。
• 最后的Xid event，用于表示事务被正确地提交了

mixed格式

• statement格式的日期，因为记载的是SQL原文，所以如果sql语句写的不好，可能会在主库和备用库执行的效果不一样，导致主备不一致，所以需要使用row
• row格式的缺点就是很占空间，删除一个10万行的数据，如果用的是statement格式，直接就是一个delete语句，只会占用很少的空间，但如果是row格式，则会记录每一个记录的id，也就是10万行id；不仅占用空间，而且备用库执行的时候也需要写入binlog日志，也会耗费io资源。
• 所以MYSQL就有了mixed格式的binlog；MYSQL自己会判断SQL语句是否会引起主备不一致，如果会，就使用row格式，否则就是用statment格式。

考虑一条插入语句：

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

主库执行这条语句，并且记录到binlog日志中；然后等收到备用库的请求之后，再把日志发给备用库，备用库执行这条语句；这期间肯定会有时间间隔，那这样是不是就可能出现主备不一致？那mysql记录这条语句应该使用的是row格式么?

并不是，mysql还是会使用statement，因为binlog在记录event的时候，多记了一条命令：SET TIMESTAMP=1546103491。它用 SETTIMESTAMP命令约定了接下来的now()函数的返回时间。

用binlog来恢复数据的正确做法是，用mysqlbinlog工具解析出来，然后把解析结果整个发给mysql执行：

mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

这个命令的意思是，将 master.000001文件里面从第2738字节到第2973字节中间这段内容解析出来，放到MySQL去执行。

双M结构

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

业务逻辑在节点A上更新了一条语句，然后再把生成的binlog发给节点B，节点B执行完这条更新 语句后也会生成binlog。（我建议你把参数log_slave_updates设置为on，表示备库执行relay log 后生成binlog）。

那么，如果节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然
后节点A和B间，会不断地循环执行这个更新语句，也就是循环复制了。这个要怎么解决呢？

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

按照这个逻辑，如果我们设置了双M结构，日志的执行流就会变成这样：

1. 从节点A更新的事务，binlog里面记的都是A的server id；
2. 传到节点B执行一次以后，节点B生成的binlog 的server id也是A的server id；
3. 再传回给节点A，A判断到这个server id与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。