生産環境使用 pt-table-checksum 檢查MySQL資料一緻性

公司資料中心從托管機房遷移到阿裡雲，需要對mysql遷移（replication）後的資料一緻性進行校驗，但又不能對生産環境使用造成影響，pt-table-checksum 成為了絕佳也是唯一的檢查工具。

<code>pt-table-checksum</code> 是 percona-toolkit 的元件之一，用于檢測mysql主、從庫的資料是否一緻。其原理是在主庫執行基于statement的sql語句來生成主庫資料塊的checksum，把相同的sql語句傳遞到從庫執行，并在從庫上計算相同資料塊的checksum，最後，比較主從庫上相同資料塊的checksum值，由此判斷主從資料是否一緻。檢測過程根據唯一索引将表按row切分為塊（chunk），以為機關計算，可以避免鎖表。檢測時會自動判斷複制延遲、 master的負載， 超過閥值後會自動将檢測暫停，減小對線上服務的影響。

<code>pt-table-checksum</code> 預設情況下可以應對絕大部分場景，官方說，即使上千個庫、上萬億的行，它依然可以很好的工作，這源自于設計很簡單，一次檢查一個表，不需要太多的記憶體和多餘的操作；必要時，<code>pt-table-checksum</code> 會根據伺服器負載動态改變 chunk 大小，減少從庫的延遲。

為了減少對資料庫的幹預，<code>pt-table-checksum</code>還會自動偵測并連接配接到從庫，當然如果失敗，可以指定<code>--recursion-method</code>選項來告訴從庫在哪裡。它的易用性還展現在，複制若有延遲，在從庫 checksum 會暫停直到趕上主庫的計算時間點（也通過選項<code>--</code>設定一個可容忍的延遲最大值，超過這個值也認為不一緻）。

為了保證主資料庫服務的安全，該工具實作了許多保護措施：

自動設定 <code>innodb_lock_wait_timeout</code> 為1s，避免引起

預設當資料庫有25個以上的并發查詢時，<code>pt-table-checksum</code>會暫停。可以設定 <code>--max-load</code> 選項來設定這個閥值

當用 ctrl+c 停止任務後，工具會正常的完成目前 chunk 檢測，下次使用 <code>--resume</code> 選項啟動可以恢複繼續下一個 chunk

1\. 連接配接到主庫：pt工具連接配接到主庫，然後自動發現主庫的所有從庫。預設采用show full processlist來查找從庫，但是這隻有在主從執行個體端口相同的情況下才有效。 3\. 查找主庫或者從庫是否有複制過濾規則：這是為了安全而預設檢查的選項。你可以關閉這個檢查，但是這可能導緻checksum的sql語句要麼不會同步到從庫，要麼到了從庫發現從庫沒有要被checksum的表，這都會導緻從庫同步卡庫。 5\. 開始擷取表，一個個的計算。 6\. 如果是表的第一個chunk，那麼chunk-size一般為1000；如果不是表的第一個chunk，那麼采用19步中分析出的結果。 7\. 檢查表結構，進行資料類型轉換等，生成checksum的sql語句。 8\. 根據表上的索引和資料的分布，選擇最合适的split表的方法。 9\. 開始checksum表。 10\. 預設在chunk一個表之前，先删除上次這個表相關的計算結果。除非–resume。 14\. 根據explain的結果，判斷chunk的size是否超過了你定義的chunk-size的上限。如果超過了，為了不影響線上性能，這個chunk将被忽略。 15\. 把要checksum的行加上for update鎖，并計算。 17-18\. 把計算結果存儲到master_crc master_count列中。 19\. 調整下一個chunk的大小。 20\. 等待從庫追上主庫。如果沒有延遲備份的從庫在運作，最好檢查所有的從庫，如果發現延遲最大的從庫延遲超過max-lag秒，pt工具在這裡将暫停。 21\. 如果發現主庫的max-load超過某個門檻值，pt工具在這裡将暫停。 22\. 繼續下一個chunk，直到這個table被chunk完畢。 23-24\. 等待從庫執行完checksum，便于生成彙總的統計結果。每個表彙總并統計一次。 25-26\. 循環每個表，直到結束。

