如何理解MySQL中GTID和自增列的數(shù)據(jù)測(cè)試

今天就跟大家聊聊有關(guān)如何理解MySQL中GTID和自增列的數(shù)據(jù)測(cè)試，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

  昨天的一篇文章，今天有不少網(wǎng)友向我確認(rèn)一些細(xì)節(jié)，我想最近正好在看GTID的東西，可以揉在一起來(lái)說(shuō)說(shuō)。

   GTID這個(gè)概念看似簡(jiǎn)單，實(shí)際上還是有不少的門(mén)道。

我們來(lái)從架構(gòu)的設(shè)計(jì)角度來(lái)看看存在哪些場(chǎng)景需要考慮GTID的變化。  

一主兩從的架構(gòu)模式下GTID的變化

  我們就以一主兩從的架構(gòu)為基準(zhǔn)進(jìn)行闡述。在這個(gè)架構(gòu)模式下我們會(huì)用到MHA的方案。

如果這個(gè)時(shí)候Master節(jié)點(diǎn)宕機(jī)了，MHA就會(huì)開(kāi)啟檢查機(jī)制。

這個(gè)時(shí)候Slave 1節(jié)點(diǎn)就會(huì)變?yōu)樾碌腗aster,Slave 2會(huì)從Slave 1上重新應(yīng)用數(shù)據(jù)變更，這個(gè)時(shí)候GTID是怎么變化的，從庫(kù)的Executed GTID Set到底是一個(gè)還是兩個(gè)。

這個(gè)場(chǎng)景繼續(xù)往下延伸。如果宕機(jī)的主庫(kù)啟動(dòng)之后，假設(shè)是硬件問(wèn)題，比如電源故障燈原因，Master節(jié)點(diǎn)啟動(dòng)了，那么Master節(jié)點(diǎn)的重新加入主從環(huán)境中GTID是如何變化的。這樣就是下面的架構(gòu)圖了。

而我們把這個(gè)問(wèn)題繼續(xù)細(xì)化，那就是和自增列值的問(wèn)題結(jié)合起來(lái)?？纯丛谶@種場(chǎng)景下，MySQL的實(shí)現(xiàn)方式是否會(huì)出現(xiàn)數(shù)據(jù)不一致，無(wú)法復(fù)制的情況。兩者結(jié)合起來(lái)算是一個(gè)相對(duì)完整的測(cè)試場(chǎng)景了。當(dāng)然我要標(biāo)記為第一篇，因?yàn)檫€會(huì)有第二篇出來(lái)。

 我們看看如何操作。

一主兩從的架構(gòu)模式下GTID的實(shí)踐

一主兩從我們標(biāo)識(shí)為主（Master節(jié)點(diǎn)），從庫(kù)1（Slave 1）,從庫(kù)2 （Slave 2）,大體的測(cè)試步驟如下：

1. 初始化一主兩從

2. Master節(jié)點(diǎn)初始化數(shù)據(jù)，測(cè)試自增列值

3. 配置MHA,Master節(jié)點(diǎn)宕機(jī)

4. MHA切換，Slave 1節(jié)點(diǎn)升為主庫(kù)，Slave 2節(jié)點(diǎn)為從庫(kù)

5. Master節(jié)點(diǎn)啟動(dòng)

6. Master節(jié)點(diǎn)加入主從復(fù)制環(huán)境

步驟1：初始化，得到一主兩從的GTID情況

步驟1相對(duì)簡(jiǎn)單，可以使用sandbox或者是快速腳本的方式搭建。

搭建完成后，先來(lái)看看Gtid的情況。

mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000001
         Position: 1475
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-7
查看server_uuid的情況如下：

mysql> show global variables like 'server_uuid%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_uuid    | 4f7b0b93-2400-11e7-99cb-782bcb377193
+----------------+--------------------------------------+
3 rows in set (0.01 sec)我們后續(xù)的測(cè)試都會(huì)參考這個(gè)值。

