vlambda博客
学习文章列表

环境信息:

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 testCREATE 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 keyupdate测试，发现也会遇到上述的情况

问题解决:

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

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