成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

MySQL如何解決主從數(shù)據(jù)不一致問(wèn)題

IT那活兒 / 3427人閱讀
MySQL如何解決主從數(shù)據(jù)不一致問(wèn)題

點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了?。?！





背景介紹



兩臺(tái)MySQL,發(fā)生了種種種種，導(dǎo)致了兩個(gè)表的數(shù)據(jù)不一致，但是同步還在正常進(jìn)行，后來(lái)意識(shí)到這種問(wèn)題（可能之前skip啊，或者一開(kāi)始搭建的時(shí)候就是不一致的狀態(tài)），該如何修復(fù)呢？
我們可以來(lái)看下percona-toolkit這個(gè)工具是如何修復(fù)這種情況的。





環(huán)境搭建



1. 校驗(yàn)

  • 主庫(kù)：192.168.100.8 3306

  • 從庫(kù)：192.168.100.12 3305

  • MySQL version：MySQL-5.6.30

1）master 服務(wù)器安裝yum依賴包：
yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL
2）安裝percona-toolkit工具包：
wget http://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數(shù)據(jù)庫(kù)創(chuàng)建以及用戶授權(quán)：
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）進(jìn)行校驗(yàn) master上執(zhí)行：
pt-table-checksum --nocheck-binlog-format --nocheck-plan --
nocheck-replication-filters --replicate=pt.checksums --set-
vars innodb_lock_wait_timeout=120 --databases tmp -ubackup 
-pabc123 -h192.168.100.8 -P3306


  • -h -u -p -P -S -d 連接信息；
  • --nocheck-replication-filters 檢測(cè)中忽略mysql 配置參數(shù)binlog_ignore_db等；
  • --nocheck-binlog-format 不檢測(cè)日志格式；
  • --replicate 指定checksum 存儲(chǔ)的db和表， 如pt.checksum；
  • --chunk-size， --chunk-size-limit 用于指定檢測(cè)塊的大小，可控性更強(qiáng)；
  • --ignore-databases/tables/column 跳出指定元素的過(guò)濾；
  • --lock-wait-timeout innodb 鎖的超時(shí)設(shè)定， 默認(rèn)為1；
  • --max-load 設(shè)置最大并發(fā)連接數(shù)；
  • --replicate-check-only 只輸出數(shù)據(jù)不一致的信息；
  • --help。
2. 校驗(yàn)結(jié)果
A software update is available:

* The current version for Percona::Toolkit is 2.2.14.

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

  • 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ò)誤或警告或過(guò)大，則跳過(guò)塊的數(shù)目；

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

  • TABLE ：被檢查的表名。

注意：
要是在執(zhí)行命令的過(guò)程遇到找不到從服務(wù)器的錯(cuò)誤，默認(rèn)是通過(guò)show processlist 找到host的值或show slave hosts 找到host的值。
Diffs cannot be detected because no slaves were found. 
Please read the --recursion-method documentation for information.
1）關(guān)于--recursion-method參數(shù)的設(shè)置：
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 -ubackup -pabc123 --host=10.0.1.72 -P3306 --databases=HS_Order（校驗(yàn)）