Slave 1節(jié)點(diǎn)的情況如下,和Master節(jié)點(diǎn)的server_uuid明顯不同。這個(gè)信息可以在初始化的目錄auto.cnf可以得到。

mysql> show global variables like 'server%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 24802                                |
| server_id_bits | 32                                   |
| server_uuid    | 5433468e-2400-11e7-a834-782bcb377193
+----------------+--------------------------------------+查看master status的信息如下，這一點(diǎn)可以看出是和Master節(jié)點(diǎn)的Gtid Set值相同，證明這個(gè)Gtid的值是一個(gè)唯一性標(biāo)識(shí)，當(dāng)然從GTID的全稱就是全局事務(wù)標(biāo)識(shí)。

mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000001
         Position: 438
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-7
1 row in set (0.00 sec)我們來(lái)看看show slave status的結(jié)果。
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.127.128.78
                  Master_User: rpl_user
。。。           
           Retrieved_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:6-7
            Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-7
。。。
1 row in set (0.00 sec)

步驟2:初始化Master節(jié)點(diǎn)，測(cè)試自增列問(wèn)題

步驟2我們來(lái)初始化一下Master節(jié)點(diǎn)。就創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)test
create database test;Slave 1節(jié)點(diǎn)的情況如下,可以看到末尾的事務(wù)ID序號(hào)開(kāi)始增加。
mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000001
         Position: 589
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-8下面的初始化就是關(guān)鍵了，我們會(huì)測(cè)試自增列的情況，來(lái)復(fù)現(xiàn)一個(gè)經(jīng)典問(wèn)題。創(chuàng)建一個(gè)表t1，然后插入3條記錄。
mysql>  drop table if exists t1;
mysql> create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
mysql> insert into t1 values (1,2);
mysql> insert into t1 values (null,2);
mysql> insert into t1 values (null,2);
mysql> select *from t1;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
+----+------+毫無(wú)疑問(wèn)，這個(gè)時(shí)候自增列的值是4.

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在Slave 1節(jié)點(diǎn)和Slave 2節(jié)點(diǎn)得到的數(shù)據(jù)情況是一致的，都是4
然后我們做下面的變更，刪除表中id=3的值。這個(gè)情況也很容易理解，那就是自增列不會(huì)變化。

mysql> delete from t1 where id=3;
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
Slave 1節(jié)點(diǎn)和Slave 2節(jié)點(diǎn)也是如此，自增列值都是4

步驟3：配置MHA,Master節(jié)點(diǎn)宕機(jī)

這個(gè)步驟可以參考 sandbox和MHA快速測(cè)試(r12筆記第32天)，對(duì)MHA的配置有一個(gè)基本的介紹，可以使用如下的兩個(gè)腳本來(lái)做基本的檢驗(yàn)，app1.cnf就是基礎(chǔ)的配置文件。內(nèi)容大體如下：

[server default]
manager_workdir=/home/mha/manager
manager_log=/home/mha/manager/app1/manager.log
port=24801   -指定的端口
user=mha_test
password=mha_test  --需要提前創(chuàng)建
repl_user=rpl_user
repl_password=rpl_pass
master_ip_failover_script= /home/mha/conf/master_ip_failover2
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change

[server1]
hostname=10.127.128.78
port=24801
candidate_master=1

[server2]
hostname=10.127.128.78
candidate_master=1
port=24802

[server3]
hostname=10.127.128.78
candidate_master=1
port=24803ssh的互信檢查。

# masterha_check_ssh  --conf=app1.cnf主從復(fù)制的檢查。

# masterha_check_repl  --conf=app1.cnf  
檢查無(wú)誤后，我們啟動(dòng)MHA manager服務(wù)。

nohup masterha_manager --conf=/home/mha/conf/app1.cnf  > /tmp/mha_manager.log 2>&1 &
然后我們查到對(duì)應(yīng)的進(jìn)程號(hào)，直接Kill即可。

