mysql中replicate_wild_do_table和replicate_do_db區別

標籤：cas   form   fit   environ   疑惑   選項   imp   benefit   use   

使用replicate_do_db和replicate_ignore_db時有一個隱患，跨庫更新時會出錯。

如在Master（主）伺服器上設定 replicate_do_db=test（my.conf中設定）
use mysql;
update test.table1 set ......
那麼Slave（從）伺服器上第二句將不會被執行

 

如Master設定 replicate_ignore_db=mysql
use mysql;
update test.table1 set ......
那麼Slave上第二句會被忽略執行

 

原因是設定replicate_do_db或replicate_ignore_db後，MySQL執行sql前檢查的是當前預設資料庫，所以跨庫更新語句在Slave上會被忽略。

可以在Slave上使用 replicate_wild_do_table 和 replicate_wild_ignore_table 來解決跨庫更新的問題，如：
replicate_wild_do_table=test.%
或
replicate_wild_ignore_table=mysql.%

這樣就可以避免出現上述問題了

 

---------------------華麗麗的分界線------------------------

 

完整版：

原文: http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/

作者: Baron Schwartz

Why MySQL’s binlog-do-db option is dangerous

為什麼 MySQL的 binlog-do-db 選項是危險的.

 

I see a lot of people filtering replication with binlog-do-db, binlog-ignore-db, replicate-do-db, and replicate-ignore-db. Although there are uses for these, they are dangerous and in my opinion, they are overused. For many cases, there‘s a safer alternative.

 

我發現很多人通過 binlog-do-db, binlog-ignore-db, replicate-do-db 和 replicate-ignore-db 來過濾複製(某些資料庫), 儘管有些使用, 但是,在我看來,他們是危險的,並且他們被濫用了. 對於很多的執行個體,有更安全的替換方案.

 

The danger is simple: they don‘t work the way you think they do. Consider the following scenario: you set binlog-ignore-db to "garbage" so data in the garbage database (which doesn‘t exist on the slave) isn‘t replicated. (I‘ll come back to this in a second, so if you already see the problem, don‘t rush to the comment form.)

 

為什麼危險很簡單: 他們並不像你想的那樣工作. 想象如下的情境: 你設定了 binlog-ignore-db = garbage, 所以 garbage資料庫(在slave上不存在這個資料庫) 中的資料不會被複製,(待會兒我再講這個，如果你已經發現問題了，不要急於到評論表單)

 

Now you do the following:

現在做下面的事情:

$ mysql
mysql> delete from garbage.junk; 
mysql> use garbage;
mysql> update production.users set disabled = 1 where user = "root";

You just broke replication, twice. Once, because your slave is going to execute the first query and there‘s no such table "garbage.junk" on the slave. The second time, silently, because the update to production.users isn‘t replicated, so now the root user isn‘t disabled on the slave.

 

複製會broke2次, 第一次，因為 slave嘗試著去之西你給第一條語句，但是slave上並沒有這樣的表"garbage.junk" , 第二次, 隱含的, 因為 對 production.users不會被 複製,因為 root帳號並沒有在slave上被禁用掉.

 

Why? Because binlog-ignore-db doesn‘t do what you think. The phrase I used earlier, "data in the garbage database isn‘t replicated," is a fallacy. That‘s not what it does. In fact, it filters out binary logging for statements issued from connections whose default database is "garbage." In other words, filtering is not based on the contents of the query -- it is based on what database you USE.

 

為什麼? 因為 binlog-ignore-db 並不像你想的那樣執行, 我之前說的, "在garbage資料庫中的資料不會被複製" 是錯的, 實際上(資料庫)並沒有這麼做.事實上, 他是通過預設的資料庫為“garbage" 的串連, 過濾二進位的(SQL)語句日誌的. 換句話說, 過濾不是基於 查詢的字串的, 而實際於你used的資料庫.

 

 

The other configuration options I mentioned work similarly. The binlog-do-db and binlog-ignore-db statements are particularly dangerous because they keep statements from ever being written to the binary log, which means you can‘t use the binary log for point-in-time recovery of your data from a backup.

 

其他我提到的配置選項也都類似. binlog-do-db 和 binlog-ignore-db 語句是特別危險的，因為他們將語句寫入了二進位日誌. 意味著你不能使用二進位日誌從備份恢複指定時間的資料.

 

In a carefully controlled environment, these options can have benefits, but I won‘t talk about that here. (We covered that in our book.)

 

在嚴格控制的環境中, 這些選項是很有用的，但是我不會談論這些(這些包含在我們的書中),

 

The safer alternative is to configure filters on the slave, with options that actually operate on the tables mentioned in the query itself. These are replicate-wild-* options. For example, the safer way to avoid replicating data in the garbage database is to configure replicate-wild-ignore-table=garbage.%. There are still edge cases where that won‘t work, but it works in more cases and has fewer gotchas.

 

安全的替換方案是 在 slave上配置過濾, 使用基於查詢中真正涉及到的表的選項, 這些是: replicate-wild-* 選項, 例如, 避免複製 garbage資料庫中的資料的安全的方案是 配置: replicate-wild-ignore-table=garbage.%. 這樣做仍然有一些特殊的情況, 不能正常工作,但可以在更多的情況下正常工作,並且會遇到更少的意外 (gotchas).

 

If you are confused, you should read the replication rules section of the manual until you know it by heart

如果你有些疑惑了,你應該去讀一讀手冊上的複製規則一節,直到你真正明白為止.

 

Refer from http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous

mysql中replicate_wild_do_table和replicate_do_db區別