利用percona-toolkit工具檢查MySQL數(shù)據(jù)庫主從復(fù)制數(shù)據(jù)的一致性，以及修復(fù)。

一、pt-table-checksum檢查主從庫數(shù)據(jù)的一致性

pt-table-checksum在MASTER上校驗(yàn)指定庫、表，將結(jié)果存在一個(gè)庫表里，復(fù)制進(jìn)程將檢驗(yàn)sql傳遞到slave上再執(zhí)行一次。通過比較M/S的檢驗(yàn)值確定數(shù)據(jù)是否一致。利用主從復(fù)制做檢驗(yàn)，不需要在檢驗(yàn)期間對(duì)主從數(shù)據(jù)庫同時(shí)鎖表，可以控制校驗(yàn)的數(shù)據(jù)和速度，不影響到正常服務(wù)。

安裝：

#依賴包yum  –y  perl-DBI  perl-DBD-MySQL  perl-TermReadKey#percona-toolkit包wget http://www.percona.com/downloads/percona-toolkit/LATEST/percona-toolkit-2.2.4.tar.gztar xzvf percona-toolkit-2.2.4.tar.gz ;  cd percona-toolkit-2.2.4 ;  perl Makefile.pl && make && make install

使用方法：

pt-table-checksum [OPTIONS] [DSN]

pt-table-checksum：在主<M>上通過執(zhí)行校驗(yàn)的查詢對(duì)復(fù)制的一致性進(jìn)行檢查，對(duì)比主從的校驗(yàn)值，從而產(chǎn)生結(jié)果。DSN指向的是主的地址，該工具的退出狀態(tài)不為零，如果發(fā)現(xiàn)有任何差別，或者如果出現(xiàn)任何警告或錯(cuò)誤，更多信息請(qǐng)見官網(wǎng)。

不指定任何參數(shù)，會(huì)直接對(duì)本地的所有數(shù)據(jù)庫的表進(jìn)行檢查。

pt-table-checksum –S /tmp/mysqld.sock u=root,p=123456

環(huán)境：

#主庫：mysql> select * from t1;+----+------+| id | name |+----+------+|  1 | aa   ||  2 | bb   ||  3 | cc   ||  4 | dd   ||  5 | ee   |+----+------+5 rows in set (0.00 sec)#從庫：mysql> select * from t1;+----+------+| id | name |+----+------+|  1 | aa   ||  2 | bb   ||  3 | cc   ||  4 | dd   |+----+------+4 rows in set (0.00 sec)

注意：

1、  根據(jù)測(cè)試，需要一個(gè)技能登錄主庫，也能登錄從庫，而且還能同步數(shù)據(jù)庫的賬號(hào)；

2、  只能指定一個(gè)host，必須為主庫的IP；

3、  在檢查時(shí)會(huì)向表加S鎖；

4、  運(yùn)行之前需要從庫的同步IO和SQL進(jìn)程是YES狀態(tài)。

可以用該語句授權(quán)用戶，這里為了方便直接用root用戶了

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums’@’x.x.x.x’ IDENTIFIED BY ‘xxxx’;

執(zhí)行檢測(cè)（MASTER上）：

pt-table-checksum --nocheck-replication-filters --replicate=test.checksum --databases=test h=192.168.68.235,u=root,p=123 --empty-replicate-table --create-replicate-table            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE09-18T12:03:16      0      1        5       1       0   0.018 test.t1

參數(shù)說明：

TS            ：完成檢查的時(shí)間。

ERRORS        ：檢查時(shí)候發(fā)生錯(cuò)誤和警告的數(shù)量。

DIFFS         ：0表示一致，1表示不一致。當(dāng)指定--no-replicate-check時(shí)，會(huì)一直為0，當(dāng)指定--replicate-check-only會(huì)顯示不同的信息。

ROWS          ：表的行數(shù)。

CHUNKS        ：被劃分到表中的塊的數(shù)目。

SKIPPED       ：由于錯(cuò)誤或警告或過大，則跳過塊的數(shù)目。

TIME          ：執(zhí)行的時(shí)間。

TABLE         ：被檢查的表名。

參數(shù)意義：

