MySQL 8-0 主從複製原理分析與實戰

複製（Replication）

什麼是複製

官網：https://dev.mysql.com/doc/refman/8.0/en/replication.html

MySQL Replication 是官方提供的主從同步方案，也是用的最廣的同步方案。Replication(複製) 使來自一個 MySQL 數據庫服務器（稱爲源（Source））的數據能夠複製到一個或多個 MySQL 服務器（稱爲副本（Replica））。默認情況下，複製是異步的；副本不需要永久連接即可從源接收更新。根據配置，您可以複製所有數據庫、指定數據庫，甚至某個數據庫中的指定表。

說明：舊版本的 MySQL 複製將源（Source）稱爲主（Master），將副本（Replica）稱爲從（Slave）

複製的優勢:

高可用：通過配置一定的複製機制，MySQL 實現了跨主機的數據複製，從而獲得一定的高可用能力，如果需要獲得更高的可用性，只需要配置多個副本，或者進行級聯複製就可以達到目的。
性能擴展：由於複製機制提供了多個數據備份，可以通過配置一個或多個副本，將讀請求分發至副本節點，從而獲得整體上讀寫性能的提升。
異地災備：只需要將副本節點部署到異地機房，就可以輕鬆獲得一定的異地災備能力。實際當中，需要考慮網絡延遲等可能影響整體表現的因素。
交易分離：通過配置複製機制，並將低頻、大運算量的交易發送至副本節點執行，就可以避免這些交易與高頻交易競爭運算資源，從而避免整體的性能問題。

缺點：

沒有故障自動轉移，容易造成單點故障
主庫從庫之間有主從複製延遲問題，容易造成最終數據的不一致
從庫過多對主庫的負載以及網絡帶寬都會帶來很大的負擔

應用場景

電子商務平臺：在電商平臺中，主從複製可以用於實現讀寫分離，提高併發處理能力，同時確保數據的一致性。
社交網絡：在社交網絡應用中，可以利用主從複製來提供快速的讀取服務，同時將數據變更復制到從數據庫以備份數據。
實時監控和報警系統：在監控系統中，主從複製可以用於實現數據的分佈式存儲和快速數據查詢。
新聞和媒體網站：在高訪問量的新聞網站中，可以使用主從複製來提供高可用性和快速的內容訪問。
金融服務：在金融行業，數據的安全性和可用性至關重要，主從複製可以用於數據備份和高可用性的實現。

複製的方式

MySQL 8.0 支持多種複製方式：

傳統的方法是基於從源的二進制日誌（binlog）複製事件，並要求日誌文件及其中的位置在源和副本之間進行同步。作爲源 (數據庫更改發生的地方) 的 MySQL 實例將更新和更改作爲 “事件” 寫入二進制日誌。根據所記錄的數據庫更改，二進制日誌中的信息以不同的日誌格式存儲。副本配置爲從源中讀取二進制日誌，並在副本的本地數據庫上執行二進制日誌中的事件。

#獲取binlog文件列表
mysql> show binary logs;
#查看指定binlog文件的內容 
mysql> show binlog events in 'binlog.000003';

官網：https://dev.mysql.com/doc/refman/8.0/en/binlog-replication-configuration-overview.html

基於全局事務標識符 (GTID) 的方式。基於 GTID 的複製是完全基於事務的，所以很容易確定源和副本是否一致; 只要在源上提交的所有事務也在副本上提交，就可以保證兩者之間的一致性。

官網：https://dev.mysql.com/doc/refman/8.0/en/replication-gtids.html

複製的數據同步類型

MySQL 中的複製支持不同類型的同步。同步的原始類型是單向異步複製，其中一個服務器充當源，而一個或多個其他服務器充當副本。在 MySQL 8.0 中，除了內置的異步複製之外，還支持半同步複製。使用半同步複製，在返回執行事務的會話之前，對源執行提交，直到至少有一個副本確認它已經接收並記錄了事務的事件。MySQL 8.0 還支持延遲複製，以使副本故意落後於源至少指定的時間。

異步複製

默認情況下，MySQL 採用異步複製的方式，執行事務操作的線程不會等複製 Binlog 的線程。具體的時序你可以看下面這個圖：

MySQL 主庫在收到客戶端提交事務的請求之後，會先寫入 Binlog，然後再提交事務，更新存儲引擎中的數據，事務提交完成後，給客戶端返回操作成功的響應。同時，從庫會有一個專門的複製線程，從主庫接收 Binlog，然後把 Binlog 寫到一箇中繼日誌裏面，再給主庫返回複製成功的響應。從庫還有另外一個回放 Binlog 的線程，去讀中繼日誌，然後回放 Binlog 更新存儲引擎中的數據。

提交事務和複製這兩個流程在不同的線程中執行，互相不會等待，這是異步複製。異步複製的劣勢是，可能存在主從延遲，如果主節點宕機，可能會丟數據。

半同步複製

MySQL 從 5.7 版本開始，增加一種半同步複製（Semisynchronous Replication）的方式。這種機制與異步複製相比主要有如下區別：

