MySQL在線開啟或禁用GTID模式

Posted on by

基本概述

我們知道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的逆向操作,以下隻寫出步驟和具體命令,不做詳細解釋

  1. 先將GTID模式的復制改為基於POS點的復制
  2. 設置GTID_MODE為ON_PERMISSIVE
  3. 設置GTID_MODE為OFF_PERMISSIVE
  4. 觀測GTID_OWNED狀態變量變為空值及replication_applier_status_by_worker表中事務均轉為匿名事務
  5. 觸發FLUSH LOGS
  6. 設置GTID_MODE為OFF
  7. 設置ENFORCE_GTID_CONSISTENCY為OFF
  8. 修改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

參考鏈接

https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online.html

https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html

https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-disable-gtids.html

以上所述是小編給大傢介紹的MySQL在線開啟或禁用GTID模式，希望對大傢有所幫助。在此也非常感謝大傢對WalkonNet網站的支持！

推薦閱讀：