MySQL主从自增列AUTO_INCREMENT不同步

环境信息:

MySQL版本:5.7.32

架构：一主(192.168.1.110:3306)一从(192.168.1.111:3306)

binlog:on

binlog_row_image:full

binlog_format:row

gtid:on

问题:

        MySQL主从自增列AUTO_INCREMENT不同步

问题复现以及分析:

        创建测试表

use test
CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`bank_code` bigint(20) NOT NULL ,
`bank_name` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_bank_code` (`bank_code`)
) ENGINE=InnoDB

        插入数据

insert into t1 values(1,1,1),(2,2,2),(3,3,3);

        查看表t1数据

select * from t1;

主192.168.1.110:3306:

         从192.168.1.111:3306:

        查看表t1的自增列使用,主从自增列使用都为4

show create table t1;

        主192.168.1.110:3306:

        从192.168.1.111:3306: 

        继续插入数据，采用replace into，在没有主键冲突的情况下，replace into 跟insert 一样直接插入一行数据(1 row affected) 

replace into t1(bank_code,bank_name) values(4,10);

        查看数据以及自增列的使用,主从一致

      主192.168.1.110:3306:

        从192.168.1.111:3306:

继续采用replace into插入，可以看到这次操作了2行数据( 2 rows affected replace ino )，这是因为发生唯一冲突的时候，采用先delete唯一值冲突的数据，再insert新数据的方式

replace into t1(bank_code,bank_name) values(1,11);

        MySQL官方文档也有说明 

        再一次查看表的数据以及自增列的使用，会发现主库的自增列加1变为6，但从库的自增列依然为5，没有变化

        主192.168.1.110:3306:

        从192.168.1.111:3306:

        为什么从库的自增列没有更新？

       查看binlog的日志记录，可以发现在binlog里面冲突的操作数据 记录的是update的操作不是(delete+insert),而update操作是不更新表自增列的最大使用值

 

        自增列AUTO_INCREMENT不同步会导致主从切换之后，插入数据出现主键冲突的问题

 

 问题总结:

        该问题在MySQL官方的Bug#73563有记录，执行replace into插入，遇到主键或者唯一键冲突，会进行delete再insert的操作，但binlog里面记录的是直接update的操作，在从库应用binlog时，由于update操作是不更新自增主键的值，这会导致从库自增列的使用最大值小于当前已有数据的最大值，在主从切换之后，导致主键或者唯一键冲突的情况 。

        此外，使用insert … on duplcate key update测试，发现也会遇到上述的情况。

问题解决:

        1 升级到MySQL 8.0，在8.0版本，对于update自增列的操作，也会更新AUTO_INCREMENT值

        2 数据库禁用replace into 以及insert … on duplicate key update，由应用层逻辑实现