【MySQL】你所不知道的行复制(binlog_format=row)
⒈ 本文目的:很多MySQL DBA 自认为对 mysql 的行复制有了很深刻的理解(这行复制不是初级 DBA 就该掌握的内容嘛,你怎么提这么低级的问题),那么接下来可能会出乎你的意料。
网站制作、成都网站建设服务团队是一支充满着热情的团队,执着、敏锐、追求更好,是创新互联的标准与要求,同时竭诚为客户提供服务是我们的理念。创新互联公司把每个网站当做一个产品来开发,精雕细琢,追求一名工匠心中的细致,我们更用心!
⒉ 问题引出:
假设有A,B 两个数据库实例, A (主) ->B (从)
⑴ 第一问:
① 在主库上建立表结构如下(特别注意这里有唯一键,没主键):
CREATE TABLE `test` ( `a` int(11) DEFAULT NULL, `b` int(11) NOT NULL, `c` varchar(20) DEFAULT NULL, UNIQUE KEY `b_uique` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
② 我们在从库B 上插入一条记录:
insert into test values(2,2,'222');
③ 然后再主库A 上插入一条记录 :
insert into test values(3,2,'333');// 毫无疑问,从库会出现 1032 的错误,唯一键冲突
然后再从库上跳过一个事件( gtid 跳过一个事物); // 这样主从复制 A->B 就会正常
④ 接下,我们在主库A 上做一个操作:
update test set c='333ab' where b=2;
请回答 :B 库主从是出现 1032 的错误呢,还是主从复制正常,如果正常,那么 B 库 test 表的数据是什么?
⑵ 第二问:
① 在主库上建立表结构如下(特别注意这里有主键与唯一键):
CREATE TABLE `test1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
② 我们在从库B 上插入一条记录:
insert into test values(2,2,2,'222');
③ 然后再主库A 上插入一条记录 :
insert into test values(3,2,2,'333');// 毫无疑问,从库会出现 1032 的错误,唯一键冲突
然后再从库上跳过一个事件( gtid 跳过一个事物); // 这样主从复制 A->B 就会正常
④ 接下,我们在主库A 上做一个操作:
update test set c='333ab' where b=2;
请回答: B 库主从是出现 1032 的错误呢,还是主从复制正常,如果正常,那么 B 库 test 表的数据是什么?
⒊ 问题分析与结论:
先别急着看答案,我们来看( 主库 ) 解析 update 行复制 binlog 打印的是什么?
BEGIN /*!*/; # at 4255 #190228 20:59:13 server id 212493307 end_log_pos 4307 CRC32 0x902463fc Table_map: `bcd`.`test1` mapped to number 340 # at 4307 #190228 20:59:13 server id 212493307 end_log_pos 4382 CRC32 0x06b71e7b Update_rows: table id 340 flags: STMT_END_F ### UPDATE `bcd`.`test1` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2=3 /* INT meta=0 nullable=0 is_null=0 */ ### @3=3 /* INT meta=0 nullable=0 is_null=0 */ ### @4='33333' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */ ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2=3 /* INT meta=0 nullable=0 is_null=0 */ ### @3=3 /* INT meta=0 nullable=0 is_null=0 */ ### @4='3aaa33' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */ # at 4382 #190228 20:59:13 server id 212493307 end_log_pos 4413 CRC32 0x9b13368e Xid = 14175 COMMIT/*!*/; 没错,他记录了 原来行的内容,这里原来行是 (3,3,3,'33333') 然后被改成了 (3,3,3,'3aaa33');
那么这条binlog 被复制到从库后,就涉及到如何定位一行进行修改的问题了:
是通过(3,3,3,'33333' )所有元组定位一行吗?是通过主键唯一键 (3,3) 定位一行吗?是通过主键 (3) 定位一行吗?是通过唯一键 (3) 定位一行吗?
其实统统都不是,从库定位一行如下:
当表中有主键时,则通过主键定位一行
当表中同时有主键与唯一键时,则通过主键定位一行;主键不同,唯一键相同,代表的是不同行
当表只有唯一键是,则通过唯一键定位一行
系统自动生成的主键,不参与复制,不能定位一行
那么上面的问题,各位看官可以答出来了吗?
问题一:B 库不会出现 1032 的错误,最终从库 B 的数据是 (3,2,'333ab')
问题二:B 库会出现 1032 的错误,最终 B 库的数据是 (2,2,2,'222')
文章标题:【MySQL】你所不知道的行复制(binlog_format=row)
文章出自:http://scyanting.com/article/ppopgp.html