在另外部署的機器上執行:
pt-table-checksum h='192.168.122.47',u='root',p='cc.123',P=3306 -d cat --nocheck-replication-filters --replicate=test_checksum.checksums --nocheck-binlog-format --nocheck-plan --recursion-method=hosts
執行報錯:
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
這個報錯又是為什麼呢?
上面的提示資訊很清楚,因為找不到從,所以執行失敗。用引數--recursion-method 可以指定模式解決,關於--recursion-method引數的設定有:
METHOD USES
=========== =============================================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
cluster SHOW STATUS LIKE 'wsrep\_incoming\_addresses'
dsn=DSN DSNs from a table
none Do not find slaves
預設是通過show processlist 找到host的值或show slave hosts 找到host的值。
改成:
pt-table-checksum h='192.168.122.47',u='root',p='cc.123',P=3306 -d cat --nocheck-replication-filters --replicate=test_checksum.checksums --nocheck-binlog-format --nocheck-plan --recursion-method=processlist --set-vars binlog_format='statement'
即可
公司資料中心從託管機房遷移到阿里雲,需要對mysql遷移(Replication)後的資料一致性進行校驗,但又不能對生產環境使用造成影響,pt-table-checksum 成為了絕佳也是唯一的檢查工具。
pt-table-checksum
是 Percona-Toolkit 的元件之一,用於檢測MySQL主、從庫的資料是否一致。其原理是在主庫執行基於statement的sql語句來生成主庫資料塊的checksum,把相同的sql語句傳遞到從庫執行,並在從庫上計算相同資料塊的checksum,最後,比較主從庫上相同資料塊的checksum值,由此判斷主從資料是否一致。檢測過程根據唯一索引將表按row切分為塊(chunk),以為單位計算,可以避免鎖表。檢測時會自動判斷複製延遲、 master的負載, 超過閥值後會自動將檢測暫停,減小對線上服務的影響。
pt-table-checksum
預設情況下可以應對絕大部分場景,官方說,即使上千個庫、上萬億的行,它依然可以很好的工作,這源自於設計很簡單,一次檢查一個表,不需要太多的記憶體和多餘的操作;必要時,pt-table-checksum
會根據伺服器負載動態改變 chunk 大小,減少從庫的延遲。
為了減少對資料庫的干預,pt-table-checksum
還會自動偵測並連線到從庫,當然如果失敗,可以指定--recursion-method
選項來告訴從庫在哪裡。它的易用性還體現在,複製若有延遲,在從庫 checksum 會暫停直到趕上主庫的計算時間點(也通過選項--
設定一個可容忍的延遲最大值,超過這個值也認為不一致)。
為了保證主資料庫服務的安全,該工具實現了許多保護措施:
-
自動設定
innodb_lock_wait_timeout
為1s,避免引起 -
預設當資料庫有25個以上的併發查詢時,
pt-table-checksum
會暫停。可以設定--max-load
選項來設定這個閥值 -
當用 Ctrl+C 停止任務後,工具會正常的完成當前 chunk 檢測,下次使用
--resume
選項啟動可以恢復繼續下一個 chunk
工作過程
直接看 nettedfish 的說明:
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語句即可看到是否有主從不一致發生:
select * from percona.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR
ISNULL(master_crc) <> ISNULL(this_crc) \G
你需要知道的選項
-
--replicate-check
:執行完 checksum 查詢在percona.checksums表中,不一定馬上檢視結果呀 —— yes則馬上比較chunk的crc32值並輸出DIFFS列,否則不輸出。預設yes,如果指定為--noreplicate-check
,一般後續使用下面的--replicate-check-only
去輸出DIFF結果。 -
--replicate-check-only
:不在主從庫做 checksum 查詢,只在原有percona.checksums
表中查詢結果,並輸出資料不一致的資訊。週期性的檢測一致性時可能用到。 -
--nocheck-binlog-format
:不檢測日誌格式。這個選項對於 ROW 模式的複製很重要,因為pt-table-checksum
會在 Master和Slave 上設定binlog_format=STATEMENT
(確保從庫也會執行 checksum SQL),MySQL限制從庫是無法設定的,所以假如行復制從庫,再作為主庫複製出新從庫時(A->B->C),B的checksums資料將無法傳輸。(沒驗證) -
--replicate=
指定 checksum 計算結果存到哪個庫表裡,如果沒有指定,預設是 percona.checksums 。
但是我們檢查使用的mysql使用者一般是沒有 create table 許可權的,所以你可能需要先手動建立:
CREATE DATABASE IF NOT EXISTS percona;
CREATE TABLE IF NOT EXISTS percona.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;
生產環境中資料庫使用者許可權一般都是有嚴格管理的,假如連線使用者是repl_user
(即直接用複製使用者來檢查),它應該額外賦予對其它庫的 SELECT ,LOCK TABLES 許可權,如果後續要用 pt-table-sync 就就需要寫許可權了。對percona庫有寫許可權:
GRANT ALL PRIVILEGEES on percona.* to repl_user@'%' IDENTIFIED BY 'repl_pass';
GRANT SELECT,LOCK TABLES,PROCESS,SUPER on *.* to repl_user@'%';
注:
-
為了減少不必要的麻煩,確保你的 repl_user@'xxx' 使用者能同時登陸主庫和從庫
-
--create-replicate-table
選項會自動建立 percona.checksums 表,但也意味著賦予額外的CREATE TABLE
許可權給 percona_tk@'xxx' 使用者。預設yes -
PROCESS用於自動發現從庫資訊,SUPER許可權用於set binlog_format。
-
--no-check-replication-filters
表示不需要檢查 Master 配置裡是否指定了 Filter。 預設會檢查,如果配置了 Filter,如 replicate_do_db,replicate-wild-ignore-table,binlog_ignore_db 等,在從庫checksum就與遇到表不存在而報錯退出,所以官方預設是yes(--check-replication-filters
)但我們實際在檢測中時指定--databases=
,所以就不存在這個問題,乾脆不檢測 -
--empty-replicate-table
:每個表checksum開始前,清空它之前的檢測資料(不影響其它表的checksum資料),預設yes。當然如果使用--resume
啟動檢測資料不會清空。
當啟用--noempty-replicate-table
即不清空時,不計算計算chunk,只計算。 -
--databases=
,-d
:要檢查的資料庫,逗號分隔。用腳趾頭想也知道--databases-regex
正則匹配要檢測的資料庫,--ignore-databases[-regex]
忽略檢查的庫。Filter選項。 -
--tables=
,-t
:要檢查的表,逗號分隔。如果要檢查的表分佈在不同的db中,可以用--tables=dbname1.table1,dbnamd2.table2
的形式。同理有--tables-regex
,--ignore-tables
,--ignore-tables-regex
。--replicate
指定的checksum表始終會被過濾。 -
--recursion-method
:發現從庫的方式。pt-table-checksum 預設可以在主庫的processlist
中找到從庫複製程式,從而識別出有哪些從庫,但如果使用是非標準3306埠,會導致找不到從庫資訊。此時就會自動採用host
方式,但需要提前在從庫 my.cnf 裡面配置report_host
、report_port
資訊,如:report_host = MASTER_HOST report_port = 13306
最終極的辦法是dsn
,dsn指定的是某個表(如 percona.dsns ),錶行記錄是改主庫的(多個)從庫的連線資訊。適用以下任一情形:
-
主庫不能自動發現從庫
-
不想在從庫新增額外配置(因為要重啟)
-
主從檢測連線使用者資訊不一樣
-
多個從庫時只想驗證指定從庫的一致
我比較傾向使用DSN的方式。這個dsns表只需要在執行 pt-table-checksum
命令的伺服器上能夠訪問到就行。這裡糾正一個認識,網上很多人說 pt-table-checksum 要在主庫上執行,其實不是的,我的mysql例項比較多,只需在某一臺伺服器上安裝percona-toolkit,這臺服務能夠同時訪問主庫和從庫就行了。具體用法見後面例項。
檢測例項
同網段間主從一致檢查
場景:
-
標準埠3306,只檢查某一個庫的關鍵表
-
一主一從,binlog不是ROW模式
-
同網段複製,percona_tk@'192.168.5.%' 具備該有的許可權:
GRANT ALL PRIVILEGEES on repl_user.* to repl_user@'192.168.5.%' IDENTIFIED BY 'repl_pass'; GRANT SELECT,LOCK TABLES,PROCESS,SUPER on *.* to repl_user@'192.168.5.%';
這是最簡單的方式,把要連線和檢查的資訊交代就行了:
# pt-table-checksum h=MASTER_HOST,u=repl_user,p='repl_pass',P=3306 \
--databases=d_ts_profile --tables=t_user,t_user_detail,t_user_group --nocheck-replication-filters
如果是首次執行,會在主庫自動建立 percona.checksums 表。
輸出結果:
Replica lag is 2307 seconds on mysql-5. Waiting.
Checksumming d_ts_profile.t_user_account: 3% 54:48 remain
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
12-18T16:07:48 0 0 313641 9 0 146.417 d_ts_profile.t_user
12-18T16:08:00 0 0 397734 12 0 11.747 d_ts_profile.t_user_detail
12-18T16:08:24 0 0 1668327 20 0 23.941 d_ts_profile.t_user_group
-
TS :完成檢查的時間戳。
-
ERRORS :檢查時候發生錯誤和警告的數量。
-
DIFFS :不一致的chunk數量。當指定
--no-replicate-check
即檢查完但不立即輸出結果時,會一直為0;當指定--replicate-check-only
即不檢查只從checksums表中計算crc32,且只顯示不一致的資訊(畢竟輸出的大部分應該是一致的,容易造成干擾)。 -
ROWS :比對的錶行數。
-
CHUNKS :被劃分到表中的塊的數目。
-
SKIPPED :由於錯誤或警告或過大,則跳過塊的數目。
-
TIME :執行的時間。
-
TABLE :被檢查的表名
使用dsn跨資料中心檢測
場景:
-
非標準埠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表:
create database percona;
CREATE TABLE `percona`.`dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
GRANT ALL PRIVILEGEES on percona.* to percona_tk@'PTCHECK_HOST' IDENTIFIED BY 'percona_pass';
如果有多個例項要檢查,可以建立多個類似的dsns表。上面的percona_tk使用者只是用來訪問dsn庫。插入從庫資訊:
use percona;
insert into dsns(dsn) values('h=REPLICA_HOST,P=3306,u=repl_user,p=repl_pass');
DSNs記錄 dsn 列格式如 h=REPLICA_HOST,u=repl_user,p=repl_pass
在 PTCHECK_HOST 上執行檢查命令:
# pt-table-checksum --replicate=percona.checksums --nocheck-replication-filters --no-check-binlog-format \
h=MASTER_HOST,u=repl_user,p='repl_pass',P=13306 --databases-regex=d_ts.* \
--recursion-method dsn=h=DSN_DBHOST,u=percona_tk,p='percona_pass',P=3306,D=percona,t=dsn
選項的意思就不多說了。
檢測完如果一致,其實是求個心安,特別是在做資料遷移的時候。如果不一致,那就需要藉助 pt-table-sync
工具了,不作介紹。
如何解決
個人認為只有在statement格式下才能進行,因為兩邊要計算CRC32,計算完後再把主上的master_crc、master_cnt更新到從庫,最後在從庫對比master和this相關列。pt-table-checksum 3.0.4在執行時缺少SET @@binlog_format='STATEMENT',建議不要使用。
有一種很挫的方法,僅僅是為了看差異結果(生產環境勿用),執行pt-table-checksum前,在主上 set global binlog_format='STATEMENT';
常見錯誤
-
Diffs cannot be detected because no slaves were found
不能自動找到從庫,確認processlist或host或dsns方式用對了。 -
Cannot connect to h=slave1.*.com,p=...,u=percona_user
可以在pt-table-checksum
命令前加PTDEBUG=1
來看詳細的執行過程,如埠、使用者名稱、許可權錯誤。 -
Waiting for the --replicate table to replicate to XXX
問題出在 percona.checksums 表在從庫不存在,根本原因是沒有從主庫同步過來,所以看一下從庫是否延遲嚴重。 -
Pausing because Threads_running=25
反覆列印出類似上面停止檢查的資訊。這是因為當前資料庫正在執行的執行緒數大於預設25,pt-table-checksum 為了減少對庫的壓力暫停檢查了。等資料庫壓力過了就好了,或者也可以直接 Ctrl+C 終端,下一次加上--resume
繼續執行,或者加大--max-load=
值。 -
字符集問題