MySQL-5.5主從關于‘複制過濾’的深入探究

    關于MySQL主從複制的過濾，例如通過binlog-ignore-db、replicate-do-db、replicate-wild-do-table等。如果不好好研究過這些過濾選項就用的話，是有可能造成主從資料不一緻問題的。本文将參考MySQL-5.5官方文檔并結合實驗，和各位一起探讨下這裡的各個設定。

    以下内容參考5.5官方文檔

binlog_format（STATEMENT，ROW，MIXED，5.5預設為STATEMENT）的設定會導緻一些複制執行上的差異。

當使用MIXED格式時，binlog絕大多數情況也是以STATEMENT格式記錄，隻有在下列情況下才會切換到ROW格式：

1、 當時用UUID()函數時

2、 當一個或多個擁有AUTO_INCREMENT列的表被更新同時有‘trigger’或者‘stored function’被調用時

   # MIXED對于‘trigger’和‘stored function’總是使用statement-based

3、 執行INSERT DELAYED時

4、 當視圖裡的某一部分需要row-based複制（例如UUID()）時，建立該視圖的語句被改為row-based

5、 使用使用者自定義函數（UDF）時

6、 當某語句被判定為row-based，并且執行它的session需要用到臨時表，則session下的所有子語句都将以ROW格式記錄

7、 當使用USER(),CURRENT_USER()或者 CURRENT_USER

8、 當語句引用了一個或多個system variables。

9、 當使用LOAD_FILE()

所有DDL語句都是基于statements，不論binlog_format如何設定

複制雙方binlog_format需一緻，否則複制無法進行

Binlog格式影響到以下‘複制過濾’配置的行為

--binlog-do-db

--binlog-ignore-db=ljk

該選項的行為取決于binlog格式

Statement-based logging：當use ljk後（即目前庫為ljk時），所有的語句不被記錄進binlog

當登陸mysql後不use/或者use ljk之外的庫，執行update ljk.table 依然會記錄近binlog并複制

Row-based format：告訴伺服器不記錄任何ljk庫下表的更改，無論目前在哪個庫（即無論有無use語句，是否use  ljk）

--replicate-do-db = ljk

Statement-based replication：隻有主庫在use ljk 之後執行的語句才會被從庫複制

無use語句或者use其他庫後執行的語句均不被複制

Row-based replication：隻有ljk庫的更改會被複制（無論use哪個庫或者是否use）

無論是否use或use哪個庫，ljk庫之外的變更都不會被複制

--replicate-ignore-db

總結：Statement-based跟目前use的庫有關，Row-based更直接，隻關心指定的庫‘做或不做’。

還有以下兩種參數可‘過濾複制’

以下兩種選項隻對表的更改有影響，庫的複制不受這些參數影響（但是類似ljk.%這種，也會對庫起作用）

--replicate-do-table

這兩個選項在我的實驗裡跟描述不太一緻，詳細見下文實驗結果

--replicate-ignore-table

--replicate-wild-do-table = ljk.%

無論use ljk或use 其他庫或不use，對ljk庫的更新都能被複制,同時，其他庫任何情況下均不會複制（包括建庫建表操作）

--replicate-wild-ignore-table

根據以上，綜合建議：對複制的過濾，采用replicate-wild-do-table/ replicate-wild-ignore-table，比較嚴格和明确

同時，我們先說下根據以上理論以及下面實驗得出的結論：

對于每一個添加的‘複制過濾’配置，應從兩方面考慮

    1. 不用use語句引用庫，或者use xxx引用其他庫之後再執行sql（又分兩部分：對‘過濾的庫/表’ 或 ‘對其他庫/表’）會怎樣

    2. use xxx引用‘過濾的庫/表’，再執行sql（也分兩部分：對‘過濾的庫/表’ 或 ‘對其他庫/表’）會怎樣

除replicate-wild-do-table=/replicate-wild-ignore-table=外，其他過濾規則會受到“binlog_format”以及“目前所在庫”的影響（即所謂的跨庫問題）

下面是實驗過程（MySQL-5.5.39）

一、 主庫添加“binlog-ignore-db = mysql”，從庫不加過濾

庫操作：

    1. 主庫不use，執行建庫語句

    mysql> create database kai;

    從庫複制

    2. 主庫use  mysql,再執行

在kai資料庫執行建表操作：

    1. 不use

    create table kai.li (id int,name char(15));

    2. use mysql;

    從庫不複制

    3. use 其他庫;

對表内容修改：

    1. 主庫不use，向li表增加資料

    insert into kai.li values('1','ljk');

    2. 主庫 use mysql，向li表增加資料

    3. 主庫use 其他庫，更新li庫（即跨庫更新）

    mysql> use picture;

    mysql> insert into kai.li values('2','lhy');

對mysql庫進行更改：

    1. 不 use

    mysql> create table mysql.ljk (id int,name varchar(15));

    2. Use 其他庫

    mysql> drop table mysql.ljk;

二、從庫添加“replicate-ignore-db = mysql”，主庫不加過濾

庫操作

在kai資料庫執行建表操作

對mysql庫進行更改

    從庫不複制，且從庫狀态正常

    3. Use mysql

    mysql> create table ljk (id int,name varchar(15));

三、從庫添加“replicate-ignore-table = mysql.%”，主庫不加過濾

注：這條規則加完在任何庫下執行任何語句均複制；相反，在從庫添加replicate-do-table = mysql.%後,在任何庫下執行任何sql都不會被複制。不知道是不是bug

四、 從庫添加“replicate-wild-ignore-table = mysql.%”，主庫不加過濾

實驗也驗證了上文提到的結論。

     本文轉自kai404 51CTO部落格，原文連結：<b>http://blog.51cto.com/kaifly/1702679</b>，如需轉載請自行聯系原作者