如何理解MySQL中GTID和自增列的數據測試

158次閱讀

沒有評論

共計 10848 個字符，預計需要花費 28 分鐘才能閱讀完成。

今天就跟大家聊聊有關如何理解 MySQL 中 GTID 和自增列的數據測試，可能很多人都不太了解，為了讓大家更加了解，丸趣 TV 小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

昨天的一篇文章，今天有不少網友向我確認一些細節，我想最近正好在看 GTID 的東西，可以揉在一起來說說。

GTID 這個概念看似簡單，實際上還是有不少的門道。

我們來從架構的設計角度來看看存在哪些場景需要考慮 GTID 的變化。

一主兩從的架構模式下 GTID 的變化

我們就以一主兩從的架構為基準進行闡述。在這個架構模式下我們會用到 MHA 的方案。

如果這個時候 Master 節點宕機了，MHA 就會開啟檢查機制。

這個時候 Slave 1 節點就會變為新的 Master,Slave 2 會從 Slave 1 上重新應用數據變更，這個時候 GTID 是怎么變化的，從庫的 Executed GTID Set 到底是一個還是兩個。

這個場景繼續往下延伸。如果宕機的主庫啟動之后，假設是硬件問題，比如電源故障燈原因，Master 節點啟動了，那么 Master 節點的重新加入主從環境中 GTID 是如何變化的。這樣就是下面的架構圖了。

而我們把這個問題繼續細化，那就是和自增列值的問題結合起來。看看在這種場景下，MySQL 的實現方式是否會出現數據不一致，無法復制的情況。兩者結合起來算是一個相對完整的測試場景了。當然我要標記為第一篇，因為還會有第二篇出來。

我們看看如何操作。

一主兩從的架構模式下 GTID 的實踐

一主兩從我們標識為主（Master 節點），從庫 1（Slave 1）, 從庫 2（Slave 2）, 大體的測試步驟如下：

初始化一主兩從

Master 節點初始化數據，測試自增列值

配置 MHA,Master 節點宕機

MHA 切換，Slave 1 節點升為主庫，Slave 2 節點為從庫

Master 節點啟動

Master 節點加入主從復制環境

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

步驟 1 相對簡單，可以使用 sandbox 或者是快速腳本的方式搭建。

搭建完成后，先來看看 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 的情況如下：

Slave 1 節點的情況如下, 和 Master 節點的 server_uuid 明顯不同。這個信息可以在初始化的目錄 auto.cnf 可以得到。

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)我們來看看 show slave status 的結果。
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 節點，測試自增列問題

步驟 2 我們來初始化一下 Master 節點。就創建一個數據庫 test
create database test;Slave 1 節點的情況如下, 可以看到末尾的事務 ID 序號開始增加。
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 下面的初始化就是關鍵了，我們會測試自增列的情況，來復現一個經典問題。創建一個表 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 |
+—-+——+ 毫無疑問，這個時候自增列的值是 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 節點和 Slave 2 節點得到的數據情況是一致的，都是 4
然后我們做下面的變更，刪除表中 id= 3 的值。這個情況也很容易理解，那就是自增列不會變化。

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 節點和 Slave 2 節點也是如此，自增列值都是 4

步驟 3：配置 MHA,Master 節點宕機

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

[server default]
manager_workdir=/home/mha/manager
manager_log=/home/mha/manager/app1/manager.log
port=24801 - 指定的端口
user=mha_test
password=mha_test – 需要提前創建
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 主從復制的檢查。

# masterha_check_repl –conf=app1.cnf
檢查無誤后，我們啟動 MHA manager 服務。

nohup masterha_manager –conf=/home/mha/conf/app1.cnf /tmp/mha_manager.log 2 1
然后我們查到對應的進程號，直接 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 我們簡單描述一下，Master 節點殺掉后，主庫的表 t1 的自增列值如果啟動之后就會是 3，即上一次的 max(id)+ 1 開始計算。而從庫的自增列值為 4，這個該怎么平衡呢？

步驟 4：MHA 切換，Slave1 節點為主庫

整個切換的過程是自動完成的，MHA 會檢測心跳，然后自動開始切換主從復制關系。整個過程 GTID 就是一個需要注意的地方。
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 節點就正式接管環境。

查看新主庫 Slave 1 節點的信息如下, 這個 GTID 還是原來 Master 節點的。

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 的部分還是原來的設置。

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 節點就會重新調整復制關系，
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
這里可能會有些疑惑，而且對于 GTID 的理解會有一些誤差，我們在 Slave 1 節點上插入一行數據。
mysql insert into t1 values(null,2); 這個時候查看自增列的情況如下，會逐步遞增。

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 這個時候就需要重新查看下 Gtid 的情況了。可以看到這里有原來 Master 節點的 server_uuid, 也有當前新主庫的 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

新的從庫 Slave 2 節點的信息如下：

節點 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
。。。
所以可以發現 failover 以后的自增列值不會受到影響，而且 GTID set 會包含當前主庫和原來的主庫信息。

步驟 5：Master 節點啟動

啟動 Master 節點步驟相對簡單。

# /usr/local/mysql_5.7.17/bin/mysqld_safe –defaults-file=/home/data/s1/s1.cnf 啟動之后有很多細節需要確認，一個是關于 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 這個地方明顯不對，那是因為主從復制關系還沒有調整。
我們看看這個時候的自增列值情況。糾結的問題就是自增列之為 3，而 Slave 1 節點和 Slave 2 節點的自增列值為 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 節點加入主從復制環境

重新配置主從復制關系：

CHANGE MASTER TO MASTER_HOST= 10.127.128.78 , MASTER_PORT=24802,
MASTER_AUTO_POSITION=1, MASTER_USER= rpl_user ,
MASTER_PASSWORD= rpl_pass 啟動新的從庫, 啟動后會發現 GTID 會是兩個。

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
。。。這個時候再次查看自增列的情況。這個步驟看起來復雜一些，其實就是新的從庫會去接收應用在 Slave 1 節點上的數據變化，相當于在 Master 節點插入了一條記錄，導致這個自增列之繼續增加。

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 值恢復了
1 row in set (0.00 sec) 我們可以查看 binlog 的信息來進行基本的驗證。
[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*/; 這樣一來對于 GTID 的理解就會更加清晰一些。對于自增列的問題也會更加明確，確確實實目前能夠解決數據不一致的情況。

看完上述內容，你們對如何理解 MySQL 中 GTID 和自增列的數據測試有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注丸趣 TV 行業資訊頻道，感謝大家的支持。

正文完