生產(chǎn)環(huán)境使用pt-table-checksum檢查MySQL數(shù)據(jù)一致性
公司數(shù)據(jù)中心從托管機(jī)房遷移到阿里云，需要對(duì)MySQL遷移（Replication）后的數(shù)據(jù)一致性進(jìn)行校驗(yàn)，但又不能對(duì)生產(chǎn)環(huán)境使用造成影響，pt-table-checksum 成為了絕佳也是唯一的檢查工具。
成都創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比棗強(qiáng)網(wǎng)站開(kāi)發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式棗強(qiáng)網(wǎng)站制作公司更省心,省錢(qián),快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋棗強(qiáng)地區(qū)。費(fèi)用合理售后完善，10多年實(shí)體公司更值得信賴。
pt-table-checksum 是 Percona-Toolkit 的組件之一，用于檢測(cè)MySQL主、從庫(kù)的數(shù)據(jù)是否一致。其原理是在主庫(kù)執(zhí)行基于statement的sql語(yǔ)句來(lái)生成主庫(kù)數(shù)據(jù)塊的checksum，把相同的sql語(yǔ)句傳遞到從庫(kù)執(zhí)行，并在從庫(kù)上計(jì)算相同數(shù)據(jù)塊的checksum，最后，比較主從庫(kù)上相同數(shù)據(jù)塊的checksum值，由此判斷主從數(shù)據(jù)是否一致。檢測(cè)過(guò)程根據(jù)唯一索引將表按row切分為塊（chunk），以為單位計(jì)算，可以避免鎖表。檢測(cè)時(shí)會(huì)自動(dòng)判斷復(fù)制延遲、 master的負(fù)載， 超過(guò)閥值后會(huì)自動(dòng)將檢測(cè)暫停，減小對(duì)線上服務(wù)的影響。
pt-table-checksum 默認(rèn)情況下可以應(yīng)對(duì)絕大部分場(chǎng)景，官方說(shuō)，即使上千個(gè)庫(kù)、上萬(wàn)億的行，它依然可以很好的工作，這源自于設(shè)計(jì)很簡(jiǎn)單，一次檢查一個(gè)表，不需要太多的內(nèi)存和多余的操作；必要時(shí)，pt-table-checksum 會(huì)根據(jù)服務(wù)器負(fù)載動(dòng)態(tài)改變 chunk 大小，減少?gòu)膸?kù)的延遲。
為了減少對(duì)數(shù)據(jù)庫(kù)的干預(yù)，pt-table-checksum還會(huì)自動(dòng)偵測(cè)并連接到從庫(kù)，當(dāng)然如果失敗，可以指定--recursion-method選項(xiàng)來(lái)告訴從庫(kù)在哪里。它的易用性還體現(xiàn)在，復(fù)制若有延遲，在從庫(kù) checksum 會(huì)暫停直到趕上主庫(kù)的計(jì)算時(shí)間點(diǎn)（也通過(guò)選項(xiàng)--設(shè)定一個(gè)可容忍的延遲最大值，超過(guò)這個(gè)值也認(rèn)為不一致）。
為了保證主數(shù)據(jù)庫(kù)服務(wù)的安全，該工具實(shí)現(xiàn)了許多保護(hù)措施：
-
自動(dòng)設(shè)置 innodb_lock_wait_timeout 為1s，避免引起
-
默認(rèn)當(dāng)數(shù)據(jù)庫(kù)有25個(gè)以上的并發(fā)查詢時(shí)，pt-table-checksum會(huì)暫停?？梢栽O(shè)置 --max-load 選項(xiàng)來(lái)設(shè)置這個(gè)閥值
-
當(dāng)用 Ctrl+C 停止任務(wù)后，工具會(huì)正常的完成當(dāng)前 chunk 檢測(cè)，下次使用 --resume 選項(xiàng)啟動(dòng)可以恢復(fù)繼續(xù)下一個(gè) chunk
工作過(guò)程
直接看 nettedfish 的說(shuō)明：
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)
-
--replicate-check：執(zhí)行完 checksum 查詢?cè)趐ercona.checksums表中，不一定馬上查看結(jié)果呀 —— yes則馬上比較chunk的crc32值并輸出DIFFS列，否則不輸出。默認(rèn)yes，如果指定為--noreplicate-check，一般后續(xù)使用下面的--replicate-check-only去輸出DIFF結(jié)果。
-
--replicate-check-only：不在主從庫(kù)做 checksum 查詢，只在原有 percona.checksums 表中查詢結(jié)果，并輸出數(shù)據(jù)不一致的信息。周期性的檢測(cè)一致性時(shí)可能用到。
-
--nocheck-binlog-format：不檢測(cè)日志格式。這個(gè)選項(xiàng)對(duì)于 ROW 模式的復(fù)制很重要，因?yàn)閜t-table-checksum會(huì)在 Master和Slave 上設(shè)置binlog_format=STATEMENT（確保從庫(kù)也會(huì)執(zhí)行 checksum SQL），MySQL限制從庫(kù)是無(wú)法設(shè)置的，所以假如行復(fù)制從庫(kù)，再作為主庫(kù)復(fù)制出新從庫(kù)時(shí)（A->B->C），B的checksums數(shù)據(jù)將無(wú)法傳輸。（沒(méi)驗(yàn)證）
-
--replicate= 指定 checksum 計(jì)算結(jié)果存到哪個(gè)庫(kù)表里，如果沒(méi)有指定，默認(rèn)是 percona.checksums 。
但是我們檢查使用的mysql用戶一般是沒(méi)有 create table 權(quán)限的，所以你可能需要先手動(dòng)創(chuàng)建：
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;生產(chǎn)環(huán)境中數(shù)據(jù)庫(kù)用戶權(quán)限一般都是有嚴(yán)格管理的，假如連接用戶是repl_user（即直接用復(fù)制用戶來(lái)檢查），它應(yīng)該額外賦予對(duì)其它庫(kù)的 SELECT ，LOCK TABLES 權(quán)限，如果后續(xù)要用 pt-table-sync 就就需要寫(xiě)權(quán)限了。對(duì)percona庫(kù)有寫(xiě)權(quán)限：
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' 用戶能同時(shí)登陸主庫(kù)和從庫(kù)
-
--create-replicate-table 選項(xiàng)會(huì)自動(dòng)創(chuàng)建 percona.checksums 表，但也意味著賦予額外的 CREATE TABLE權(quán)限給 percona_tk@'xxx' 用戶。默認(rèn)yes
-
PROCESS用于自動(dòng)發(fā)現(xiàn)從庫(kù)信息，SUPER權(quán)限用于set binlog_format。
-
--no-check-replication-filters 表示不需要檢查 Master 配置里是否指定了 Filter。 默認(rèn)會(huì)檢查，如果配置了 Filter，如 replicate_do_db,replicate-wild-ignore-table,binlog_ignore_db 等，在從庫(kù)checksum就與遇到表不存在而報(bào)錯(cuò)退出，所以官方默認(rèn)是yes（--check-replication-filters）但我們實(shí)際在檢測(cè)中時(shí)指定--databases=，所以就不存在這個(gè)問(wèn)題，干脆不檢測(cè)
-
--empty-replicate-table：每個(gè)表checksum開(kāi)始前，清空它之前的檢測(cè)數(shù)據(jù)（不影響其它表的checksum數(shù)據(jù)），默認(rèn)yes。當(dāng)然如果使用--resume啟動(dòng)檢測(cè)數(shù)據(jù)不會(huì)清空。
當(dāng)啟用--noempty-replicate-table即不清空時(shí)，不計(jì)算計(jì)算chunk,只計(jì)算。 -
--databases=，-d：要檢查的數(shù)據(jù)庫(kù)，逗號(hào)分隔。用腳趾頭想也知道 --databases-regex 正則匹配要檢測(cè)的數(shù)據(jù)庫(kù)，--ignore-databases[-regex]忽略檢查的庫(kù)。Filter選項(xiàng)。
-
--tables=，-t：要檢查的表，逗號(hào)分隔。如果要檢查的表分布在不同的db中，可以用--tables=dbname1.table1,dbnamd2.table2的形式。同理有--tables-regex，--ignore-tables，--ignore-tables-regex。--replicate指定的checksum表始終會(huì)被過(guò)濾。
-
--recursion-method：發(fā)現(xiàn)從庫(kù)的方式。pt-table-checksum 默認(rèn)可以在主庫(kù)的 processlist 中找到從庫(kù)復(fù)制進(jìn)程，從而識(shí)別出有哪些從庫(kù)，但如果使用是非標(biāo)準(zhǔn)3306端口，會(huì)導(dǎo)致找不到從庫(kù)信息。此時(shí)就會(huì)自動(dòng)采用host方式，但需要提前在從庫(kù) my.cnf 里面配置report_host、report_port信息，如：
report_host = MASTER_HOST report_port = 13306
最終極的辦法是dsn，dsn指定的是某個(gè)表（如 percona.dsns ），表行記錄是改主庫(kù)的（多個(gè)）從庫(kù)的連接信息。適用以下任一情形：
-
主庫(kù)不能自動(dòng)發(fā)現(xiàn)從庫(kù)
-
不想在從庫(kù)添加額外配置（因?yàn)橐貑ⅲ?
-
主從檢測(cè)連接用戶信息不一樣
-
多個(gè)從庫(kù)時(shí)只想驗(yàn)證指定從庫(kù)的一致
我比較傾向使用DSN的方式。這個(gè)dsns表只需要在執(zhí)行 pt-table-checksum 命令的服務(wù)器上能夠訪問(wèn)到就行。這里糾正一個(gè)認(rèn)識(shí)，網(wǎng)上很多人說(shuō) pt-table-checksum 要在主庫(kù)上執(zhí)行，其實(shí)不是的，我的mysql實(shí)例比較多，只需在某一臺(tái)服務(wù)器上安裝percona-toolkit，這臺(tái)服務(wù)能夠同時(shí)訪問(wèn)主庫(kù)和從庫(kù)就行了。具體用法見(jiàn)后面實(shí)例。
檢測(cè)實(shí)例
同網(wǎng)段間主從一致檢查
場(chǎng)景：
-
標(biāo)準(zhǔn)端口3306，只檢查某一個(gè)庫(kù)的關(guān)鍵表
-
一主一從，binlog不是ROW模式
-
同網(wǎng)段復(fù)制，percona_tk@'192.168.5.%' 具備該有的權(quán)限：
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.%';
這是最簡(jiǎn)單的方式，把要連接和檢查的信息交代就行了：
# 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
如果是首次運(yùn)行，會(huì)在主庫(kù)自動(dòng)創(chuàng)建 percona.checksums 表。
輸出結(jié)果：
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 ：完成檢查的時(shí)間戳。
-
ERRORS ：檢查時(shí)候發(fā)生錯(cuò)誤和警告的數(shù)量。
-
DIFFS ：不一致的chunk數(shù)量。當(dāng)指定 --no-replicate-check 即檢查完但不立即輸出結(jié)果時(shí)，會(huì)一直為0；當(dāng)指定 --replicate-check-only 即不檢查只從checksums表中計(jì)算crc32，且只顯示不一致的信息（畢竟輸出的大部分應(yīng)該是一致的，容易造成干擾）。
-
ROWS ：比對(duì)的表行數(shù)。
-
CHUNKS ：被劃分到表中的塊的數(shù)目。
-
SKIPPED ：由于錯(cuò)誤或警告或過(guò)大，則跳過(guò)塊的數(shù)目。
-
TIME ：執(zhí)行的時(shí)間。
-
TABLE ：被檢查的表名
使用dsn跨數(shù)據(jù)中心檢測(cè)
場(chǎng)景：
-
非標(biāo)準(zhǔn)端口13306，只檢查以 d_ts 開(kāi)頭的所有庫(kù)
-
一主二從，binlog是ROW模式，其中一從在阿里云ECS上，主庫(kù)是無(wú)法直接訪問(wèn)該從庫(kù)的
-
檢測(cè)用的賬號(hào)因?yàn)椴皇?，所以不一樣
-
以下是我環(huán)境的情況
MASTER_HOST:13306 主庫(kù)
REPLICA_HOST:3306 從庫(kù)
PTCHECK_HOST pt-table-checksum所在服務(wù)器
DSN_DBHOST，記錄從庫(kù)（連接）dsns的數(shù)據(jù)庫(kù)
最優(yōu)的方式就是dsn指定從庫(kù)了。在從庫(kù)或從庫(kù)同網(wǎng)段主機(jī)里裝上 percona-toolkit。
在DSN_DBHOST 數(shù)據(jù)庫(kù)實(shí)例上創(chuàng)建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';
如果有多個(gè)實(shí)例要檢查，可以創(chuàng)建多個(gè)類似的dsns表。上面的percona_tk用戶只是用來(lái)訪問(wèn)dsn庫(kù)。插入從庫(kù)信息：
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 上執(zhí)行檢查命令：
# 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
選項(xiàng)的意思就不多說(shuō)了。
檢測(cè)完如果一致，其實(shí)是求個(gè)心安，特別是在做數(shù)據(jù)遷移的時(shí)候。如果不一致，那就需要借助 pt-table-sync 工具了，不作介紹。
常見(jiàn)錯(cuò)誤
-
Diffs cannot be detected because no slaves were found
不能自動(dòng)找到從庫(kù)，確認(rèn)processlist或host或dsns方式用對(duì)了。 -
Cannot connect to h=slave1.*.com,p=...,u=percona_user
可以在pt-table-checksum命令前加PTDEBUG=1來(lái)看詳細(xì)的執(zhí)行過(guò)程，如端口、用戶名、權(quán)限錯(cuò)誤。 -
Waiting for the --replicate table to replicate to XXX
問(wèn)題出在 percona.checksums 表在從庫(kù)不存在，根本原因是沒(méi)有從主庫(kù)同步過(guò)來(lái)，所以看一下從庫(kù)是否延遲嚴(yán)重。 -
Pausing because Threads_running=25
反復(fù)打印出類似上面停止檢查的信息。這是因?yàn)楫?dāng)前數(shù)據(jù)庫(kù)正在運(yùn)行的線程數(shù)大于默認(rèn)25，pt-table-checksum 為了減少對(duì)庫(kù)的壓力暫停檢查了。等數(shù)據(jù)庫(kù)壓力過(guò)了就好了，或者也可以直接 Ctrl+C 終端，下一次加上--resume繼續(xù)執(zhí)行，或者加大--max-load=值。 -
字符集問(wèn)題
Error checksumming table Error executing checksum query: DBD::mysql::st execute failed: Illegal mix of collations
12-17T14:48:04 Error checksumming table d_ec_cs.t_online_cs: Error executing checksum query:
DBD::mysql::st execute failed: Illegal mix of collations for operation 'concat_ws' [for Statement
"REPLACE INTO `percona`.`ali_checksum` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `f_cs_id`, `f_corp_id`, `f_valid`, `f_show_name`, `f_online_msg`, `f_offline_msg`, `f_show_mobile`, `f_group_id`, `f_qq`, `f_show_qq`, `f_msn`, `f_show_msn`, `f_sms_online`, `f_scheme`, `f_tel`, `f_telno`, `f_show_tel`, `f_contact`, `f_mobile`, `f_position`, `f_other1`, `f_other2`, `f_other_text1`, `f_other_text2`, `f_email`, `f_qq_first`, `f_qq_first_type`, `f_aids_open`, `f_aids_qq`, `f_aids_crmqq`, `f_aids_yahoo`, `f_aids_skype`, `f_aids_aliww`, `f_aids_msn`, `f_aids_alibaba`, `f_aids_alitrade`, CONCAT(ISNULL(`f_show_name`), ISNULL(`f_group_id`), ISNULL(`f_qq`), ISNULL(`f_show_qq`), ISNULL(`f_sms_online`), ISNULL(`f_other_text1`), ISNULL(`f_other_text2`), ISNULL(`f_email`)) )) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `d_ec_cs`.`t_online_cs`
/*checksum table*/" with ParamValues: 0='d_ts_profile', 1='t_user_account', 2=1, 3=undef, 4=undef, 5=undef] at /usr/bin/pt-table-checksum line 10520.是個(gè)bug，暫時(shí)無(wú)法解決，Illegal mix of collations for operation 'concat_ws'。
轉(zhuǎn)自：https://segmentfault.com/a/1190000004309169
網(wǎng)頁(yè)題目：生產(chǎn)環(huán)境使用pt-table-checksum檢查MySQL數(shù)據(jù)一致性
網(wǎng)頁(yè)鏈接：http://m.jiaotiyi.com/article/iecgoi.html