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

來源:互聯網
上載者:User

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

    最近核查一個基於從庫複製某張特定的表到另外一個主庫調整,未配置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.

 

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.