--nocheck-replication-filters ：不檢查復(fù)制過濾器，建議啟用。后面可以用--databases來指定需要檢查的數(shù)據(jù)庫。

--no-check-binlog-format      : 不檢查復(fù)制的binlog模式，要是binlog模式是ROW，則會(huì)報(bào)錯(cuò)。

--replicate-check-only :只顯示不同步的信息。

--replicate=   ：把checksum的信息寫入到指定表中，建議直接寫到被檢查的數(shù)據(jù)庫當(dāng)中。

--databases=   ：指定需要被檢查的數(shù)據(jù)庫，多個(gè)則用逗號(hào)隔開。

--tables=      ：指定需要被檢查的表，多個(gè)用逗號(hào)隔開

h=127.0.0.1    ：Master的地址

u=root         ：用戶名

p=123456       ：密碼

P=3306         ：端口

更多的參數(shù)請(qǐng)見官網(wǎng)，上面指出來的是常用的，對(duì)該場(chǎng)景夠用的參數(shù)。

通過DIFFS是1可以看出主從的表數(shù)據(jù)不一致。通過查看從庫上的test.checksum表可以看到主從庫的檢驗(yàn)信息。

 mysql> select * from checksum\G;*************************** 1. row ***************************            db: test           tbl: t1         chunk: 1    chunk_time: 0.001604   chunk_index: NULLlower_boundary: NULLupper_boundary: NULL      this_crc: 13fa7d9d    #從的校驗(yàn)值      this_cnt: 4            #從的行數(shù)    master_crc: aa7a56c3    #主的校驗(yàn)值    master_cnt: 5            #主的行數(shù)            ts: 2013-09-18 12:03:161 row in set (0.00 sec)

通過上面的 this_crc <> master_crc 更能清楚的看出他們的不一致了，通過chunk知道是這個(gè)張表的哪個(gè)塊上的記錄出現(xiàn)不一致。要是主的binlog模式是Row 則會(huì)報(bào)錯(cuò)：

Replica db2 has binlog_format ROW which could cause pt-table-checksum to break replication. 

Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation. 

If you understand the risks, specify --no-check-binlog-format to disable this check.

從錯(cuò)誤信息得出，要是不改binlog模式的話，則在執(zhí)行上面的命令時(shí)候要指定：--no-check-binlog-format，即：

pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format --replicate-check-only --replicate=test.checksum --databases=test --tables=t1  h=127.0.0.1,u=root,p=123,P=3306

指定--replicate-check-only參數(shù)會(huì)在前一次pt-table-checksum檢驗(yàn)的數(shù)據(jù)之上比較（不會(huì)再執(zhí)行計(jì)算），顯示出數(shù)據(jù)不一致的SLAVE主機(jī)名：

[root@host125 ~]# pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format --replicate-check-only --replicate=test.checksum --databases=test --tables=t1  h=127.0.0.1,u=root,p=123456,P=3306[root@host125 ~]# pt-table-checksum --nocheck-replication-filters --replicate=test.checksum --databases=test h=192.168.68.235,u=root,p=123 --empty-replicate-table --create-replicate-table --replicate-check-only Differences on host122TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARYtest.t1 1 -1 1

數(shù)據(jù)不一致的SLAVE和表都找出來了，下面就用pt-table-sync來修補(bǔ)數(shù)據(jù)。

二、pt-table-sync修復(fù)從庫不一致的數(shù)據(jù)

使用方法：

pt-table-sync [OPTIONS] DSN [DSN]

pt-table-sync: 高效的同步MySQL表之間的數(shù)據(jù)，他可以做單向和雙向同步的表數(shù)據(jù)。他可以同步單個(gè)表，也可以同步整個(gè)庫。它不同步表結(jié)構(gòu)、索引、或任何其他模式對(duì)象。所以在修復(fù)一致性之前需要保證他們表存在。

繼續(xù)上面的復(fù)制環(huán)境，主和從的t1表數(shù)據(jù)不一致，需要修復(fù)，

執(zhí)行：

