MySQL自增列主從不一致的測(cè)試(r12筆記第37天)

    MySQL里面有一個(gè)問題尤其值得注意，那就是自增列的重復(fù)值問題，之前也簡(jiǎn)單分析過一篇，但是在后續(xù)我想了下，還有很多地方需要解釋，一個(gè)就是從庫的自增列是如何維護(hù)的，是否重啟從庫，自增列會(huì)受到影響。

   我們繼續(xù)來測(cè)試一下。首先復(fù)現(xiàn)這個(gè)問題。

   創(chuàng)建表t1，插入3行數(shù)據(jù)。

use test;
[test]> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
> create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
insert into t1 values (1,2);
insert into t1 values (null,2);
insert into t1 values (null,2);
[test]> select *from t1;               
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
+----+------+因?yàn)榇嬖?行數(shù)據(jù)，這個(gè)時(shí)候自增列的值是4.

[test]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)我們刪除id值最大的記錄id=3

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.02 sec)這個(gè)時(shí)候會(huì)發(fā)現(xiàn)AUTO_INCREMENT=4的值不會(huì)有任何變化。

我們來挖掘一下binlog的內(nèi)容，就會(huì)發(fā)現(xiàn)insert語句很特別。

# /usr/local/mysql_5.7.17/bin/mysqlbinlog --socket=/home/data/s1/s1.sock --port=24801 -vv  /home/data/s1/binlog.000001可以看到insert語句是MySQL獨(dú)有的語法形式。
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
# at 2271

delete也會(huì)基于行級(jí)變更，定位到具體的記錄的方式來刪除。

### DELETE FROM `test`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
# at 2509
我們重啟一下數(shù)據(jù)庫。

# mysqladmin --socket=/home/data/s1/s1.sock --port=24801 shutdown                        
# /bin/sh /usr/local/mysql_5.7.17/bin/mysqld_safe --defaults-file=/home/data/s1/s1.cnf &重啟之后就會(huì)發(fā)現(xiàn)情況發(fā)生了變化，原來的自增值4現(xiàn)在變?yōu)榱?，這個(gè)也是基于max(id)+1的方式來計(jì)算的。

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)這個(gè)時(shí)候我們來關(guān)注一下從庫，從庫的自增列值會(huì)變化嗎？
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)這個(gè)時(shí)候就會(huì)發(fā)現(xiàn)重啟數(shù)據(jù)庫以后，主從的自增列的值不同了。
那么我們來進(jìn)一步測(cè)試，在主庫插入一條記錄，這樣自增列的值就是4.

mysql> insert into t1 values (null,2);
Query OK, 1 row affected (0.01 sec)自增列的值為4，而從庫的自增列的值依舊沒有任何變化。

繼續(xù)插入一條記錄，這個(gè)時(shí)候主庫的自增列就會(huì)是5

mysql> insert into t1 values (null,2);
Query OK, 1 row affected (0.00 sec)而從庫呢，這個(gè)時(shí)候自增列會(huì)持續(xù)發(fā)生變化嗎？我們來驗(yàn)證一下，這個(gè)時(shí)候從庫的自增列又開始生效了。

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
還有一點(diǎn)需要注意，那就是指定了自增列的值，這一點(diǎn)上和Oracle有一定的差距，但是又很相似。
這個(gè)時(shí)候數(shù)據(jù)庫主庫中的數(shù)據(jù)如下：

mysql> select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
|  4 |    2 |
|  5 |    2 |
+----+------+
5 rows in set (0.00 sec)為了方便測(cè)試，我們繼續(xù)插入一條數(shù)據(jù)，這一次我指定了id值。

mysql> insert into t1 values(6,2);
Query OK, 1 row affected (0.00 sec)讓人感到安慰的是，這張情況下自增列還是會(huì)持續(xù)增加。

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)此時(shí)查看從庫，這個(gè)自增列也還是7，,
通過這個(gè)案例，我們能夠看到在MySQL會(huì)存在這樣一類問題，實(shí)際上在多環(huán)境歷史數(shù)據(jù)歸檔的情況下，如果主庫重啟，很可能會(huì)出現(xiàn)數(shù)據(jù)不一致的情況。

  我也在MySQL的官方bug列表中看到很多人在討論這個(gè)問題，看來很多人碰到這個(gè)坑。而這個(gè)問題其實(shí)細(xì)究起來實(shí)現(xiàn)也不是一個(gè)很繁瑣的工作，為什么一直沒有修復(fù)。

   這個(gè)問題在MySQL很久以前就有，在現(xiàn)在依舊存在，什么時(shí)候會(huì)修復(fù)呢，根據(jù)官方的計(jì)劃會(huì)在8.0中修復(fù)。讓我們拭目以待。