閱讀469 返回首頁    go 小米 go 小米5

MySQL如何對主從數據不一致的情況進行校驗並繼續同步

兩台MySQL,發生了種種種種，導致了兩個表的數據不一致，但是同步還在正常進行，後來意識到這種問題（可能之前skip啊，或者一開始搭建的時候就是不一致的狀態），該如何修複呢？

我們看可以來看下percona-toolkit這個工具是如何修複這種情況的

校驗：
主庫：192.168.100.8 3306
從庫：192.168.100.12 3305
MySQL version：MySQL-5.6.30

1.master 服務器安裝yum依賴包

yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL

2.安裝percona-toolkit工具包

wget https://www.percona.com/get/percona-toolkit.tar.gz
tar zxf percona-toolkit-2.2.13.tar.gz
cd percona-toolkit-2.2.13
perl Makefile.PL
make && make install

3.master與slave數據庫創建以及用戶授權

Create database pt CHARACTER SET utf8;
GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'backup'@'192.168.100.8' identified by 'abc123';
GRANT ALL ON pt.* TO 'backup'@'192.168.100.8' IDENTIFIED BY 'abc123';
flush privileges;



use pt;
CREATE TABLE IF NOT EXISTS 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;

4.進行校驗 master上執行

pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases tmp -u'backup' -p'abc123' -h192.168.100.8 -P3306

#-h -u -p -P -S -d 連接信息 
#--nocheck-replication-filters 檢測中忽略mysql 配置參數binlog_ignore_db等。
#--nocheck-binlog-format 不檢測日誌格式 
#--replicate 指定checksum 存儲的db和表， 如pt.checksum 
# --chunk-size， --chunk-size-limit 用於指定檢測塊的大小。 可控性更強 
# --ignore-databases/tables/column 跳出指定元素的過濾 
# --lock-wait-timeout innodb 鎖的超時設定， 默認為1 
# --max-load 設置最大並發連接數 
# --replicate-check-only 隻輸出數據不一致的信息。 
# --help 

校驗結果

# A software update is available: 
# * The current version for Percona::Toolkit is 2.2.14. 

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 
07-21T12:21:59 0 0 3523 4 0 0.385 tmp.COMM_REGION 
07-21T12:21:59 0 0 0 1 0 0.011 tmp.UCT_USER
07-21T12:22:04 0 0 115020 1 0 5.007 tmp.UCT_USER_1 
07-21T12:22:04 0 0 0 1 0 0.017 tmp.UCT_USER_2 
07-21T12:22:04 0 0 710 1 0 0.009 tmp.VOX_APPLICATION_MICROPHONE
07-21T12:22:04 0 0 3778 1 0 0.084 tmp.VOX_CLASS

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

注意：
要是在執行命令的過程遇到找不到從服務器的錯誤： 

Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.

默認是通過show processlist 找到host的值或show slave hosts 找到host的值。

關於--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

pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120  --recursion-method dsn=h=10.0.1.73,D=stats,t=dsns -u'backup' -p'abc123' --host=10.0.1.72 -P3306 --databases=HS_Order（校驗）

[root@goufu data]# pt-table-sync --print --sync-to-master h=10.0.1.73,P=3306,u=backup,p='abc123' --replicate pt.checksums --databases=HS_Order --tables=VOX_REWARD_ORDER>zzz.sql （也可直接加--execute自動修複）

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 123305 | | 3305 | 533306 | 9645c4a3-178a-11e6-ac5d-d4ae52d034a7 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

在從庫的配置文件裏加： 
report_host = 192.168.200.25 #設置成本地地址

mysql> show slave hosts; 
+-----------+----------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+----------------+------+-----------+--------------------------------------+
| 123305 | 192.168.100.12 | 3305 | 533306 | 9645c4a3-178a-11e6-ac5d-d4ae52d034a7 |
+-----------+----------------+------+-----------+--------------------------------------+

最後再執行以上命令（多加--recursion-method=hosts 參數）： 

pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method=hosts --databases tmp -u'backup' -p'abc123' -h192.168.100.8 -P3306

先校驗出哪些庫的表不同步，然後指定庫與表生成語句，采用第二種方法， 將生成的SQL語句在從庫執行即可。
5.數據同步，複製，消除差異（Master服務器運行） 要是有中文的則需要加上：--charset=utf8，防止亂碼。

i.自動消除差異（不推薦） 

pt-table-sync --print --execute --sync-to-master h=192.168.100.87,P=3341,u=backup,p='abc123' --databases=test --tables=goufu

ii.打印出sql語句，人工幹預到Slave庫執行（推薦） 