主節點在收到客戶端的請求後，必須在完成本節點日誌寫入的同時，還需要等待至少一個從節點完成數據同步的響應之後（或超時），纔會響應請求。
從節點只有在寫入 relay-log 並完成刷盤之後，纔會向主節點響應。
當從節點響應超時時，主節點會將同步機制退化爲異步複製。在至少一個從節點恢復，並完成數據追趕後，主節點會將同步機制恢復爲半同步複製。

可以看出，相比於異步複製，半同步複製在一定程度上提高了數據的可用性，在未退化至異步複製時，如果主節點宕機，此時數據已複製至至少一臺從節點。同時，由於向客戶端響應時需要從節點完成響應，相比於異步複製，此時多出了主從節點上網絡交互的耗時以及從節點寫文件並刷盤的耗時，因此整體上集羣對於客戶端的響應性能表現必然有所降低。

半同步複製有兩個重要的參數：

rpl_semi_sync_master_wait_slave_count

（8.0.26 之後改爲 rpl_semi_sync_source_wait_for_replica_count）：至少等待數據複製到幾個從節點再返回。這個數量配置的越大，丟數據的風險越小，但是集羣的性能和可用性就越差。
rpl_semi_sync_master_wait_point

（8.0.26 之後改爲 rpl_semi_sync_source_wait_point）：這個參數控制主庫執行事務的線程，是在提交事務之前（AFTER_SYNC）等待複製，還是在提交事務之後（AFTER_COMMIT）等待複製。默認是 AFTER_SYNC，也就是先等待複製，再提交事務，這樣就不會丟數據。

設計理念：複製狀態機——幾乎所有的分佈式存儲都是這麼複製數據的

在 MySQL 中，無論是複製還是備份恢復，依賴的都是全量備份和 Binlog，全量備份相當於備份那一時刻的一個數據快照，Binlog 則記錄了每次數據更新的變化，也就是操作日誌。這種基於 “快照 + 操作日誌” 的方法，不是 MySQL 特有的。比如說，Redis Cluster 中，它的全量備份稱爲 Snapshot，操作日誌叫 backlog，它的主從複製方式幾乎和 MySQL 是一模一樣的。Elasticsearch 用的是 translog，它備份和恢復數據的原理和實現方式也是完全一樣的。

任何一個存儲系統，無論它存儲的是什麼數據，用什麼樣的數據結構，都可以抽象成一個狀態機。存儲系統中的數據稱爲狀態（也就是 MySQL 中的數據），狀態的全量備份稱爲快照（Snapshot），就像給數據拍個照片一樣。我們按照順序記錄更新存儲系統的每條操作命令，就是操作日誌（Commit Log，也就是 MySQL 中的 Binlog）。

複製數據的時候，只要基於一個快照，按照順序執行快照之後的所有操作日誌，就可以得到一個完全一樣的狀態。在從節點持續地從主節點上覆制操作日誌並執行，就可以讓從節點上的狀態數據和主節點保持同步。

主從同步做數據複製時，一般可以採用幾種複製策略。

性能最好的方法是異步複製，主節點上先記錄操作日誌，再更新狀態數據，然後異步把操作日誌複製到所有從節點上，並在從節點執行操作日誌，得到和主節點相同的狀態數據。異步複製的劣勢是，可能存在主從延遲，如果主節點宕機，可能會丟數據。
另外一種常用的策略是半同步複製，主節點等待操作日誌最少成功複製到 N 個從節點上之後，再更新狀態，這種方式在性能、高可用和數據可靠性幾個方面都比較平衡，很多分佈式存儲系統默認採用的都是這種方式。

基於 binlog 位點同步的主從複製原理

1、主庫會生成多個 binlog 日誌文件。

2、從庫的 I/O 線程請求指定文件和指定位置的 binlog 日誌文件（位點）。

3、主庫 dump 線程獲取指定位點的 binlog 日誌。

4、主庫按照從庫發送給來的位點信息讀取 binlog，然後推送 binlog 給從庫。

5、從庫將得到的 binlog 寫到本地的 relay log (中繼日誌) 文件中。

6、從庫的 SQL 線程讀取和解析 relay log 文件。

7、從庫的 SQL 線程重放 relay log 中的命令。

異步複製示例

1）快速創建 mysql 實例

參考：Docker 快速安裝 MySQL8.0

利用 Docker 快速搭建 Mysql8 一主兩從複製架構

uyVvSf

2 ) 配置 mysql 主從複製

主節點

2.1）創建掛載目錄

mkdir -p /mysql/replication/source/data /mysql/replication/source/conf /mysql/replication/source/log

2.2) 準備配置文件

vim /mysql/replication/source/conf/custom.cnf
[mysql]
# 設置mysql客戶端默認編碼
default-character-set=utf8
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 服務器唯一ID，默認是1
server-id=10
# 啓用二進制日誌
log-bin=mysql-bin
# 最大連接數 
max_connections=1000
# 設置默認時區
default-time_zone='+8:00'
# 0:區分大小寫
# 1:不區分大小寫
lower_case_table_names=1
!includedir /etc/mysql/conf.d/

