主備資料不一致常見原因
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修復。

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

相關文章