[root@host125 ~]# pt-table-sync --print --replicate=test.checksum h=192.168.68.235,u=root,p=123,P=3306 h=192.168.68.232,u=root,p=123,P=3306 #先MASTER的IP，再SLAVE的IPREPLACE INTO `test`.`t1`(`id`, `name`) VALUES ('5', 'ee') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:P=3306,h=192.168.68.235,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:P=3306,h=192.168.68.232,p=...,u=root lock:1 transaction:1 changing_src:test.checksum replicate:test.checksum bidirectional:0 pid:24763 user:root host:host125*/;

參數(shù)的意義：

--replicate=  ：指定通過pt-table-checksum得到的表，這2個(gè)工具差不多都會(huì)一直用。

--databases=  : 指定執(zhí)行同步的數(shù)據(jù)庫，多個(gè)用逗號(hào)隔開。

--tables=     ：指定執(zhí)行同步的表，多個(gè)用逗號(hào)隔開。

--sync-to-master ：指定一個(gè)DSN，即從的IP，他會(huì)通過show processlist或show slave status 去自動(dòng)的找主。

h=127.0.0.1   ：服務(wù)器地址，命令里有2個(gè)ip，第一次出現(xiàn)的是M的地址，第2次是Slave的地址。

u=root        ：帳號(hào)。

p=123456      ：密碼。

--print       ：打印，但不執(zhí)行命令。

--execute     ：執(zhí)行命令。

更多的參數(shù)請(qǐng)見官網(wǎng)，上面指出來的是常用的，對(duì)該場(chǎng)景夠用的參數(shù)。

和上面的命令一樣效果的命令：

[root@host125 ~]# pt-table-sync --print --sync-to-master h=192.168.68.232,u=root,p=123,P=3306 --databases test --tables t1#用一個(gè)IP （SLAVE）就可以了。REPLACE INTO `test`.`t1`(`id`, `name`) VALUES ('5', 'ee') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:P=3306,h=192.168.68.235,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:P=3306,h=192.168.68.232,p=...,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:24798 user:root host:host125*/;

還可以讓它自己執(zhí)行修復(fù)數(shù)據(jù)的SQL語句，但是這樣就沒有輸出了：

[root@host125 ~]# pt-table-sync --execute --sync-to-master h=192.168.68.232,u=root,p=123,P=3306 --databases test --tables t1

數(shù)據(jù)已經(jīng)修復(fù)完成了:

mysql> select * from t1;+----+------+| id | name |+----+------+|  1 | aa   ||  2 | bb   ||  3 | cc   ||  4 | dd   ||  5 | ee   |+----+------+5 rows in set (0.00 sec)

建議還是用--print 打印出來的好，這樣就可以知道那些數(shù)據(jù)有問題，可以人為的干預(yù)下。不然直接執(zhí)行了，出現(xiàn)問題之后更不好處理。總之還是在處理之前做好數(shù)據(jù)的備份工作。

注意：要是表中沒有唯一索引或則主鍵則會(huì)報(bào)錯(cuò)：

Can't make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.

補(bǔ)充：

要是從庫有的數(shù)據(jù)，而主庫沒有，那這個(gè)數(shù)據(jù)怎么處理？會(huì)給出刪除SLAVE多余數(shù)據(jù)，和修復(fù)SLAVE缺失數(shù)據(jù)的SQL語句。

如果在shell窗口不想顯示輸入密碼則可以添加：--ask-pass 參數(shù)，如：

[root@host125 ~]# pt-table-sync --print --ask-pass --sync-to-master h=192.168.68.232,u=root,P=3306 --databases test --tables t1Enter password for 192.168.68.232:

如果使用--ask-pass，報(bào)錯(cuò)：

Cannot read response; is Term::ReadKey installed? Can't locate Term/ReadKey.pm in @INC

安裝Term/ReadKey.pm模塊：

[root@host125 ~]# perl -MCPAN -e "shell"cpan[1]> install Term::ReadKey

總結(jié)：

該工具執(zhí)行檢查表動(dòng)作，檢查連接的帳號(hào)需要有很高的權(quán)限，在一般權(quán)限上需要加SELECT, PROCESS, SUPER, REPLICATION SLAVE等權(quán)限。pt-table-checksm 配合pt-table-sync使用，在執(zhí)行pt-table-sync數(shù)據(jù)同步之前，一定要執(zhí)行pt-table-checksm命令檢查。

最新評(píng)論