用pt-table-checksum校驗(yàn)數(shù)據(jù)一致性

主從數(shù)據(jù)的一致性校驗(yàn)是個(gè)頭疼的問(wèn)題，偶爾被業(yè)務(wù)投訴主從數(shù)據(jù)不一致，或者幾個(gè)從庫(kù)之間的數(shù)據(jù)不一致，這會(huì)令人沮喪。通常我們僅有一種辦法，熱備主庫(kù)，然后替換掉所有的從庫(kù)。這不僅代價(jià)非常大，而且類似治標(biāo)不治本的方案，讓人十分不安。因此我們需要合適的工具，至少幫我們回答下面三個(gè)問(wèn)題：

• 是從庫(kù)延遲導(dǎo)致了用戶看到的數(shù)據(jù)不一致，還是真的主從數(shù)據(jù)就不一致？
• 如果不一致，這個(gè)比例究竟多大？
• 下次還會(huì)出現(xiàn)嗎？

回答清楚這幾個(gè)問(wèn)題，有助于我們決定是否修復(fù)，以及修復(fù)的方式，還可以幫我們找出不一致的數(shù)據(jù)，進(jìn)而定位問(wèn)題根源。而percona的pt-table-checksum正是我們想要的。

pt-table-checksum簡(jiǎn)介

pt-table-checksum是著名的?percona-toolkit?工具集的工具之一。它通過(guò)在主庫(kù)執(zhí)行基于statement的sql語(yǔ)句來(lái)生成主庫(kù)數(shù)據(jù)塊的checksum，把相同的sql語(yǔ)句傳遞到從庫(kù)，并在從庫(kù)上計(jì)算相同數(shù)據(jù)塊的checksum，最后，比較主從庫(kù)上相同數(shù)據(jù)塊的checksum值，由此判斷主從數(shù)據(jù)是否一致。這種校驗(yàn)是分表進(jìn)行的，在每個(gè)表內(nèi)部又是分塊進(jìn)行的，而且pt工具本身提供了非常多的限流選項(xiàng)，因此對(duì)線上服務(wù)的沖擊較小。

checksum計(jì)算原理

1. 單行數(shù)據(jù)checksum值的計(jì)算

pt工具先檢查表的結(jié)構(gòu)，并獲取每一列的數(shù)據(jù)類型，把所有數(shù)據(jù)類型都轉(zhuǎn)化為字符串，然后用?concat_ws()?函數(shù)進(jìn)行連接，由此計(jì)算出該行的checksum值。checksum默認(rèn)采用crc32，你可以自己定義效率更高的udf。

2. 數(shù)據(jù)塊checksum值的計(jì)算

如果一行一行的計(jì)算checksum再去和從庫(kù)比較，那么效率會(huì)非常低下。pt工具選擇智能分析表上的索引，然后把表的數(shù)據(jù)split成一個(gè)個(gè)chunk，計(jì)算的時(shí)候也是以chunk為單位。因此引入了聚合函數(shù)?BIT_XOR()?。它的功能可以理解為把這個(gè)chunk內(nèi)的所有行的數(shù)據(jù)拼接起來(lái)，再計(jì)算crc32的值，就得到這個(gè)chunk的checksum值。sql語(yǔ)句如下：
這其中還有count(*)，用來(lái)計(jì)算chunk包含的行數(shù)。每一次對(duì)chunk進(jìn)行checksum后，pt工具都會(huì)對(duì)耗時(shí)進(jìn)行統(tǒng)計(jì)分析，并智能調(diào)整下一個(gè)chunk的大小，避免chunk太大對(duì)線上造成影響，同時(shí)也要避免chunk太小而效率低下。

3. 一致性如何保證

