当前位置：网站首页>sync-diff-inspector 使用实践

sync-diff-inspector 使用实践

2022-08-05 12:02:00 【InfoQ】

作者： banana_jian原文来源：
https://tidb.net/blog/7ffd821a

简介

在数据同步的场景下，上下游数据的一致性校验是非常重要的一个环节，缺少数据校验，在某种程度上甚至可以说名整个数据同步是无效的。sync-diff-inspector 是一个用于校验 MySQL／TiDB 中两份数据是否一致的工具。该工具提供了修复数据的功能（适用于修复少量不一致的数据）。首先我们看下 Sync-diff-inspector 的架构图，了解一下 Sync-diff-inspector 的作用和实现原。

主要功能

对比表结构和数据

如果数据不一致，则生成用于修复数据的 SQL 语句

支持不同库名或表名的数据校验

支持分库分表场景下的数据校验

支持 TiDB 主从集群的数据校验

支持从 TiDB DM 拉取配置的数据校验

工具下载

https://download.pingcap.org/tidb-community-toolkit-v6.1.0-linux-amd64.tar.gz

[tidb\@jian tidb-community-toolkit-v6.1.0-linux-amd64]$ pwd

/home/tidb/tidb-community-toolkit-v6.1.0-linux-amd64

[tidb\@jian tidb-community-toolkit-v6.1.0-linux-amd64]$ ls sync_diff_inspector

sync_diff_inspector

数据库用户创建

sync-diff-inspector 需要获取表结构信息、查询数据，需要的数据库权限如下：

源端的目标端的权限需求是一样的

([email protected]) [jian] 16:44:25> create user [email protected]'%' identified by '123456';

Query OK, 0 rows affected (0.04 sec)

([email protected]) [jian] 16:44:41> grant show databases,reload,select on *.* to [email protected]'%';

Query OK, 0 rows affected (0.40 sec)

实践

1 对比源端和目标端的同一张表

数据状态

源端：                                                                                                          目标端：

(root\@localhost) [jian] 16:55:48> select * from jian.jiantb;                   (root\@127.0.01) [jian] 16:53:32> select * from jian.jiantb;

+——+——+                                                                                                    +——+——+

| id   | name |                                                                                                          | id   | name |

+——+——+                                                                                                    +——+——+

|    1 | a    |                                                                                                               |    1 | a    |

|    2 | a    |                                                                                                               |    2 | a    |

|    3 | a    |                                                                                                             +——+——+

+——+——+                  

对于以上的数据情况我们期望看到的结果是检查数据一致性失败，并且提供 sql 可以插入目标端不存在的数据（3，‘a’）

配置文件


export-fix-sql = true
check-struct-only = false

[data-sources]
[data-sources.mysql1]

 host = &quot;192.168.135.149&quot;
 port = 3306
 user = &quot;root&quot;
 password = &quot;123456&quot;
[data-sources.tidb0]

 host = &quot;127.0.0.1&quot;
 port = 4000
 user = &quot;root&quot;
 password = &quot;123456&quot;
[task]

 output-dir = &quot;./output&quot;
 source-instances = [&quot;mysql1&quot;]
 target-instance = &quot;tidb0&quot;
 target-check-tables = [&quot;jian.jiantb&quot;]

执行校验

[tidb\@jian ~]$ sync_diff_inspector –config=sync_check

A total of 1 tables need to be compared

Comparing the table structure of ``jian`.`jiantb`` … equivalent

Comparing the table data of ``jian`.`jiantb`` … failure

_____________________________________________________________________________

Progress [============================================================>] 100% 0/0

The data of `jian`.`jiantb` is not equal

The rest of tables are all equal.

The patch file has been generated in

        ’output/fix-on-tidb0/’

You can view the comparision details through ‘./output/sync_diff.log’

修复 sql 查看

[tidb\@jian ~]$ cat output/fix-on-tidb0/jian\:jiantb\:0\:0-0\:0.sql

-- table: jian.jiantb

-- range in sequence: Full

REPLACE INTO `jian`.`jiantb`(`id`,`name`) VALUES (3,‘a’);

2 源端和目标端的表名不同

数据状态

源端：                                                                                                          目标端：

(root\@localhost) [jian] 16:55:48> select * from jian.yao;                   (root\@127.0.01) [jian] 16:53:32> select * from jian.jiantb;

+——+——+                                                                                                    +——+——+

| id   | name |                                                                                                          | id   | name |

+——+——+                                                                                                    +——+——+

+——+——+                                                                                                       |    2 | a    |

                                                                                                                              +——+——+

对于以上的数据情况我们期望看到的结果是，检查出 jian.yao 和 jian.jiantb 两张不同表名的表检查数据一致性失败，并且提供 sql 可以删除源端不存在的数据（2，‘a’）