pid-file: 這是 MySQL 服務器的進程 ID 文件的位置。通過這個文件，您可以在系統上找到正在運行的 MySQL 服務器的進程。
socket: 這是 MySQL 服務器用於本地通信的 Unix 套接字文件的位置。
datadir: 這是 MySQL 服務器存儲其數據文件的位置。
secure-file-priv: 這是一個用於限制 LOAD_FILE() 和 SELECT ... INTO OUTFILE 命令的文件路徑。如果此選項被設置，那麼這兩個命令只能用於讀取在這個路徑下的文件。設置爲 NULL 表示禁用這個功能。
symbolic-links: 如果設置爲 0，MySQL 服務器將不允許在數據目錄中使用符號鏈接。這有助於防止安全風險。
server-id: 每個 MySQL 服務器實例在複製時需要有一個唯一的 ID。這有助於區分不同的服務器，特別是在複製環境中。
log-bin: 啓用二進制日誌記錄所有對數據庫的更改，這對於複製和恢復操作是必要的。
max_connections: 這是 MySQL 服務器可以接受的最大併發連接數。
default-time_zone: 這設置了 MySQL 服務器的默認時區。
lower_case_table_names: 這決定了 MySQL 如何存儲和比較表名。設置爲 1 意味着表名不區分大小寫（但在文件系統中它們仍然會區分大小寫）。
!includedir /etc/mysql/conf.d/: 這告訴 MySQL 服務器從 / etc/mysql/conf.d / 目錄中包含其他配置文件。這意味着該目錄下的任何. cnf 或. ini 文件都會被合併到這個主配置文件中。
replicate_do_db ：待同步的數據庫日誌
replicate_ignore_db：不同步的數據庫日誌

2.3）運行 mysql 容器

# 創建主從複製的網絡
docker network create --driver bridge mysql-source-replica
#運行mysql容器
docker run  -d  \
--name mysql-source \
--privileged=true \
--restart=always \
--network  mysql-source-replica \
-p 3307:3306 \
-v /mysql/replication/source/data:/var/lib/mysql \
-v /mysql/replication/source/conf:/etc/mysql/conf.d  \
-v /mysql/replication/source/log:/logs \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai mysql:8.0.27 \
--lower_case_table_names=1

2.4）配置遠程訪問

docker exec -it mysql-source /bin/bash
mysql -u root -p
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

從節點 1

# 創建掛載目錄
mkdir -p /mysql/replication/replica1/data /mysql/replication/replica1/conf /mysql/replication/replica1/log
#準備配置文件
vim /mysql/replication/replica1/conf/custom.cnf
[mysql]
# 設置mysql客戶端默認編碼
default-character-set=utf8
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 服務器唯一ID，默認是1
server-id=11
# 啓用二進制日誌
log-bin=mysql-bin
# 最大連接數 
max_connections=1000
# 設置默認時區
default-time_zone='+8:00'
# 0:區分大小寫
# 1:不區分大小寫
lower_case_table_names=1
!includedir /etc/mysql/conf.d/
#運行mysql容器
docker run  -d  \
--name mysql-replica1 \
--privileged=true \
--restart=always \
--network  mysql-source-replica \
-p 3308:3306 \
-v /mysql/replication/replica1/data:/var/lib/mysql \
-v /mysql/replication/replica1/conf:/etc/mysql/conf.d  \
-v /mysql/replication/replica1/log:/logs \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai mysql:8.0.27 \
--lower_case_table_names=1  
#配置遠程訪問
docker exec -it mysql-replica1 /bin/bash
mysql -u root -p
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

從節點 2

mkdir -p /mysql/replication/replica2/data /mysql/replication/replica2/conf /mysql/replication/replica2/log
#準備配置文件
vim /mysql/replication/replica2/conf/custom.cnf
[mysql]
# 設置mysql客戶端默認編碼
default-character-set=utf8
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 服務器唯一ID，默認是1
server-id=12
# 啓用二進制日誌
log-bin=mysql-bin
# 最大連接數 
max_connections=1000
# 設置默認時區
default-time_zone='+8:00'
# 0:區分大小寫
# 1:不區分大小寫
lower_case_table_names=1
!includedir /etc/mysql/conf.d/
#運行mysql容器
docker run  -d  \
--name mysql-replica2 \
--privileged=true \
--restart=always \
--network  mysql-source-replica \
-p 3309:3306 \
-v /mysql/replication/replica2/data:/var/lib/mysql \
-v /mysql/replication/replica2/conf:/etc/mysql/conf.d  \
-v /mysql/replication/replica2/log:/logs \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai mysql:8.0.27 \
--lower_case_table_names=1 
#配置遠程訪問
docker exec -it mysql-replica2 /bin/bash
mysql -u root -p
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

3）主庫配置複製用戶