[root@goufu data]# pt-table-sync --print --sync-to-master h=192.168.100.87,P=3341,u=goufu,p='abc123' --databases=test --tables=goufu
REPLACE INTO `test`.`goufu`(`a`) VALUES ('1') /*percona-toolkit src_db:test src_tbl:goufu
src_dsn:P=3340,h=192.168.100.87,p=...,u=goufu dst_db:test dst_tbl:goufu dst_dsn:P=3341,h=192.168.100.87,p=...,u=backup lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:7020 user:root host:VM-TEST-87*/; 

pt-table-sync --print --sync-to-master h=10.1.1.7,P=3306,u=backup,p='abc123'--replicate pt.checksums

#--sync-to-master ：指定一個DSN，即從的IP，他會通過show processlist或show slave status 去自動的找主。
#--replicate ：指定通過pt-table-checksum得到的表，這2個工具差不多都會一直用。 
#--print ：打印，但不執行命令。 
#--execute ：執行命令。


7.實際解決：

[root@goufu ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method=hosts --databases bbs,HomeworkSitter_UAT --tables=dz_common_session,VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF -u 'backup' -p'goufu' -h192.168.100.8 -P3306

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
07-21T15:37:03 0 2 106683 4 0 0.588 HomeworkSitter_UAT.VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF
07-21T15:37:03 0 1 3 1 0 0.026 bbs.dz_common_session

由於其中一個表的數據較大，采用dump導出的方式在從庫上恢複
1.第一個執行采用pt-table-sync方式恢複

pt-table-sync --print --sync-to-master h=192.168.100.12,P=3305,u=goufu,p='abc123' --databases=bbs --tables=dz_common_session

2.第二個表采用導出導入的方式恢複

pt-table-sync --print --sync-to-master h=192.168.100.12,P=3305,u=goufu,p='Ll2g$26Dqm' --databases=bbs --tables=dz_common_session

同步完成後再校驗：

[root@goufu ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method=hosts --databases bbs,HomeworkSitter_UAT --tables=dz_common_session,VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF -u'backup' -p'abc123' -h192.168.100.8 -P3306
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
07-21T16:50:19 0 0 106683 5 0 1.006 HomeworkSitter_UAT.VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF
07-21T16:50:19 0 0 3 1 0 0.013 bbs.dz_common_session

注意事項

1.采用replace into來修複主從不一致，必須保證被replace的表上有主鍵或唯一鍵，否則replace into退化成insert into，起不到修複的效果。這種情況下pt-table-sync會采用其他校驗和修複算法，但是效率非常低，例如對所有列的group by然後求count(*)(表一定要有主鍵！)。

2.主從數據不一致需要通過replace into來修複，該sql語句必須是語句級。pt-table-sync會把它發起的所有sql語句都設置為statement格式，而不管全局的binlog_format值。這在級聯A-B-C結構中，也會遇到pt-table-checksum曾經遇到的問題，引起行格式的中繼庫的從庫卡庫是必然。不過pt-table-sync默認會無限遞歸的對從庫的binlog格式進行檢查並警告。

3.由於pt-table-sync每次隻能修複一個表，所以如果修複的是父表，則可能導致子表數據連帶被修複，這可能會修複一個不一致而引入另一個不一致；如果表上有觸發器，也可能遇到同樣問題。所以在有觸發器和主外鍵約束的情況下要慎用。pt-table-sync工具同樣也不歡迎主從異構的結構。pt-table-sync工具默認會進行先決條件的檢查。

4.pt-table-sync在修複過程中不能容忍從庫延遲，這正好與pt-table-checksum相反。如果從庫延遲太多，pt-table-sync會長期持有對chunk的for update鎖，然後等待從庫的master_pos_wait執行完畢或超時。從庫延遲越大，等待過程就越長，主庫加鎖的時間就越長，對線上影響就越大。因此要嚴格設置max-lag。

5.對從庫數據的修複通常是在主庫執行sql來同步到從庫。因此，在有多個從庫時，修複某個從庫的數據實際會把修複語句同步到所有從庫。數據修複的代價取決於從庫與主庫不一致的程度，如果某從庫數據與主庫非常不一致，舉例說，這個從庫隻有表結構，那麼需要把主庫的所有數據重新灌一遍，然後通過binlog同步，同時會傳遞到所有從庫。這會給線上帶來很大壓力，甚至拖垮集群。正確的做法是，先用pt-table-checksum校驗一遍，確定不一致的程度：如果不同步的很少，用pt-table-sync直接修複；否則，用備份先替換它，然後用pt-table-sync修複。 說明： 這實際提供了一種對myisam備份的思路：如果僅有一個myisam的主庫，要為其增加從庫，則可以：先mysqldump出表結構到從庫上，然後啟動同步，然後用pt-table-sync來修複數據。

最後更新：2017-07-23 20:42:29

  上一篇：go  我的考駕照之路
  下一篇：go  ubuntu+docker+docker-compose+intellij idea 部署java web項目