主从一致性检查和修复

环境说明

1
2
主: 192.168.1.96
从: 192.168.1.68

基本步骤

授权主从一致性检查和同步账号

主库:

1
2
mysql> GRANT update,insert,delete,SELECT,PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'geekwolf'@'192.168.1.%' IDENTIFIED BY 'geekwolf';
mysql> flush privileges;
创建检查信息表和dsns从库连接信息表(此处创建在test库)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> 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;
1
2
3
4
5
6
mysql> CREATE TABLE `dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
插入从库连接信息
1
mysql> insert into dsns values(1,1,'192.168.1.68,u=geekwolf,p=geekwolf,P=3306');
测试

主库

1
2
3
mysql> use test
 mysql> create table now(id int,name vachar(10));
 mysql> nsert into now values(1,'terry'),(2,'honey'),(3,'geekwolf');

从库

1
2
3
mysql> update test.now set name='john' where id=1;
 mysql> delete  from test.now where id=2;
 mysql> insert  into test.now values(4,'test');

检查库test数据是否一致并修复
1
2
3
4
5
6
7
8
[root@master ~]# pt-table-checksum h='192.168.1.96',u='geekwolf',p='geekwolf',P=3306 --databases test --nocheck-replication-filters --replicate=test.checksums --no-check-binlog-format 
Cannot connect to P=3306,h=192.168.1.78,p=...,u=geekwolf 
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 
10-30T14:56:32 0 0 1 1 0 0.015 test.dsns 
10-30T14:56:32 0 0 0 1 0 0.019 test.keyword_setting 
10-30T14:56:32 0 1 3 1 0 0.014 test.now 
10-30T14:56:32 0 0 0 1 0 0.020 test.searchkeyword 
10-30T14:56:32 0 0 0 1 0 0.012 test.sms
1
2
3
4
5
6
7
8
TS :完成检查的时间
ERRORS :检查时候发生错误和警告的数量
DIFFS :0表示一致,1表示不一致。当指定–no-replicate-check时,会一直为0,当指定–replicate-check-only会显示不同的信息
ROWS :表的行数
CHUNKS :被划分到表中的块的数目
SKIPPED :由于错误或警告或过大,则跳过块的数目
TIME :执行的时间
TABLE :被检查的表名

注释

1
2
3
4
5
6
7
8
--check-replication-filters 检查复制中是否设置了过滤条件,如果设置了,程序将退出;可以使用--no-check-replication-filters禁用
--no-check-binlog-format 不对binlog的格式进行检查
--replicate-check-only  只显示主从不一致部分,此参数不会生成新的checksums数据,只会根据checksums表已经有的数据来显示
--databases test   指定检查的库,多库用逗号隔开
--tables    now   指定检查的表
--no-check-binlog-format 不对binlog的格式进行检查
具体主从一致性检查涉及影响性能的参数:--max-load、--chunk-size、--chunk-size-limit等参考官网介绍
http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html

将主库一致性同步到从库

将从库192.168.1.68的test库的now表与主库192.168.1.96同步,并打印出相关SQL

1
2
3
4
5
[root@master ~]# pt-table-sync --replicate=test.checksums --databases=test --tables=now h=192.168.1.96,P=3306,u=geekwolf,p=geekwolf h=192.168.1.68,P=3306,u=geekwolf,p=geekwolf --execute --print
REPLACE INTO `test`.`now`(`id`, `name`) VALUES ('1', 'terry') /*percona-toolkit src_db:test src_tbl:now src_dsn:P=3306,h=192.168.1.96,p=...,u=geekwolf dst_db:test dst_tbl:now dst_dsn:P=3306,h=192.168.1.68,p=...,u=geekwolf lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:7279 user:root host:master*/;
REPLACE INTO `test`.`now`(`id`, `name`) VALUES ('2', 'honey') /*percona-toolkit src_db:test src_tbl:now src_dsn:P=3306,h=192.168.1.96,p=...,u=geekwolf dst_db:test dst_tbl:now dst_dsn:P=3306,h=192.168.1.68,p=...,u=geekwolf lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:7279 user:root host:master*/;
DELETE FROM `test`.`now` WHERE `id`='4' LIMIT 1 /*percona-toolkit src_db:test src_tbl:now src_dsn:P=3306,h=192.168.1.96,p=...,u=geekwolf dst_db:test dst_tbl:now dst_dsn:P=3306,h=192.168.1.68,p=...,u=geekwolf lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:7282 user:root host:master*/;

另外,只显示同步时要进行的SQL操作,并不执行

1
pt-table-sync --print --sync-to-master --databases test h=192.168.1.68,P=3306,u=geekwolf,p=geekwolf

注意:
在规范的操作中,从库是要做只读操作的(set global read_only=1 或者my.cnf添加read_only=1 只对普通用户有效);但如果从库做了写操作造成的数据差异,在主库上同步数据时会和主库保持一致,从库多余的会被删除,变化的会被更新,没有的会被插入,通过上面的命令就可以看到相关的SQL操作

推荐工具

Percona-toolkit
Maatkit

参考

pt-table-checksum原理分析 http://nettedfish.sinaapp.com/blog/2013/06/04/check-replication-consistency-by-pt-table-checksum/
http://www.cnblogs.com/zhoujinyi/archive/2013/05/09/3067045.html

坚持原创分享,您的支持将鼓励我继续创作