pt-table-checksum检测不出主从差异处理

根据课堂上吴老师和各位同学提供的思路整理而成~
最近几版pt-table-checksum在binlog_format='row',且主从存在差异数据时,却检测不出主从差异。原因就是处理过程中主库没有SET binlog_format = 'STATEMENT',导致下面两个核心语句不是以statement格式记录,从库不会进行CRC32相关运算,主从永远一致~
# 查看pt版本
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --version
pt-table-checksum 3.0.4
[root@ZST2 ~]#
# pt-table-checksum 3.0.4检测不出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:41:15 0 0 8 1 0 0.257 replcrash.py_user
05-08T09:41:16 0 0 67740 5 0 1.056 replcrash.py_user_innodb

# 两个核心语句
REPLACE INTO `replcrash`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'replcrash', 'py_user', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `uid`, convert(`name` using utf8mb4), `add_time`, convert(`server_id` using utf8mb4), CONCAT(ISNULL(`name`), ISNULL(`add_time`), ISNULL(`server_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `replcrash`.`py_user` /*checksum table*/
UPDATE `replcrash`.`checksums` SET chunk_time = '0.004092', master_crc = '5abbd632', master_cnt = '8' WHERE db = 'replcrash' AND tbl = 'py_user' AND chunk = '1'
方法一:修改代码
# pt-table-checksum 3.0.4 只需注释掉第9335行和9364行,这层逻辑应该应用于任何情况下,因此不需要使用if判断
[root@ZST2 ~]# vim /usr/local/bin/pt-table-checksum
...
9335 #if ( $o->get('check-binlog-format') ) {
9336 # https://bugs.launchpad.net/per ... 19352
9337 # The tool shouldn't blindly attempt to change binlog_format;
9338 # instead, it should check if it's already set to STATEMENT.
9339 # This is becase starting with MySQL 5.1.29, changing the format
9340 # requires a SUPER user.
9341 if ( VersionParser->new($dbh) >= '5.1.5' ) {
9342 $sql = 'SELECT @@binlog_format';
9343 PTDEBUG && _d($dbh, $sql);
9344 my ($original_binlog_format) = $dbh->selectrow_array($sql);
9345 PTDEBUG && _d('Original binlog_format:', $original_binlog_format);
9346 if ( $original_binlog_format !~ /STATEMENT/i ) {
9347 $sql = q{/*!50108 SET @@binlog_format := 'STATEMENT'*/};
9348 eval {
9349 PTDEBUG && _d($dbh, $sql);
9350 $dbh->do($sql);
9351 };
9352 if ( $EVAL_ERROR ) {
9353 die "Failed to $sql: $EVAL_ERROR\n"
9354 . "This tool requires binlog_format=STATEMENT, "
9355 . "but the current binlog_format is set to "
9356 ."$original_binlog_format and an error occurred while "
9357 . "attempting to change it. If running MySQL 5.1.29 or newer, "
9358 . "setting binlog_format requires the SUPER privilege. "
9359 . "You will need to manually set binlog_format to 'STATEMENT' "
9360 . "before running this tool.\n";
9361 }
9362 }
9363 }
9364 #}
...

# 修改后检测出主从不一致
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:51:32 0 1 8 1 0 0.030 replcrash.py_user
05-08T09:51:33 0 0 67740 5 0 0.561 replcrash.py_user_innodb
[root@ZST2 ~]#
方法二、命令行带上--set-vars参考
详细说明参考:pt-table-checksum not detecting diffs
# pt-table-checksum 3.0.4不修改代码,带上--set-vars参数检测出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721 --set-vars binlog_format='statement'
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-10T09:25:01 0 1 11 1 0 0.024 replcrash.py_user
05-10T09:25:01 0 0 67740 5 0 0.695 replcrash.py_user_innodb
[root@ZST2 ~]#
善于思考,善于搜索,不要期盼别人把结果原封不动地送到嘴里~.~
吐槽一下,本来想将代码中的关键字加粗,编辑时显示正常,一发布就乱套了

0 个评论

要回复文章请先登录注册