當(dāng)pt工具在計(jì)算主庫(kù)上某chunk的checksum時(shí)，主庫(kù)可能還在更新，同時(shí)從庫(kù)可能延遲使得relay-log中還有與這個(gè)chunk數(shù)據(jù)相關(guān)的更新，那該怎么保證主庫(kù)與從庫(kù)計(jì)算的是”同一份”數(shù)據(jù)？答案是加for update當(dāng)前讀鎖，這保證了主庫(kù)的某個(gè)chunk內(nèi)部數(shù)據(jù)的一致性。否則，1000個(gè)人chekcusm同樣的1000行數(shù)據(jù)，可能得到1000個(gè)不同的結(jié)果，你無(wú)法避開(kāi)mvcc的干擾！獲得for update鎖后，pt工具開(kāi)始計(jì)算chunk的checksum值，并把計(jì)算結(jié)果保存到pt工具自建的結(jié)果表中(采用replace into select的方式)，然后釋放鎖。該語(yǔ)句最終會(huì)傳遞到從庫(kù)并執(zhí)行相同的計(jì)算邏輯。

內(nèi)部工作過(guò)程

有了上面關(guān)鍵的幾點(diǎn)說(shuō)明，我們?cè)賮?lái)看看pt工具的內(nèi)部工作過(guò)程，如下圖:簡(jiǎn)單解釋下工作過(guò)程：

• 1. 連接到主庫(kù)：pt工具連接到主庫(kù)，然后自動(dòng)發(fā)現(xiàn)主庫(kù)的所有從庫(kù)。默認(rèn)采用show full processlist來(lái)查找從庫(kù)，但是這只有在主從實(shí)例端口相同的情況下才有效。
• 3. 查找主庫(kù)或者從庫(kù)是否有復(fù)制過(guò)濾規(guī)則：這是為了安全而默認(rèn)檢查的選項(xiàng)。你可以關(guān)閉這個(gè)檢查，但是這可能導(dǎo)致checksum的sql語(yǔ)句要么不會(huì)同步到從庫(kù)，要么到了從庫(kù)發(fā)現(xiàn)從庫(kù)沒(méi)有要被checksum的表，這都會(huì)導(dǎo)致從庫(kù)同步卡庫(kù)。
• 5. 開(kāi)始獲取表，一個(gè)個(gè)的計(jì)算。
• 6. 如果是表的第一個(gè)chunk，那么chunk-size一般為1000；如果不是表的第一個(gè)chunk，那么采用19步中分析出的結(jié)果。
• 7. 檢查表結(jié)構(gòu)，進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換等，生成checksum的sql語(yǔ)句。
• 8. 根據(jù)表上的索引和數(shù)據(jù)的分布，選擇最合適的split表的方法。
• 9. 開(kāi)始checksum表。
• 10. 默認(rèn)在chunk一個(gè)表之前，先刪除上次這個(gè)表相關(guān)的計(jì)算結(jié)果。除非–resume。
• 14. 根據(jù)explain的結(jié)果，判斷chunk的size是否超過(guò)了你定義的chunk-size的上限。如果超過(guò)了，為了不影響線上性能，這個(gè)chunk將被忽略。
• 15. 把要checksum的行加上for update鎖，并計(jì)算。
• 17-18. 把計(jì)算結(jié)果存儲(chǔ)到master_crc master_count列中。
• 19. 調(diào)整下一個(gè)chunk的大小。
• 20. 等待從庫(kù)追上主庫(kù)。如果沒(méi)有延遲備份的從庫(kù)在運(yùn)行，最好檢查所有的從庫(kù)，如果發(fā)現(xiàn)延遲最大的從庫(kù)延遲超過(guò)max-lag秒，pt工具在這里將暫停。
• 21. 如果發(fā)現(xiàn)主庫(kù)的max-load超過(guò)某個(gè)閾值，pt工具在這里將暫停。
• 22. 繼續(xù)下一個(gè)chunk，直到這個(gè)table被chunk完畢。
• 23-24. 等待從庫(kù)執(zhí)行完checksum，便于生成匯總的統(tǒng)計(jì)結(jié)果。每個(gè)表匯總并統(tǒng)計(jì)一次。
• 25-26. 循環(huán)每個(gè)表，直到結(jié)束。?
  校驗(yàn)結(jié)束后，在每個(gè)從庫(kù)上，執(zhí)行如下的sql語(yǔ)句即可看到是否有主從不一致發(fā)生：?
  select * from percona.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc) \G