每個副本使用一個 MySQL 用戶名和密碼連接到源，因此在源上必須有一個用戶帳戶，副本可以使用該帳戶進行連接。

# 連接主庫mysql-source
CREATE USER 'fox'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'fox'@'%';
flush privileges;

4）查看 master 機器的狀態

使用 SHOW MASTER STATUS 語句確定當前二進制日誌文件的名稱和位置

# 主庫上執行
SHOW MASTER STATUS;

“文件” 列顯示日誌文件的名稱，“位置” 列顯示文件內的位置。

從節點設置主庫信息

文檔：https://dev.mysql.com/doc/refman/8.0/en/replication-howto-slaveinit.html

在從庫上執行 CHANGE REPLICATION SOURCE TO 語句 (來自 MySQL 8.0.23) 或 CHANGE MASTER TO 語句 (在 MySQL 8.0.23 之前)

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='source_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;
Or from MySQL 8.0.23:
mysql> CHANGE REPLICATION SOURCE TO
    ->     SOURCE_HOST='source_host_name',
    ->     SOURCE_USER='replication_user_name',
    ->     SOURCE_PASSWORD='replication_password',
    ->     SOURCE_LOG_FILE='recorded_log_file_name',
    ->     SOURCE_LOG_POS=recorded_log_position;

從庫 1 和從庫 2 上執行

# from MySQL 8.0.23 執行下面的命令。   
change replication source to source_host='192.168.65.185', source_user='fox', source_password='123456', source_port=3307, source_log_file='mysql-bin.000003', source_log_pos=1273, source_connect_retry=30;

source_host：主數據庫的 IP 地址；
source_port：主數據庫的運行端口；
source_user：在主數據庫創建的用於同步數據的用戶賬號；
source_password：在主數據庫創建的用於同步數據的用戶密碼；
source_log_file：指定從數據庫要複製數據的日誌文件，通過查看主數據的狀態，獲取 File 參數；
source_log_pos：指定從數據庫從哪個位置開始複製數據，通過查看主數據的狀態，獲取 Position 參數；
source_connect_retry：連接失敗重試的時間間隔，單位爲秒。

6）開啓從庫

#開啓從庫
start slave;  或者  start replica;
#查看從庫狀態
show slave status \G;  或者 show replica status \G;

看到 Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates 基本說明配置成功了，已經開始了主從複製。

7）測試主從複製功能

測試腳本

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
CREATE DATABASE IF NOT EXISTS test;
USE test; 
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `last_updated` bigint(0) NULL DEFAULT NULL,
  `is_deleted` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (2, '張三', '廣州白雲山', 1691563465, 0);
SET FOREIGN_KEY_CHECKS = 1;

半同步複製示例

文檔：https://dev.mysql.com/doc/refman/8.0/en/replication-semisync-installation.html

1）安裝半同步插件

主節點

#from MySQL 8.0.26:
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
#驗證是否成功安裝
SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE '%semi%';

從節點

#from MySQL 8.0.26:
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
#驗證是否成功安裝
SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE '%semi%';

2）開啓半同步功能

主節點

SET GLOBAL rpl_semi_sync_source_enabled=1;
#查看是否開啓
show variables like "%semi_sync%";

從節點

set global rpl_semi_sync_replica_enabled=1;

重啓從節點上的 I/O 線程

如果在運行時啓用副本上的半同步複製，您還必須啓動複製 I/O(接收端) 線程 (如果已經運行，則先停止它)，以使副本連接到源端並註冊爲半同步副本。

STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

半同步複製測試

測試：當從節點響應超時時，主節點會將同步機制退化爲異步複製。從節點恢復後，同步機制是否會恢復爲半同步複製

# 修改主節點半同步屬性
set global rpl_semi_sync_source_wait_for_replica_count=2;
set global rpl_semi_sync_source_timeout=100000;
#停掉從節點2
docker stop mysql-replica2
#恢復從節點2
docker start mysql-replica2
#從節點2中執行
set global rpl_semi_sync_replica_enabled=1;
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

基於 binlog 位點主從複製痛點分析

痛點 1：首次開啓主從複製的步驟複雜

第一次開啓主從同步時，要求從庫和主庫是一致的。
找到主庫的 binlog 位點。
設置從庫的 binlog 位點。
開啓從庫的複製線程。

痛點 2：恢復主從複製的步驟複雜

找到從庫複製線程停止時的位點。
解決複製異常的事務。無法解決時就需要手動跳過指定類型的錯誤，比如通過設置 slave_skip_errors=1032,1062。當然這個前提條件是跳過這類錯誤是無損的。（1062 錯誤是插入數據時唯一鍵衝突；1032 錯誤是刪除數據時找不到行）

不論是首次開啓同步時需要找位點和設置位點，還是恢復主從複製時，設置位點和忽略錯誤，這些步驟都顯得過於複雜，而且容易出錯。所以 MySQL 5.6 版本引入了 GTID，徹底解決了這個困難。

