Mysql table id問題導致主從復制失敗該怎么辦

這篇文章將為大家詳細講解有關Mysql table id問題導致主從復制失敗該怎么辦，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

0、導讀

主從復制環境中，IO、SQL線程都很正常，也沒設置過濾規則，但數據就是無法復制到slave上，什么原因？

1、問題描述

事實上，這個案例發生已經有一陣子了，一直拖到現在我才整理。

發現一個主從環境中，slave上的io_thread、sql_thread狀態均正常，relay log也正常接收來自master的event，但slave上卻無法正常應用這些event，個別表數據沒有復制過來。而且slave上的binlog也沒有記錄這些表上的操作。

2、原因分析

接到現場后，第一反應是是先檢查是否設置了ignore/do規則，發現并不是這個原因引起的。

我自己手動測試創建了個新的測試表，寫了幾條數據，發現在slave上這個表能被創建，但寫入的測試數據仍舊無法復制過來。這說明，slave上的復制并不是完全失效的，只是有特殊情形下才會失效。

結合上面的問題，想到了可能是因為binlog format以及事務隔離級別等原因導致失效的，于是做了下面的嘗試。

//首先修改事務隔離級別為RR（此前是RC），盡可能保證主從數據一致性

root@imysql [mydb]> set session transaction isolation level repeatable read;

//測試寫入2條數據

root@imysql [mydb]> insert into z select 5,5;

root@imysql [mydb]> insert into z select 6,6;

經過觀察，這2條數據不可以復制到slave上。

//修改binlog format為statement（此前是row），再寫入2條數據

root@imysql [mydb]> set session binlog_format=’statement’;

root@imysql [mydb]> insert into z select 7,7;

root@imysql [mydb]> insert into z select 8,8;

經過觀察，這2條數據則可以復制到slave上。

現在至少表面上看起來，是由于binlog format+事務隔離級別綜合因素引起的，所以我們來對比下不同binlog format下的binlog有什么區別吧。

這些日志中，前兩條是row模式下的日志，后兩條則是statement模式下的。我們注意到紅框中內容是：table_id: 24874588093，正是由于這個原因導致了slave無法正常復制數據。

正常情況下，row模式下的binlog event應該是這樣的：

在上面的日志中，我們看到的是：table_id: 108，這種情況下就可以正常復制了。

現在問題很明確了，就是由于binlog中table id異常導致無法復制。那么，到底什么原因導致table id出現異常呢。

3、案例建議

搜索了一些資料，發現也有別人遇到同樣的問題。我就不多啰嗦了，大家可以看下方參考文章詳細了解下。簡言之，發生這中問題的原因，主要是因為table cache不夠了，導致要頻繁打開、關閉table，導致table id急劇增長，因而導致主從數據復制失敗。

解決辦法有幾個：

1. 加大 table_cache_size，或者 table_open_cache 值，以及 table_definition_cache 選項。一般設置不低于總table數量的1.5倍，更嚴謹的話，要看 Open_tables 和 Opened_tables 這兩個status值。Open_tables 表示當前正被打開的table數量，而 Opened_tables 表示歷史上反復打開table的總次數。如果 Opened_tables 值特別高，表明 table cache 很可能不夠用所致。

2. 擇機重啟主庫實例，讓table id的值再次從0開始計數。

3. 臨時解決方案：把binlog format改成statement，并且把事務隔離級別改成RR，盡量避免數據不一致的風險。

關于Mysql table id問題導致主從復制失敗該怎么辦就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。