重要選項(xiàng)

安全選項(xiàng)：

–check-replication-filters 是否檢查復(fù)制過(guò)濾規(guī)則?
–check-slave-tables 檢查是否所有從庫(kù)都有被檢查的表和列?
–chunk-size-limit 每個(gè)chunk最大不能超過(guò)這個(gè)大小，超過(guò)就忽略它

限速選項(xiàng)：

–check-interval 多久檢查一次主從延遲、主庫(kù)負(fù)載是否達(dá)到上限?
–check-slave-lag 是否只檢查這個(gè)從庫(kù)的延遲?
–max-lag 最大延遲，超過(guò)這個(gè)就等待?
–max-load 最大負(fù)載，超過(guò)這個(gè)就等待

過(guò)濾選項(xiàng)：

–databases 只檢查某些庫(kù)?
–tables 只檢查某些表?
這些過(guò)濾選項(xiàng)在修復(fù)不一致數(shù)據(jù)后，檢查修復(fù)效果很有用。

其他選項(xiàng)

–resume 因某種原因中斷，下次接著執(zhí)行，不用從頭開(kāi)始?
–chunk-time 每個(gè)chunk被計(jì)算的時(shí)間，一般默認(rèn)為0.5秒

實(shí)戰(zhàn)舉例

PTDEBUG=1 ./pt-table-checksum --user=user --password=pass --host=10.10.10.10 --port=3306 --databases=nettedfish --tables=just_do_it --recursion-method=processlist

缺陷和注意事項(xiàng)

• 如果表沒(méi)有主鍵或唯一索引，或者干脆沒(méi)有任何索引，那么pt工具在chunk表的時(shí)候，將無(wú)所適從。不過(guò)我們已強(qiáng)制在建表的時(shí)候，每個(gè)表都必須有主鍵。
• –check-binlog-format是默認(rèn)選項(xiàng)，建議不要關(guān)閉它。pt-table-checksum工具自身產(chǎn)生的所有sql語(yǔ)句要基于語(yǔ)句格式同步到從庫(kù)，這是由它的實(shí)現(xiàn)原理決定的。但是在A-B-C的級(jí)聯(lián)復(fù)制結(jié)構(gòu)中，如果B是行格式的復(fù)制，那么B與C的數(shù)據(jù)一致性校驗(yàn)就沒(méi)法做了。在A上設(shè)置該sql語(yǔ)句為語(yǔ)句級(jí)并不會(huì)把set這個(gè)動(dòng)作記錄到binlog中，這個(gè)屬性無(wú)法級(jí)聯(lián)傳遞。
• 主從異構(gòu)的情況下，checksum語(yǔ)句可能在從庫(kù)上執(zhí)行失敗，即使是索引的不一致。例如sql語(yǔ)句中有force index某個(gè)索引，但是從庫(kù)的表上沒(méi)有這個(gè)索引，就會(huì)導(dǎo)致卡庫(kù)。

總結(jié)

pt-table-checksum是校驗(yàn)主從數(shù)據(jù)不一致的最好工具。由于MySQL復(fù)制自身的缺陷，或主從切換不嚴(yán)謹(jǐn)，或備份軟件bug等原因，都可能導(dǎo)致主從數(shù)據(jù)的不一致。不管你管不管，不一致都在那里，就看數(shù)據(jù)對(duì)你重不重要，重要的話，就定期做下檢查并修復(fù)吧。?
且看下回分解：用pt-table-sync修復(fù)不一致的數(shù)據(jù)。

用pt-table-sync修復(fù)不一致的數(shù)據(jù)

percona-toolkit 之 【pt-summary】、【pt-mysql-summary】、【pt-config-diff】、【pt-variable-advisor】說(shuō)明?http://www.linuxidc.com/Linux/2014-08/104929.htm

pt-table-sync簡(jiǎn)介

