MySQL主從同步部分庫跨庫問題排查分析

MySQL主從同步部分庫跨庫問題排查分析

問題：使用復(fù)制是設(shè)置   Replicate_Do_DB 參數(shù)發(fā)現(xiàn)跨庫操作時從庫數(shù)據(jù)不更新

1 設(shè)置從庫的 replicate_do_db = test

2 主庫的sql語句是跨庫的insert    在test7 上插入數(shù)據(jù)到test.a 的表上。

  use test7;

3 主庫數(shù)據(jù)更新后查看從庫信息發(fā)現(xiàn)數(shù)據(jù)并沒有插入

4 原因是mysql 在執(zhí)行sql前檢查的當(dāng)前默認(rèn)庫，所以跨庫的sql語句不會被執(zhí)行。

解決方案：

1 使用參數(shù)   replicate-wild-ignore-table =test.%

主庫插入數(shù)據(jù)

從庫查看數(shù)據(jù) 

----------2 使用復(fù)制的組合過濾規(guī)則： replicate-do-db和 replicate-do-table兩個參數(shù)的過濾規(guī)則-------------暫時測試失敗

參數(shù)說明

Replicate_Do_DB :

The effects of this option depend on whether statement-based or row-based replication is in use.

Statement-based replication.  Tell the slave SQL thread to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not replicate cross-database statements such asUPDATE some_db.some_table SET foo='bar' while a different database (or no database) is selected.

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

· 

replicate-do-db =db_name  告訴從庫sql線程限制復(fù)制sql語句，只復(fù)制默認(rèn)的數(shù)庫，多個數(shù)據(jù)庫可以使用","

· 

跨庫sql不被執(zhí)行的原因：“檢查默認(rèn)的數(shù)據(jù)庫”行為是從sql語句中很難得知是否復(fù)制。 sql進(jìn)程檢查的只是默認(rèn)的數(shù)據(jù)庫，而不是所有的數(shù)據(jù)

· 

o 

—replicate-ignore-db =db_name

o 

意義與replicate-do-db =db_name 相反是告訴從庫數(shù)據(jù)庫sql進(jìn)程忽略指定的數(shù)據(jù)庫，不進(jìn)行任何復(fù)制。

USE prices;

UPDATE sales.january SET amount=amount+1000;

The UPDATE statement is replicated in such a case because --replicate-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based replication, the UPDATEstatement's effects are not propagated to the slave, and the slave's copy of the sales.january table is unchanged; in this instance, --replicate-ignore-db=sales causes all changes made to tables in the master's copy of the sales database to be ignored by the slave.

同樣是因為“檢查默認(rèn)的數(shù)據(jù)庫”導(dǎo)致被忽略的數(shù)據(jù)庫數(shù)據(jù)更新

· 

replicate-do-table =db_name.tbl_name

· 

告訴從庫sql進(jìn)程僅復(fù)制指定的表，指定多個表多次使用這個選項。 這個選項適用于跨庫的更新和默認(rèn)的數(shù)據(jù)庫更新，

· 

· 

This option affects only statements that apply to tables. It does not affect statements that apply only to other database objects, such as stored routines. To filter statements operating on stored routines, use one or more of the

· 

· 

· 

o 

replicate-ignore-table =db_name.tbl_name

o 

告訴從庫sql進(jìn)程不復(fù)制指定的表，指定多個表多次使用這個選項。 這個選項適用于跨庫的更新和默認(rèn)的數(shù)據(jù)庫更新，

o 

· 

replicate-wild-do-table =db_name.tbl_name

· 

從庫的sql進(jìn)程復(fù)制任何更新表的操作到指定的數(shù)據(jù)庫名和表名，模式可以包含‘%’和“——”通配符，like模式匹配符的操作。適用于跨庫操作

這個選項適用于表、視圖和觸發(fā)器。  它并不適用于存儲過程和函數(shù),或事件。 過濾語句后面的對象上的操作,

This option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events. To filter statements operating on the latter objects, use one or more of the

· 

replicate-wild-ignore-table =db_name.tbl_name

· 

從庫的sql進(jìn)程不復(fù)制任何更新表的操作到指定的數(shù)據(jù)庫名和表名，模式可以包含‘%’和“——”通配符，like模式匹配符的操作。

參考： http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html 

參數(shù)的使用可以參考：  http://dev.mysql.com/doc/refman/5.1/en/replication-rules.html