校驗結束後，在每個從庫上，執行如下的sql語句即可看到是否有主從不一緻發生：

<code>--replicate-check</code>：執行完 checksum 查詢在percona.checksums表中，不一定馬上檢視結果呀 —— yes則馬上比較chunk的crc32值并輸出diffs列，否則不輸出。預設yes，如果指定為<code>--noreplicate-check</code>，一般後續使用下面的<code>--replicate-check-only</code>去輸出diff結果。

<code>--replicate-check-only</code>：不在主從庫做 checksum 查詢，隻在原有 <code>percona.checksums</code> 表中查詢結果，并輸出資料不一緻的資訊。周期性的檢測一緻性時可能用到。

<code>--nocheck-binlog-format</code>：不檢測日志格式。這個選項對于 row 模式的複制很重要，因為<code>pt-table-checksum</code>會在 master和slave 上設定<code>binlog_format=statement</code>（確定從庫也會執行 checksum sql），mysql限制從庫是無法設定的，是以假如行複制從庫，再作為主庫複制出新從庫時（a-&gt;b-&gt;c），b的checksums資料将無法傳輸。（沒驗證）

<code>--replicate=</code> 指定 checksum 計算結果存到哪個庫表裡，如果沒有指定，預設是 percona.checksums 。

但是我們檢查使用的mysql使用者一般是沒有 create table 權限的，是以你可能需要先手動建立：

生産環境中資料庫使用者權限一般都是有嚴格管理的，假如連接配接使用者是<code>repl_user</code>（即直接用複制使用者來檢查），它應該額外賦予對其它庫的 select ，lock tables 權限，如果後續要用 pt-table-sync 就就需要寫權限了。對percona庫有寫權限：

注：

為了減少不必要的麻煩，確定你的 repl_user@'xxx' 使用者能同時登陸主庫和從庫

<code>--create-replicate-table</code> 選項會自動建立 percona.checksums 表，但也意味着賦予額外的 <code>create table</code>權限給 percona_tk@'xxx' 使用者。預設yes

process用于自動發現從庫資訊，super權限用于set binlog_format。

<code>--no-check-replication-filters</code> 表示不需要檢查 master 配置裡是否指定了 filter。 預設會檢查，如果配置了 filter，如 replicate_do_db,replicate-wild-ignore-table,binlog_ignore_db 等，在從庫checksum就與遇到表不存在而報錯退出，是以官方預設是yes（<code>--check-replication-filters</code>）但我們實際在檢測中時指定<code>--databases=</code>，是以就不存在這個問題，幹脆不檢測

<code>--empty-replicate-table</code>：每個表checksum開始前，清空它之前的檢測資料（不影響其它表的checksum資料），預設yes。當然如果使用<code>--resume</code>啟動檢測資料不會清空。

當啟用<code>--noempty-replicate-table</code>即不清空時，不計算計算chunk,隻計算。

<code>--databases=</code>，<code>-d</code>：要檢查的資料庫，逗号分隔。用腳趾頭想也知道 <code>--databases-regex</code> 正則比對要檢測的資料庫，<code>--ignore-databases[-regex]</code>忽略檢查的庫。filter選項。

<code>--tables=</code>，<code>-t</code>：要檢查的表，逗号分隔。如果要檢查的表分布在不同的db中，可以用<code>--tables=dbname1.table1,dbnamd2.table2</code>的形式。同理有<code>--tables-regex</code>，<code>--ignore-tables</code>，<code>--ignore-tables-regex</code>。<code>--replicate</code>指定的checksum表始終會被過濾。

<code>--recursion-method</code>：發現從庫的方式。pt-table-checksum 預設可以在主庫的 <code>processlist</code> 中找到從庫複制程序，進而識别出有哪些從庫，但如果使用是非标準3306端口，會導緻找不到從庫資訊。此時就會自動采用<code>host</code>方式，但需要提前在從庫 my.cnf 裡面配置<code>report_host</code>、<code>report_port</code>資訊，如：

