Mysql跨庫主從熱備失效問題

lihu0512發表於2011-10-28
MySql
 

Mysql跨庫主從熱備失效問題

在InnoDB引擎下發現，Mysql的主從熱備存在資料不一致的問題，一些資料沒有成功同步到備機。在use databases後，更新的表必須是當前選擇的database才同步。譬如連上Mysql服務後操作：

USE test2;
UPDATE client SET name='test' WHERE uid=1;
資料未能同步到備機，而使用use  test後，才可以成功同步，如下方式：
USE test;
UPDATE client SET name='test' WHERE uid=1;
 
仔細看Mysql手冊，發現同步啟動選項中還有玄機，只設定replicate-do-db指定同步資料庫還不夠。是沒有設定replicate-wild-do-table導致的跨庫同步問題。Mysql預設是同步指定資料庫下的更新操作，若要跨庫操作更新同步，就必須指定replicate-wild-do-table引數。下面是Mysql手冊中對replicate-do-db和replicate-wild-do-table啟動選項的說明：
 
–replicate-do-db=db_name
告訴slave只同步那些預設資料庫是 db_name (也就是用 USE選中的)的語句。想要指定更多的資料庫，只需多次使用該選項，每次指定一個資料庫。注意，類似 UPDATE some_db.some_table SET foo='bar'這樣的跨庫操作語句以及沒有選中資料庫的操作都不會被同步。如果必須使用跨庫操作，要確保使用MySQL 3.23.28或更高，並且使用 –replicate-wild-do-table=db_name.%選項。請仔細閱讀最後面的注意事項。
下面是一個不能按照預期工作的例子：如果啟動slave時使用 –replicate-do-db=sales選項，並且在master上執行下列語句，那麼這個 UPDATE語句不會被同步：
USE prices;
UPDATE sales.january SET amount=amount+1000;
如果需要同步跨庫操作，只需使用 –replicate-wild-do-table=db_name.%選項。這個"只檢查預設資料庫"特性的主要原因是因為想要單從一個語句中判斷是否要被同步比較困難(例如，使用多表 DELETE或者 UPDATE，這就跨庫了)。不過想要檢查是否是預設資料庫卻很快。
 
–replicate-wild-do-table=db_name.tbl_name
限制slave只同步那些匹配指定模式的資料表。模式中可以包含萬用字元 `%`和 `_`，它們的含義和 LIKE模式一樣。想要指定更多的資料表，只需多次使用該選項，每次指定一個資料表。請仔細閱讀最後面的注意事項。
例如： –replicate-wild-do-table=foo%.bar%會同步所有以 foo 開頭的資料庫下的以 bar開頭的資料表上的更新操作。
如果匹配模式是 %，則匹配所有的表名，且應用到資料庫級語句(CREATE DATABASE, DROP DATABASE,和 ALTER DATABASE)。例如，使用 –replicate-wild-do-table=foo%.% 選項的話，所有匹配 foo%模式的資料庫級操作都會被同步。
如果想要在資料庫/表模式中包含原義萬用字元，需要用反斜槓來轉義它們。例如，想要同步 my_own%db資料庫下的所有表，但是不想同步 my1ownAABCdb 資料庫下的表，就需要轉義字元 `_`： –replicate-wild-do-table=my\_own\%db。如果是在命令列中使用這個選項，就可能需要兩個反斜槓來轉義，這依賴於命令列直譯器。例如，在 bash shell下，就需要輸入： –replicate-wild-do-table=my\\_own\\%db。
 
還存在一些問題
不管有沒有replicate-wild-do-table選項，更新操作必須是mysql連線已經有選擇的資料庫了才進行，譬如新建的mysql（新連線是沒有預設選擇的database的）連線中執行：
UPDATE test.client SET name='test' WHERE uid=1;
這條更新無法同步到備機，必須在update前use database操作，該database必須是replicate-wild-do-table中指定的database。
保險的解決方式：連線上mysql後，呼叫mysql_select_db()選擇資料庫，之後進行的更新操作就可以自動同步了。
 
SQL模式匹配
SQL的模式匹配允許你使用“_”匹配任何單個字元，而“%”匹配任意數目字元(包括零個字元)。在 MySQL中，SQL的模式預設是忽略大小寫的。下面顯示一些例子。注意在你使用SQL模式時，你不能使用=或!=；而使用LIKE或NOT LIKE比較操作符。

相關文章