基於全局事務標識符（GTID）複製

官網：https://dev.mysql.com/doc/refman/8.0/en/replication-gtids.html

GTID 是一個基於原始 mysql 服務器生成的一個已經被成功執行的全局事務 ID，它由服務器 ID 以及事務 ID 組合而成。這個全局事務 ID 不僅僅在原始服務器器上唯一，在所有存在主從關係的 mysql 服務器上也是唯一的。正是因爲這樣一個特性使得 mysql 的主從複製變得更加簡單，以及數據庫一致性更可靠。

一個 GTID 在一個服務器上只執行一次，避免重複執行導致數據混亂或者主從不一致。
GTID 用來代替傳統複製方法，不再使用 MASTER_LOG_FILE+MASTER_LOG_POS 開啓複製。而是使用 MASTER_AUTO_POSTION=1 的方式開始複製。
在傳統的 replica 端，binlog 是不用開啓的，但是在 GTID 中 replica 端的 binlog 是必須開啓的，目的是記錄執行過的 GTID（強制）。

GTID 的優勢

更簡單的實現 failover，不用以前那樣在需要找位點（log_file 和 log_pos）。
更簡單的搭建主從複製。
比傳統的複製更加安全。
GTID 是連續的沒有空洞的，保證數據的一致性，零丟失。

GTID 結構

GTID 表示爲一對座標，由冒號 (:) 分隔，如下所示:

GTID = source_id:transaction_id

source_id 標識 source 服務器，即源服務器唯一的 server_uuid

，由於 GTID 會傳遞到 replica，所以也可以理解爲源 ID。
transaction_id 是一個序列號，由事務在源上提交的順序決定。序列號的上限是有符號 64 位整數（2^63-1）

例如，最初要在 UUID 爲 3E11FA47-71CA-11E1-9E33-C80AA9429562 的服務器上提交的第 23 個事務具有此 GTID

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

GTID 集合是由一個或多個 GTID 或 GTID 範圍組成的集合。來自同一服務器的一系列 gtid 可以摺疊成單個表達式，如下所示:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

源自同一服務器的多個單一 gtid 或 gtid 範圍也可以包含在單個表達式中，gtid 或範圍以冒號分隔，如下例所示:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49

GTID 集合可以包括單個 GTID 和 GTID 範圍的任意組合，也可以包括來自不同服務器的 GTID。

2174B383-5441-11E8-B90A-C80AA9429562:1-3, 24DA167-0C0C-11E8-8442-00059A3C7B00:1-19

GTID 存儲在 mysql 數據庫中名爲 gtid_executed 的表中。該表中的一行包含它所代表的每個 GTID 或 GTID 集合的起始服務器的 UUID，以及該集合的開始和結束事務 id。

GTID 工作原理

主庫計算主庫 GTID 集合和從庫 GTID 的集合的差集，主庫推送差集 binlog 給從庫。

當從庫設置完同步參數後，主庫 A 的 GTID 集合記爲集合 x，從庫 B 的 GTID 集合記爲 y。從庫同步的邏輯如下：

從庫 B 指定主庫 A，基於主備協議建立連接。
從庫 B 把集合 y 發給主庫 A。
主庫 A 計算出集合 x 和集合 y 的差集，也就是集合 x 中存在，集合 y 中不存在的 GTID 集合。比如集合 x 是 1~100，集合 y 是 1~90，那麼這個差集就是 91~100。這裏會判斷集合 x 是不是包含有集合 y 的所有 GTID，如果不是則說明主庫 A 刪除了從庫 B 需要的 binlog，主庫 A 直接返回錯誤。
主庫 A 從自己的 binlog 文件裏面，找到第一個不在集合 y 中的事務 GTID，也就是找到了 91。
主庫 A 從 GTID = 91 的事務開始，往後讀 binlog 文件，按順序取 binlog，然後發給 B。
從庫 B 的 I/O 線程讀取 binlog 文件生成 relay log，SQL 線程解析 relay log，然後執行 SQL 語句。

GTID 同步方案和位點同步的方案區別是：

位點同步方案是通過人工在從庫上指定哪個位點

，主庫就發哪個位點，不做日誌的完整性判斷。
而 GTID 方案是通過主庫來自動計算位點的

，不需要人工去設置位點，對運維人員友好。

GTID 的配置

1）修改主庫配置

修改主庫的配置文件

#GTID:
#啓用全局事務標識符（GTID）模式    
gtid_mode=on    
# 強制GTID的一致性。這意味着在執行事務時，MySQL將確保所有涉及的服務器都使用相同的GTID集。
enforce_gtid_consistency=on

2）修改從庫配置

修改從庫配置文件

#GTID:
gtid_mode=on  
enforce_gtid_consistency=on

從節點設置主庫信息

# 從庫配置同步參數
mysql> CHANGE MASTER TO
     >     MASTER_HOST = host,
     >     MASTER_PORT = port,
     >     MASTER_USER = user,
     >     MASTER_PASSWORD = password,
     >     MASTER_AUTO_POSITION = 1;  
