pt-table-checksum 主从数据一致性验证

原创

tiger2013 2014-04-20 12:20:25 博主文章分类：mysql 管理 ©著作权

©著作权归作者所有：来自51CTO博客作者tiger2013的原创作品，谢绝转载，否则将追究法律责任

创建用户checksums,192.168.250.249为当前写库

[root@mysqldbmaster percona-toolkit-2.1.1]# mysql -uroot -p

1.授权

mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO'checksums'@localhost IDENTIFIED BY 'checksums'; #创建主库的检测账户

mysql> flush privileges;

授权原因：

select	查看所有库的表,原理可加 explain选项查看
process	show processlist
super	set binlog_format='statement'
replication slave	show slave hosts

CREATE TABLE `checksums` (

`db` char(64) NOT NULL,

`tbl` char(64) NOT NULL,

`chunk` int(11) NOT NULL,

`chunk_time` float DEFAULT NULL,

`chunk_index` varchar(200) DEFAULT NULL,

`lower_boundary` text,

`upper_boundary` text,

`this_crc` char(40) NOT NULL,

`this_cnt` int(11) NOT NULL,

`master_crc` char(40) DEFAULT NULL,

`master_cnt` int(11) DEFAULT NULL,

`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`db`,`tbl`,`chunk`),

KEY `ts_db_tbl` (`ts`,`db`,`tbl`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.主库执行

[root@mysqldbmaster percona-toolkit-2.1.1]# pt-table-checksum h=localhost,u='checksums',p='checksums',P=3306 -d jforum --nocheck-replication-filters --create-replicate-table --replicate=test.checksums ;

结果显示：

Cannot connect to P=3306,h=192.168.1.14,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.12,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.41,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.13,p=...,u=checksums

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

04-20T12:02:52 0 0 1 1 0 0.004 jforum.heartbeat

04-20T12:02:52 0 0 0 1 0 0.003 jforum.jforum_api

04-20T12:02:52 0 0 0 1 0 0.003 jforum.jforum_attach

04-20T12:02:52 0 0 0 1 0 0.003 jforum.jforum_attach_desc

04-20T12:02:52 0 0 0 1 0 0.003 jforum.jforum_attach_quota

04-20T12:02:52 0 0 14 1 0 0.003 jforum.jforum_banlist

04-20T12:02:52 0 0 0 1 0 0.003 jforum.jforum_banner

04-20T12:02:52 0 0 0 1 0 0.003 jforum.jforum_bookmarks

04-20T12:02:52 0 0 4 1 0 0.003 jforum.jforum_categories

04-20T12:02:52 0 0 2 1 0 0.003 jforum.jforum_config

04-20T12:02:52 0 0 0 1 0 0.004 jforum.jforum_extension_groups

04-20T12:02:52 0 0 0 1 0 0.003 jforum.jforum_extensions

04-20T12:02:52 0 0 12 1 0 0.004 jforum.jforum_forums

04-20T12:02:52 0 0 0 1 0 0.003 jforum.jforum_forums_watch

04-20T12:02:52 0 0 7 1 0 0.004 jforum.jforum_groups

04-20T12:02:52 0 0 3 1 0 0.004 jforum.jforum_karma

04-20T12:02:52 0 0 0 1 0 0.003 jforum.jforum_mail_integration

04-20T12:02:53 0 0 13532 4 0 0.293 jforum.jforum_moderation_log

04-20T12:02:53 0 0 169206 5 0 0.725 jforum.jforum_posts

04-20T12:02:54 0 0 169206 1 0 0.370 jforum.jforum_posts_text

04-20T12:02:54 0 0 0 1 0 0.002 jforum.jforum_privmsgs

04-20T12:02:54 0 0 0 1 0 0.002 jforum.jforum_privmsgs_text

04-20T12:02:54 0 0 0 1 0 0.001 jforum.jforum_quota_limit

04-20T12:02:54 0 0 0 1 0 0.001 jforum.jforum_ranks

04-20T12:02:54 0 0 366 1 0 0.002 jforum.jforum_role_values

04-20T12:02:54 0 0 95 1 0 0.002 jforum.jforum_roles

04-20T12:02:54 0 0 134427 1 0 0.225 jforum.jforum_sessions

04-20T12:02:54 0 0 31 1 0 0.002 jforum.jforum_smilies

04-20T12:02:54 0 0 0 1 0 0.002 jforum.jforum_themes

04-20T12:02:54 0 0 20742 1 0 0.050 jforum.jforum_topics

04-20T12:02:54 0 0 54249 1 0 0.043 jforum.jforum_topics_watch

04-20T12:02:54 0 0 135261 1 0 0.070 jforum.jforum_user_groups

04-20T12:02:55 0 0 135259 1 0 0.859 jforum.jforum_users

04-20T12:02:55 0 0 185 1 0 0.002 jforum.jforum_vote_desc

04-20T12:02:55 0 0 707 1 0 0.002 jforum.jforum_vote_results

04-20T12:02:55 0 0 6497 1 0 0.030 jforum.jforum_vote_voters

04-20T12:02:55 0 0 0 1 0 0.002 jforum.jforum_words

而且到从库看还造成了主从同步不一致，没有在test建表。解决办法就是在test库里建立checksums那张表。

继续：

报错

[root@mysqldbmaster ~]# pt-table-checksum h=localhost,u='checksums',p='checksums',P=3306 -d jforum --nocheck-replication-filters --create-replicate-table --replicate=jforum.checksums ;

Cannot connect to P=3306,h=192.168.1.13,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.12,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.14,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.41,p=...,u=checksums

04-20T13:35:51 User does not have all privileges on --replicate table `jforum`.`checksums`.

mysql>

GRANT SELECT,insert, UPDATE, DELETE, CREATE, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'localhost' IDENTIFIED BY 'checksums';

还是报错：

[root@mysqldbmaster ~]# pt-table-checksum u='checksums',p='checksums',P=3306 -d jforum --nocheck-replication-filters --create-replicate-table --replicate=jforum.checksums ;

Cannot connect to P=3306,h=192.168.1.13,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.12,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.14,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.41,p=...,u=checksums

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

04-20T14:09:12 0 0 1 1 0 0.003 jforum.heartbeat

04-20T14:09:12 0 0 0 1 0 0.002 jforum.jforum_api

04-20T14:09:12 0 0 0 1 0 0.002 jforum.jforum_attach

04-20T14:09:12 0 0 0 1 0 0.002 jforum.jforum_attach_desc

04-20T14:09:12 0 0 0 1 0 0.002 jforum.jforum_attach_quota

04-20T14:09:12 0 0 14 1 0 0.002 jforum.jforum_banlist

04-20T14:09:12 0 0 0 1 0 0.002 jforum.jforum_banner

尝试办法：

在从库上执行：

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.1.40' IDENTIFIED BY 'checksums';

在主库上建表：

CREATE TABLE dsns ( id int(11) NOT NULL primary key AUTO_INCREMENT, parent_id int(11) DEFAULT NULL,dsn varchar(255) NOT NULL);

INSERT INTO dsns (parent_id,dsn) values(1, "h=192.168.1.41,u=checksums,p=checksums,P=3306");

[root@mysqldbmaster ~]# pt-table-checksum u='checksums',p='checksums',P=3306 -d jforum --nocheck-replication-filters --create-replicate-table --replicate=jforum.checksums --replicate-check-only ;

Cannot connect to P=3306,h=192.168.1.13,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.12,p=...,u=checksums

Cannot connect to P=3306,h=192.168.1.14,p=...,u=checksums

Differences on 1.41 standby

TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY

jforum.jforum_topics 1 0 1

jforum.jforum_users 1 0 1

注释的意思：

TS            ：完成检查的时间。
ERRORS        ：检查时候发生错误和警告的数量。
DIFFS         ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS          ：表的行数。
CHUNKS        ：被划分到表中的块的数目。
SKIPPED       ：由于错误或警告或过大，则跳过块的数目。
TIME          ：执行的时间。
TABLE         ：被检查的表名。

从库上执行：

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO checksums@'192.168.1.40' identified by 'checksums';

二：sync：

注意是先checksum，然后再sync；

[root@localhost mysql]# pt-table-sync --print --replicate jforum.checksums --sync-to-master --databases=jforum,--tables=jforum_users, h=192.168.1.41,P=3306,u=checksums,p=checksums