配置文件

export-fix-sql = true

check-struct-only = false

[data-sources]

[data-sources.mysql1]

 host = &quot;192.168.135.149&quot;
 port = 3306
 user = &quot;root&quot;
 password = &quot;123456&quot;
 route-rules = [&quot;rule1&quot;]
[data-sources.tidb0]

 host = &quot;127.0.0.1&quot;
 port = 4000
 user = &quot;root&quot;
 password = &quot;123456&quot;

[routes]
[routes.rule1]

schema-pattern = &quot;jian&quot;
table-pattern = &quot;yao&quot;
target-schema = &quot;jian&quot;
target-table = &quot;jiantb&quot;
[task]

 output-dir = &quot;./output&quot;
 source-instances = [&quot;mysql1&quot;]
 target-instance = &quot;tidb0&quot;
 target-check-tables = [&quot;jian.jiantb&quot;]

执行校验

[tidb\@jian ~]$ sync_diff_inspector –config=sync_check2

A total of 1 tables need to be compared

Comparing the table structure of ``jian`.`jiantb`` … equivalent

Comparing the table data of ``jian`.`jiantb`` … failure

_____________________________________________________________________________

Progress [============================================================>] 100% 0/0

The data of `jian`.`jiantb` is not equal

The rest of tables are all equal.

The patch file has been generated in

        ’output/fix-on-tidb0/’

You can view the comparision details through ‘./output/sync_diff.log’

修复 sql 查看

[tidb\@jian ~]$ cat output/fix-on-tidb0/jian\:jiantb\:0\:0-0\:0.sql

-- table: jian.jiantb

-- range in sequence: Full

DELETE FROM `jian`.`jiantb` WHERE `id` = 2 AND `name` = ‘a’ LIMIT 1;

3 对比时指定条件范围

数据状态

源端：                                                                                                          目标端：

(root\@localhost) [jian] 18:10:51> select * from jiantb;                            (root\@localhost) [jian] 18:10:51> select * from jiantb;

+——+                                                                                                                        +——+

| id   |                                                                                                                                | id   |

+——+                                                                                                                         +——+

|   10 |                                                                                                                               |   10 |

|   20 |                                                                                                                               |   11 |

|   30 |                                                                                                                               |   20 |

|   26 |                                                                                                                               |   30 |

对于以上的数据情况我们期望看到的结果是，检查数据一致性失败，并且提供 sql 可以插入目标端不存在的数据（26）, 但是对于目标端比源端多出的 11 不希望生成删除的 sql，因为我们在配置文件中指定了只检查 id>20 的部分数据。

配置文件

export-fix-sql = true
check-struct-only = false
[data-sources]
[data-sources.mysql1]
 host = &quot;192.168.135.149&quot;
 port = 3306
 user = &quot;root&quot;
 password = &quot;123456&quot;
[data-sources.tidb0]

 host = &quot;127.0.0.1&quot;
 port = 4000
 user = &quot;root&quot;
 password = &quot;123456&quot;
[task]

 output-dir = &quot;./output&quot;
 source-instances = [&quot;mysql1&quot;]
 target-instance = &quot;tidb0&quot;
 target-check-tables = [&quot;jian.\*&quot;]
 target-configs = [&quot;config1&quot;]
[table-configs.config1]
target-tables=[&quot;jian.jiantb&quot;]
range = &quot;id > 20&quot;

执行校验

[tidb\@jian ~]$ ./tidb-community-toolkit-v6.1.0-linux-amd64/sync_diff_inspector –config=sync_check3

A total of 1 tables need to be compared

Comparing the table structure of ``jian`.`jiantb`` … equivalent

Comparing the table data of ``jian`.`jiantb`` … failure

_____________________________________________________________________________

Progress [============================================================>] 100%

The data of `jian`.`jiantb` is not equal

The rest of tables are all equal.

The patch file has been generated in

        ’output/fix-on-tidb0/’

You can view the comparision details through ‘./output/sync_diff.log’

修复 sql 查看

[tidb\@jian ~]$ cat output/fix-on-tidb0/

jian:jiantb:0:0-0:0.sql           .trash-2022-07-28T18:11:39+08:00/

[tidb\@jian ~]$ cat output/fix-on-tidb0/jian\:jiantb\:0\:0-0\:0.sql

-- table: jian.jiantb

-- range in sequence: Full

REPLACE INTO `jian`.`jiantb`(`id`) VALUES (26);

4 对比时数据存在部分不一致

数据状态

    源端：                                                                                                          目标端：

(root\@localhost) [jian] 16:55:48> select * from jian.yao;                    (root\@127.0.01) [jian] 16:53:32> select * from jian.jiantb;

+——+——+                                                                                                    +——+——+

| id   | name |                                                                                                          | id   | name |