[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自動(dòng)修復(fù)）

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)
在從庫(kù)的配置文件里加：report_host = 192.168.200.25 #設(shè)置成本地地址。
mysql> show slave hosts;
+-----------+----------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+----------------+------+-----------+--------------------------------------+
| 123305 | 192.168.100.12 | 3305 | 533306 | 9645c4a3-178a-11e6-ac5d-d4ae52d034a7 |
+-----------+----------------+------+-----------+--------------------------------------+
最后再執(zhí)行以上命令（多加--recursion-method=hosts 參數(shù)）：
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 -ubackup -pabc123 -h192.168.100.8 -P3306
先校驗(yàn)出哪些庫(kù)的表不同步，然后指定庫(kù)與表生成語(yǔ)句，采用第二種方法， 將生成的SQL語(yǔ)句在從庫(kù)執(zhí)行即可。
2）數(shù)據(jù)同步，復(fù)制，消除差異（Master服務(wù)器運(yùn)行） 要是有中文的則需要加上：--charset=utf8，防止亂碼。
i. 自動(dòng)消除差異（不推薦）：
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語(yǔ)句，人工干預(yù)到Slave庫(kù)執(zhí)行（推薦）：
[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 ：指定一個(gè)DSN，即從的IP，他會(huì)通過(guò)show processlist或show slave status 去自動(dòng)的找主；

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

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

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





解決方案




[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 -pgoufu -h192.168.100.8 -P3306

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T15:37:03 0 2 106683 4 0 0.588 HomeworkSitter_UAT.VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF
01-21T15:37:03 0 1 3 1 0 0.026 bbs.dz_common_session
由于其中一個(gè)表的數(shù)據(jù)較大，采用dump導(dǎo)出的方式在從庫(kù)上恢復(fù)。
1. 第一個(gè)執(zhí)行采用pt-table-sync方式恢復(fù)：
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. 第二個(gè)表采用導(dǎo)出導(dǎo)入的方式恢復(fù)：
pt-table-sync --print --sync-to-master h
=192.168.100.12,P=3305,u=goufu,p=abc123 --databases=bbs --
tables=dz_common_session
3. 同步完成后再校驗(yàn)：
[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_R
EF -ubackup -pabc123 -h192.168.100.8 -P3306 
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T16:50:19 0 0 106683 5 0 1.006 HomeworkSitter_UAT.VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF
01-21T16:50:19 0 0 3 1 0 0.013 bbs.dz_common_session






經(jīng)驗(yàn)總結(jié)



1. 采用replace into來(lái)修復(fù)主從不一致，必須保證被replace的表上有主鍵或唯一鍵，否則replace into退化成insert into，起不到修復(fù)的效果。
這種情況下pt-table-sync會(huì)采用其他校驗(yàn)和修復(fù)算法，但是效率非常低，例如對(duì)所有列的group by然后求count(*)(表一定要有主鍵！)。
2. 主從數(shù)據(jù)不一致需要通過(guò)replace into來(lái)修復(fù)，該sql語(yǔ)句必須是語(yǔ)句級(jí)。
pt-table-sync會(huì)把它發(fā)起的所有sql語(yǔ)句都設(shè)置為statement格式，而不管全局的binlog_format值。這在級(jí)聯(lián)A-B-C結(jié)構(gòu)中，也會(huì)遇到pt-table-checksum曾經(jīng)遇到的問(wèn)題，引起行格式的中繼庫(kù)的從庫(kù)卡庫(kù)是必然。
不過(guò)pt-table-sync默認(rèn)會(huì)無(wú)限遞歸的對(duì)從庫(kù)的binlog格式進(jìn)行檢查并警告。
3. 由于pt-table-sync每次只能修復(fù)一個(gè)表，所以如果修復(fù)的是父表，則可能導(dǎo)致子表數(shù)據(jù)連帶被修復(fù)，這可能會(huì)修復(fù)一個(gè)不一致而引入另一個(gè)不一致；如果表上有觸發(fā)器，也可能遇到同樣問(wèn)題。
所以在有觸發(fā)器和主外鍵約束的情況下要慎用。
pt-table-sync工具同樣也不歡迎主從異構(gòu)的結(jié)構(gòu)。pt-table-sync工具默認(rèn)會(huì)進(jìn)行先決條件的檢查。
4. pt-table-sync在修復(fù)過(guò)程中不能容忍從庫(kù)延遲，這正好與pt-table-checksum相反。
如果從庫(kù)延遲太多，pt-table-sync會(huì)長(zhǎng)期持有對(duì)chunk的for update鎖，然后等待從庫(kù)的master_pos_wait執(zhí)行完畢或超時(shí)。從庫(kù)延遲越大，等待過(guò)程就越長(zhǎng)，主庫(kù)加鎖的時(shí)間就越長(zhǎng)，對(duì)線上影響就越大。
因此要嚴(yán)格設(shè)置max-lag。
5. 對(duì)從庫(kù)數(shù)據(jù)的修復(fù)通常是在主庫(kù)執(zhí)行sql來(lái)同步到從庫(kù)。
因此，在有多個(gè)從庫(kù)時(shí)，修復(fù)某個(gè)從庫(kù)的數(shù)據(jù)實(shí)際會(huì)把修復(fù)語(yǔ)句同步到所有從庫(kù)。數(shù)據(jù)修復(fù)的代價(jià)取決于從庫(kù)與主庫(kù)不一致的程度，如果某從庫(kù)數(shù)據(jù)與主庫(kù)非常不一致。
舉例說(shuō)，這個(gè)從庫(kù)只有表結(jié)構(gòu)，那么需要把主庫(kù)的所有數(shù)據(jù)重新灌一遍，然后通過(guò)binlog同步，同時(shí)會(huì)傳遞到所有從庫(kù)。這會(huì)給線上帶來(lái)很大壓力，甚至拖垮集群。
正確的做法是：先用pt-table-checksum校驗(yàn)一遍，確定不一致的程度：如果不同步的很少，用pt-table-sync直接修復(fù)；否則，用備份先替換它，然后用pt-table-sync修復(fù)。
說(shuō)明：
這實(shí)際提供了一種對(duì)myisam備份的思路：如果僅有一個(gè)myisam的主庫(kù)，要為其增加從庫(kù)，則可以：先mysqldump出表結(jié)構(gòu)到從庫(kù)上，然后啟動(dòng)同步，然后用pt-table-sync來(lái)修復(fù)數(shù)據(jù)。



本文作者：王家福

本文來(lái)源：IT那活兒（上海新炬王翦團(tuán)隊(duì)）

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/129609.html

相關(guān)文章

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<