[root@grtest app1]# ps -ef|grep 24801
mysql     2168  1918  0 14:29 pts/7    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/home/data/s1/s1.cnf --basedir=/usr/local/mysql_5.7.17 --datadir=/home/data/s1 --plugin-dir=/usr/local/mysql_5.7.17/lib/plugin --user=mysql --log-error=/home/data/s1/grtest.err --pid-file=/home/data/s1/grtest.pid --socket=/home/data/s1/s1.sock --port=24801
root      3623 12108  0 14:40 pts/7    00:00:00 grep 24801
[root@grtest app1]# kill -9 1918 2168我們簡(jiǎn)單描述一下，Master節(jié)點(diǎn)殺掉后，主庫(kù)的表t1的自增列值如果啟動(dòng)之后就會(huì)是3，即上一次的max(id)+1開(kāi)始計(jì)算。而從庫(kù)的自增列值為4，這個(gè)該怎么平衡呢？

步驟4：MHA切換，Slave1節(jié)點(diǎn)為主庫(kù)

整個(gè)切換的過(guò)程是自動(dòng)完成的，MHA會(huì)檢測(cè)心跳，然后自動(dòng)開(kāi)始切換主從復(fù)制關(guān)系。整個(gè)過(guò)程GTID就是一個(gè)需要注意的地方。
Started automated(non-interactive) failover.
Invalidated master IP address on 10.127.128.78(10.127.128.78:24801)
Selected 10.127.128.78(10.127.128.78:24802) as a new master.
10.127.128.78(10.127.128.78:24802): OK: Applying all logs succeeded.
10.127.128.78(10.127.128.78:24802): OK: Activated master IP address.
10.127.128.78(10.127.128.78:24803): OK: Slave started, replicating from 10.127.128.78(10.127.128.78:24802)
10.127.128.78(10.127.128.78:24802): Resetting slave info succeeded.
Master failover to 10.127.128.78(10.127.128.78:24802) completed successfully.于是Slave 1節(jié)點(diǎn)就正式接管環(huán)境。

查看新主庫(kù)Slave 1節(jié)點(diǎn)的信息如下,這個(gè)GTID還是原來(lái)Master節(jié)點(diǎn)的。

mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000001
         Position: 1895
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14server_uuid的部分還是原來(lái)的設(shè)置。

mysql> show global variables like 'server%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 24802                                |
| server_id_bits | 32                                   |
| server_uuid    | 5433468e-2400-11e7-a834-782bcb377193 |
+----------------+--------------------------------------+這個(gè)地方需要關(guān)注，那就是查看自增列的情況，因?yàn)樵瓉?lái)是從庫(kù)，所以得到的最新值為4.

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在這種情況下Slave 2節(jié)點(diǎn)就會(huì)重新調(diào)整復(fù)制關(guān)系，
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.127.128.78
                  Master_User: rpl_user
                  Master_Port: 24802
。。。
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14
                Auto_Position: 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=4 DEFAULT CHARSET=latin1
