主從復(fù)制中忽略庫的參數(shù)

replicate-ignore-db

創(chuàng)新互聯(lián)公司服務(wù)項目包括江北網(wǎng)站建設(shè)、江北網(wǎng)站制作、江北網(wǎng)頁制作以及江北網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，江北網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到江北省份的部分城市，未來相信會繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

在slave服務(wù)器上設(shè)置 replicate-ignore-db = test（my.conf中設(shè)置）

在master上如下執(zhí)行

use test

delete from moedb.moe_userinfo where id=3;  slave上此語句不執(zhí)行

replicate_do_db

如在slave服務(wù)器上設(shè)置 replicate_do_db = test（my.conf中設(shè)置）

在master上如下執(zhí)行

use moedb

insert into test.moe(id,name) values (1,'moe');  slave上此語句不執(zhí)行

原因是設(shè)置replicate_ignore_db或replicate_do_db后，MySQL執(zhí)行sql前檢查的是當(dāng)前默認(rèn)數(shù)據(jù)庫，所以跨庫更新語句在Slave上會被忽略。

可以在Slave上使用 replicate_wild_do_table 和 replicate_wild_ignore_table 來解決跨庫更新的問題，如：

replicate_wild_ignore_table=test.%

或

replicate_wild_do_table=test.%

如果是針對多個庫，那每行一個庫名，例如，需要忽略test、mysql，如下：

replicate_wild_ignore_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.

 

我發(fā)現(xiàn)很多人通過 binlog-do-db, binlog-ignore-db, replicate-do-db 和 replicate-ignore-db 來過濾復(fù)制(某些數(shù)據(jù)庫), 盡管有些使用, 但是,在我看來,他們是危險的,并且他們被濫用了. 對于很多的實例,有更安全的替換方案.

 

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.)

 

為什么危險很簡單: 他們并不像你想的那樣工作. 想象如下的場景: 你設(shè)置了 binlog-ignore-db = garbage, 所以 garbage數(shù)據(jù)庫(在slave上不存在這個數(shù)據(jù)庫) 中的數(shù)據(jù)不會被復(fù)制,(待會兒我再講這個，如果你已經(jīng)發(fā)現(xiàn)問題了，不要急于到評論表單)

 

Now you do the following:

現(xiàn)在做下面的事情:

$ 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.

 

復(fù)制會broke2次, 第一次，因為 slave嘗試著去之西你給第一條語句，但是slave上并沒有這樣的表"garbage.junk" , 第二次, 隱含的, 因為 對 production.users不會被 復(fù)制,因為 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 并不像你想的那樣執(zhí)行, 我之前說的, "在garbage數(shù)據(jù)庫中的數(shù)據(jù)不會被復(fù)制" 是錯的, 實際上(數(shù)據(jù)庫)并沒有這么做.事實上, 他是通過默認(rèn)的數(shù)據(jù)庫為“garbage" 的連接, 過濾二進(jìn)制的(SQL)語句日志的. 換句話說, 過濾不是基于 查詢的字符串的, 而實際于你used的數(shù)據(jù)庫.

 

 

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 語句是特別危險的，因為他們將語句寫入了二進(jìn)制日志. 意味著你不能使用二進(jìn)制日志從備份恢復(fù)指定時間的數(shù)據(jù).

 

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

 

在嚴(yán)格控制的環(huán)境中, 這些選項是很有用的，但是我不會談?wù)撨@些(這些包含在我們的書中),

 

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-* 選項, 例如, 避免復(fù)制 garbage數(shù)據(jù)庫中的數(shù)據(jù)的安全的方案是 配置: 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

如果你有些疑惑了,你應(yīng)該去讀一讀手冊上的復(fù)制規(guī)則一節(jié),直到你真正明白為止.

 

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

文章題目：主從復(fù)制中忽略庫的參數(shù)
標(biāo)題鏈接：http://bm7419.com/article10/gijhgo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、企業(yè)網(wǎng)站制作、App開發(fā)、關(guān)鍵詞優(yōu)化、營銷型網(wǎng)站建設(shè)、品牌網(wǎng)站設(shè)計

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)