binlog保证主备一致

binlog可以用来归档，也可以用来做主备同步。

MySQL主备的基本原理

客户端的读写直接访问的节点为主库，另一个节点是备库，只是将主库的更新都同步过来，到本地执行。保持主库和备库的数据都是相同的。

备库没有被直接访问，设置成只读(readonly)模式：

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

备库设置成只读，依然能跟主库保持同步更新。readonly设置对超级(super)权限用户是无效的，用于同步更新的线程，拥有超级权限。

主备同步流程

主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。

备库跟主库之间维持了一个长连接，主库A内部有一个线程，专门服务备库的长连接。

一个事务日志同步的完整过程：

在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量。
在备库B上执行start slave命令，这时备库会启动两个线程：io_thread和sql_thread。其中io_thread负责与主库建立连接。
主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B。(如果这个文件现在还在page cache中，直接读；如果不在page cache里，就去磁盘读)。(binlog write阶段就发起主从同步日志)
备库B拿到binlog后，写到本地文件，称为中转日志relay log。
sql_thread读取中转日志，解析出日志里的命令，并执行(relay log应用后就丢弃了)。(多线程复制方案引入，sql_thread演化成为了多个线程。)

binlog的三种格式对比

binlog有三种格式：statement、row、mixed。

binlog示例代码

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 from t where a>=4 and t_modified<=’2018-11-10’ limit 1;
show binlog events in ‘master.000001’;– 查看binlog中的内容。

statement

binlog记录的是SQL语句的原文。


mater.000001	5889	Anonymous_Gtid	1	5954	SET @@SESSION.GTID_NEXT=’ANONYMOUS’
mater.000001	5954	Query	1	6041	BEGIN
mater.000001	6041	Query	1	6197	use ‘test’;delete from t where a>=4 and t_modified<=’2018-11-10’ limit 1
mater.000001	6197	Xid	1	6228	COMMIT /xid=61/

第一行SET@@SESSION.GTID_NEXT=’ANONYMOUS’；
第二行begin，跟第四行的commit对应，表示中间是一个事务；
第三行是真实执行的语句。use ‘test’命令是MySQL根据当前要操作做的表所在的数据库，自行添加的。这样可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确的更新到test库的表t。
第四行commit。xid=61可以查找到redo log与binlog对应的事务。

statement存在的问题：
当binlog设置的是statement格式，并且语句中有limit，这个命令可能是unsafe的。可能会出现主备数据不一致的情况：

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

row

binlog中么有SQL语句的原文，替换成了两个event:Table_map和Delete_rows。


mater.000001	8900	Anonymous_Gtid	1	8965	SET @@SESSION.GTID_NEXT=’ANONYMOUS’
mater.000001	8965	Query	1	9045	BEGIN
mater.000001	9045	Table_map	1	9092	table_id:226(test.t)
mater.000001	9092	Delete_rows	1	9140	table_id:226 flags:STMT_END_F
mater.000001	9140	Xid	1	9171	COMMIT /xid=68/

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

借助mysqlbinlog工具解析和查看binlog中的内容：

mysqlbinlog -vv data/master.000001 –start-position=8900;– start-position参数指定从8900这个位置的日志开始解析。

server id 1，表示这个事务是在server_id=1的这个库上执行的。(避免循环复制)
每个event都有CRC32的值，参数binlog_checksum设置为CRC32。
Table_map event显示了接下来要打开的表，map到数字226。当前SQL语句只操作了一张表，如果要操作多张表，每个表都有一个对应的Table_map event、都会map到一个单独的数字，用于区分对不同表的操作。
mysqlbinlog命令使用-vv参数可以把内容都解析出来，从结果里可以看到各个字段的值。
binlog_row_image的默认配置是FULL，Delete_event里面包含了删除的行的所有字段的值。如果把binlog_row_image设置为MINIMAL，则只会记录必要的信息。(如本例删除时只记录id=4)
Xid event用于表示事物被正确的提交了。

binlog记录了真实删除行的主键id，binlog传到备库去的时候，就肯定会删除id=4的行，不会有主备删除不同行的问题。

mixed

有些statement格式的binlog可能会导致主备不一致，所以要使用row格式。
row格式的缺点是很占空间。(用一个delete语句删掉10万行数据，用statement就是一个SQL语句被记录到binlog中，占用几十个字节的空间。用row就要把这10万条记录都写到binlog中，不仅占用更大的空间，写binlog也要耗费更多IO资源，影响执行速度)。
折中方案mixed。MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。

insert into t values(10,10,now());– binlog设置为mixed格式

binlog选择了statement格式，在记录event的时候，多记了一条命令：SET TIMESTAMP=1546103491。它用set timestamp命令约定了接下来now()函数的返回时间。

不论这个binlog是一分钟之后被备库执行，还是3天之后用来恢复这个库的备份，这个insert语句插入的行，值都是固定的。通过这条set timestamp命令，MySQL确保了主备数据的一致性。

有些语句的执行结果是依赖于上下文命令的，直接执行的结果可能是错误的。用binlog来恢复数据的标准做法是，用mysqlbinlog工具解析出来，然后把解析结果整个发给MySQL执行:

mysqlbinlog master.000001 –start-position=2738
–stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p@pwd;
将master.00001文件里面从第2738字节到第2938字节中间这段内容解析出来，放到MySQL去执行。

数据恢复原理

binlog格式设置为row，便于数据恢复。

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

MariaDB的Flashback工具回滚数据原理与此相同。

循环复制问题

binlog的特性确保了再备库执行相同的binlog，可以得到与主库相同的状态。正常情况下主备的数据是一致的。(流式发送，一个事务提交就会发送)

M-S结构：一主一备；
双M结构：互为主备，切换的时候不用修改主备关系。但任何时刻只有一个节点在接受更新。

一开始创建主备关系的时候，是由备库指定的。主备复制关系搭建完成以后，是主库来决定“要发数据给备库”的，主库有生成新的日志，就会发给备库。

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

如果节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点AB之间，会不断的循环执行这个更新语句，导致循环复制。

MySQL在binlog中记录了这个命令第一次执行时所在实例的server id。来解决两个节点间循环复制的问题：

规定两个库的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与自己的相同，就不会再处理这个日志。死循环在这里断掉。

双M结构，出现循环复制情况：

在一个主库更新事务后，用命令set global server_id=x修改了server_id。等日志再传回来的时候，发现server_id跟自己的server_id不同，就只能执行了。
有三个节点，trx在节点B执行，binlog上的server_id就是B，binlog传给节点A，然后A和A’搭建了双M结构，就会出现循环复制。

-- 数据库迁移。循环复制解决
stop slave;  
change master to ignore_server_ids=(server_id_of_B);  
start slave;  

-- 迁移

stop slave;  
change master to ignore_server_ids=();  
start slave;