MySQL 線上開啟&關閉GTID模式
基本概述
我們知道MySQL有2種方式指定複製同步的方式,分別為:
- 基於binlog檔名及位點的指定方式
- 匿名事務(Anonymous_gtid_log_event)- 基於GTID(全域性事務ID)的指定方式
- GTID事務(Gtid_log_event)
而基於GTID的方式一方面在一主多從的架構下主從切換有著明顯優勢外,對於日常複製異常的故障診斷方面也更為方便,從MySQL 5.7.6之後便開始支援動態開啟和關閉GTID模式,其引數GTID_MODE有以下取值
- OFF - 只允許匿名事務被複制同步
- OFF_PERMISSIVE - 新產生的事務都是匿名事務,但也允許有GTID事務被複制同步
- ON_PERMISSIVE - 新產生的都是GTID事務,但也允許有匿名事務被複制同步
- ON - 只允許GTID事務被複制同步
線上開啟GTID
1. 設定GTID校驗ENFORCE_GTID_CONSISTENCY為WARN
該操作的目的是允許在主庫執行的SQL語句違反GTID一致性校驗,且只在主庫的錯誤日誌中輸出warning級別日誌以作提醒,目的是為了考慮如果複製方式改為GTID模式後應用程式不會因為GTID的一些限制導致異常報錯,
- 如:使用
CREATE TABLE AS SELECT語法在GTID模式下不支援,而ENFORCE_GTID_CONSISTENCY設定為WARN時,只會在錯誤日誌提示,不會直接報錯,
## 該操作在主從庫均執行
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
2. 設定GTID校驗ENFORCE_GTID_CONSISTENCY為ON
觀測一段時間(嚴謹的情況可能觀測一天以上),確認上一個步驟未在錯誤日誌中出現相關Warning資訊後,正式開啟GTID一致性校驗,當設定為ON後,如果再執行CREATE TABLE AS SELECT語句則會直接報錯
## 該操作在主從庫均執行
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
3. 設定GTID_MODE為OFF_PERMISSIVE
該操作是一個過渡屬性,表示新產生的事務依舊是匿名事務,但也允許有GTID事務被複制同步
## 該操作在主從庫均執行
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
4. 設定GTID_MODE為ON_PERMISSIVE
該操作依舊是一個過渡屬性,而其表示的則是新產生的都是GTID事務,但也允許有匿名事務被複制
## 該操作在主從庫均執行
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
5. (關鍵點)確保匿名事務回放完畢
該步驟的目的是確保在正式轉換為完整的GTID模式前,老的匿名事務均以被回放完畢,確保GTID_MODE設定為ON時,不會因為殘留的匿名事務導致複製同步報錯,有以下2種方式進行校驗
## 該操作僅在從庫執行即可
## 方式1:確保該狀態值輸出的匿名事務數顯示為0(注意:只要出現過0即可表示已經轉換完成,即使後續該狀態值從0變為了大於0的值也不影響)
SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
## 在從庫上多次執行該語句
## 方式2: 查詢該檢視中LAST_SEEN_TRANSACTION可以觀測當前同步的事務是否還存在ANONYMOUS事務
select * from performance_schema.replication_applier_status_by_worker;
確保匿名事務數為0
確保回放執行緒回放的事務都已是GTID事務
6. 觸發一輪日誌切換FLUSH LOGS
該操作的目的是為了在主庫觸發binlog的輪換,使新生成的binlog都是包含GTID的事務(防止一個binlog中包含2種型別的事務日誌)
## 該操作僅在主庫執行即可
FLUSH LOGS;
7. 正式開啟GTID_MODE為ON
正式開啟GTID
## 該操作在主從庫均執行
SET @@GLOBAL.GTID_MODE = ON;
SELECT @@GTID_MODE,@@ENFORCE_GTID_CONSISTENCY;
8. 修改配置檔案確保GTID引數持久化
在my.cnf配置檔案中增加GTID引數,確保重啟不會失效,該操作也可在第一步進行
## 該操作在主從庫均執行
gtid-mode = ON
enforce-gtid-consistency = 1
9. 修改複製模式為GTID方式
將複製模式從基於POS點改為基於GTID
## 停止複製
STOP SLAVE;
## 修改為GTID模式
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
## 開啟複製
START SLAVE;
## 觀測複製同步狀態
SHOW SLAVE STATUS\G
線上關閉GTID
方式類似於線上開啟GTID的逆向操作,以下只寫出步驟和具體命令,不做詳細解釋
- 先將GTID模式的複製改為基於POS點的複製
- 設定GTID_MODE為ON_PERMISSIVE
- 設定GTID_MODE為OFF_PERMISSIVE
- 觀測GTID_OWNED狀態變數變為空值及replication_applier_status_by_worker表中事務均轉為匿名事務
- 觸發FLUSH LOGS
- 設定GTID_MODE為OFF
- 設定ENFORCE_GTID_CONSISTENCY為OFF
- 修改my.cnf配置檔案中GTID相關引數為OFF
1. 將複製改為基於POS點方式
stop slave;
show slave status\G
## 取show slave status\G中的Master_Log_File和Exec_Master_Log_Pos填入
CHANGE MASTER TO
MASTER_AUTO_POSITION = 0,
MASTER_LOG_FILE='mysql-bin.000017',
MASTER_LOG_POS=224126137;
start slave;
show slave status\G
2. 設定GTID_MODE為ON_PERMISSIVE
## 該操作在主從庫均執行
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
3. 設定GTID_MODE為OFF_PERMISSIVE
## 該操作在主從庫均執行
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
4. (關鍵點)確保GTID事務回放完畢
觀測GTID_OWNED狀態變數變為空值及replication_applier_status_by_worker表中事務均轉為匿名事務
## 該操作在從庫執行即可
SELECT @@GLOBAL.GTID_OWNED;
select * from performance_schema.replication_applier_status_by_worker;
5. 觸發FLUSH LOGS
## 該操作在主庫執行即可
FLUSH LOGS;
6. 設定GTID_MODE為OFF
## 該操作在主從庫均執行
SET @@GLOBAL.GTID_MODE = OFF;
7. 設定ENFORCE_GTID_CONSISTENCY為OFF
## 該操作在主從庫均執行
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;
8. 修改my.cnf配置檔案中GTID相關引數為OFF
## 該操作在主從庫均執行
gtid-mode = OFF
enforce-gtid-consistency = 1
命令簡版
1. 線上開啟GTID
自行判斷命令在主庫還是從庫執行
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
select * from performance_schema.replication_applier_status_by_worker;
FLUSH LOGS;
SET @@GLOBAL.GTID_MODE = ON;
## 配置檔案修改
gtid-mode = ON
enforce-gtid-consistency = 1
## 將複製模式從基於POS點改為基於GTID
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;
SHOW SLAVE STATUS\G
2. 線上關閉GTID
自行判斷命令在主庫還是從庫執行
stop slave;
show slave status\G
## 取show slave status\G中的Master_Log_File和Exec_Master_Log_Pos填入
CHANGE MASTER TO
MASTER_AUTO_POSITION = 0,
MASTER_LOG_FILE='mysql-bin.000017',
MASTER_LOG_POS=224126137;
start slave;
show slave status\G
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SELECT @@GLOBAL.GTID_OWNED;
select * from performance_schema.replication_applier_status_by_worker;
FLUSH LOGS;
SET @@GLOBAL.GTID_MODE = OFF;
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;
## 修改my.cnf配置檔案中GTID相關引數為OFF
gtid-mode = OFF
enforce-gtid-consistency = 1