pt-table-checksum與pt-table-sync使用實踐

小亮520cl發表於2015-11-09

在工作中接觸最多的就是mysql replication,由於現在公司也還在使用mysql 5.1.x版本,在複製方面還是比較多的問題,比如主庫當機或者從庫當機都會導致複製中斷,通常我們需要進行人為修復(mysql 5.5版本解決大部分問題),或者很多時候需要把一個從庫提升為主庫,但對從庫和主庫的資料一致性不能保證一樣,所以就利用 pt-table-checksum 工作來檢查主從的一致性,以及透過 pt-table-sync 如何修復這些不一致的資料。當然如果你資料量小,slave只是當做一個備份使用,那麼出現資料不一致完全可以重做,或者透過其他方法解決。如果資料量非常大,重做就是非常蛋碎的一件事情了。^_^

工具安裝:

1.軟體下載:

[root@MySQL-01 ~]# wget http://

2.安裝該工具依賴的軟體包:

[root@MySQL-01 ~]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes -y

3.軟體安裝:

[root@MySQL-01 ~]# rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm 
Preparing...                ########################################### [100%] 1:percona-toolkit        ########################################### [100%]
[root@MySQL-02 ~]# 

使用方法:

pt-table-checksum [OPTIONS] [DSN]

在主(master)上透過執行校驗的查詢對複製的一致性進行檢查,對比主從的校驗值,從而產生結果。DSN指向的是主的地址,該工具的退出狀態不為零,如果發現有任何差別,或者如果出現任何警告或錯誤,更多資訊請檢視官方資料。

下面透過實際的例子來解釋該工具如何使用:

主庫:


mysql> select * from yayun.t1;
 +----+-------+ | id | name |
 +----+-------+ | 1 | yayun | 
                | 2 | atlas | 
                | 3 | mysql |
 +----+-------+ 3 rows in set (0.00 sec)

mysql> 

備庫:


mysql> select * from yayun.t1
 +----+----------+ | id | name |
 +----+----------+ | 1 | yayun |
                   | 2 | atlas | 
                   | 3 | mysql |
                   | 4 | dengyy | 
                   | 5 | love sql |
 +----+----------+ 5 rows in set (0.00 sec)

mysql> 

很明顯主從資料不一致,那麼我們使用工具來檢測:

[root@MySQL-01 ~]# pt-table-checksum --nocheck-replication-filters --replicate=yayun.checksums --databases=yayun --tables=t1 h=127.0.0.1,u=root,p=123456,P=3306 
Replica MySQL-02 has binlog_format MIXED 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. 
[root@MySQL-01 ~]# 

上面的提示資訊很清楚,因為找不到從,所以執行失敗。用引數--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的值。


 Id: mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 80290761
   User: repluser
   Host: 192.168.1.138:57652
     db: NULL
Command: Binlog Dump
   Time: 918
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row *************************** 

還有一種方法是show slave hosts;前提從庫配置檔案裡面已經配置自己的地址和埠:

[root@MySQL-02 ~]# grep 'report' /etc/my.cnf 
report_host = 192.168.0.20 report_port = 3306 [root@MySQL-02 ~]# 
所以找不到從伺服器時。在從庫配置檔案新增

report_host=slave_ip

report_port=slave_port

最重要的一點是我們需要在從庫上授權,能讓主庫訪問。很多文章沒提,卻能正常進行,不得不說大牛就是牛B

注意:

1、  根據測試,需要一個即能登入主庫,也能登入從庫的賬號;

2、  只能指定一個host,必須為主庫的IP;

3、  在檢查時會向表加S鎖;

4、  執行之前需要從庫的同步IO和SQL程式是YES狀態。

mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'root'@'192.168.0.10' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 

現在我們再來檢測資料一致性:

[chenliang@sh240 backup]$ pt-table-checksum --nocheck-replication-filters  --no-check-binlog-format --replicate=fengkong.checksums --databases=fengkong --tables=api_log  h=192.168.1.240,u=ptuser,p=zAhJ7nLM5I04590r,P=3306
Cannot connect to P=3306,h=124.207.192.34,p=...,u=ptuser
Checksumming fengkong.api_log:  32% 01:07 remain
Checksumming fengkong.api_log:  60% 00:41 remain
Checksumming fengkong.api_log:  88% 00:12 remain
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
06-27T18:01:46      0      1  9096194     112       0 113.795 fengkong.api_log

TS            :完成檢查的時間。
ERRORS        :檢查時候發生錯誤和警告的數量。
DIFFS         :0表示一致,1表示不一致。當指定--no-replicate-check時,會一直為0,當指定--replicate-check-only會顯示不同的資訊。
ROWS          :表的行數。
CHUNKS        :被劃分到表中的塊的數目。
SKIPPED       :由於錯誤或警告或過大,則跳過塊的數目。
TIME          :執行的時間。
TABLE         :被檢查的表名。

引數的意思:


