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
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類