這里可能會(huì)有些疑惑，而且對(duì)于GTID的理解會(huì)有一些誤差，我們?cè)赟lave 1節(jié)點(diǎn)上插入一行數(shù)據(jù)。
mysql> insert into t1 values(null,2);這個(gè)時(shí)候查看自增列的情況如下，會(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=5 DEFAULT CHARSET=latin1這個(gè)時(shí)候就需要重新查看下Gtid的情況了?？梢钥吹竭@里有原來(lái)Master節(jié)點(diǎn)的server_uuid,也有當(dāng)前新主庫(kù)的server_uuid值。

mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000001
         Position: 2133
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14,
5433468e-2400-11e7-a834-782bcb377193:1   

新的從庫(kù)Slave 2節(jié)點(diǎn)的信息如下：

節(jié)點(diǎn)3：
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.127.128.78
                  Master_User: rpl_user
                  Master_Port: 24802
      。。。
           Retrieved_Gtid_Set: 5433468e-2400-11e7-a834-782bcb377193:1
            Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14,
5433468e-2400-11e7-a834-782bcb377193:1   
                Auto_Position: 1
。。。
所以可以發(fā)現(xiàn)failover以后的自增列值不會(huì)受到影響，而且GTID set會(huì)包含當(dāng)前主庫(kù)和原來(lái)的主庫(kù)信息。

步驟5：Master節(jié)點(diǎn)啟動(dòng)

啟動(dòng)Master節(jié)點(diǎn)步驟相對(duì)簡(jiǎn)單。

#  /usr/local/mysql_5.7.17/bin/mysqld_safe --defaults-file=/home/data/s1/s1.cnf &啟動(dòng)之后有很多細(xì)節(jié)需要確認(rèn)，一個(gè)是關(guān)于master status的信息。

mysql> show slave status\G
Empty set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000002
         Position: 190
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14這個(gè)地方明顯不對(duì)，那是因?yàn)橹鲝膹?fù)制關(guān)系還沒(méi)有調(diào)整。
我們看看這個(gè)時(shí)候的自增列值情況。糾結(jié)的問(wèn)題就是自增列之為3，而Slave 1節(jié)點(diǎn)和Slave 2節(jié)點(diǎn)的自增列值為5.

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

步驟:6：Master節(jié)點(diǎn)加入主從復(fù)制環(huán)境

重新配置主從復(fù)制關(guān)系：

CHANGE MASTER TO MASTER_HOST='10.127.128.78', MASTER_PORT=24802, MASTER_AUTO_POSITION=1, MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass';啟動(dòng)新的從庫(kù),啟動(dòng)后會(huì)發(fā)現(xiàn)GTID會(huì)是兩個(gè)。

mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.127.128.78
                  Master_User: rpl_user
                  Master_Port: 24802
   。。。
           Retrieved_Gtid_Set: 5433468e-2400-11e7-a834-782bcb377193:1
            Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14,
5433468e-2400-11e7-a834-782bcb377193:1
                Auto_Position: 1
。。。這個(gè)時(shí)候再次查看自增列的情況。這個(gè)步驟看起來(lái)復(fù)雜一些，其實(shí)就是新的從庫(kù)會(huì)去接收應(yīng)用在Slave 1節(jié)點(diǎn)上的數(shù)據(jù)變化，相當(dāng)于在Master節(jié)點(diǎn)插入了一條記錄，導(dǎo)致這個(gè)自增列之繼續(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=5 DEFAULT CHARSET=latin1  --id值 恢復(fù)了
1 row in set (0.00 sec)我們可以查看binlog的信息來(lái)進(jìn)行基本的驗(yàn)證。
[root@grtest app1]# /usr/local/mysql_5.7.17/bin/mysqlbinlog -vv /home/data/s1/binlog.000002
...
BEGIN
/*!*/;
# at 310
#170418 14:44:01 server id 24802  end_log_pos 352       Table_map: `test`.`t1` mapped to number 219
# at 352
#170418 14:44:01 server id 24802  end_log_pos 392       Write_rows: table id 219 flags: STMT_END_F

BINLOG '
sbX1WBPiYAAAKgAAAGABAAAAANsAAAAAAAEABHRlc3QAAnQxAAIDAwAC
sbX1WB7iYAAAKAAAAIgBAAAAANsAAAAAAAEAAgAC//wEAAAAAgAAAA==
'/*!*/;
### INSERT INTO `test`.`t1`
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
# at 392
#170418 14:44:01 server id 24802  end_log_pos 419       Xid = 19
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;這樣一來(lái)對(duì)于GTID的理解就會(huì)更加清晰一些。對(duì)于自增列的問(wèn)題也會(huì)更加明確，確確實(shí)實(shí)目前能夠解決數(shù)據(jù)不一致的情況。

看完上述內(nèi)容，你們對(duì)如何理解MySQL中GTID和自增列的數(shù)據(jù)測(cè)試有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。