最終極的辦法是<code>dsn</code>，dsn指定的是某個表（如 percona.dsns ），表行記錄是改主庫的（多個）從庫的連接配接資訊。适用以下任一情形：

主庫不能自動發現從庫

不想在從庫添加額外配置（因為要重新開機）

主從檢測連接配接使用者資訊不一樣

多個從庫時隻想驗證指定從庫的一緻

我比較傾向使用dsn的方式。這個dsns表隻需要在執行 <code>pt-table-checksum</code> 指令的伺服器上能夠通路到就行。這裡糾正一個認識，網上很多人說 pt-table-checksum 要在主庫上執行，其實不是的，我的mysql執行個體比較多，隻需在某一台伺服器上安裝percona-toolkit，這台服務能夠同時通路主庫和從庫就行了。具體用法見後面執行個體。

場景：

标準端口3306，隻檢查某一個庫的關鍵表

一主一從，binlog不是row模式

同網段複制，percona_tk@'192.168.5.%' 具備該有的權限：

這是最簡單的方式，把要連接配接和檢查的資訊交代就行了：

如果是首次運作，會在主庫自動建立 percona.checksums 表。

輸出結果：

ts ：完成檢查的時間戳。

errors ：檢查時候發生錯誤和警告的數量。

diffs ：不一緻的chunk數量。當指定 <code>--no-replicate-check</code> 即檢查完但不立即輸出結果時，會一直為0；當指定 <code>--replicate-check-only</code> 即不檢查隻從checksums表中計算crc32，且隻顯示不一緻的資訊（畢竟輸出的大部分應該是一緻的，容易造成幹擾）。

rows ：比對的表行數。

chunks ：被劃分到表中的塊的數目。

skipped ：由于錯誤或警告或過大，則跳過塊的數目。

time ：執行的時間。

table ：被檢查的表名

非标準端口13306，隻檢查以 d_ts 開頭的所有庫

一主二從，binlog是row模式，其中一從在阿裡雲ecs上，主庫是無法直接通路該從庫的

檢測用的賬号因為不是%，是以不一樣

以下是我環境的情況

master_host:13306 主庫

replica_host:3306 從庫

ptcheck_host pt-table-checksum所在伺服器

dsn_dbhost，記錄從庫（連接配接）dsns的資料庫

最優的方式就是dsn指定從庫了。在從庫或從庫同網段主機裡裝上 percona-toolkit。

在dsn_dbhost 資料庫執行個體上建立dsns表：

如果有多個執行個體要檢查，可以建立多個類似的dsns表。上面的percona_tk使用者隻是用來通路dsn庫。插入從庫資訊：

dsns記錄 dsn 列格式如 <code>h=replica_host,u=repl_user,p=repl_pass</code>

在 ptcheck_host 上執行檢查指令：

選項的意思就不多說了。

檢測完如果一緻，其實是求個心安，特别是在做資料遷移的時候。如果不一緻，那就需要借助 <code>pt-table-sync</code> 工具了，不作介紹。

diffs cannot be detected because no slaves were found

不能自動找到從庫，确認processlist或host或dsns方式用對了。

cannot connect to h=slave1.*.com,p=...,u=percona_user

可以在<code>pt-table-checksum</code>指令前加<code>ptdebug=1</code>來看詳細的執行過程，如端口、使用者名、權限錯誤。

waiting for the --replicate table to replicate to xxx

問題出在 percona.checksums 表在從庫不存在，根本原因是沒有從主庫同步過來，是以看一下從庫是否延遲嚴重。

pausing because threads_running=25

反複列印出類似上面停止檢查的資訊。這是因為目前資料庫正在運作的線程數大于預設25，pt-table-checksum 為了減少對庫的壓力暫停檢查了。等資料庫壓力過了就好了，或者也可以直接 ctrl+c 終端，下一次加上<code>--resume</code>繼續執行，或者加大<code>--max-load=</code>值。

字元集問題