+——+——+                                                                                                    +——+——+

 |    2 | a    |                                                                                                               |    2 | b   |

+——+——+                                                                                                      +——+——+

对于以上的数据情况我们期望看到的结果是，检查数据一致性失败，并且提供 sql 可以将目标端的错误数据（2，b）修正为（2,a）

配置文件

export-fix-sql = true
check-struct-only = false
[data-sources]
[data-sources.mysql1]

 host = &quot;192.168.135.149&quot;
 port = 3306
 user = &quot;root&quot;
 password = &quot;123456&quot;
[data-sources.tidb0]
 host = &quot;127.0.0.1&quot;
 port = 4000
 user = &quot;root&quot;
 password = &quot;123456&quot;
[task]
 output-dir = &quot;./output&quot;
 source-instances = [&quot;mysql1&quot;]
 target-instance = &quot;tidb0&quot;
 target-check-tables = [&quot;jian.jiantb&quot;]

执行校验

[tidb\@jian ~]$ ./tidb-community-toolkit-v6.1.0-linux-amd64/sync_diff_inspector –config=sync_check_1

A total of 1 tables need to be compared

Comparing the table structure of ``jian`.`jiantb`` … equivalent

Comparing the table data of ``jian`.`jiantb`` … failure

_____________________________________________________________________________

Progress [============================================================>] 100

The data of `jian`.`jiantb` is not equal

The rest of tables are all equal.

The patch file has been generated in

        ’output/fix-on-tidb0/’

You can view the comparision details through ‘./output/sync_diff.log’

修复 sql 查看

[tidb\@jian ~]$ cat output/fix-on-tidb0/jian\:jiantb\:0\:0-0\:0.sql

-- table: jian.jiantb

-- range in sequence: Full

  DIFF COLUMNS ╏ `NAME`

╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍╍╍╍

  source data  ╏ ‘a’

╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍╍╍╍

  target data  ╏ ‘b’

╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍╍╍╍

REPLACE INTO `jian`.`jiantb`(`id`,`name`) VALUES (2,‘a’);

5 目标端的数据来自多个数据库

数据状态

     源端：                                                                                                          目标端：

(root\@localhost) [jian] 16:55:48> select * from jian.yao;                     (root\@127.0.01) [jian] 16:53:32> select * from jian.jiantb;

+——+——+                                                                                                    +——+——+

| id   | name |                                                                                                          | id   | name |

+——+——+                                                                                                    +——+——+

+——+——+                                                                                                      +——+——+

(root\@localhost) [jian] 10:37:57> select * from yao.yaotb;                 (root\@127.0.01) [jian] 10:37:57> select * from yao.yaotb;

Empty set (0.01 sec)                                                                                              +—-+——+

                                                                                                                                | id | name |

                                                                                                                              +—-+——+

                                                                                                                                |  1 | a    |

                                                                                                                               +—-+——+

对于以上的数据情况我们期望看到的结果是，yao.yaotb 检查数据一致性失败，jian.jiantb 检查一致性通过，并且生成 sql 可以将目标端多余的数据（1，a）删除。

配置文件

export-fix-sql = true
check-struct-only = false
[data-sources]
[data-sources.mysql1]
 host = &quot;192.168.135.149&quot;
 port = 3306
 user = &quot;jian&quot;
 password = &quot;123456&quot;
[data-sources.mysql2]
 host = &quot;127.0.0.1&quot;
 port = 3306
 user = &quot;yao&quot;
 password = &quot;123456&quot;
[data-sources.tidb0]
 host = &quot;127.0.0.1&quot;
 port = 4000
 user = &quot;root&quot;
 password = &quot;123456&quot;

[task]
 output-dir = &quot;./output&quot;
 source-instances = [&quot;mysql1&quot;,&quot;mysql2&quot;]
 target-instance = &quot;tidb0&quot;
 target-check-tables = [&quot;jian.jiantb&quot;,&quot;yao.yaotb&quot;]

执行校验

[tidb\@jian ~]$ ./tidb-community-toolkit-v6.1.0-linux-amd64/sync_diff_inspector –config=sync_check

A total of 2 tables need to be compared

Comparing the table structure of ``jian`.`jiantb`` … equivalent

Comparing the table data of ``jian`.`jiantb`` … equivalent

Comparing the table structure of ``yao`.`yaotb`` … equivalent

Comparing the table data of ``yao`.`yaotb`` … failure

_____________________________________________________________________________

Progress [============================================================>] 100% 0/0

The data of `yao`.`yaotb` is not equal

The rest of tables are all equal.

The patch file has been generated in

        ’output/fix-on-tidb0/’

You can view the comparision details through ‘./output/sync_diff.log’

修复 sql 查看

[tidb\@jian ~]$ cat output/fix-on-tidb0/yao\:yaotb\:0\:0-0\:0.sql