Or from MySQL 8.0.23:
mysql> CHANGE REPLICATION SOURCE TO
     >     SOURCE_HOST = host,
     >     SOURCE_PORT = port,
     >     SOURCE_USER = user,
     >     SOURCE_PASSWORD = password,
     >     SOURCE_AUTO_POSITION = 1;

SOURCE_AUTO_POSITION = 1：這告訴從服務器使用自動位置跟蹤功能，以便它可以自動從主服務器獲取最新的二進制日誌事件，而無需手動指定位置。

基於 GTID 主從複製示例

文檔：https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-howto.html

在前面基於 binlog 日誌主從複製的 mysql 服務上配置 GTID 複製。

同步所有的 mysql 服務器

在主從服務器上都執行下面的命令：

# 設置MySQL服務器的全局只讀模式
mysql> SET @@GLOBAL.read_only = ON;

注意：只有在使用已經在進行復制而不使用 gtid 的服務器時才需要此步驟。對於新服務器，請繼續執行步驟 3。

停止所有的服務器

docker stop mysql-source mysql-replica1 mysql-replica2

主從節點都啓用 GTID

修改 custom.cnf

# 啓用GTID
gtid_mode=ON
enforce-gtid-consistency=ON

啓動主從節點

docker start mysql-source mysql-replica1 mysql-replica2

4）從節點配置基於 GTID 的自動定位

進入從節點，執行下面命令：

mysql> stop replica;
mysql> change replication source to source_host='192.168.65.185', source_user='fox', source_password='123456', source_port=3307,source_auto_position=1;

開啓從庫複製，並禁用只讀模式

# 開啓從庫複製
mysql> start replica;
# 只有在步驟1中將服務器配置爲只讀時，才需要執行以下步驟。要允許服務器再次開始接受更新
mysql> SET @@GLOBAL.read_only = OFF;

查看從庫狀態是否正常

mysql> show replica status \G

主從切換演練

場景 1：模擬主庫 down 機、從庫 1 數據同步完成、從庫 2 數據未同步完成

1）從庫 2 停止複製

mysql> stop replica;

2）主庫創建測試數據

INSERT INTO `test`.`user` VALUES (12, 'fox', NULL, NULL, NULL);

查詢數據

從庫 1

從庫 2

很顯然，從庫 1 同步了最新數據，比從庫 2 數據新

場景 2：將主庫宕機，從庫 1 升級爲主庫、從庫 2 切換主庫爲從庫 1（新的主庫），觀察從庫 2 是否同步未完成的事務

1）停止主庫

docker stop mysql-source

2）設置新主庫

設置 replica1 爲 replica2 的主庫，因爲 replica1 的數據是完整的。

按照普通複製方法，需要計算主庫的 log_pos 和從庫設置成主庫的 log_pos，可能出現錯誤

因爲同一事務的 GTID 在所有節點上的值一致，那麼根據 replica2 當前停止點的 GTID 就能定位到要主庫的 GTID，所以直接在 replica2 上執行 change 即可

# replica1上創建複製用戶
CREATE USER 'fox'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'fox'@'%';
flush privileges;
# replica2上執行 從replica1查詢
mysql> stop replica;
mysql> change replication source to source_host='192.168.65.185',source_port=3308,source_user='fox',source_password='123456',source_auto_position=1;
 mysql> start replica;

查詢同步結果

場景 3：模擬從庫刪除測試表，主庫對錶進行插入操作。觀察從庫複製是否報錯

1）replica1 刪除 test.user 表，主庫插入新記錄

# 從庫1 刪除user表
drop table test.user;
# 主庫插入新記錄
INSERT INTO `test`.`user` VALUES (14, 'AAA', NULL, NULL, NULL);

查看從庫同步情況

# 從庫1執行
mysql> show replica status\G

報錯信息：事務 aac92b21-b6a4-11ee-bab5-0242ac120002:6 執行失敗

Coordinator stopped because there were error(s) in the worker(s).The most recent failure being: Worker 1 failed executing transaction 'aac92b21-b6a4-11ee-bab5-0242ac120002:6' at master log mysql-bin.000008, end_log_pos 1335.See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

在主庫繼續進行其他事務，觀察 gitd 是否複製成功

# 主庫插入新記錄
INSERT INTO `test`.`user` VALUES (15, 'BBB', NULL, NULL, NULL);

從庫狀態

可以看出從庫複製中斷（注意：刪除了表，無法插入）

4）複製中斷修復：採用從庫跳過錯誤事務修復

因爲從庫 user 表已經刪了（user 表中部分數據不是利用 gtid 複製過去的），先從主庫將表數據拷貝到從庫

獲取從庫最新狀態

從庫執行

