MySQL主從備庫過濾參數分析和測試

来源:https://www.cnblogs.com/ordinarydba/archive/2022/06/29/16424501.html
-Advertisement-
Play Games

測試環境： GTID的主從複製，主庫（9900）——》備庫（9909），存在測試庫表： 9900_db1庫：t1、t2、t3、t4、t5表 9900_db2庫：t6、t7、t8、t9、t10表 1、replicate-do-db參數： --replicate-do-db=name，只同步指定的資料庫 ...


測試環境：

GTID的主從複製，主庫（9900）——》備庫（9909），存在測試庫表：

9900_db1庫：t1、t2、t3、t4、t5表

9900_db2庫：t6、t7、t8、t9、t10表

 


1、replicate-do-db參數：

--replicate-do-db=name，只同步指定的資料庫，可以使用CHANGE REPLICATION FILTER REPLICATE_DO_DB來創建，比如現在只同步9900_db1庫，需要在從庫執行：

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.01 sec)

mysql> change replication filter replicate_do_db=(9900_db1); //(9900_db1,...)可指定多個

Query OK, 0 rows affected (0.01 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.04 sec)

也可以寫入到參數文件永久生效（若要指定多個，需要多次指定該參數）。

此時在主庫9900_db2庫中的表插入數據的話，將不會同步到備庫，在9900_db1庫中的表插入數據的話，是可以同步到備庫的。

但是需要註意的如下：

①該參數不能再group replication架構中使用，因為可能會使組無法達到一致的狀態。

②在binlog為statement格式下（row不會有下麵問題）：

sql線程會將replicate-do-db限制的資料庫為use使用的資料庫。

如果在主庫執行如下操作，備庫將不會複製（不單單是insert，這裡只是用insert舉例，只要涉及到複製庫和未複製庫的跨庫操作就不會複製）：

mysql> use 9900_db2;

Database changed

mysql> insert into 9900_db1.t1(name) values('gg');

Query OK, 1 row affected (0.63 sec)

產生這種行為的原因是：僅通過語句很難判斷是否該複製（比如跨庫update和delete多個表），如果沒有必要，只檢查預設庫比檢查所有庫要快。

③在binlog為row格式下，以下操作不會同步到備庫：

mysql> use 9900_db1;

Database changed

mysql> insert into 9900_db2.t6(name) values('gg');

Query OK, 1 row affected (0.50 sec)

這個也很好理解，因為沒有同步9900_db2即便跨同步的庫也沒用。

總之，在statement格式下的binlog，只要涉及到同步庫和未同步庫的跨庫操作就不會同步；在row格式下的binlog只要涉及到未同步庫的操作，都不會同步。

④以下操作在statement格式下和row格式下有區別：

mysql> use 9900_db1;  

Database changed

mysql> update 9900_db1.t1,9900_db2.t6 set 9900_db1.t1.name='aaa',9900_db2.t6.name='bbb' where 9900_db1.t1.name='aa' and 9900_db2.t6.name='bb';

Query OK, 2 rows affected (3.23 sec)

Rows matched: 2  Changed: 2  Warnings: 0

使用的是replicate-do-db指定的庫，在binlog為statement格式下會同步到備庫，如果不是使用的replicate-do-db庫的話將不會同步。

使用的是replicate-do-db指定的庫，在binlog為row格式下不論是不是使用的replicate-do-db庫，都會只同步9900_db1的操作。

總之，修改操作涉及到同步的庫和未同步的庫的時候，在binlog為statement格式下如果使用的是（這裡使用指的是use dbname）同步的庫操作都會同步到備庫，如果使用未同步的庫的話操作不會同步；在row格式的binlog下無論使用的哪個庫都只會同步，replicate-do-db指定的庫的操作。

 

2、replicate-ignore-db參數：

--replicate-ignore-db=name，不同步指定的資料庫，可以使用CHANGE REPLICATION FILTER REPLICATE_DO_DB來創建，和replicate-do-db一樣，不過多解釋了。

這裡CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(9900_db1);

需要註意的如下：

①該參數不能再group replication架構中使用，因為可能會使組無法達到一致的狀態。同replicate-do-db。

