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

主库：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 or --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