主库: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