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

    MySQL裡面有一個問題尤其值得注意，那就是自增列的重複值問題，之前也簡單分析過一篇，但是在後續我想了下，還有很多地方需要解釋，一個就是從庫的自增列是如何維護的，是否重啟從庫，自增列會受到影響。

   我們繼續來測試一下。首先復現這個問題。

   建立表t1，插入3行資料。

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 |
+----+------+因為存在3行資料，這個時候自增列的值是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)這個時候會發現AUTO_INCREMENT=4的值不會有任何變化。

我們來挖掘一下binlog的內容，就會發現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獨有的語法形式。
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
# at 2271

delete也會基於行級變更，定位到具體的記錄的方式來刪除。

### 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
我們重啟一下資料庫。

# 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 &重啟之後就會發現情況發生了變化，原來的自增值4現在變為了3，這個也是基於max(id)+1的方式來計算的。

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)這個時候我們來關注一下從庫，從庫的自增列值會變化嗎？
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)這個時候就會發現重啟資料庫以後，主從的自增列的值不同了。
那麼我們來進一步測試，在主庫插入一條記錄，這樣自增列的值就是4.

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

繼續插入一條記錄，這個時候主庫的自增列就會是5

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

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)
還有一點需要注意，那就是指定了自增列的值，這一點上和Oracle有一定的差距，但是又很相似。
這個時候資料庫主庫中的資料如下：

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

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

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)此時檢視從庫，這個自增列也還是7，,
透過這個案例，我們能夠看到在MySQL會存在這樣一類問題，實際上在多環境歷史資料歸檔的情況下，如果主庫重啟，很可能會出現資料不一致的情況。

  我也在MySQL的官方bug列表中看到很多人在討論這個問題，看來很多人碰到這個坑。而這個問題其實細究起來實現也不是一個很繁瑣的工作，為什麼一直沒有修復。

   這個問題在MySQL很久以前就有，在現在依舊存在，什麼時候會修復呢，根據官方的計劃會在8.0中修復。讓我們拭目以待。