环境信息:
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:
![](https://img-blog.csdnimg.cn/049df12bffda42f38135212ac9689a5f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
从192.168.1.111:3306:
![](https://img-blog.csdnimg.cn/6a3d72b35c5f4c7ab7b35ed879ab7794.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
查看表t1的自增列使用,主从自增列使用都为4
show create table t1;
主192.168.1.110:3306:
![](https://img-blog.csdnimg.cn/c437384648834f5b8e5933e6364de137.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
从192.168.1.111:3306:
![](https://img-blog.csdnimg.cn/c03ddfb3f7e547168daf6145c8d5597e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
继续插入数据,采用replace into,在没有主键冲突的情况下,replace into 跟insert 一样直接插入一行数据(1 row affected)
replace into t1(bank_code,bank_name) values(4,10);
![](https://img-blog.csdnimg.cn/f226400011a74dd7b91bd2b136e85ea7.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
查看数据以及自增列的使用,主从一致
主192.168.1.110:3306:
![](https://img-blog.csdnimg.cn/e1f2ea11faa44a59b295e12dd1293955.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
从192.168.1.111:3306:
![](https://img-blog.csdnimg.cn/ce44c522698c49afbdefdf5405862f91.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
继续采用replace into插入,可以看到这次操作了2行数据( 2 rows affected replace ino ),这是因为发生唯一冲突的时候,采用先delete唯一值冲突的数据,再insert新数据的方式
replace into t1(bank_code,bank_name) values(1,11);
![](https://img-blog.csdnimg.cn/be6b7126b9374e5bb1e9080c68f36c33.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
MySQL官方文档也有说明
![](https://img-blog.csdnimg.cn/c08fda80dc374c05972f39b6317f8488.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
再一次查看表的数据以及自增列的使用,会发现主库的自增列加1变为6,但从库的自增列依然为5,没有变化
主192.168.1.110:3306:
![](https://img-blog.csdnimg.cn/d863a1d9c4e54ecebd57abd5fac2646f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
从192.168.1.111:3306:
![](https://img-blog.csdnimg.cn/74101e927bd24b2fb3a1431234fba7ca.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
为什么从库的自增列没有更新?
查看binlog的日志记录,可以发现在binlog里面冲突的操作数据 记录的是update的操作不是(delete+insert),而update操作是不更新表自增列的最大使用值
![](https://img-blog.csdnimg.cn/a37a594f4a604eaa9e75fd766e874da6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/acfd7c7c98db43c884c21a20f9bb9538.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
自增列AUTO_INCREMENT不同步会导致主从切换之后,插入数据出现主键冲突的问题
![](https://img-blog.csdnimg.cn/66176780215745d6b4e0786c93216f78.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
问题总结:
该问题在MySQL官方的Bug#73563有记录,执行replace into插入,遇到主键或者唯一键冲突,会进行delete再insert的操作,但binlog里面记录的是直接update的操作,在从库应用binlog时,由于update操作是不更新自增主键的值,这会导致从库自增列的使用最大值小于当前已有数据的最大值,在主从切换之后,导致主键或者唯一键冲突的情况 。
此外,使用insert … on duplcate key update测试,发现也会遇到上述的情况。
![](https://img-blog.csdnimg.cn/0791e97f55084c5db0fd71e2930aa8f0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mb54mb55qE56yU6K6w,size_20,color_FFFFFF,t_70,g_se,x_16)
问题解决:
1 升级到MySQL 8.0,在8.0版本,对于update自增列的操作,也会更新AUTO_INCREMENT值
2 数据库禁用replace into 以及insert … on duplicate key update,由应用层逻辑实现