有记录进行插入时，自增列产生的值就有可能与已有的记录主键冲突，导致出错。首先想办法解决问题，通过人工调大自增列的值，保证大于表内已有的主键即可，调整后，导数据正常

问题发生的前置条件：

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 tabletest_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=InnoDBAUTO_INCREMENT=6DEFAULT 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

手工调大自增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一致