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

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

問題：使用復制是設置   Replicate_Do_DB 參數發現跨庫操作時從庫數據不更新

1 設置從庫的 replicate_do_db = test

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

  use test7;

3 主庫數據更新后查看從庫信息發現數據并沒有插入

4 原因是mysql 在執行sql前檢查的當前默認庫，所以跨庫的sql語句不會被執行。

解決方案：

1 使用參數   replicate-wild-ignore-table =test.%

主庫插入數據

從庫查看數據 

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

參數說明

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線程限制復制sql語句，只復制默認的數庫，多個數據庫可以使用","

· 

跨庫sql不被執行的原因：“檢查默認的數據庫”行為是從sql語句中很難得知是否復制。 sql進程檢查的只是默認的數據庫，而不是所有的數據

· 

o 

—replicate-ignore-db =db_name

o 

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

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.

同樣是因為“檢查默認的數據庫”導致被忽略的數據庫數據更新

· 

replicate-do-table =db_name.tbl_name

· 

告訴從庫sql進程僅復制指定的表，指定多個表多次使用這個選項。 這個選項適用于跨庫的更新和默認的數據庫更新，

· 

· 

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進程不復制指定的表，指定多個表多次使用這個選項。 這個選項適用于跨庫的更新和默認的數據庫更新，

o 

· 

replicate-wild-do-table =db_name.tbl_name

· 

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

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

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進程不復制任何更新表的操作到指定的數據庫名和表名，模式可以包含‘%’和“——”通配符，like模式匹配符的操作。

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

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