初学MySQL—主备一致

binlog可以用来归档，也可以用来做主备同步，为什么备库执行了binlog就可以和主库保持一致？

MySQL主备的基本原理

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

状态2中，客户端读写访问的都是节点B,节点A是节点B的备库；

在状态一将节点B设置为只读(Readonly)模式的考虑如下：

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

readonly对于超级(super)权限用户是无效的，而用于同步更新的线程，就拥有超级权限；

节点A到B这条线的内部流程是什么？
下图是update语句在节点A执行，同步到节点B的完整流程图；

上图中可以看到主库接收到客户端的更新请求之后，执行内部事务的更新逻辑，同时写入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的三种格式对比

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');

来看一下delete语句的binlog是如何记录的；

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

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

show binlog events in 'master.000001'; 命令查看binlog中的内容

下图是statement格式binlog示例；

第二行BEGIN和第四行commit对应,表示中间是一个事务；

第三行 "use ‘test’"是MySQL根据当前要操作表所在的数据库，自行添加的；这样可以保证日志传到备库去执行的时候，不论工作线程在哪个库里，都能正确的更新到test库的表t;之后的delete语句就是输入的SQL原文，被记录到了binlog中；

第四行就是commit,XID是redo log和binlog的共同数据字段；

崩溃恢复的时候，会顺序扫描redo log:如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务

statemen和row格式的区别
运行这条 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 这一行；

在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时
候，却使用了索引 t_modified,所以认为这是有风险的；

将binlog中的格式改为binlog_format=‘row’；

与 statement 格式的 binlog 相比，前后的 BEGIN 和 COMMIT 是一样的。
但是，row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：
Table_map 和 Delete_rows;

1. Table_map event，用于说明接下来要操作的表是 test 库的表 t;
2. Delete_rows event，用于定义删除的行为;

需要借助 mysqlbinlog 工具，用下面这个命令解析和查看 binlog 中的内容

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

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

为什么会有mixed的binlog格式

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式；
但 row 格式的缺点是，很占空间；mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式；

循环复制问题

上图是双M结构，节点 A 和 B 之间总是互为主备关系。这样在切换的时候就不用再修改主备关系；

存在一个问题：业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog；双方会出现循环复制的问题；

解决循环复制：

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

采用了双M结构后的，日志执行流程如下：

• 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；
• 传到节点B在执行一次，节点 B 生成的 binlog 的 server id 也是 A 的 server id；
• 再传回节点A,A 判断到这个 server id 与自己的相同，就不会再处理这个日志,死循环直接断掉；

总结

主要介绍了MySQL binlog 的格式和一些基本机制，和三种binlog格式的优缺点；