--nocheck-replication-filters :不檢查複製過濾器,建議啟用。後面可以用--databases來指定需要檢查的資料庫。 
--no-check-binlog-format      : 不檢查複製的binlog模式,要是binlog模式是ROW,則會報錯。 
--replicate-check-only :只顯示不同步的資訊。 
--replicate= :把checksum的資訊寫入到指定表中,建議直接寫到被檢查的資料庫當中。 
--databases= :指定需要被檢查的資料庫,多個則用逗號隔開。 
--tables= :指定需要被檢查的表,多個用逗號隔開
h=127.0.0.1 :Master的地址
u=root         :使用者名稱
p=123456 :密碼
P=3306 :埠

好了,命令以及常用引數都介紹了,一起解釋下上面執行的效果:
透過DIFFS 是1 就可以看出主從的表資料不一致。怎麼不一致呢? 透過指定--replicate=yayun.checksums 引數,就說明把檢查資訊都寫到了checksums表中。

透過上面找到了這些不一致的資料表,如何同步資料呢?即如何修復MySQL主從不一致的資料,讓他們保持一致性呢?利用另外一個工具 。
使用方法:

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

高效的同步MySQL表之間的資料,他可以做單向和雙向同步的表資料。他可以同步單個表,也可以同步整個庫。它不同步表結構、索引、或任何其他模式物件。所以在修復一致性之前需要保證他們表存在。

接著上面的複製情況,主和從的t1表資料不一致,需要修復,
執行

先master的ip,使用者,密碼,然後是slave的ip,使用者,密碼


[root@MySQL-01 ~]# pt-table-sync --replicate=yayun.checksums h=127.0.0.1,u=root,p=123456 h=192.168.0.20,u=root,p=123456 --print
DELETE FROM `yayun`.`t1` WHERE `id`='4' LIMIT 1 /*percona-toolkit src_db:yayun src_tbl:t1 src_dsn:h=127.0.0.1,p=...,u=root dst_db:yayun dst_tbl:t1 dst_dsn:h=192.168.0.20,p=...,u=root lock:1 transaction:1 changing_src:yayun.checksums replicate:yayun.checksums bidirectional:0 pid:2190 user:root host:MySQL-01*/;
DELETE FROM `yayun`.`t1` WHERE `id`='5' LIMIT 1 /*percona-toolkit src_db:yayun src_tbl:t1 src_dsn:h=127.0.0.1,p=...,u=root dst_db:yayun dst_tbl:t1 dst_dsn:h=192.168.0.20,p=...,u=root lock:1 transaction:1 changing_src:yayun.checksums replicate:yayun.checksums bidirectional:0 pid:2190 user:root host:MySQL-01*/;
[root@MySQL-01 ~]# 

引數的意義:


--replicate=  :指定透過pt-table-checksum得到的表,這2個工具差不多都會一直用。 --databases= : 指定執行同步的資料庫,多個用逗號隔開。 --tables= :指定執行同步的表,多個用逗號隔開。 --sync-to-master :指定一個DSN,即從的IP,他會透過show processlist或show slave status 去自動的找主。
h=127.0.0.1 :伺服器地址,命令裡有2個ip,第一次出現的是Master的地址,第2次是Slave的地址。
u=root        :帳號。
p=123456 :密碼。 --print       :列印,但不執行命令。 --execute     :執行命令。

命令介紹完了,一起解釋下執行的效果:透過(--print)列印出來了修復資料的sql語句,可以手動的去從行執行,讓他們資料保持一致性。那能否直接執行?當然可以,透過(--execute)

[root@MySQL-01 ~]# pt-table-sync --replicate=yayun.checksums h=127.0.0.1,u=root,p=123456 h=192.168.0.20,u=root,p=123456 --execute

沒發現任何異常,然後檢查主從資料的一致性:

[root@MySQL-01 ~]# pt-table-checksum --nocheck-replication-filters  --no-check-binlog-format --replicate=yayun.checksums --databases=yayun --tables=t1 h=127.0.0.1,u=root,p=123456,P=3306 TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE 04-13T16:27:28 0 0 3 1 0 0.097 yayun.t1
[root@MySQL-01 ~]# 

主庫:


mysql> select * from t1; +----+-------+
| id | name  |
+----+-------+
| 1 | yayun |
| 2 | atlas |
| 3 | mysql |
+----+-------+ 3 rows in set (0.00 sec)

mysql> 

備庫:


mysql> select * from t1;
 +----+-------+ | id | name | +----+-------+
 | 1 | yayun |
 | 2 | atlas | 
| 3 | mysql | +----+-------+
 3 rows in set (0.00 sec)

mysql> 

OK,資料已經保持一致了。不過建議還是用--print 列印出來的好,這樣就可以知道那些資料有問題,可以人為的干預下。不然直接執行了,出現問題之後更不好處理。總之還是在處理之前做好資料的備份工作。

注意:要是表中沒有唯一索引或則主鍵則會報錯:

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

總結:

工具很給力,工作中常常在使用。注意使用該工具需要授權,一般SELECT, PROCESS, SUPER, REPLICATION SLAVE等許可權就已經足夠了。


參考資料:
http://www.cnblogs.com/gomysql/p/3662264.html

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29096438/viewspace-1826334/,如需轉載,請註明出處,否則將追究法律責任。

相關文章