顧名思義，它用來(lái)修復(fù)多個(gè)實(shí)例之間數(shù)據(jù)的不一致。它可以讓主從的數(shù)據(jù)修復(fù)到最終一致，也可以使通過(guò)應(yīng)用雙寫(xiě)或多寫(xiě)的多個(gè)不相關(guān)的數(shù)據(jù)庫(kù)實(shí)例修復(fù)到一致。同時(shí)它還內(nèi)部集成了pt-table-checksum的校驗(yàn)功能，可以一邊校驗(yàn)一邊修復(fù)，也可以基于pt-table-checksum的計(jì)算結(jié)果來(lái)進(jìn)行修復(fù)。

工作原理

1. 單行數(shù)據(jù)checksum值的計(jì)算

計(jì)算邏輯與pt-table-checksum一樣，也是先檢查表結(jié)構(gòu)，并獲取每一列的數(shù)據(jù)類型，把所有數(shù)據(jù)類型都轉(zhuǎn)化為字符串，然后用concat_ws()函數(shù)進(jìn)行連接，由此計(jì)算出該行的checksum值。checksum默認(rèn)采用crc32計(jì)算。

2. 數(shù)據(jù)塊checksum值的計(jì)算

同pt-table-checksum工具一樣，pt-table-sync會(huì)智能分析表上的索引，然后把表的數(shù)據(jù)split成若干個(gè)chunk，計(jì)算的時(shí)候以chunk為單位。可以理解為把chunk內(nèi)所有行的數(shù)據(jù)拼接起來(lái)，再計(jì)算crc32的值，即得到該chunk的checksum值。

3. 壞塊檢測(cè)和修復(fù)

前面兩步，pt-table-sync與pt-table-checksum的算法和原理一樣。再往下，就開(kāi)始有所不同：

pt-table-checksum只是校驗(yàn)，所以它把checksum結(jié)果存儲(chǔ)到統(tǒng)計(jì)表，然后把執(zhí)行過(guò)的sql語(yǔ)句記錄到binlog中，任務(wù)就算完成。語(yǔ)句級(jí)的復(fù)制把計(jì)算邏輯傳遞到從庫(kù)，并在從庫(kù)執(zhí)行相同的計(jì)算。pt-table-checksum的算法本身并不在意從庫(kù)的延遲，延遲多少都一樣計(jì)算(有同事對(duì)此不理解，可以參考我的前一篇文章)，不會(huì)影響計(jì)算結(jié)果的正確性(但是我們還是會(huì)檢測(cè)延遲，因?yàn)檠舆t太多會(huì)影響業(yè)務(wù)，所以總是要加上—max-lag來(lái)限流)。pt-table-sync則不同。它首先要完成chunk的checksum值的計(jì)算，一旦發(fā)現(xiàn)主從上同樣的chunk的checksum值不同，就深入到該chunk內(nèi)部，逐行比較并修復(fù)有問(wèn)題的行。其計(jì)算邏輯描述如下(以修復(fù)主從結(jié)構(gòu)的數(shù)據(jù)不一致為例，業(yè)務(wù)雙寫(xiě)的情況修復(fù)起來(lái)更復(fù)雜—因?yàn)樯婕暗經(jīng)_突解決和基準(zhǔn)選擇的問(wèn)題，限于篇幅，這里不介紹)：

對(duì)每一個(gè)從庫(kù)，每一個(gè)表，循環(huán)進(jìn)行如下校驗(yàn)和修復(fù)過(guò)程。

對(duì)每一個(gè)chunk，在校驗(yàn)時(shí)加上for update鎖。一旦獲得鎖，就記錄下當(dāng)前主庫(kù)的show master status值。

在從庫(kù)上執(zhí)行select?master_pos_wait()?函數(shù)，等待從庫(kù)sql線程執(zhí)行到show master status得到的位置。以此保證，主從上關(guān)于這個(gè)chunk的內(nèi)容均不再改變。

對(duì)這個(gè)chunk執(zhí)行checksum，然后與主庫(kù)的checksum進(jìn)行比較。

如果checksum相同，說(shuō)明主從數(shù)據(jù)一致，就繼續(xù)下一個(gè)chunk。

