sync-diff-inspector 2.0版本中怎么配置MySQL源库源表?

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【 TiDB 使用环境】
本地最新Master编译

【概述】 场景 + 问题概述
按照默认配置,未找到源表/源库配置项,校验的只有目标表自身;
重新配置routes.rule后,报数据不一致(sql全是repleace,实际上数据完全一致)

【备份和数据迁移策略逻辑】
上游MySQL,下游TiDB,校验数据一致性

【背景】 做过哪些操作
配置文件:

【现象】 业务和数据库现象
日志:
[2021/11/26 17:44:16.142 +08:00] [INFO] [printer.go:46] [“Welcome to sync_diff_inspector”] [“Release Version”=v5.2.2-112-gdd94424-dirty] [“Git Commit Hash”=dd94424db9a55f66accdf91199f58a38c5ebea24] [“Git Branch”=master] [“UTC Build Time”=“2021-11-26 06:06:28”] [“Go Version”=go1.17.2]
[2021/11/26 17:44:16.144 +08:00] [INFO] [main.go:76] [config="{“check-thread-count”:4,“export-fix-sql”:true,“check-struct-only”:false,“dm-addr”:"",“dm-task”:"",“data-sources”:{“mysql1”:{“host”:“114.115..”,“port”:13306,“user”:“root”,“password”:"*",“sql-mode”:"",“snapshot”:"",“route-rules”:[“rule1”],“Router”:{“Selector”:{}},“Conn”:null},“tidb0”:{“host”:"114.116..",“port”:4000,“user”:“rd_rttax_rw”,“password”:"***",“sql-mode”:"",“snapshot”:"",“route-rules”:null,“Router”:{“Selector”:{}},“Conn”:null}},“routes”:{“rule1”:{“schema-pattern”:“yid”,“table-pattern”:“tax_certs_test_mysql”,“target-schema”:“rttax_auto”,“target-table”:“tax_certs_test”}},“table-configs”:{“config1”:{“target-tables”:[“rttax_auto.tax_certs_test”],“Schema”:"",“Table”:"",“ConfigIndex”:0,“HasMatched”:false,“IgnoreColumns”:[],“Fields”:[""],“Range”:“I_ID \u003e 0”,“TargetTableInfo”:null,“Collation”:"",“chunk-size”:1000}},“task”:{“source-instances”:[“mysql1”],“source-routes”:null,“target-instance”:“tidb0”,“target-check-tables”:[“yid.tax_certs_test_mysql”,“rttax_auto.tax_certs_test”],“target-configs”:[“config1”],“output-dir”:"./checking",“SourceInstances”:[{“host”:“114.115.147.165”,“port”:13306,“user”:“root”,“password”:“uTMZ65C6t02uzGxYCEipJt4WDSFP76C6”,“sql-mode”:"",“snapshot”:"",“route-rules”:[“rule1”],“Router”:{“Selector”:{}},“Conn”:null}],“TargetInstance”:{“host”:“114.116.140.2”,“port”:4000,“user”:“rd_rttax_rw”,“password”:“Xny97szFNBPW2kK-kyF!W8gC”,“sql-mode”:"",“snapshot”:"",“route-rules”:null,“Router”:{“Selector”:{}},“Conn”:null},“TargetTableConfigs”:[{“target-tables”:[“rttax_auto.tax_certs_test”],“Schema”:"",“Table”:"",“ConfigIndex”:0,“HasMatched”:false,“IgnoreColumns”:[],“Fields”:[""],“Range”:“I_ID \u003e 0”,“TargetTableInfo”:null,“Collation”:"",“chunk-size”:1000}],“TargetCheckTables”:[{},{}],“FixDir”:“checking/fix-on-tidb0”,“CheckpointDir”:“checking/checkpoint”,“HashFile”:""},“ConfigFile”:"./config.toml",“PrintVersion”:false}"]
[2021/11/26 17:44:16.145 +08:00] [INFO] [diff.go:826] [“set tidb cfg”]
[2021/11/26 17:44:18.979 +08:00] [INFO] [mysql_shard.go:340] [“will increase connection configurations for DB of instance”] [“connection limit”=9]
[2021/11/26 17:44:18.991 +08:00] [INFO] [tidb.go:195] [“find router for tidb source”]
[2021/11/26 17:44:19.031 +08:00] [INFO] [diff.go:361] [“The downstream is TiDB. pick it as work source first”]
[2021/11/26 17:44:29.071 +08:00] [INFO] [pd.go:116] ["[automatically GC] check whether fetched pd addr and TiDB belong to one cluster failed"] [“pd address”="[172.16.21.227:2379,172.16.21.74:2379,172.16.21.192:2379]"] [error=“context deadline exceeded”] [errorVerbose=“context deadline exceeded\ngithub.com/pingcap/errors.AddStack\ \t/usr/local/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20210513014640-40f9a1999b3b/errors.go:174\ github.com/pingcap/errors.Trace\ \t/usr/local/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20210513014640-40f9a1999b3b/juju_adaptor.go:15\ github.com/pingcap/tidb-tools/sync_diff_inspector/utils.getPDDDLIDs\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/utils/pd.go:53\ github.com/pingcap/tidb-tools/sync_diff_inspector/utils.checkSameCluster\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/utils/pd.go:85\ github.com/pingcap/tidb-tools/sync_diff_inspector/utils.GetPDClientForGC\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/utils/pd.go:115\ main.(*Diff).startGCKeeperForTiDB\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/diff.go:324\ main.(*Diff).pickSource\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/diff.go:362\ main.(*Diff).init\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/diff.go:144\ main.NewDiff\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/diff.go:97\ main.checkSyncState\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/main.go:92\ main.main\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/main.go:79\ runtime.main\ \t/usr/local/Cellar/go/1.17.2/libexec/src/runtime/proc.go:255\ runtime.goexit\ \t/usr/local/Cellar/go/1.17.2/libexec/src/runtime/asm_amd64.s:1581”]
[2021/11/26 17:44:29.071 +08:00] [INFO] [diff.go:193] [“not found checkpoint file, start from beginning”]
[2021/11/26 17:44:29.123 +08:00] [INFO] [diff.go:705] [“start writeSQLs goroutine”]
[2021/11/26 17:44:29.122 +08:00] [INFO] [diff.go:375] [“start handleCheckpoint goroutine”]
[2021/11/26 17:44:29.139 +08:00] [INFO] [tidb.go:55] [“failed to build bucket iterator, fall back to use random iterator”] [error=“Error 1044: Access denied for user ‘rd_rttax_rw’@’%’ to database ‘mysql’”] [errorVerbose=“Error 1044: Access denied for user ‘rd_rttax_rw’@’%’ to database ‘mysql’\ngithub.com/pingcap/errors.AddStack\ \t/usr/local/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20210513014640-40f9a1999b3b/errors.go:174\ github.com/pingcap/errors.Trace\ \t/usr/local/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20210513014640-40f9a1999b3b/juju_adaptor.go:15\ github.com/pingcap/tidb-tools/pkg/dbutil.GetBucketsInfo\ \t/Users/yunzhanghu1151/code/tidb-tools/pkg/dbutil/common.go:482\ github.com/pingcap/tidb-tools/sync_diff_inspector/splitter.(*BucketIterator).init\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/splitter/bucket.go:129\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/splitter.NewBucketIteratorWithCheckpoint\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/splitter/bucket.go:75\ github.com/pingcap/tidb-tools/sync_diff_inspector/source.(*TiDBTableAnalyzer).AnalyzeSplitter\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/source/tidb.go:51\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/source.(*ChunksIterator).produceChunks.func3\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/source/chunks_iter.go:130\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/utils.(*WorkerPool).Apply.func1\ \t/Users/yunzhanghu1151/code/tidb-tools/sync_diff_inspector/utils/utils.go:75\ runtime.goexit\ \t/usr/local/Cellar/go/1.17.2/libexec/src/runtime/asm_amd64.s:1581”]
[2021/11/26 17:44:29.148 +08:00] [INFO] [random.go:108] [“get chunk size for table”] [“chunk size”=1000] [db=rttax_auto] [table=tax_certs_test]
[2021/11/26 17:44:29.148 +08:00] [INFO] [random.go:114] [“split range by random”] [“row count”=6] [“split chunk num”=1]
[2021/11/26 17:44:29.148 +08:00] [INFO] [diff.go:282] [“global consume chunk info”] [“chunk index”="{“table-index”:0,“bucket-index-left”:0,“bucket-index-right”:0,“chunk-index”:0,“chunk-count”:1}"] [“chunk bound”="[]"]
[2021/11/26 17:44:29.480 +08:00] [WARN] [utils.go:446] [“target lack data”] [row="{ I_ID: 1, I_YEAR_TAX_REDUCTION: 0, D_CREATED_AT: 2021-07-15 13:33:40, D_UPDATED_AT: 2021-07-15 13:33:40, I_CERT_TYPE: 1, I_MONTH_TAX_REDUCTION: 0, IF_MARK: 0, I_STATIS_REF: 554802107924877313, CH_DEALER_ID: 02398553, CH_YEAR_MONTH: 2021-06, CH_ID_CARD_ENCRYPT: //8AAXWfQj6A021ZHfQx8nkarVd+IVzsqyoKnc0iBj6xuKn2, CH_TAX_OWNER: accumulus_tj, }"]
[2021/11/26 17:44:29.480 +08:00] [WARN] [utils.go:446] [“target lack data”] [row="{ I_MONTH_TAX_REDUCTION: 0, D_UPDATED_AT: 2021-07-15 13:33:40, I_STATIS_REF: 554802107924877314, CH_YEAR_MONTH: 2021-06, I_CERT_TYPE: 1, I_YEAR_TAX_REDUCTION: 0, IF_MARK: 0, D_CREATED_AT: 2021-07-15 13:33:40, I_ID: 2, CH_DEALER_ID: 02398553, CH_ID_CARD_ENCRYPT: //8AAZRbaFpf1WRccvB3ziiIJcao5/15NFJuJ+RPbjsV4xRv, CH_TAX_OWNER: accumulus_tj, }"]
[2021/11/26 17:44:29.480 +08:00] [WARN] [utils.go:446] [“target lack data”] [row="{ I_ID: 3, CH_DEALER_ID: 02398553, CH_ID_CARD_ENCRYPT: //8AAXWfQj6A021ZHfQx8nkarVd+IVzsqyoKnc0iBj6xuKn2, CH_TAX_OWNER: accumulus_tj, I_CERT_TYPE: 2, I_STATIS_REF: 554802107924877315, CH_YEAR_MONTH: 2021-06, I_YEAR_TAX_REDUCTION: 0, I_MONTH_TAX_REDUCTION: 0, IF_MARK: 0, D_CREATED_AT: 2021-07-15 13:33:40, D_UPDATED_AT: 2021-07-15 13:33:40, }"]
[2021/11/26 17:44:29.480 +08:00] [WARN] [utils.go:446] [“target lack data”] [row="{ I_STATIS_REF: 554802107924877316, CH_TAX_OWNER: accumulus_tj, I_YEAR_TAX_REDUCTION: 0, IF_MARK: 0, D_CREATED_AT: 2021-07-15 13:33:40, I_MONTH_TAX_REDUCTION: 0, D_UPDATED_AT: 2021-07-15 13:33:40, I_ID: 4, CH_DEALER_ID: 02398553, CH_YEAR_MONTH: 2021-06, CH_ID_CARD_ENCRYPT: //8AAZRbaFpf1WRccvB3ziiIJcao5/15NFJuJ+RPbjsV4xRv, I_CERT_TYPE: 2, }"]
[2021/11/26 17:44:29.480 +08:00] [WARN] [utils.go:446] [“target lack data”] [row="{ CH_ID_CARD_ENCRYPT: //8AAXWfQj6A021ZHfQx8nkarVd+IVzsqyoKnc0iBj6xuKn2, CH_TAX_OWNER: accumulus_tj, I_MONTH_TAX_REDUCTION: 0, D_CREATED_AT: 2021-07-15 13:33:40, I_ID: 5, I_STATIS_REF: 554802107924877318, CH_DEALER_ID: 02398553, CH_YEAR_MONTH: 2021-06, D_UPDATED_AT: 2021-07-15 13:33:40, I_CERT_TYPE: 2, I_YEAR_TAX_REDUCTION: 0, IF_MARK: 0, }"]
[2021/11/26 17:44:29.481 +08:00] [INFO] [diff.go:716] [“write sql channel closed”]
[2021/11/26 17:44:29.481 +08:00] [INFO] [diff.go:707] [“close writeSQLs goroutine”]
[2021/11/26 17:44:29.481 +08:00] [INFO] [diff.go:403] [“Stop do checkpoint”]
[2021/11/26 17:44:29.482 +08:00] [INFO] [checkpoints.go:225] [“save checkpoint”] [chunk="{“state”:“failed”,“chunk-range”:{“index”:{“table-index”:0,“bucket-index-left”:0,“bucket-index-right”:0,“chunk-index”:0,“chunk-count”:1},“type”:2,“bounds”:[],“is-first”:false,“is-last”:false,“where”:"((TRUE) AND (I_ID \u003e 0))",“args”:null},“index-id”:0}"] [state=failed]
[2021/11/26 17:44:29.482 +08:00] [INFO] [diff.go:377] [“close handleCheckpoint goroutine”]
[2021/11/26 17:44:29.514 +08:00] [INFO] [main.go:89] [“check data finished”] [cost=13.368774032s]
[2021/11/26 17:44:29.514 +08:00] [WARN] [main.go:80] [“check failed!!!”]

