关于 sync-diff-inspector [table-rules] 标签的应用

来了老弟 · 2020 年7 月 10 日 08:08

【对比需求】

对上游有规则的分表（table-202001/202002/202003），与下游汇总表（table-total）进行数据对比。

【注意事项】

table-rules 与 table-config 标签不能混用

【使用方式】

上游数据初始化

drop table t0,t1,t2,t3;
create table t0 (id int not null auto_increment PRIMARY key,name char(22),weiyi char(22) UNIQUE KEY);
create table t1 (id int not null auto_increment PRIMARY key,name char(22),weiyi char(22) UNIQUE KEY);
create table t2 (id int not null auto_increment PRIMARY key,name char(22),weiyi char(22) UNIQUE KEY);
create table t3 (id int not null auto_increment PRIMARY key,name char(22),weiyi char(22) UNIQUE KEY);

insert into t0(name,weiyi) values('a','001');
insert into t0(name,weiyi) values('b','002');
insert into t0(name,weiyi) values('c','003');
insert into t0(name,weiyi) values('d','004');
insert into t0(name,weiyi) values('e','005');

insert into t1(name,weiyi) values('a','101');
insert into t1(name,weiyi) values('b','102');
insert into t1(name,weiyi) values('c','103');
insert into t1(name,weiyi) values('d','104');
insert into t1(name,weiyi) values('e','105');

insert into t2(name,weiyi) values('a','201');
insert into t2(name,weiyi) values('b','202');
insert into t2(name,weiyi) values('c','203');
insert into t2(name,weiyi) values('d','204');
insert into t2(name,weiyi) values('e','205');

insert into t3(name,weiyi) values('a','301');
insert into t3(name,weiyi) values('b','302');
insert into t3(name,weiyi) values('c','303');
insert into t3(name,weiyi) values('d','304');
insert into t3(name,weiyi) values('e','305');

下游表数据初始化：

drop table t_total;
create table t_total (id int not null,name char(22),weiyi char(22) UNIQUE KEY,index(id));

insert into t_total values(1,'a','001');
insert into t_total values(2,'b','002');
insert into t_total values(3,'c','003');
insert into t_total values(4,'d','004');
insert into t_total values(5,'e','005');

insert into t_total values(1,'a','101');
insert into t_total values(2,'b','102');
insert into t_total values(3,'c','103');
insert into t_total values(4,'d','104');
insert into t_total values(5,'e','105');

insert into t_total values(1,'a','201');
insert into t_total values(2,'b','202');
insert into t_total values(3,'c','203');
insert into t_total values(4,'d','204');
insert into t_total values(5,'e','205');

insert into t_total values(1,'a','301');
insert into t_total values(2,'b','302');
insert into t_total values(3,'c','303');
insert into t_total values(4,'d','304');
insert into t_total values(5,'e','305');

sync-diff-inspector 配置文件展示，特殊配置为： ignore-struct-check = true

# Diff Configuration.

######################### Global config #########################

# 日志级别，可以设置为 info、debug
log-level = "info"

# sync-diff-inspector 根据主键／唯一键／索引将数据划分为多个 chunk，
# 对每一个 chunk 的数据进行对比。使用 chunk-size 设置 chunk 的大小
chunk-size = 1000

# 检查数据的线程数量
check-thread-count = 4

# 抽样检查的比例，如果设置为 100 则检查全部数据
sample-percent = 100

# 通过计算 chunk 的 checksum 来对比数据，如果不开启则逐行对比数据
use-checksum = true

# 如果设置为 true 则只会通过计算 checksum 来校验数据，如果上下游的 checksum 不一致也不会查出数据再进行校验
only-use-checksum = false

# 是否使用上次校验的 checkpoint，如果开启，则只校验上次未校验以及校验失败的 chunk
use-checkpoint = true

# 不对比数据
ignore-data-check = false

# 不对比表结构
ignore-struct-check = true

# 保存用于修复数据的 sql 的文件名称
fix-sql-file = "fix.sql"

######################### Tables config #########################

# 配置需要对比的目标数据库中的表
[[check-tables]]
    # 库的名称
    schema = "test2"

    # 需要检查的表的名称
    tables = ["t_total"]

# 通过 table-rule 来设置上游分表与下游总表的映射关系。可以只配置 schema 或者 table 的映射关系，也可以都配置
[[table-rules]]
    # schema-pattern 和 table-pattern 支持通配符 *?
    # 在 source-db 中配置的上游数据库中所有满足 schema-pattern 和 table-pattern 规则的表都为 target-schema.target-table 的分表
    schema-pattern = "test"
    table-pattern = "t*"
    target-schema = "test2"
    target-table = "t_total"

######################### Databases config #########################

# 源数据库实例的配置
[[source-db]]
    host = "172.16.4.107"
    port = 3306
    user = "root"
    password = "123"
    instance-id = "MySQL-1"


# 目标数据库实例的配置
[target-db]
    host = "172.16.4.107"
    port = 24000
    user = "root"
    password = ""
    instance-id = "target-1"

执行结果：

[root@node4107 bin]# ./sync_diff_inspector --config=./sync-diff.toml
[2020/07/06 21:47:44.393 +08:00] [INFO] [diff.go:199] [“find matched source tables”] [“source tables”=“[{"instance-id":"MySQL-1","Schema":"test","Table":"t2"},{"instance-id":"MySQL-1","Schema":"test","Table":"t3"},{"instance-id":"MySQL-1","Schema":"test","Table":"t0"},{"instance-id":"MySQL-1","Schema":"test","Table":"t1"}]”] [“target schema”=test2] [table=t_total]
[2020/07/06 21:47:44.444 +08:00] [INFO] [diff.go:457] [“checksum is equal”] [table=test2.t_total] [where=“(TRUE AND TRUE)”] [args=null] [checksum=0]
[2020/07/06 21:47:44.502 +08:00] [INFO] [checkpoint.go:231] [“summary info”] [instance_id=target-1] [schema=test2] [table=t_total] [“chunk num”=1] [“success num”=1] [“failed num”=0] [“ignore num”=0]
[2020/07/06 21:47:44.513 +08:00] [INFO] [main.go:84] [“check report”] [report="
check result: pass!
1 tables’ check passed, 0 tables’ check failed.

table: test2.t_total
table’s struct equal
table’s data equal

“]
[2020/07/06 21:47:44.513 +08:00] [INFO] [main.go:71] [“check data finished”] [cost=148.695628ms]
[2020/07/06 21:47:44.513 +08:00] [INFO] [main.go:65] [” test pass!!! "]
[root@node4107 bin]# cat fix.sql
[root@node4107 bin]# cat sync-diff.toml

【相关 issue】

https://github.com/pingcap/docs-cn/pull/3814