如果checksum不同，說(shuō)明該chunk有不一致。深入chunk內(nèi)部，逐行計(jì)算checksum并比較(單行的checksum的比較過(guò)程與chunk的比較過(guò)程一樣，單行實(shí)際是chunk的size為1的特例)。

如果發(fā)現(xiàn)某行不一致，則標(biāo)記下來(lái)。繼續(xù)檢測(cè)剩余行，直到這個(gè)chunk結(jié)束。

對(duì)找到的主從不一致的行，采用replace into語(yǔ)句，在主庫(kù)執(zhí)行一遍以生成該行全量的binlog，并同步到從庫(kù)，這會(huì)以主庫(kù)數(shù)據(jù)為基準(zhǔn)來(lái)修復(fù)從庫(kù)；對(duì)于主庫(kù)有的行而從庫(kù)沒(méi)有的行，采用replace在主庫(kù)上插入(必須不能是insert)；對(duì)于從庫(kù)有而主庫(kù)沒(méi)有的行，通過(guò)在主庫(kù)執(zhí)行delete來(lái)刪除(pt-table-sync強(qiáng)烈建議所有的數(shù)據(jù)修復(fù)都只在主庫(kù)進(jìn)行，而不建議直接修改從庫(kù)數(shù)據(jù)；但是也有特例，后面會(huì)講到)。

直到修復(fù)該chunk所有不一致的行。繼續(xù)檢查和修復(fù)下一個(gè)chunk。

直到這個(gè)從庫(kù)上所有的表修復(fù)結(jié)束。開(kāi)始修復(fù)下一個(gè)從庫(kù)。

重要選項(xiàng)

安全選項(xiàng)

—[no]check-triggers 檢查是否有觸發(fā)器，有則警告

—[no]foreign-key-checks 默認(rèn)檢查主外鍵約束，有則警告

—[no]unique-checks 檢查是否有唯一索引，無(wú)則警告

過(guò)濾選項(xiàng)

—ignore-databases

—ignore-engines

—ignore-tables

其他選項(xiàng)

—replicate=s 與pt-table-checksum結(jié)合起來(lái)，只修復(fù)，而不校驗(yàn)。使用pt-table-checksum之前校驗(yàn)的結(jié)果—bidirectional 雙向同步。通常都以主庫(kù)的數(shù)據(jù)為準(zhǔn)，如果開(kāi)啟雙向同步，就要定義沖突解決規(guī)則，會(huì)比較復(fù)雜

用法舉例

假設(shè)10.55.55.55是主庫(kù)，10.73.73.73是它的從庫(kù)，端口在3306。

1. 先校驗(yàn)：

PTDEBUG=1 ./pt-table-checksum --user=user --password=pass --host=10.55.55.55 --port=3306 --databases=elink --tables=my_cms_10 --recursion-method=processlist?2. 根據(jù)校驗(yàn)結(jié)果，只修復(fù)10.73.73.73從庫(kù)與主庫(kù)不一致的地方：

PTDEBUG=1 ./pt-table-sync --execute --replicate percona.checksums --sync-to-master h=10.73.73.73,P=3306,u=user,p=pass?3. 修復(fù)后，再重新校驗(yàn)一次。執(zhí)行第一步的語(yǔ)句即可。

4. 檢查修復(fù)結(jié)果： 登陸到10.73.73.73，執(zhí)行如下sql語(yǔ)句返回若為空，則說(shuō)明修復(fù)成功：?select * from percona.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)

注意事項(xiàng)

• 采用replace into來(lái)修復(fù)主從不一致，必須保證被replace的表上有主鍵或唯一鍵，否則replace into退化成insert into，起不到修復(fù)的效果。這種情況下pt-table-sync會(huì)采用其他校驗(yàn)和修復(fù)算法，但是效率非常低，例如對(duì)所有列的group by然后求count(*)(表一定要有主鍵！)。
• 主從數(shù)據(jù)不一致需要通過(guò)replace into來(lái)修復(fù)，該sql語(yǔ)句必須是語(yǔ)句級(jí)。pt-table-sync會(huì)把它發(fā)起的所有sql語(yǔ)句都設(shè)置為statement格式，而不管全局的binlog_format值。這在級(jí)聯(lián)A-B-C結(jié)構(gòu)中，也會(huì)遇到pt-table-checksum曾經(jīng)遇到的問(wèn)題，引起行格式的中繼庫(kù)的從庫(kù)卡庫(kù)是必然。不過(guò)pt-table-sync默認(rèn)會(huì)無(wú)限遞歸的對(duì)從庫(kù)的binlog格式進(jìn)行檢查并警告：?
  