②在binlog為statement格式下，只要use的是replicate-ignore-db的資料庫那麼將不會複製到備庫，use的其他資料庫是可以複製到備庫的，比如如下操作是可以被覆制的，因為use的不是replicate-ignore-db庫：

mysql> use 9900_db2;

Database changed

mysql> update 9900_db1.t1 set name='a' where name='aa';  //因為這裡是顯示指定的9900_db1庫基於statement格式下不會進行過濾。

Query OK, 1 row affected (1.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

在binlog為row格式下，無論use的是不是ignore的資料庫，都不會更新ignore庫中的表。

其實和replicate-do-db大同小異只不過這裡是不同步的庫。

對於上面的④步中的：

如果為statement格式的話，use的是ignore庫，那麼不會同步ignore庫中的表，其他的表可以同步；如果use的不是ignore庫，那麼都會同步。

如果為row格式的話，無論use的是不是ignore的庫，都不會同步ignore庫中的表的操作。

 

3、replicate-do-table參數：

--replicate-do-table=db_name.tbl_name，只同步指定的表，可以使用CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(9900_db1.t1,9900_db2.t6);進行過濾，也可寫入到參數文件，如果要過濾多個表的話，需要指定多次該參數。

測試指定的是只同步9900_db1.t1,9900_db2.t6這兩個表，replicate-do-table指定該參數後不管use的是哪個資料庫都會進行同步（和binlog格式無關），測試如下：

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name='a' where name='aa';

Query OK, 1 row affected (1.02 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> use 9900_db2; //使用其他資料庫也是如此

Database changed

mysql> update 9900_db1.t1 set name='aa' where name='a';

Query OK, 1 row affected (1.10 sec)

Rows matched: 1  Changed: 1  Warnings: 0

如果像1中的④進行多個更新，也是無論use哪個資料庫都會進行更新（不分binlog格式）。當更新操作中既包含db table表又包含不能同步的表時，statement格式時都會同步到備庫，但是如果備庫不存在其他表的話主從將會報錯（與use資料庫無關）；row格式的時候備庫只同步db table的表（與use資料庫無關）。

 

 

4、replicate-ignore-table參數：

--replicate-ignore-table=db_name.tbl_name，不同步指定的表，可以使用CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE=(9900_db1.t1,9900_db2.t6);進行過濾，也可寫入到參數文件，如果要過濾多個表的話，需要指定多次該參數。

測試指定的是不同步9900_db1.t1,9900_db2.t6這兩個表，replicate-ignore-table指定該參數後不管use的是哪個資料庫都不會進行同步，測試如下：

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name='a' where name='aa';

Query OK, 1 row affected (1.17 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> use 9900_db2;

Database changed

mysql> update 9900_db1.t1 set name='a' where name='aa';

Query OK, 1 row affected (1.09 sec)

Rows matched: 1  Changed: 1  Warnings: 0

如果像1中的④進行多個更新，也是無論use哪個資料庫備庫都不會進行更新（不分binlog格式）。對於操作中包含既然包含ignore table表又包含可以同步的表的時候，那麼在statement格式下replicate-ignore-table指定的表和可以同步的表都不會同步（與use資料庫無關）；在row格式下replicate-ignore-table指定的表不同步外其他的表示可以同步的（與use資料庫無關）。

 

5、replicate-wild-do-table參數：

--replicate-wild-do-table=db_name.tbl_name，只同步指定的表，可以包含%和_通配符，它們與LIKE模式匹配操作符的含義相同。可以使用CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=('9900_db1.t1','9900_db2.t6')進行過濾(註意庫名錶名的引號)，也可以寫入參數文件如果不使用通配符等的話，想要同步多個表，需要在參數文件寫多次。

測試指定的是只同步9900_db1.t1,9900_db2.t6這兩個表，replicate-wild-do-table指定該參數後不管use的是哪個資料庫都會進行同步（和binlog格式無關），測試如下：

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name='a' where name='aa';

Query OK, 1 row affected (1.02 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> use 9900_db2; //使用其他資料庫也是如此

Database changed

mysql> update 9900_db1.t1 set name='aa' where name='a';

Query OK, 1 row affected (1.10 sec)

Rows matched: 1  Changed: 1  Warnings: 0

如果像1中的④進行多個更新，也是無論use哪個資料庫都會進行更新（不分binlog格式）。當更新操作中既包含db table表又包含不能同步的表時，statement格式時都會同步到備庫，但是如果備庫不存在其他表的話主從將會報錯（與use資料庫無關）；row格式的時候備庫只同步db table的表（與use資料庫無關）。

replicate-wild-do-table和replicate-do-table效果是一樣的，不同的是replicate-wild-do-table可以使用通配符等。

該選項適用於表、視圖和觸發器，不適用於存儲過程和函數等事件。

例如：replication -wild-do-table=foo%.Bar%.只複製使用資料庫名稱以foo開頭、表名稱以Bar開頭的表的更新。如果表名模式是%，那麼它將匹配任何表名。

該選項不適用隱式更新表，比如授權語句grant會隱式更新mysql.user表，備庫不受該參數限制。

 

6、replicate-wild-ignore-table參數：

--replicate-wild-ignore-table=db_name.tbl_name，不同步指定的表，可以使用CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=('9900_db1.t1','9900_db2.t6');進行過濾，也可寫入到參數文件，如果要過濾多個表的話，需要指定多次該參數。

測試指定的是不同步9900_db1.t1,9900_db2.t6這兩個表，replicate-wild-ignore-table指定該參數後不管use的是哪個資料庫都不會進行同步( 和binlog格式無關)，測試如下：

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name='a' where name='aa';

Query OK, 1 row affected (1.17 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> use 9900_db2;

Database changed

mysql> update 9900_db1.t1 set name='a' where name='aa';

Query OK, 1 row affected (1.09 sec)

Rows matched: 1  Changed: 1  Warnings: 0

如果像1中的④進行多個更新，也是無論use哪個資料庫備庫都不會進行更新（和binlog格式無關）。對於操作中包含既然包含ignore table表又包含可以同步的表的時候，那麼在statement格式下replicate-wild-ignore-table指定的表和可以同步的表都不會同步（與use資料庫無關）；在row格式下replicate-ignore-table指定的表不同步外其他的表示可以同步的（與use資料庫無關）。

因為row格式的binlog中將語句拆分了兩個：

截圖.png

replicate-wild-ignore-table和replicate-ignore-table效果是一樣的，不同的是replicate-wild-ignore-table可以使用通配符等。

例如：replication-wild-ignore-table=foo%.Bar%.不會複製使用資料庫名稱以foo開頭、表名稱以Bar開頭的表的更新。如果表名模式是%，那麼它將匹配任何表名。

該選項不適用隱式更新表，比如授權語句grant會隱式更新mysql.user表，備庫不受該參數限制。如果需要過濾掉GRANT語句或其他管理語句，可能的解決方法是使用--replication-ignore-db參數。

例如：

USE nonreplicated;

GRANT SELECT, INSERT ON replicated.t1 TO 'someuser'@'somehost';

上面的語句序列會導致GRANT語句被忽略。

 

7、replicate-rewrite-db：

--replicate-rewrite-db=from_name->to_name將主庫的資料庫名from_name轉換為從庫的to_name，要指定多個需要多次使用該參數，或者寫入參數文件，例如：

mysqld --replicate-rewrite-db="olddb->newdb"

也可以通過CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB來創建。

這裡使用主庫的9900_db1和從庫的test：CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=((9900_db1,test));

對於binlog為statement和row格式該參數的效果是不同的：

①statement格式下，需改語句中指定庫名(比如update dbname.tblname)這樣的話無論use的哪個庫都不會同步到test庫（如果備庫也有和主庫同名的庫9900_db1，那麼會同步到該庫），如果語句中不指定庫名，use 參數指定的主庫的庫名（也就是9900_db1）的話直接寫表名更新是可以同步到備庫test庫的。

②row格式下，無論use的是哪個庫，也無論更新的時候有沒有指定庫名，都會同步到備庫的test庫，而如果備庫中9900_db1庫也不會同步。

DDL的話不論binlog格式如何，都是根據use的庫來進行的，也就是說，指定庫名(比如update dbname.tblname)這樣的話無論use的哪個庫都DDL不會同步到test庫（如果備庫也有和主庫同名的庫9900_db1，那麼會同步到該庫），有可能sql thread會報錯；如果語句中不指定庫名，use 參數指定的主庫的庫名（也就是9900_db1）的話直接寫表名更新是可以同步到備庫test庫的。

為確保重寫產生預期的結果，尤其是與其他複製篩選選項結合使用時，請在使用選項--replicate-rewrite-db時遵循以下建議：

①在源和具有不同名稱的副本上手動創建from_name 和 to_name 資料庫。

②如果您使用基於語句的或混合的二進位日誌記錄格式，請不要使用跨資料庫查詢，也不要在查詢中指定資料庫名稱。對於 DDL 和 DML語句，都依賴use語句指定的當前資料庫，並且在查詢中僅使用table名

③如果僅對 DDL 語句使用基於row格式的binlog，依靠use語句指定當前資料庫，並且在查詢中使用table名，對於DML語句，可以根據需要使用完全限定的table名（db.table）。

如果遵循這些 建議，則可以安全的將--replicate-rewrite-db選項與table級複製過濾選項（例如--replicate-do-table）結合使用。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • mysql備份資料庫linux
    備份資料庫 問題描述： ​ 我們用的是mysql，以今天遇到的情況為例，我們是在兩台伺服器上要搭相同的平臺，部署完成後頁面報錯，發現是資料庫的問題，我們打開資料庫查看，確實資料庫中少建一個wind資料庫，但是我們沒有建這個資料庫的腳本，資料庫裡面涉及到很多表，很複雜，於是採用linux備份的方法，成 ...
  • 通過 hosts文件配置本地功能變數名稱
    通過 hosts文件配置本地功能變數名稱 概念 DNS: 功能變數名稱系統（Domain Name System），是互聯網的一項服務。它作為將功能變數名稱和IP地址相互映射的一個分散式資料庫，能夠使人更方便地訪問互聯網。 將功能變數名稱映射到對應的IP地址。 互聯網通過IP定位瀏覽器建立連接，但是我們不易區別IP，為了方便用戶辨 ...
  • CentOS中實現基於Docker部署BI數據分析
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 作為一個專業小白，咱啥都不懂。 linux不懂，docker不懂。 但是我還想要完成領導下達的任務：在linux中安裝docker後部署數據可視化工具。作為一名敬業 的打工人擺爛不可以，躺平不可以，弱小，無助，可憐中。。這能力，這要去，要不直接散 ...
  • POWER後的說明
    編寫MBR主引導記錄，開始掌權 一.一些說明 CPU的硬體電路被設計成只能運行處於記憶體中的程式，這是硬體基因的問題，其原因是首先記憶體比較快且容量大，其次由於各個硬體特性不同，若被設計成運行硬體里的程式則操作系統要分別考慮每種硬體特性才行，為了達到統一，故選擇只運行記憶體中的程式。其次記憶體不僅僅是DRA ...
  • Win10桌面右鍵新增內容修改
    桌面右鍵之後，點擊新建，感覺出來了的內容太雜亂了，看強迫症犯了，如圖： 強迫症患者，表示不能忍 1. win + R打開註冊表 2.在註冊表上複製： HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Discar ...
  • MariaDB的安裝與配置
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 MariaDB的安裝與配置 (菜雞我是用阿裡源安裝的) MariaDB是MySQL的一個分支，由開源社區維護，採用GPL授權許可,完全相容MySQL. 1.安裝相對應的源 vi /etc/yum.repos.d/MariaDB.repo # 填寫 ...
  • LVGL庫入門教程 - 動畫
    使用磁碟空間過程 分區：毛坯房 格式化：配置創建文件系統（裝修） 掛載：把分區和文件系統進行組裝 Linux一切皆文件：通過文件的方式來管理磁碟 linux一切皆文件，所以磁碟也表現為文件。每個硬碟文件命名方式和磁碟的類型有關。硬碟文件在內核中自動生成識別，並存放在/dev這個文件下麵。 lvm：邏 ...
  • Flink-基於 DataStream API 實現欺詐檢測
    案例來源於 https://nightlies.apache.org/flink/flink-docs-release-1.14/docs/try-flink/datastream/ 案例背景 在當今數字時代，信用卡欺詐行為越來越被重視。 罪犯可以通過詐騙或者入侵安全級別較低系統來盜竊信用卡卡號。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類