-- table: yao.yaotb

-- range in sequence: Full

DELETE FROM `yao`.`yaotb` WHERE `id` = 1 LIMIT 1;

校验信息

当校验结束时，sync-diff-inspector 会输出一份校验报告，位于 

${output}/summary.txt

 中，其中 

${output}

 是配置文件中 

output-dir

 的值。报告中会详细写出本次数据校验的参与对象，以及数据差异的详细信息，对比执行所消耗的时间以及速度。

[tidb\@jian ~]$ cat output/summary.txt

Summary

Source Database

host = &quot;192.168.135.149&quot;

port = 3306

user = &quot;root&quot;

Target Databases

host = &quot;127.0.0.1&quot;

port = 4000

user = &quot;root&quot;

Comparison Result

The table structure and data in following tables are equivalent

The following tables contains inconsistent data

+-----------------+--------------------+----------------+---------+-----------+

| TABLE | STRUCTURE EQUALITY | DATA DIFF ROWS | UPCOUNT | DOWNCOUNT |

+-----------------+--------------------+----------------+---------+-----------+

| `jian`.`jiantb` | true | +1/-1 | 2 | 2 |

+-----------------+--------------------+----------------+---------+-----------+

Time Cost: 39.521197ms

Average Speed: 0.000000MB/s

几点解释

sync-diff-inspector 也可以只做表结构的检查相关参数是
check-struct-only
，默认情况下是 false

配置文件中 datasource 的定义的是无序的，只需要在 task 模块中指定对应的
source-instances
和
target-instance

下游数据库缺失行，则是 REPLACE 语句, 可见上边的样例 3下游数据库冗余行，则是 DELETE 语句, 可见上边的样例 2下游数据库行部分数据不一致，则是 REPLACE 语句，但会在 SQL 文件中通过注释的方法标明不同的列, 可见上边的样例 4

对于匹配规则，sync-diff-inspector 支持通配符 “*” 和 “?”

使用限制

对于 MySQL 和 TiDB 之间的数据同步不支持在线校验，需要保证上下游校验的表中没有数据写入，或者保证某个范围内的数据不再变更，通过配置 range 来校验这个范围内的数据。

不支持 JSON 类型的数据，在校验时需要设置 ignore-columns 忽略检查这些类型的数据。

FLOAT、DOUBLE 等浮点数类型在 TiDB 和 MySQL 中的实现方式不同，在计算 checksum 时会分别取 6 位和 15 位有效数字。如果不使用该特性，需要设置 ignore-columns 忽略这些列的检查。

支持对不包含主键或者唯一索引的表进行校验，但是如果数据不一致，生成的用于修复的 SQL 可能无法正确修复数据。

注意事项

sync-diff-inspector 在校验数据时会消耗一定的服务器资源，需要避免在业务高峰期间校验。

在数据对比前，需要注意表中的 collation 设置。如果表的主键或唯一键为 varchar 类型，且上下游数据库中 collation 设置不同，可能会因为排序问题导致最终校验结果不正确，需要在 sync-diff-inspector 的配置文件中增加 collation 设置。

sync-diff-inspector 会优先使用 TiDB 的统计信息来划分 chunk，需要尽量保证统计信息精确，可以在
业务空闲期
手动执行 
analyze table {table_name}
。

table-rule 的规则需要特殊注意，例如设置了 
schema-pattern="test1"
，
table-pattern = "t_1"
，
target-schema="test2"
，
target-table = "t_2"
，会对比 source 中的表 
test1
.
t_1
 和 target 中的表 
test2
.
t_2
。sync-diff-inspector 默认开启 sharding，如果 source 中还有表 
test2
.
t_2
，则会把 source 端的表 
test1
.
t_1
 和表 
test2
.
t_2
 作为 sharding 与 target 中的表 
test2
.
t_2
 进行一致性校验。

生成的 SQL 文件仅作为修复数据的参考，需要确认后再执行这些 SQL 修复数据。

总结

sync_diff_inspector 是 TiDB 团队为了方便用户在 MySQL 数据迁移到 TiDB 后对数据一致性进行检查的开源工具，他不要求被检查的两个数据库之间存在任何的复制关系，并且对于表中是否存在索引也不会强制性要求，只要建立起对应的 mapping 关系即可以对两个数据库进行数据的一致性检验。如果 sync_diff_inspector 发现某个 chunk 的上下游的 checksum 不一致，可以通过二分法将原来的 chunk 划分成大小接近的两个子 chunk，对子 chunk 进行 checksum 对比，进一步缩小不一致行的可能范围。

虽然 sync_diff_inspector 目前还不能做到实时的数据一致性检验，但也提供类似 range 和 snapshot 等一些变相的方式来检查某一个特定“区域”的数据的一致性。