• 由于pt-table-sync每次只能修復(fù)一個(gè)表，所以如果修復(fù)的是父表，則可能導(dǎo)致子表數(shù)據(jù)連帶被修復(fù)，這可能會(huì)修復(fù)一個(gè)不一致而引入另一個(gè)不一致；如果表上有觸發(fā)器，也可能遇到同樣問(wèn)題。所以在有觸發(fā)器和主外鍵約束的情況下要慎用。pt-table-sync工具同樣也不歡迎主從異構(gòu)的結(jié)構(gòu)。pt-table-sync工具默認(rèn)會(huì)進(jìn)行先決條件的檢查。
• pt-table-sync在修復(fù)過(guò)程中不能容忍從庫(kù)延遲，這正好與pt-table-checksum相反。如果從庫(kù)延遲太多，pt-table-sync會(huì)長(zhǎng)期持有對(duì)chunk的for update鎖，然后等待從庫(kù)的master_pos_wait執(zhí)行完畢或超時(shí)。從庫(kù)延遲越大，等待過(guò)程就越長(zhǎng)，主庫(kù)加鎖的時(shí)間就越長(zhǎng)，對(duì)線上影響就越大。因此要嚴(yán)格設(shè)置max-lag。
• 對(duì)從庫(kù)數(shù)據(jù)的修復(fù)通常是在主庫(kù)執(zhí)行sql來(lái)同步到從庫(kù)。因此，在有多個(gè)從庫(kù)時(shí)，修復(fù)某個(gè)從庫(kù)的數(shù)據(jù)實(shí)際會(huì)把修復(fù)語(yǔ)句同步到所有從庫(kù)。數(shù)據(jù)修復(fù)的代價(jià)取決于從庫(kù)與主庫(kù)不一致的程度，如果某從庫(kù)數(shù)據(jù)與主庫(kù)非常不一致，舉例說(shuō)，這個(gè)從庫(kù)只有表結(jié)構(gòu)，那么需要把主庫(kù)的所有數(shù)據(jù)重新灌一遍，然后通過(guò)binlog同步，同時(shí)會(huì)傳遞到所有從庫(kù)。這會(huì)給線上帶來(lái)很大壓力，甚至拖垮集群。正確的做法是，先用pt-table-checksum校驗(yàn)一遍，確定不一致的程度：如果不同步的很少，用pt-table-sync直接修復(fù)；否則，用備份先替換它，然后用pt-table-sync修復(fù)。 說(shuō)明： 這實(shí)際提供了一種對(duì)myisam備份的思路：如果僅有一個(gè)myisam的主庫(kù)，要為其增加從庫(kù)，則可以：先mysqldump出表結(jié)構(gòu)到從庫(kù)上，然后啟動(dòng)同步，然后用pt-table-sync來(lái)修復(fù)數(shù)據(jù)。

總結(jié)

pt-table-sync工具很復(fù)雜也很強(qiáng)大。它修改數(shù)據(jù)庫(kù)的內(nèi)容，所以可能造成安全事故。為了安全，建議：使用pt-table-checksum定期檢測(cè)數(shù)據(jù)的一致性，并手動(dòng)重建損壞較為嚴(yán)重的從庫(kù)，最后才用pt-table-sync修復(fù)剩下的從庫(kù)。

本文永久更新鏈接地址：?http://www.linuxidc.com/Linux/2014-11/109852.htm

總結(jié)

以上是生活随笔為你收集整理的mysql数据库主备表校验与修复的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。