replace into 導致MASTER/SLAVE的auto_increment值不同

準備工作

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

此時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