背景介紹
環境搭建
主庫:192.168.100.8 3306
從庫:192.168.100.12 3305
MySQL version:MySQL-5.6.30
yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL
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
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;
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
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 :完成檢查的時間;
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.
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(校驗)
[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)
mysql> show slave hosts;
+-----------+----------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+----------------+------+-----------+--------------------------------------+
| 123305 | 192.168.100.12 | 3305 | 533306 | 9645c4a3-178a-11e6-ac5d-d4ae52d034a7 |
+-----------+----------------+------+-----------+--------------------------------------+
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
pt-table-sync --print --execute --sync-to-master h
=192.168.100.87,P=3341,u=backup,p=abc123 --databases=test
--tables=goufu
[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 :執行命令。
解決方案
[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
pt-table-sync --print --sync-to-master h
=192.168.100.12,P=3305,u=goufu,p=abc123 --databases=bbs --tables=dz_common_session
pt-table-sync --print --sync-to-master h
=192.168.100.12,P=3305,u=goufu,p=abc123 --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_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
經驗總結
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/129609.html
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4100·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3597·2023-01-11 13:20