一. 参考
http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html
pt-table-checksum是一个在线验证主从数据一致性的工具,主要用于以下场景:
1. 数据迁移前后,进行数据一致性检查
2. 当主从复制出现问题,待修复完成后,对主从数据进行一致性检查
3. 把从库当成主库,进行数据更新,产生了”脏数据”
4. 定期校验
下载,安装:
- 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
|—-slave1
四. 临时校验(端口3306)
- 准备工作:
- 1 在master 端(M)授权
- GRANT update,insert,delete,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums’@’localhost′ IDENTIFIED BY‘checksums’;
CREATE TABLE test.checksums ( ==>工具默认创建percona.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; CREATE TABLE `test.dsns` ( ==>用于记录slave连接方式 `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ); insert into dsns values(1,1,'h=slave的IP,u=checksums,p=checksums,P=3306'); ==>指定slave连接方式,多个slave的话插入多条
- 在slave端
- GRANT update,insert,delete,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums’@’master IP′ IDENTIFIEDBY ‘checksums’;
CREATE TABLE test.checksums ( ==>工具默认创建percona.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; CREATE TABLE `test.dsns` ( ==>用于记录slave连接方式 `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ); insert into dsns values(1,1,'h=slave的IP,u=checksums,p=checksumsk,P=3306'); ==>指定slave连接方式,多个slave的话插入多条
- 在Master 端pt-table-checksum h='localhost',u='checksums',p='checksums',P=3306 -d DBManage --nocheck-replication-filters --replicate=test.checksums --no-check-binlog-format
- PS: -d 只检查哪个库 --no-check-binlog-format 忽略检查binlog格式,否则会报错,默认回去检查statement模式 一般我们都用row模式
检查:
- 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; 查看是否一致
一. 参考
http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html
pt-table-checksum是一个在线验证主从数据一致性的工具,主要用于以下场景:
1. 数据迁移前后,进行数据一致性检查
2. 当主从复制出现问题,待修复完成后,对主从数据进行一致性检查
3. 把从库当成主库,进行数据更新,产生了”脏数据”
4. 定期校验
下载,安装:
- 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
|—-slave1
四. 临时校验(端口3306)
- 准备工作:
- 1 在master 端(M)授权
- GRANT update,insert,delete,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums’@’localhost′ IDENTIFIED BY‘checksums’;
CREATE TABLE test.checksums ( ==>工具默认创建percona.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; CREATE TABLE `test.dsns` ( ==>用于记录slave连接方式 `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ); insert into dsns values(1,1,'h=slave的IP,u=checksums,p=checksums,P=3306'); ==>指定slave连接方式,多个slave的话插入多条
- 在slave端
- GRANT update,insert,delete,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums’@’master IP′ IDENTIFIEDBY ‘checksums’;
CREATE TABLE test.checksums ( ==>工具默认创建percona.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; CREATE TABLE `test.dsns` ( ==>用于记录slave连接方式 `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ); insert into dsns values(1,1,'h=slave的IP,u=checksums,p=checksumsk,P=3306'); ==>指定slave连接方式,多个slave的话插入多条
- 在Master 端pt-table-checksum h='localhost',u='checksums',p='checksums',P=3306 -d DBManage --nocheck-replication-filters --replicate=test.checksums --no-check-binlog-format
- PS: -d 只检查哪个库 --no-check-binlog-format 忽略检查binlog格式,否则会报错,默认回去检查statement模式 一般我们都用row模式
检查:
- 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; 查看是否一致