# 1.停止從庫1複製進程
mysql> stop replica;
# 2.設置事務號，事務號從 Retrieved_Gtid_Set 獲取，在session裏設置gtid_next，即跳過這個GTID
# 注意，選擇跳過出現錯誤的事務
mysql> SET @@SESSION.GTID_NEXT= 'aac92b21-b6a4-11ee-bab5-0242ac120002:7';
# 3.設置空事物
mysql> BEGIN; COMMIT;
# 4.恢復自增事物號
mysql> SET SESSION GTID_NEXT = AUTOMATIC;
# 5.啓動從庫1複製進程
mysql> start replica;
# 再次查詢會發現主庫數據已經同步過來了
mysql> select * from test.user;

如果需要一次跳過多條，找出需要跳過的 gtid，批量執行：

stop replica; 
SET @@SESSION.GTID_NEXT= 'd58e6bad-ef3e-11ee-8285-0242ac120003:4';begin;commit;
SET @@SESSION.GTID_NEXT= 'd58e6bad-ef3e-11ee-8285-0242ac120003:5';begin;commit;
SET @@SESSION.GTID_NEXT= 'd58e6bad-ef3e-11ee-8285-0242ac120003:6';begin;commit;
SET SESSION GTID_NEXT = AUTOMATIC;
start replica;

完整的演示步驟：

從庫刪除 user 表

mysql> drop table user;

主庫連續刪除兩條數據

mysql> select * from user;
+----+--------+-----------------+--------------+------------+
| id | name   | address         | last_updated | is_deleted |
+----+--------+-----------------+--------------+------------+
|  2 | 張三   | 廣州白雲山      |   1691563465 |          0 |
|  3 | fox    | NULL            |         NULL |       NULL |
|  4 | 李四   | NULL            |         NULL |       NULL |
|  5 | 111    | NULL            |         NULL |       NULL |
| 12 | fox    | NULL            |         NULL |       NULL |
| 14 | AAA    | NULL            |         NULL |       NULL |
| 15 | BBB    | NULL            |         NULL |       NULL |
| 16 | CCC    | NULL            |         NULL |       NULL |
| 20 | DDD    | NULL            |         NULL |       NULL |
| 22 | EEE    | NULL            |         NULL |       NULL |
+----+--------+-----------------+--------------+------------+
10 rows in set (0.00 sec)
mysql> delete from user where id=20;
Query OK, 1 row affected (0.19 sec)
mysql> select * from user;
+----+--------+-----------------+--------------+------------+
| id | name   | address         | last_updated | is_deleted |
+----+--------+-----------------+--------------+------------+
|  2 | 張三   | 廣州白雲山      |   1691563465 |          0 |
|  3 | fox    | NULL            |         NULL |       NULL |
|  4 | 李四   | NULL            |         NULL |       NULL |
|  5 | 111    | NULL            |         NULL |       NULL |
| 12 | fox    | NULL            |         NULL |       NULL |
| 14 | AAA    | NULL            |         NULL |       NULL |
| 15 | BBB    | NULL            |         NULL |       NULL |
| 16 | CCC    | NULL            |         NULL |       NULL |
| 22 | EEE    | NULL            |         NULL |       NULL |
| 23 | 333    | NULL            |         NULL |       NULL |
+----+--------+-----------------+--------------+------------+
10 rows in set (0.00 sec)
mysql> delete from user where id=22;
Query OK, 1 row affected (0.29 sec)
mysql> select * from user;
+----+--------+-----------------+--------------+------------+
| id | name   | address         | last_updated | is_deleted |
+----+--------+-----------------+--------------+------------+
|  2 | 張三   | 廣州白雲山      |   1691563465 |          0 |
|  3 | fox    | NULL            |         NULL |       NULL |
|  4 | 李四   | NULL            |         NULL |       NULL |
|  5 | 111    | NULL            |         NULL |       NULL |
| 12 | fox    | NULL            |         NULL |       NULL |
| 14 | AAA    | NULL            |         NULL |       NULL |
| 15 | BBB    | NULL            |         NULL |       NULL |
| 16 | CCC    | NULL            |         NULL |       NULL |
| 23 | 333    | NULL            |         NULL |       NULL |
+----+--------+-----------------+--------------+------------+
9 rows in set (0.00 sec)

藉助 Navicat 將主庫 user 表複製到從庫，包括數據。這樣一來從庫就有了當前的全量數據，只需要跳過錯誤的 gtid，從最新的 gtid 開始執行就可以了。

# 查詢從庫，確定數據是否和主庫一致
mysql> select * from user;
+----+--------+-----------------+--------------+------------+
| id | name   | address         | last_updated | is_deleted |
+----+--------+-----------------+--------------+------------+
|  2 | 張三   | 廣州白雲山      |   1691563465 |          0 |
|  3 | fox    | NULL            |         NULL |       NULL |
|  4 | 李四   | NULL            |         NULL |       NULL |
|  5 | 111    | NULL            |         NULL |       NULL |
| 12 | fox    | NULL            |         NULL |       NULL |
| 14 | AAA    | NULL            |         NULL |       NULL |
| 15 | BBB    | NULL            |         NULL |       NULL |
| 16 | CCC    | NULL            |         NULL |       NULL |
| 23 | 333    | NULL            |         NULL |       NULL |
+----+--------+-----------------+--------------+------------+
9 rows in set (0.00 sec)

