MySQL 主從複製過濾新增庫表過濾方案

爱可生开源社区發表於2024-12-02
MySql

技術背景

如圖所示，需要將原有的主從複製過濾的配置再新增幾個庫做同步，需要考慮一個效率最高的方式。

主從複製過濾

這裡效率主要表示時間、空間成本，如何用更少的時間和空間完成配置變化。

技術方案

方案1：一併備份需要複製過濾的所有庫

  • 優點：操作簡單，這個方案和常規的備份恢復沒有區別，注意備份時只對指定庫做備份即可。
  • 缺點：如果已經在同步的資料庫資料量過大的情況下，恢復成本較高，如上圖所示，假設 DB1 和 DB2 都是幾百 G 的大小，做一次備份的成本相對較高。

方案2：只備份新增的資料庫

  • 優點：只需要備份和恢復新增的資料庫，備份時間和空間佔用都相對較少。
  • 缺點：操作相對方案1而言會繁瑣一點，需要對處理流程和細節清晰理解。
本文主要基於【方案 2】展開實際操作流程示例。

操作流程

流程概述

  1. 從庫-停止從庫 SQL 回放執行緒
  2. 主庫-備份 DB4、DB5 資料庫
  3. 從庫-使用 UNTIL SQL_AFTER_GTIDS 回放事務
  4. 從庫-還原 DB4、DB5 的備份
  5. 從庫-新增配置 DB4、DB5 的複製過濾策略
  6. 從庫-啟動複製執行緒

1. 從庫-停止從庫 SQL 回放執行緒

說明：這個步驟用於保持從庫 IO 執行緒繼續接收主庫新增的日誌，但不做回放。目的是為了避免在主庫做新增庫備份時，備份中記錄的 GTID 在從庫已經被應用過，導致備份還原時，資料重複。舉例說明：

  1. 2024-10-01 00:00:00 從庫同步到 GTID:1-100
  2. 2024-10-01 00:01:00 主庫開始備份，備份記錄的 GTID:1-110
  3. 2024-10-01 00:02:00 從庫同步到 GTID:1-150

  4. 2020-10-01 00:10:00 從庫做備份還原，重置 GTID 到備份記錄的 GTID:1-110（異常）

    • 實際從庫已經同步到 GTID:1-150 以上，又被還原到了 GTID:1-110，啟動複製報錯

本步驟操作只需在從庫執行如下命令即可

MySQL> STOP SLAVE SQL_THREAD;

2. 主庫-備份 DB4、DB5 資料庫

說明：本步驟僅對新增的複製過濾庫做備份即可，需要注意的是如果用 mysqldump 做備份，不要加 set-gtid-purged=off，需要讓 GTID 資訊記錄在備份檔案中，用於還原時指定。

命令示例如下

shell> mysqldump -h127.0.0.1 -uzhenxing -P3306 -pxxxxx --hex-blob --triggers --routines --events --flush-logs --single-transaction --databases db4 db5  >db4_db5.sql

3. 從庫-使用 UNTIL SQL_AFTER_GTIDS 回放事務

說明：本步驟是一個關鍵操作，需要對從庫 SQL 執行緒指定 UNTIL SQL_AFTER_GTIDS 方式，將從庫的事務回放追平到備份檔案中記錄的事務點，保持從庫的回放 GTID 和備份中記錄的一致，這樣才能正常銜接。

命令示例如下

## 1. 獲取備份檔案中的 GTID 位點資訊（從檔案末尾獲取即可）
shell> tail -n100 db4_db5.sql | grep GTID_PURGED
-- GTID state at the end of the backup
SET @@GLOBAL.GTID_PURGED='13fc4692-48d2-11ef-8a8f-02000aba382b:1-12756';

## 2. 從庫設定回放結束點（注意：這裡只需要設定指定GTID值12756，而不是備份中1-12756這個連續區間）
START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS='13fc4692-48d2-11ef-8a8f-02000aba382b:1-12756';

## 3. 持續觀測SQL執行緒還原狀態，直到Executed_Gtid_Set變為指定值12756
MySQL> show slave status\G
        -- 回放過程中的狀態
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Replicate_Wild_Do_Table: db1.%,db2.%
              Until_Condition: SQL_AFTER_GTIDS
            Executed_Gtid_Set: 13fc4692-48d2-11ef-8a8f-02000aba382b:1-11200
            
MySQL> show slave status\G
    --  SQL_AFTER_GTIDS完成的狀態：(Slave_SQL_Running為NO，且GTID結束點和備份檔案中結束點一致)
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
      Replicate_Wild_Do_Table: db1.%,db2.%
              Until_Condition: SQL_AFTER_GTIDS
            Executed_Gtid_Set: 13fc4692-48d2-11ef-8a8f-02000aba382b:1-12756

4. 從庫-還原 DB4、DB5 的備份

說明：本步驟為實際備份還原過程，直接還原對應資料庫即可，SET @@GLOBAL.GTID_PURGED 會在還原最後自動執行，還原後再做一輪確認。

注意：這裡還原時最後的 SET @@GLOBAL.GTID_PURGED 可能會報錯，可以忽略，是因為從庫已經存在GTID_EXECUTED值，不影響。

命令示例如下

## 資料還原
shell> mysql  < db4_db5.sql

## 這個報錯可以忽略，其他如果資料包錯不可忽略
ERROR 1840 (HY000) at line 424: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

5. 從庫-新增配置 DB4、DB5 的複製過濾策略

## 動態配置複製過濾策略
MySQL> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%','db2.%','db4.%','db5.%');

## 持久化配置到配置檔案
shell> cat /data/mysql/3306/my.cnf.3306
...
## Replication Filter Rules
replicate_wild_do_table = db1.%
replicate_wild_do_table = db2.%
replicate_wild_do_table = db4.%
replicate_wild_do_table = db5.%
...

6 . 從庫-啟動複製執行緒

START SLAVE;
SHOW SLAVE STATUS\G

補充說明

備份方式

文件中用的 mysqldump 做的備份恢復示例，實際用 Xtrabackup 也可，只是 Xtrabackup 在恢復時需要結合可傳輸表空間特性做恢復。

表級別複製過濾

文件中只是演示了庫級的複製過濾新增，表級別的操作方式基本相同，只是針對不同庫下不同表，不能同時備份（mysqldump 不支援）。

相關文章