pt-table-checksum

Master:

#下载,安装软件
wget http://www.percona.com/downloads/percona-toolkit/2.2.1/percona-toolkit-2.2.1.tar.gz
tar -zxf percona-toolkit-2.2.1.tar.gz
cd percona-toolkit-2.2.1
perl Makefile.PL
make
make install

Master:

#给工具分配用户和权限
GRANT update,insert,delete,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums’@'localhost’ IDENTIFIED BY ‘checksums’;
#建立工具要使用的数据库
use test;

CREATE TABLE 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,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;

#用于记录slave连接方式
CREATE TABLE dsns (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);

#指定slave连接方式,多个slave的话插入多条
insert into dsns values(1,1,’h=192.168.1.6,u=checksums,p=checksums,P=3311′);

Slave:

#建立工具使用的数据库

use test;

CREATE TABLE 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,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;
GRANT update,insert,delete,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums’@'masterip’ IDENTIFIED BY ‘checksums’;

Master:

在master上执行命令:

pt-table-checksum h=’localhost’,u=’checksums’,p=’checksums’ \
-P 3307\
–nocheck-replication-filters \replicate=test.checksums \
–no-check-binlog-format \
-d zixia_user \
–socket /data/mysql/3307/mysql.sock –empty-replicate-table

Slave:

#在slave上看不一致的结果
SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM test.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl;

目录