mysql主備庫資料不一致的原因和解決方案

myownstars發表於2014-11-25
主備資料不一致常見原因
1 備庫寫資料   
2 執行non-deterministic query   
3 回滾摻雜事務表和非事務表的事務
4 binlog或者relay log資料損壞

應對措施
1 禁止修改備庫資料
2 採用row-based replication
3 避免同一個事務中同時引用innodb和myisam表
4 開啟binlog checksum
其中binlog checksum是5.6引入的新功能,由引數binlog-checksum控制(預設關閉);
開啟該功能後,master在寫binlog event時同時記錄checksum,slave讀取relay log時對每個event執行checksum校驗,如果失敗則停止sql thread並報錯。

master-verify-checksum:預設關閉,開啟後主庫會對每個binlog event進行checksum驗證,如果失敗則停止寫入並報錯;
slave_sql_verify_checksum:預設關閉,開啟後備庫讀relay log時會對每個event進行checksum驗證;
詳細可參考 http://mysqlmusings.blogspot.co.uk/2011/04/replication-event-checksum.html?_sm_au_=iSVMJNwQ5DRFsDtN

sql_slave_skip_counter 的誤區    
當DML在slave執行出錯時sql thread會停止且報告錯誤,透過show slave status可檢視;
通常會部署監控指令碼,定時執行show slave status,如遭遇sql thread錯誤,最常見的方式是跳過該事件並記錄資訊  ;
binlog以事件組方式記錄,每組包含一系列事件, 對於Innodb,每個事務一個事件組,對於Myisam,則是每條sql一個事件組;
sql_slave_skip_counter = N意為跳過N個事件, 但當N=1時,其效果為跳過一個事務;詳細可參考http://dinglin.iteye.com/blog/1236330
                                  

如何檢測資料不一致                                                                                                                    
可定期呼叫pt-table-checksum進行主備資料校驗,發現資料不一致則pt-table-sync進行恢復;                                                   
可使用percona的pt-table-checksum,http://nettedfish.sinaapp.com/blog/2013/06/04/check-replication-consistency-by-pt-table-checksum/
原理
連線主庫同時自動偵測並連線到所有備庫,建立規則表
CREATE TABLE checksums (
   db             char(64)     NOT NULL,
   tbl            char(64)     NOT NULL,
   chunk          int          NOT NULL,
   chunk_time     float            NULL,
   chunk_index    varchar(200)     NULL,
   lower_boundary text             NULL,
   upper_boundary text             NULL,
   this_crc       char(40)     NOT NULL,
   this_cnt       int          NOT NULL,
   master_crc     char(40)         NULL,
   master_cnt     int              NULL,
   ts             timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (db, tbl, chunk),
   INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;
一次只操作一個表,在主庫執行基於statement的sql語句來生成主庫資料塊的checksum,把相同的sql語句傳遞到從庫並計算相同資料塊的checksum;
最後,比較主從庫上相同資料塊的checksum值,由此判斷主從資料是否一致。也可手工查詢

SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
FROM checksums
WHERE (
 master_cnt <> this_cnt
 OR master_crc <> this_crc
 OR ISNULL(master_crc) <> ISNULL(this_crc))
GROUP BY db, tbl;
如何計算checksum
依據表的主鍵或唯一索引,將表劃分為多個資料塊,以資料塊為單位進行計算;
將塊內資料行拼接起來,計算crc32的值,即為其checksum;
每一次對chunk進行checksum後,pt工具都會對耗時進行統計分析,並智慧調整下一個chunk的大小,避免chunk太大對造成影響,也要避免太小而效率低下。
一致性保證
當pt工具在計算主庫上某chunk的checksum時,主庫可能還在更新,為保證chunk內部資料一致性,每計算一個chunk時加for update鎖;
並把計算結果儲存到pt工具自建的結果表中(採用replace into select的方式),然後釋放鎖。該語句最終會傳遞到從庫並執行相同的計算邏輯。
注意事項
1 —check-binlog-format是預設選項,建議不要關閉它。pt-table-checksum產生的sql語句要基於語句格式同步到從庫,這是由它的實現原理決定的。
  但是在A-B-C的級聯複製結構中,如果B是行格式的複製,那麼B與C的資料一致性校驗就沒法做了。
 在A上設定該sql語句為語句級並不會把set這個動作記錄到binlog中;

2 主從異構的情況下,checksum語句可能在從庫上執行失敗,即使是索引的不一致。
 例如sql語句中有force index某個索引,但是從庫的表上沒有這個索引,就會導致卡庫。




如何同步主備庫表     
可使用pt-table-sync  http://nettedfish.sinaapp.com/blog/2013/06/05/synchronizes-data-efficiently-by-pt-table-sync/
原理
同pt-table-checksum一樣,將表分成chunk並計算checksum,一旦發現主從上同樣的chunk的checksum值不同,就深入到該chunk內部,
逐行比較並修復有問題的行。其計算邏輯描述如下:

1 對每一個從庫,每一個表,迴圈進行如下校驗和修復過程。
2 對每一個chunk,在校驗時加上for update鎖。一旦獲得鎖,就記錄下當前主庫的show master status值。
3 在從庫上執行select master_pos_wait()函式,等待從庫sql執行緒執行到show master status得到的位置。以保證主從上這個chunk的內容均不再改變。
4 對這個chunk執行checksum,然後與主庫的checksum進行比較。
5 如果checksum相同,說明主從資料一致,就繼續下一個chunk。
6 如果checksum不同,說明該chunk有不一致。深入chunk內部,逐行計算checksum並比較(單行的checksum的比較過程與chunk的比較過程一樣)。
  如果發現某行不一致,則標記下來。繼續檢測剩餘行,直到這個chunk結束。
7 對找到的主從不一致的行,採用replace into語句,在主庫執行一遍以生成該行全量的binlog,並同步到從庫,這會以主庫資料為基準來修復從庫;
 對於主庫有的行而從庫沒有的行,採用replace在主庫上插入(必須不能是insert);
 對於從庫有而主庫沒有的行,透過在主庫執行delete來刪除(pt-table-sync強烈建議所有的資料修復都只在主庫進行,而不建議直接修改從庫資料)。

  直到修復該chunk所有不一致的行。繼續檢查和修復下一個chunk。
8 直到這個從庫上所有的表修復結束。開始修復下一個從庫。
注意事項
1 pt-table-sync在修復過程中不能容忍從庫延遲,這正好與pt-table-checksum相反。
 如果從庫延遲太多,pt-table-sync會長期持有對chunk的for update鎖,然後等待從庫的master_pos_wait執行完畢或超時。
  從庫延遲越大,等待過程就越長,主庫加鎖的時間就越長,對線上影響就越大。因此要嚴格設定max-lag。

2 對從庫資料的修復通常是在主庫執行sql來同步到從庫。因此,在有多個從庫時,修復某個從庫的資料實際會把修復語句同步到所有從庫。
  資料修復的代價取決於從庫與主庫不一致的程度,如果從庫只有表結構,那麼需要把主庫的所有資料重新灌一遍,然後透過binlog同步並傳遞到所有從庫。
  正確的做法是,先用pt-table-checksum校驗一遍:如果不同步的很少,用pt-table-sync直接修復;否則,用備份先替換它,然後用pt-table-sync修復。
   

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15480802/viewspace-1346340/,如需轉載,請註明出處,否則將追究法律責任。

相關文章