mysql主備庫資料不一致的原因和解決方案
主備資料不一致常見原因
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驗證;
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修復。
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 資料庫崩潰(crash)的常見原因和解決辦法MySql資料庫
- 硬碟資料丟失原因和解決方案/資料恢復方法硬碟資料恢復
- MYSQL 主從不一致的原因分析MySql
- MySQL資料庫1236錯誤模擬和解決MySql資料庫
- 如何解決MySQL 主從複製資料不一致問題MySql
- 【mysql】mysql的資料庫主從(一主一從)MySql資料庫
- mysql 資料庫 備份MySql資料庫
- MySQL主從資料不一致,怎麼辦?MySql
- MySQL資料庫的基本備份MySql資料庫
- 資料庫主從不一致,怎麼解?資料庫
- MySQL資料庫的備份還原方案——不同伺服器間MySql資料庫伺服器
- mysql雙寫造成主從資料不一致的實驗MySql
- mysql主從資料庫配置MySql資料庫
- [資料庫]MYSQL主從同步資料庫MySql主從同步
- innobackupex 備份MySQL資料庫MySql資料庫
- MySQL資料庫常用的備份方法MySql資料庫
- MYSQL資料庫表記錄刪除解決方案MySql資料庫
- ElasticSearch(八)Elasticsearch-head 連線不上Elasticsearch的原因和解決方案Elasticsearch
- 雲伺服器出現502錯誤的原因分析和解決方案伺服器
- 09.redis 哨兵主備切換時資料丟失的解決方案Redis
- MacOS使用Docker建立MySQL主主資料庫MacDockerMySql資料庫
- MySQL 5.7 主庫崩潰切備庫MySql
- 快取與資料庫雙寫,不一致問題及解決方案快取資料庫
- 解決MySQL的主從資料庫沒有同步的兩種方法MySql資料庫
- MySQL資料庫高可用方案MySql資料庫
- MySQL資料庫之分庫分表方案MySql資料庫
- hive初始化mysql資料庫失敗的原因HiveMySql資料庫
- 8、MySQL主從資料庫配置MySql資料庫
- oracle資料庫常見故障和解決難度Oracle資料庫
- MySQL資料庫備份的shell指令碼MySql資料庫指令碼
- MySql資料庫備份的幾種方式MySql資料庫
- mysql資料庫備份命令大全MySql資料庫
- 備份mysql資料庫報告MySql資料庫
- 1. 備份MySQL資料庫MySql資料庫
- Mysql資料庫自動備份MySql資料庫
- 定時備份MySQL資料庫MySql資料庫
- 電腦主機聲音大是什麼原因?電腦主機聲音大嗡嗡響的原因和解決方法
- 《MySQL主從不一致情形與解決方法》MySql
- MySQL主從不一致情形與解決方法MySql