獲取從庫需要配置跳過錯誤的事務

# 獲取到錯誤的gtid爲d58e6bad-ef3e-11ee-8285-0242ac120003:12,
# 因爲連續刪除過兩條數據，所以d58e6bad-ef3e-11ee-8285-0242ac120003:13也是錯誤的事務
mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.65.223
                  Source_User: fox
                  Source_Port: 3308
                Connect_Retry: 30
              Source_Log_File: mysql-bin.000004
          Read_Source_Log_Pos: 4229
               Relay_Log_File: 3c946c781110-relay-bin.000012
                Relay_Log_Pos: 790
        Relay_Source_Log_File: mysql-bin.000004
           Replica_IO_Running: Yes
          Replica_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'd58e6bad-ef3e-11ee-8285-0242ac120003:12' at master log mysql-bin.000004, end_log_pos 3906. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 3645
              Relay_Log_Space: 1932
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'd58e6bad-ef3e-11ee-8285-0242ac120003:12' at master log mysql-bin.000004, end_log_pos 3906. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 11
                  Source_UUID: d58e6bad-ef3e-11ee-8285-0242ac120003
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: 
           Source_Retry_Count: 86400
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 240401 14:17:40
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: d58e6bad-ef3e-11ee-8285-0242ac120003:1-13,
dfb1f706-ef3e-11ee-82cc-0242ac120004:2
            Executed_Gtid_Set: ce6baf4a-ef3e-11ee-99fb-0242ac120002:1-13,
d58e6bad-ef3e-11ee-8285-0242ac120003:1-11,
dfb1f706-ef3e-11ee-82cc-0242ac120004:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

在主庫再次執行刪除操作

mysql> delete from user where id=12;
Query OK, 1 row affected (0.11 sec)
mysql> select * from user;
+----+--------+-----------------+--------------+------------+
| id | name   | address         | last_updated | is_deleted |
+----+--------+-----------------+--------------+------------+
|  2 | 張三   | 廣州白雲山      |   1691563465 |          0 |
|  3 | fox    | NULL            |         NULL |       NULL |
|  4 | 李四   | NULL            |         NULL |       NULL |
|  5 | 111    | NULL            |         NULL |       NULL |
| 14 | AAA    | NULL            |         NULL |       NULL |
| 15 | BBB    | NULL            |         NULL |       NULL |
| 16 | CCC    | NULL            |         NULL |       NULL |
| 23 | 333    | NULL            |         NULL |       NULL |
+----+--------+-----------------+--------------+------------+

在從庫

mysql> select * from user;
+----+--------+-----------------+--------------+------------+
| id | name   | address         | last_updated | is_deleted |
+----+--------+-----------------+--------------+------------+
|  2 | 張三   | 廣州白雲山      |   1691563465 |          0 |
|  3 | fox    | NULL            |         NULL |       NULL |
|  4 | 李四   | NULL            |         NULL |       NULL |
|  5 | 111    | NULL            |         NULL |       NULL |
| 12 | fox    | NULL            |         NULL |       NULL |
| 14 | AAA    | NULL            |         NULL |       NULL |
| 15 | BBB    | NULL            |         NULL |       NULL |
| 16 | CCC    | NULL            |         NULL |       NULL |
| 23 | 333    | NULL            |         NULL |       NULL |
+----+--------+-----------------+--------------+------------+
9 rows in set (0.00 sec)
mysql> stop replica;
Query OK, 0 rows affected (0.07 sec)
# 如果需要一次跳過多條，找出需要跳過的gtid，批量執行
mysql> SET @@SESSION.GTID_NEXT= 'd58e6bad-ef3e-11ee-8285-0242ac120003:12';begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.12 sec)
mysql> SET @@SESSION.GTID_NEXT= 'd58e6bad-ef3e-11ee-8285-0242ac120003:13';begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION GTID_NEXT = AUTOMATIC;
Query OK, 0 rows affected (0.00 sec)
mysql> start replica;
Query OK, 0 rows affected (1.10 sec)
mysql> select * from user;
+----+--------+-----------------+--------------+------------+
| id | name   | address         | last_updated | is_deleted |
+----+--------+-----------------+--------------+------------+
|  2 | 張三   | 廣州白雲山      |   1691563465 |          0 |
|  3 | fox    | NULL            |         NULL |       NULL |
|  4 | 李四   | NULL            |         NULL |       NULL |
|  5 | 111    | NULL            |         NULL |       NULL |
| 14 | AAA    | NULL            |         NULL |       NULL |
| 15 | BBB    | NULL            |         NULL |       NULL |
| 16 | CCC    | NULL            |         NULL |       NULL |
| 23 | 333    | NULL            |         NULL |       NULL |
+----+--------+-----------------+--------------+------------+
8 rows in set (0.01 sec)

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/cuaaz3H61HCD7ER4S2whyQ