有记录进行插入时,自增列产生的值就有可能与已有的记录主键冲突,导致出错。首先想办法解决问题,通过人工调大自增列的值,保证大于表内已有的主键即可,调整后,导数据正常
托里网站制作公司哪家好,找创新互联!从网页设计、网站建设、微信开发、APP开发、响应式网站建设等网站项目制作,到程序开发,运营维护。创新互联成立与2013年到现在10年的时间,我们拥有了丰富的建站经验和运维经验,来保证我们的工作的顺利进行。专注于网站建设就选创新互联。
问题发生的前置条件:
1.MySQL复制基于row模式
2.innodb表
3.表含有自增主键,并且含有唯一约束
4.load data infile 采用replace into语法插入数据【遇到重复唯一约束,直接覆盖】
问题发生的原理:
1.主库遇到重复unique约束时,进行replace操作;
2.replace在主库上面实际变化为delete+insert,但binlog记录的是update;
3.备库重做update动作,更新主键,但由于update动作不会更新自增列值,导致更新后记录值大于自增列值
问题重现实验:
准备工作 | Create table test_autoinc(id int auto_increment, c1 int,c2 varchar(100),primary key(id),unique key(c1)); insert into test_autoinc(c1,c2) values(1,'abc'); insert into test_autoinc(c1,c2) values(2,'abc'); insert into test_autoinc(c1,c2) values(3,'abcdd'); insert into test_autoinc(c1,c2) values(4,'abcdd'); insert into test_autoinc(c1,c2) values(5,'abcdd'); | |||
1 | 操作 | 备注 | Master | slave |
2 | 查看自增列值 Show create table test_autoinc\G | 插入5条记录后,自增列值变为6 | 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; | 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
|
3 | 查看表数据 | id | c1 | c2 ---+------+------ 1 | 1 | abc 2 | 2 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd | id | c1 | c2 ---+------+------ 1 | 1 | abc 2 | 2 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd | |
4 | 查看binlog位置 show master status\G | 记录当前binlog位点, 后续可以查看replace动作产生的binlog事件 | mysql-bin.000038 59242888 | |
5 | replace操作 replace into test_autoinc(c1,c2) values(2,'eeee'); | 影响两条记录,主库replace= delete+insert |
Query OK, 2 rows affected (0.00 sec) | |
6 | 查看表数据 | id | c1 | c2 ---+------+------- 1 | 1 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd 6 | 2 | eeee | id | c1 | c2 ---+------+------- 1 | 1 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd 6 | 2 | eeee | |
7 | 查看binlog事件 show binlog events in 'mysql-bin.000038' from 59242888; | 也可以通过mysqlbinlog工具分析日志,查询从库执行的update语句 | Pos | Event_type ---------+--------------- 59242888 | Query 59242957 | Table_map 59243013 |Update_rows_v1 59243072 | Xid | |
8 | 查看自增列值 Show create table test_autoinc\G; | 此时master的自增列为7,而slave的自增列为6,与表内最大值相同 | 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=InnoDBAUTO_INCREMENT=7 | 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=InnoDBAUTO_INCREMENT=6 |
9 | 手工调大自增主键 Show create table test_autoinc\G; | 手工调大自增id alter table test_autoinc auto_increment=12; | Show create table test_autoinc\G; | alter table test_autoinc auto_increment=12; Show create table test_autoinc\G; 发现master和slave的自增id一致 |