几个月前写过pt-table-checksum 3.0.4检测不出主从差异数据，当时的解决方案是使用旧版本，另一个挫方法是自行设置binlog_format='STATEMENT'。现在已经发布到3.0.9版本，结果又遇到相同的坑~
最近几版pt-table-checksum(已核实3.0.4和3.0.9有问题)在binlog_format='row'，且主从存在差异数据时，却检测不出主从差异。原因就是处理过程中主上没有SET @@binlog_format := 'STATEMENT'，导致下面两个核心语句不是以statement格式记录，从库不会进行CRC32相关运算，主从永远一致~
# 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= --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
-08T09:: 0.257 replcrash.py_user
-08T09:: 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', '', 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)), , )), ) AS crc FROM `replcrash`.`py_user` /*checksum table*/
UPDATE `replcrash`.`checksums` SET chunk_time = '0.004092', master_crc = '5abbd632', master_cnt = '' WHERE db = 'replcrash' AND tbl = 'py_user' AND chunk = ''
通过对比旧版本(2.2.3)的/usr/local/bin/pt-table-checksum代码，发现只需稍微调整代码即可正常使用
# pt-table-checksum 3.0. 只需注释掉第9335行和9364行，这层逻辑应该应用于任何情况下，因此不需要使用if判断
[root@ZST2 ~]# vim /usr/local/bin/pt-table-checksum
...
#if ( $o->get('check-binlog-format') ) {
# https://bugs.launchpad.net/percona-toolkit/+bug/919352
# The tool shouldn't blindly attempt to change binlog_format;
# instead, it should check if it's already set to STATEMENT.
# This is becase starting with MySQL 5.1., changing the format
# requires a SUPER user.
if ( VersionParser->new($dbh) >= '5.1.5' ) {
$sql = 'SELECT @@binlog_format';
PTDEBUG && _d($dbh, $sql);
my ($original_binlog_format) = $dbh->selectrow_array($sql);
PTDEBUG && _d('Original binlog_format:', $original_binlog_format);
if ( $original_binlog_format !~ /STATEMENT/i ) {
$sql = q{/*!50108 SET @@binlog_format := 'STATEMENT'*/};
eval {
PTDEBUG && _d($dbh, $sql);
$dbh->do($sql);
};
if ( $EVAL_ERROR ) {
die "Failed to $sql: $EVAL_ERROR\n"
. "This tool requires binlog_format=STATEMENT, "
. "but the current binlog_format is set to "
."$original_binlog_format and an error occurred while "
. "attempting to change it. If running MySQL 5.1.29 or newer, "
. "setting binlog_format requires the SUPER privilege. "
. "You will need to manually set binlog_format to 'STATEMENT' "
. "before running this tool.\n";
}
}
}
#}
... # 查看pt版本
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --version
pt-table-checksum 3.0.
[root@ZST2 ~]#
# 修改后检测出主从不一致
[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= --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
-08T09:: 0.030 replcrash.py_user
-08T09:: 0.561 replcrash.py_user_innodb
[root@ZST2 ~]# # general-log信息
[root@ZST1 ~]# cat /data/mysql/mysql3306/data/ZST1.log |more
...
--08T01::.315064Z Query SHOW VARIABLES LIKE 'version%'
--08T01::.318337Z Query SHOW ENGINES
--08T01::.319229Z Query SHOW VARIABLES LIKE 'innodb_version'
--08T01::.322518Z Query SELECT @@binlog_format
--08T01::.323019Z Query /*!50108 SET @@binlog_format := 'STATEMENT'*/
--08T01::.323453Z Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
--08T01::.324012Z Query SHOW VARIABLES LIKE 'wsrep_on'
--08T01::.327830Z Query SELECT @@SERVER_ID
--08T01::.328416Z Query SHOW SLAVE HOSTS
--08T01::.344178Z Query SHOW VARIABLES LIKE 'wsrep_on'
--08T01::.347571Z Query SELECT @@SERVER_ID
--08T01::.351692Z Query SHOW VARIABLES LIKE 'wsrep_on'
--08T01::.355034Z Query SELECT @@SERVER_ID
--08T01::.359543Z Query SHOW DATABASES LIKE 'replcrash'
--08T01::.360406Z Query CREATE DATABASE IF NOT EXISTS `replcrash` /* pt-table-checksum */
--08T01::.361538Z Query USE `replcrash`
--08T01::.362069Z Query SHOW TABLES FROM `replcrash` LIKE 'checksums'
--08T01::.364738Z Query CREATE TABLE IF NOT EXISTS `replcrash`.`checksums` (
db CHAR() NOT NULL,
tbl CHAR() NOT NULL,
chunk INT NOT NULL,
chunk_time FLOAT NULL,
chunk_index VARCHAR() NULL,
lower_boundary TEXT NULL,
upper_boundary TEXT NULL,
this_crc CHAR() NOT NULL,
this_cnt INT NOT NULL,
master_crc CHAR() NULL,
master_cnt INT NULL,
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--08T01::.920625Z Query SHOW GLOBAL STATUS LIKE 'Threads_running'
--08T01::.924636Z Query SELECT CONCAT(@@hostname, @@port)
...
如果遇到类似问题，建议开启general_log，查看处理过程，再核验到底什么原因导致检测不出主从差异数据(･ω･)
09:21 2018/5/10 补充，其实可以在命令行带上--set-vars binlog_format='statement'
详细说明参考：pt-table-checksum not detecting diffs
[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= --user=mydba --password=mysql5721 --set-vars binlog_format='statement'
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
-10T09:: 0.024 replcrash.py_user
-10T09:: 0.695 replcrash.py_user_innodb
[root@ZST2 ~]#
善于思考，善于搜索，不要期盼别人把结果原封不动地送到嘴里~.~