You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 是怎么记录的 mysql> delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
当 binlog_format=statement 时,binlog 里面记录的就是 SQL 语句的原文。
mysql> show binlog events in 'master.000001'; 该命令查看 binlog 中的内容。
下图为statement 格式 binlog 示例
第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’;
第二行是一个 BEGIN,跟第四行的 commit 对应,表示中间是一个事务;
第三行就是真实执行的语句了。可以看到,在真实执行的 delete 命令之前,还有一个“use ‘test’”命令。这条命令不是我们主动执行的,而是 MySQL 根据当前要操作的表所在的数据库,自行添加的。这样做可以保证日志传到备库去执行的时候,不论当前的工作线程在哪个库里,都能够正确地更新到 test 库的表 t。use 'test’命令之后的 delete 语句,就是我们输入的 SQL 原文了。可以看到,binlog“忠实”地记录了 SQL 命令,甚至连注释也一并记录了。
最后一行是一个 COMMIT,XID 用于关联 redo log 和 bin log
用 mysqlbinlog 工具解析出来,然后把解析结果整个发给 MySQL 执行。类似下面的命令: mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;
因为有些语句的执行结果是依赖于上下文命令的,直接执行的结果很可能是错误的
双 M 结构循环复制问题
实际生产上使用比较多的是双 M 结构,下图即是双 M 结构的主备切换流程
双 M 结构会存在循环复制的问题,业务逻辑在节点 A 上更新了一条语句,然后再把生成的 binlog 发给节点 B,节点 B 执行完这条更新语句后也会生成 binlog。(我建议你把参数 log_slave_updates 设置为 on,表示备库执行 relay log 后生成 binlog)。那么,如果节点 A 同时是节点 B 的备库,相当于又把节点 B 新生成的 binlog 拿过来执行了一次,然后节点 A 和 B 间,会不断地循环执行这个更新语句,也就是循环复制了
可以用下面的逻辑,来解决两个节点间的循环复制的问题:
规定两个库的 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 主备的基本原理
下图展示的是主备切换的流程
![fd75a2b37ae6ca709b7f16fe060c2c10](https://user-images.githubusercontent.com/24617415/64580990-05bb0b00-d3bb-11e9-9ec6-49673ca86b0a.png)
在状态 1 中,客户端的读写都直接访问节点 A,而节点 B 是 A 的备库,只是将 A 的更新都同步过来,到本地执行。这样可以保持节点 B 和 A 的数据是相同的。当需要切换的时候,就切成状态 2。这时候客户端读写访问的都是节点 B,而节点 A 是 B 的备库
在状态 1 中,虽然节点 B 没有被直接访问,但是我依然建议你把节点 B(也就是备库)设置成只读(readonly)模式。这样做,有以下几个考虑:
因为 readonly 设置对超级 (super) 权限用户是无效的,而用于同步更新的线程,就拥有超级权限,所以备库的 readonly 也不会影响同步
下图是update 语句在节点 A 执行,然后同步到节点 B 的流程
![a66c154c1bc51e071dd2cc8c1d6ca6a3](https://user-images.githubusercontent.com/24617415/64581631-79f6ae00-d3bd-11e9-9e3a-67865a506929.png)
备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程,专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的:
binlog 的三种格式
binlog 有三种格式:statement、row 和 mixed ,其中 mixed 是前两种格式的混合
如果要在表中删除一行数据的话,我们来看看这个 delete 语句的 binlog 是怎么记录的
mysql> delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
当 binlog_format=statement 时,binlog 里面记录的就是 SQL 语句的原文。
mysql> show binlog events in 'master.000001'; 该命令查看 binlog 中的内容。
下图为statement 格式 binlog 示例
![b9818f73cd7d38a96ddcb75350b52931](https://user-images.githubusercontent.com/24617415/64587405-d6fc5f00-d3d1-11e9-8681-667b0281b9fd.png)
第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’;
第二行是一个 BEGIN,跟第四行的 commit 对应,表示中间是一个事务;
第三行就是真实执行的语句了。可以看到,在真实执行的 delete 命令之前,还有一个“use ‘test’”命令。这条命令不是我们主动执行的,而是 MySQL 根据当前要操作的表所在的数据库,自行添加的。这样做可以保证日志传到备库去执行的时候,不论当前的工作线程在哪个库里,都能够正确地更新到 test 库的表 t。use 'test’命令之后的 delete 语句,就是我们输入的 SQL 原文了。可以看到,binlog“忠实”地记录了 SQL 命令,甚至连注释也一并记录了。
最后一行是一个 COMMIT,XID 用于关联 redo log 和 bin log
运行的这条 delete 会产生一个 warning
原因是当前 binlog 设置的是 statement 格式,并且语句中有 limit。为什么这样呢?这是因为 delete 带 limit,很可能会出现主备数据不一致的情况:
把 binlog 的格式改为 binlog_format=‘row’ 就会解决这个问题,因为 row 格式的 binlog 里没有了 SQL 语句的原文,而是替换成了两个 event:Table_map 和 Delete_rows
![d67a38db154afff610ae3bb64e266826](https://user-images.githubusercontent.com/24617415/64588053-d8c72200-d3d3-11e9-9253-d46f80719ea0.png)
其实,我们通过上图是看不到详细信息的,还需要借助 mysqlbinlog 工具,用下面这个命令解析和查看 binlog 中的内容。因为上图中的信息显示,这个事务的 binlog 是从 8900 这个位置开始的,所以可以用 start-position 参数来指定从这个位置的日志开始解析
当 binlog_format 使用 row 格式的时候,binlog 里面记录了真实删除行的主键 id,这样 binlog 传到备库去的时候,就肯定会删除 id=4 的行,不会有主备删除不同行的问题
为什么会有 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 格式。
为什么越来越多的场景要求把 MySQL 的 binlog 格式设置成 row
显而易见的好处是恢复数据。
如果执行的是 delete 语句,row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以,如果你在执行完一条 delete 语句以后,发现删错数据了,可以直接把 binlog 中记录的 delete 语句转成 insert,把被错删的数据插入回去就可以恢复了
如果执行的是 insert 语句,row 格式下,insert 语句的 binlog 里会记录所有的字段信息,这些信息可以用来精确定位刚刚被插入的那一行。这时,你直接把 insert 语句转成 delete 语句,删除掉这被误插入的一行数据就可以了
如果执行的是 update 语句,row 格式的 binlog 里面会记录修改前整行的数据和修改后的整行数据。所以,如果你误执行了 update 语句的话,只需要把这个 event 前后的两行信息对调一下,再去数据库里面执行,就能恢复这个更新操作了
用 binlog 来恢复数据的标准做法
用 mysqlbinlog 工具解析出来,然后把解析结果整个发给 MySQL 执行。类似下面的命令:
mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;
因为有些语句的执行结果是依赖于上下文命令的,直接执行的结果很可能是错误的
双 M 结构循环复制问题
实际生产上使用比较多的是双 M 结构,下图即是双 M 结构的主备切换流程
![20ad4e163115198dc6cf372d5116c956](https://user-images.githubusercontent.com/24617415/64588976-8fc49d00-d3d6-11e9-81ef-0099c7558361.png)
双 M 结构会存在循环复制的问题,业务逻辑在节点 A 上更新了一条语句,然后再把生成的 binlog 发给节点 B,节点 B 执行完这条更新语句后也会生成 binlog。(我建议你把参数 log_slave_updates 设置为 on,表示备库执行 relay log 后生成 binlog)。那么,如果节点 A 同时是节点 B 的备库,相当于又把节点 B 新生成的 binlog 拿过来执行了一次,然后节点 A 和 B 间,会不断地循环执行这个更新语句,也就是循环复制了
可以用下面的逻辑,来解决两个节点间的循环复制的问题:
按照这个逻辑,如果我们设置了双 M 结构,日志的执行流就会变成这样:\
问题
总的来说就是 server_id 改变了,当 binlog 的 server_id 与节点的 server_id 都不同时就会出现
这种三节点复制的场景,做数据库迁移的时候会出现,如果出现循环复制,可以使用如下命令:
过段时间之后再更改回来:
The text was updated successfully, but these errors were encountered: