MySQL從庫選項log-slave-updates未啟用引發的異常

MySQL從庫選項log-slave-updates未啟用引發的異常

最近核查一個基於從庫複製某張特定的表到另外一個主庫調整，未配置log-slave-updates導致表無法正常同步。我們的設定檔中使用了replicate-rewrite-db參數以及replicate_wild_do_table參數。具體情境見下面的描述。

1、環境介紹及問題由來
  DB1M(Master)  ---> DB1S(Slave)
  DB2M(Master)  ---> DB2S(Slave)
  現在的情形是需要將DB1M執行個體上的特定的表tbname上即時同步到DB2M執行個體
  為減輕DB1M上的壓力，我們將DB1S作為DB2M的主庫，即最終的拓撲結果如下：
  DB1M(tbname)  ---> DB1S(tbname) ---> DB2M(tbname) --->DB2S(tbname)
  在DB2M上配置了如下參數：
  replicate-rewrite-db=DB1->DB2
  replicate_wild_do_table=DB2.tbname
  經過上述配置後，將tbname表從DB1M一致性匯出後同步到DB2M執行個體的DB2上
  配置完畢後，DB2M(Master)  ---> DB2S(Slave)上的表tbname並沒有徹底同步，總是存在資料丟失的問題

2、分析
  a、DB1M(Master)  ---> DB1S(Slave)表tbname無異常，排除DB1S做為DB2M主存在問題的可能性
  b、DB1S(tbname) ---> DB2M(tbname)表tbname無異常，排除DB1S上啟用的相關配置等
  b、DB2M(Master)  ---> DB2S(Slave)期間表tbname存在問題，也就是說應該是在DB2M上基於表tbname的dml日誌並沒有寫入到binlog
  c、在DB2M上基於表tbname的dml日誌是來源於DB1S產生的relay log，同步到DB2M(Master)上無異常，一定可以在relay log找到tbname的相關操作
  e、驗證步驟c，再檢查DB2M(Master)上是否有tbname的binlog，如果沒有，一定是某個參數未設定或某個特定對的原因而導致在apply relay log時未添加到binlog

3、故障解決
  通過上述的分析及驗證，果然發現在DB2M(Master)上丟失了參數log-slave-updates
  通過在DB2M(Master)添加作為從庫的選項log-slave-updates後表tbname同步正常

4、有關log-slave-updates選項 
    log-slave-updates是一個全域非動態選項，其值為布爾型，即TRUE和FALSE。預設為FALSE，修改該參數需要重啟執行個體。

    Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin [2039] option to enable binary logging. Prior to MySQL 5.5, the server would not start when using the --log-slave-updates [2004] option without also starting the server with the --log-bin [2039] option, and would fail with an error; in MySQL 5.6, only a warning is generated. (Bug #44663) --log-slave-updates [2004] is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:

    A -> B -> C

    Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin [2039] to enable binary logging, and B with the --log-slave-updates [2004] option so that updates received from A are logged by B to its binary log.

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：