【问题】 当前遇到的问题
校验错误,报数据不一致,sql修复语句全是更新:
– table: rttax_auto.tax_certs_test
– range in sequence: Full
REPLACE INTO rttax_auto.tax_certs_test(I_ID,I_STATIS_REF,CH_DEALER_ID,CH_YEAR_MONTH,CH_ID_CARD_ENCRYPT,CH_TAX_OWNER,I_CERT_TYPE,I_YEAR_TAX_REDUCTION,I_MONTH_TAX_REDUCTION,IF_MARK,D_CREATED_AT,D_UPDATED_AT) VALUES (1,554802107924877313,‘02398553’,‘2021-06’,’//8AAXWfQj6A021ZHfQx8nkarVd+IVzsqyoKnc0iBj6xuKn2’,‘accumulus_tj’,1,0,0,0,‘2021-07-15 13:33:40’,‘2021-07-15 13:33:40’);
REPLACE INTO rttax_auto.tax_certs_test(I_ID,I_STATIS_REF,CH_DEALER_ID,CH_YEAR_MONTH,CH_ID_CARD_ENCRYPT,CH_TAX_OWNER,I_CERT_TYPE,I_YEAR_TAX_REDUCTION,I_MONTH_TAX_REDUCTION,IF_MARK,D_CREATED_AT,D_UPDATED_AT) VALUES (2,554802107924877314,‘02398553’,‘2021-06’,’//8AAZRbaFpf1WRccvB3ziiIJcao5/15NFJuJ+RPbjsV4xRv’,‘accumulus_tj’,1,0,0,0,‘2021-07-15 13:33:40’,‘2021-07-15 13:33:40’);
REPLACE INTO rttax_auto.tax_certs_test(I_ID,I_STATIS_REF,CH_DEALER_ID,CH_YEAR_MONTH,CH_ID_CARD_ENCRYPT,CH_TAX_OWNER,I_CERT_TYPE,I_YEAR_TAX_REDUCTION,I_MONTH_TAX_REDUCTION,IF_MARK,D_CREATED_AT,D_UPDATED_AT) VALUES (3,554802107924877315,‘02398553’,‘2021-06’,’//8AAXWfQj6A021ZHfQx8nkarVd+IVzsqyoKnc0iBj6xuKn2’,‘accumulus_tj’,2,0,0,0,‘2021-07-15 13:33:40’,‘2021-07-15 13:33:40’);
REPLACE INTO rttax_auto.tax_certs_test(I_ID,I_STATIS_REF,CH_DEALER_ID,CH_YEAR_MONTH,CH_ID_CARD_ENCRYPT,CH_TAX_OWNER,I_CERT_TYPE,I_YEAR_TAX_REDUCTION,I_MONTH_TAX_REDUCTION,IF_MARK,D_CREATED_AT,D_UPDATED_AT) VALUES (4,554802107924877316,‘02398553’,‘2021-06’,’//8AAZRbaFpf1WRccvB3ziiIJcao5/15NFJuJ+RPbjsV4xRv’,‘accumulus_tj’,2,0,0,0,‘2021-07-15 13:33:40’,‘2021-07-15 13:33:40’);
REPLACE INTO rttax_auto.tax_certs_test(I_ID,I_STATIS_REF,CH_DEALER_ID,CH_YEAR_MONTH,CH_ID_CARD_ENCRYPT,CH_TAX_OWNER,I_CERT_TYPE,I_YEAR_TAX_REDUCTION,I_MONTH_TAX_REDUCTION,IF_MARK,D_CREATED_AT,D_UPDATED_AT) VALUES (5,554802107924877318,‘02398553’,‘2021-06’,’//8AAXWfQj6A021ZHfQx8nkarVd+IVzsqyoKnc0iBj6xuKn2’,‘accumulus_tj’,2,0,0,0,‘2021-07-15 13:33:40’,‘2021-07-15 13:33:40’);
REPLACE INTO rttax_auto.tax_certs_test(I_ID,I_STATIS_REF,CH_DEALER_ID,CH_YEAR_MONTH,CH_ID_CARD_ENCRYPT,CH_TAX_OWNER,I_CERT_TYPE,I_YEAR_TAX_REDUCTION,I_MONTH_TAX_REDUCTION,IF_MARK,D_CREATED_AT,D_UPDATED_AT) VALUES (6,554802107924877319,‘02398553’,‘2021-06’,’//8AAZRbaFpf1WRccvB3ziiIJcao5/15NFJuJ+RPbjsV4xRv’,‘accumulus_tj’,2,0,0,0,‘2021-07-15 13:33:40’,‘2021-07-15 13:33:40’);

【业务影响】

【TiDB 版本】

【附件】

  • 相关日志、配置文件、Grafana 监控(https://metricstool.pingcap.com/)
  • TiUP Cluster Display 信息
  • TiUP CLuster Edit config 信息
  • TiDB-Overview 监控
  • 对应模块的 Grafana 监控(如有 BR、TiDB-binlog、TiCDC 等)
  • 对应模块日志(包含问题前后 1 小时日志)

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

1、首先sync_diff_inspector暂时是不支持在有写入的情况下进行对比的。先要排除是否有写入。
2、配置上游的MySQL的那些表进行对比,可以在[[table-config.source-tables]]下面设置,参考:https://docs.pingcap.com/zh/tidb/stable/shard-diff#使用-table-config-进行配置

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。