深度:MySQL 複製過濾詳解
一、背景
如果有這樣的一個需求:master 有3個庫A,B,C ,D,由於某種原因,現在需要將其中2個庫B,C單獨拆分出來,單獨一個實例。 如果是你,打算怎麼做呢? 常見的做法就是,單獨搭建一個只有B,C庫的實例,然後只複製master的B,C庫,過濾掉A,D庫。那麼複製過濾就應運而生了,replicate-*-do-DB/table 等。
二、理由
為了搭建這一套環境(只複製master的B,C庫),部分人會在my.cnf中這樣配置:
--replicate-do-db=B,C
當然,按照大家的慣性思維,認為這樣是沒有錯的。不幸的是,災難已經來臨。
官方文檔:
Warning
To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.
Advertisements
mysql 會認為B,C
為一個庫名,而不是2個庫。
然而,這裡不僅僅有一個複製過濾參數,一共包括:
--replicate-do-db--replicate-ignore-db--replicate-do-table--replicate-wild-do-table--replicate-ignore-table--replicate-wild-ignore-table
這裡面可以隨意組合,且不同組合有不同的含義,為了徹底搞清楚他們直接的關係,下面我們一起來一窺究竟。
三、Database-Level Replication 流程圖
注意1:庫級別的規則,只針對binlog_format='STATEMENT or MIXED『
Advertisements
注意2:如果是binlog_format=『ROW』,不受庫級別規則限制,只受表級別規則限制。
四、Table-Level Replication 流程圖
五、相關要點
注意: 以下測試和結論,前提都是row_format='MIXED'
在DB level 中,當binlog-format=statement 時,過濾以use DB為主(不允許跨庫)。為rows模式是:不以use DB為主(允許跨庫)
不管binlog格式是statement,還是rows模式,table level的判斷都是 不以use DB為主(可以跨庫的)
總的流程走向:先判斷DB-level,如果DB-level 判斷完成後需要exit,則退出。如果DB-level判斷完成後,沒有exit,則再判斷Table-level
在DB-level中,如果有replicate-do-db,則判斷replicate-do-db,將不會走到replicate-ignore-db這層。 如果判斷replicate-do-db符合條件,則判斷table-level。 如果不符合,則exit
在DB-level中,如果沒有replicate-do-db,但是有replicate-ignore-db。 流程則是:符合replicate-ignore-db規則,則exit,不符合,則走到table-level層繼續判斷
**在Table-level中,判斷邏輯順序自上而下為:replicate-do-table -> replicate-ignore-table -> replicate-wild-do-table -> replicate-wild-ignore-table **
在Table-level中, 從第一個階段(replicate-do-table)開始,如果符合replicate-do-table判斷規則,則exit。如果不符合,則跳到下一層(replicate-ignore-table)。 然後以此內推,直到最後一層(replicate-wild-ignore-table)都不符合,則最後判斷是否有(replicate-do-table or replicate-wild-do-table),如果有,則ignore & exit。如果沒有,則execute & exit
六、測試
注意: 以下測試和結論,前提都是row_format='MIXED'
6.1 特殊情況
說明:以下測試,均以statement格式為例。 rows模式參見原理同樣可以證明,這裡就不解釋。
第一種情況:設置replicate_do_DB=A,B
結論:A和B都沒有在slave上執行。因為mysql將'A,B'作為一個庫名。
6.2 只有庫級別的規則
注意:
do-db
replicate_do_DB=A
replicate_do_DB=B
ignoare-db
replicate_ignore_DB=Areplicate_ignore_DB=B
do-db & ignore-db
replicate_do_DB=Areplicate_do_DB=Breplicate-ignore-db=mysqlreplicate-ignore-db=test
6.3 只有表級別的規則
do-table
ignore-table
wild-do-table
wild-ignore-table
do-table & ignore-table
do-table & wild-ignore-table
wild-do-table & wild-ignore-table
6.4 庫和表級別的規則混用
do-DB & do-table
replicate_do_DB=A
replicate_do_DB=B
replicate-do-table=table1replicate-do-table=table2
do-DB & wild-do-table
replicate_do_DB=A
replicate_do_DB=B
replicate-wild-do-table=mysql.%replicate-wild-do-table=test.%
do-DB & ignore-table
replicate_do_DB=Areplicate_do_DB=Breplicate-ignore-table=table1replicate-ignore-table=table2
do-DB & wild-ignore-table
replicate_do_DB=Areplicate_do_DB=Breplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%
最常見場景: db-db & do-ignore-db & wild-do-table & wild-ignore-table
* 常見場景:將master上的A,B庫 拆分到 新的一組機器上。* 特點:
1) slave 不複製 master的 mysql,test庫
2) slave 只複製 master的 A,B庫所有操作
replicate-ignore-db=mysql
replicate-ignore-db=testreplicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate_do_DB=A
replicate_do_DB=B
replicate-wild-do-table=A.%
replicate-wild-do-table=B.%* 誤區:
1) 如果我的default database 不是A或者B,那麼接下來的操作就不會被slave 執行,然後悲劇就產生了。
master> use C;insert into A.id values(1);
2)所以,以上cnf配置,只適合default database 是 A,B的情況。* 如果要完成這種需求,應該這樣配置【前提:開發沒有許可權登陸到mysql,test庫】:replicate-ignore-db=mysql
replicate-ignore-db=testreplicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-do-table=A.%
replicate-wild-do-table=B.%
**實戰: wild-do-table & ignore-table & wild-ignore-table **
需求: 將老伺服器上的某個庫,遷移到新機器上old_master[庫: A , B , mysql] ---->(同步) new_maser[A]
* 驗證單庫(A)複製的正確性: 規則=> slave 只複製A庫,不複製B庫 my.cnf Replicate_Wild_Do_Table: A.% Replicate_Ignore_DB: mysql Replicate_Wild_Ignore_Table: mysql.%
a)use A/B; insert A.a select B.b from B ; --err:同步報錯,slave沒有B庫的內容
b) use A/B; insert A.a select B.b from A,B where A.b=B.b; --err:同步報錯,slave沒有B庫的內容 c) use mysql; insert into A.a values('a'); --err: 同步不報錯,但是老master的binlog沒有在slave執行,因為Replicate_Ignore_DB: mysql,Replicate_Wild_Ignore_Table: mysql.%
d) use 空庫; insert into A.a values('a'); --ok: 可以同步複製下來
e) use B; insert into A.a values('a'); --ok: 可以同步複製下來
由於組合太多,就不一一列舉。
最後
以上情況,還可以衍生出各種場景和組合,只要弄懂了原理,基本都沒有問題。
更多深度技術內容,請關注云棲社區微信公眾號:yunqiinsight。