如何解决 MySQL 主从切换后自增列数值不一致问题？ - 文章 - 开发者社区

问题描述

客户反馈主从切换后，表的中记录的最大值比自增列的值要大，导致插入异常报错 "Duplicate entry 'xxxx' for key 'PRIMARY'"

问题复现

1.主库插入测试数据

mysql> drop test_autoinc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test_autoinc' at line 1
mysql> drop table test_autoinc;
Query OK, 0 rows affected (0.01 sec)

mysql>  Create table test_autoinc(id int auto_increment, c1 int,c2 varchar(100),primary key(id),unique key(c1));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_autoinc(c1,c2) values(1,'abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_autoinc(c1,c2) values(2,'abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_autoinc(c1,c2) values(3,'abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_autoinc(c1,c2) values(4,'abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_autoinc(c1,c2) values(5,'abc');
Query OK, 1 row affected (0.01 sec)


# 查看主库元数据信息和库中实际记录最大数据
mysql> show create table  test_autoinc;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                              |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_autoinc | CREATE TABLE `test_autoinc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_autoinc;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 |    1 | abc  |
|  2 |    2 | abc  |
|  3 |    3 | abc  |
|  4 |    4 | abc  |
|  5 |    5 | abc  |
+----+------+------+
5 rows in set (0.00 sec)

2. 执行 replace 操作

mysql> replace into test_autoinc(c1,c2) values(2,'eeee');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test_autoinc;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 |    1 | abc  |
|  3 |    3 | abc  |
|  4 |    4 | abc  |
|  5 |    5 | abc  |
|  6 |    2 | eeee |
+----+------+------+
5 rows in set (0.00 sec)

# 查看记录数据的最大值为6
# auto_increment记录的值是7

mysql> show create table test_autoinc;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                              |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_autoinc | CREATE TABLE `test_autoinc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


# 查看当前的server_id，用于确认后面已经切主成功，在从库执行。
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|  2766867625 |
+-------------+
1 row in set (0.00 sec)

3. 控制台执行主从切换

4. 切换后在主从查看自增列信息并复现问题

# server_id发生了变化，说明切到了从库
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|   743088797 |
+-------------+
1 row in set (0.00 sec)

# 查看建表语句最大值6，下一个插入自增会是6，会与表中最大数据6冲突。

mysql> show create table test_autoinc;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                              |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_autoinc | CREATE TABLE `test_autoinc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 查看表中数据，表中最大数据是6
mysql> select * from test_autoinc;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 |    1 | abc  |
|  3 |    3 | abc  |
|  4 |    4 | abc  |
|  5 |    5 | abc  |
|  6 |    2 | eeee |
+----+------+------+
5 rows in set (0.00 sec)

# 插入数据，报错主键冲突，复现问题
mysql> insert into test_autoinc(c1,c2) values(6,6);
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'


# 再次重试插入可以正常插入，因为自增主键失败不回滚，AUTO_INCREMENT 会自动变成7
mysql> insert into test_autoinc(c1,c2) values(6,6);
Query OK, 1 row affected (0.00 sec)


# 再次查看验证，可以看到AUTO_INCREMENT 在insert 操作后会变成8
mysql> show create table test_autoinc;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                              |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_autoinc | CREATE TABLE `test_autoinc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

解决方案

重启从库，在从库切换为主的时候重启。在 MySQL8.0 之前，自增主键 AUTO_INCREMENT 的值如果大于 max(primay key)+1，在 MySQL 重启后，会重置 AUTO_INCREMENT=max(primay key)+1，这样切换后的从库会重新获取 AUTO_INCREMENT 的值，保持主从一致，缺点是不符合云上的产品切换逻辑。
插入新数据后，自增值自动追平，主库 replace 后如果有 insert 语句，这样会自动追平的，缺点是需要业务重试机制。
升级到 8.0 版本可以解决问题，缺点是需要数据迁移
使用 insert ..... on duplicate key update 代替，缺点是在一些情况下有主从不一致的风险，具体见参考资料[1]

参考资料

【1】https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html 如果您有其他问题，欢迎您联系火山引擎技术支持服务