MySQL主从数据不一致的情况进行校验并继续同步

布满荆棘的人生 2022-05-18 07:23 193阅读 0赞

主库：192.168.10.10

从库：192.168.10.5

可连接主从的服务器（本实例以192.168.10.10为例）

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

yum install percona-toolkit

主库  
mysql -u  -p  
Create database pt CHARACTER SET utf8;  
GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON \*.\* TO 'ptuser'@'192.168.10.10' identified by 'ptuser.123';  
GRANT ALL ON pt.\* TO 'ptuser'@'192.168.10.10' IDENTIFIED BY 'ptuser.123';  
flush privileges;

\#从库  
\#mysql -u  -p  
\#Create database pt CHARACTER SET utf8;  
\#GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON \*.\* TO 'ptuser'@'192.168.10.10' \#identified by 'ptuser.123';  
\#GRANT ALL ON pt.\* TO 'ptuser'@'192.168.10.10' IDENTIFIED BY 'ptuser.123';  
\#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;

报错：

07-31T15:41:28 install\_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted.  
Compilation failed in require at (eval 23) line 3.

at /usr/bin/pt-table-checksum line 1623  
使用命令：ln -sv /usr/lib64/mysql/libmysqlclient.so.16 /lib64/建立链接

报错：

Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file  
Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file  
07-31T15:56:53 DBI connect(';host=192.168.10.10;port=3306;mysql\_read\_default\_group=client','ptuser',...) failed: Can't initialize character set utf8mb4 (path: /usr/share/mysql/charsets/) at /usr/bin/pt-table-checksum line 1623

使用命令：vim /usr/share/mysql/charsets/Index.xml修改字符集

检查命令：

\#pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --databases=db1,db2,db3 --replicate=pt.checksums h=192.168.10.10,u=ptuser,p=ptuser.123,P=3306

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums h=192.168.10.10,u=ptuser,p=ptuser.123,P=3306 --databases=db1 --tables=tb1,tb2,tb3

修复命令：

pt-table-sync --execute --replicate pt.checksums --sync-to-master h=192.168.10.5,P=3306,u=ptuser,p=ptuser.123 --databases=db1 --tables=tb1,tb2,tb3

(修复命令中可使用--print代替--execute，只打印不执行）

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

**pt-table-sync** synchronizes data efficiently between MySQL tables.

This tool changes data, so for maximum safety, you should back up your data before using it. When synchronizing a server that is a replication slave with the [`--replicate`][--replicate] or [`--sync-to-master`][--sync-to-master] methods, it **always** makes the changes on the replication master, **never** the replication slave directly. This is in general the only safe way to bring a replica back in sync with its master; changes to the replica are usually the source of the problems in the first place. However, the changes it makes on the master should be no-op changes that set the data to their current values, and actually affect only the replica.

Sync db.tbl on host1 to host2:

pt-table-sync --execute h=host1,D=db,t=tbl h=host2

Sync all tables on host1 to host2 and host3:

pt-table-sync --execute host1 host2 host3

Make slave1 have the same data as its replication master:

pt-table-sync --execute --sync-to-master slave1

Resolve differences that pt-table-checksum found on all slaves of master1:

pt-table-sync --execute --replicate test.checksum master1

Same as above but only resolve differences on slave1:

pt-table-sync --execute --replicate test.checksum \
      --sync-to-master slave1

Sync master2 in a master-master replication configuration, where master2’s copy of db.tbl is known or suspected to be incorrect:

pt-table-sync --execute --sync-to-master h=master2,D=db,t=tbl

Note that in the master-master configuration, the following will NOT do what you want, because it will make changes directly on master2, which will then flow through replication and change master1’s data:

# Don't do this in a master-master setup!
    pt-table-sync --execute h=master1,D=db,t=tbl master2

[--replicate]: https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html?spm=a2c4e.11153940.blogcont280417.11.20fe912f658Syo#cmdoption-pt-table-sync--replicate
[--sync-to-master]: https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html?spm=a2c4e.11153940.blogcont280417.11.20fe912f658Syo#cmdoption-pt-table-sync--sync-to-master