# **练习-1 : 使用 `sync-diff-inspector` 进行数据校验** ## 概述 + 本练习中，您将使用 sync-diff-inspector 来进行 MySQL 数据库表到 TiDB 数据库表的校验工作 ## 实验环境要求 + 1. 注意，您的实验环境（包括IP，端口号，用户名，密码和目录）可能和手册不同。 + 2. 可以连接到实验用虚拟机。 + 3. TiDB 数据库提前部署完毕并导入数据。 + 4. MySQL 数据库提前部署完毕并导入数据。 + 5. 清理掉之前的实验数据。 ## 步骤 ----------------------------------------------- #### 1. sync-diff-inspector 工具集成在 tidb-toolkit 中，先进行下载安装: + **请从网址：https://pingcap.com/zh/product-community/ 中下载最新的 TiDB-community-toolkit 软件包，并上传到练习环境中。** + **解压下载的软件包**： ``` # tar xvf tidb-community-toolkit-v6.1.0-linux-amd64.tar ``` + **也可以将目录加入环境变量，方便后续执行:** ``` # cd # vi .bash_profile ``` ``` PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/root/tidb-community-toolkit-v6.1.0 ``` ----------------------------------------------- #### 2. 查看数据源 MySQL 数据库下的表和目标数据库 TiDB 数据库下的表。 + **查看数据源 MySQL 数据库的表 test1 库下的表 T1，T2，T3 和 T4，如下：** ```sql mysql> desc T1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> desc T2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> desc T3; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> desc T4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from T1; +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | | 4 | Tony | +----+-------+ 4 rows in set (0.01 sec) mysql> select * from T2; +------+-------+ | id | name | +------+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | | 4 | Tony | +------+-------+ 4 rows in set (0.01 sec) mysql> select * from T3; +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | | 4 | Tony | +----+-------+ 4 rows in set (0.01 sec) mysql> select * from T4; +------+-------+ | id | name | +------+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | | 4 | Tony | +------+-------+ 4 rows in set (0.00 sec) ``` + **查看目标数据库 TiDB 数据库的表 test 库下的表 T1，T2，T3 和 T4，如下：** ```sql mysql> desc T1; +-------+-------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+------+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+------+---------+-------+ 2 rows in set (0.02 sec) mysql> desc T2; +-------+-------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+------+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+------+---------+-------+ 2 rows in set (0.01 sec) mysql> desc T3; +-------+-------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+------+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+------+---------+-------+ 2 rows in set (0.01 sec) mysql> desc T4; +-------+-------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+------+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+------+---------+-------+ 2 rows in set (0.05 sec) mysql> select * from T1; +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | | 4 | Tony | +----+-------+ 4 rows in set (0.11 sec) mysql> select * from T2; +------+-------+ | id | name | +------+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | | 4 | Tony | +------+-------+ 4 rows in set (0.10 sec) mysql> select * from T3; +------+-------+ | id | name | +------+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | | 4 | Tony | +------+-------+ 4 rows in set (0.17 sec) mysql> select * from T4; +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | | 4 | Tony | +----+-------+ 4 rows in set (0.04 sec) ``` ----------------------------------------------- #### 3. 编辑 sync-diff-inspector 工具的配置文件: + **编辑文件 config.toml，如下:** ``` # vi config.toml ``` ----------------------------------------------- #### 4. 使用 sync-diff-inspector 工具进行数据检查: + **启动 sync-diff-inspector，如下:** ``` # sync_diff_inspector --config=./config.toml ``` ``` A total of 4 tables need to be compared Comparing the table structure of ``test`.`T3`` ... equivalent Comparing the table structure of ``test`.`T2`` ... equivalent Comparing the table structure of ``test`.`T4`` ... equivalent Comparing the table structure of ``test`.`T1`` ... equivalent Comparing the table data of ``test`.`T3`` ... equivalent Comparing the table data of ``test`.`T2`` ... equivalent Comparing the table data of ``test`.`T1`` ... equivalent Comparing the table data of ``test`.`T4`` ... equivalent _____________________________________________________________________________ Progress [============================================================>] 100% 0/0 A total of 4 table have been compared and all are equal. You can view the comparison details through './output/sync_diff.log' ``` ----------------------------------------------- #### 5. 查看校验结果: + **查看两边校验结果，如下:** ``` ``` ``` Summary Source Database host = "127.0.0.1" port = 3306 user = "root" Target Databases host = "127.0.0.1" port = 4000 user = "root" Comparison Result The table structure and data in following tables are equivalent +-------------+---------+-----------+ | TABLE | UPCOUNT | DOWNCOUNT | +-------------+---------+-----------+ | `test`.`T1` | 4 | 4 | | `test`.`T2` | 4 | 4 | | `test`.`T3` | 4 | 4 | | `test`.`T4` | 4 | 4 | +-------------+---------+-----------+ Time Cost: 1.147223044s Average Speed: 0.000173MB/s ``` + **从校验结果看，两边数据一致。** ----------------------------------------------- #### 6. 修改数据: + **修改源端 MySQL 数据库的数据，如下:** ```sql mysql> insert into T1 select 5, 'Candy'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into T2 select 5, 'Candy'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into T3 select 5, 'Candy'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into T4 select 5, 'Candy'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> delete from T1 where id=4; Query OK, 1 row affected (0.01 sec) mysql> delete from T2 where id=4; Query OK, 1 row affected (0.01 sec) mysql> delete from T3 where id=4; Query OK, 1 row affected (0.01 sec) mysql> delete from T4 where id=4; Query OK, 1 row affected (0.01 sec) mysql> update T1 set name='Andy' where id=1; Query OK, 1 row affected (0.01 sec) mysql> update T2 set name='Andy' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update T3 set name='Andy' where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update T4 set name='Andy' where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 ``` + **再次验证:** ``` # sync_diff_inspector --config=./config.toml ``` ```sql A total of 4 tables need to be compared Comparing the table structure of ``test`.`T2`` ... equivalent Comparing the table structure of ``test`.`T3`` ... equivalent Comparing the table structure of ``test`.`T4`` ... equivalent Comparing the table structure of ``test`.`T1`` ... equivalent Comparing the table data of ``test`.`T4`` ... failure Comparing the table data of ``test`.`T3`` ... failure Comparing the table data of ``test`.`T2`` ... failure Comparing the table data of ``test`.`T1`` ... failure _____________________________________________________________________________ Progress [============================================================>] 100% 0/0 The data of `test`.`T4` is not equal The data of `test`.`T3` is not equal The data of `test`.`T2` is not equal The data of `test`.`T1` is not equal The rest of tables are all equal. The patch file has been generated in 'output/fix-on-tidb/' You can view the comparison details through './output/sync_diff.log' ``` ----------------------------------------------- #### 6. 查看校验结果: + **查看两边校验结果，如下:** ``` vi output/summary.txt ``` ``` Summary Source Database host = "127.0.0.1" port = 3306 user = "root" Target Databases host = "127.0.0.1" port = 4000 user = "root" 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 | +-------------+--------------------+----------------+---------+-----------+ | `test`.`T1` | true | +2/-2 | 4 | 4 | | `test`.`T4` | true | +2/-2 | 4 | 4 | | `test`.`T3` | true | +2/-2 | 4 | 4 | | `test`.`T2` | true | +2/-2 | 4 | 4 | +-------------+--------------------+----------------+---------+-----------+ Time Cost: 34.324683ms Average Speed: 0.005779MB/s ``` + **从校验结果看，两边数据不一致。** ----------------------------------------------- ## 输出样例 ----------------------------------------------- #### 步骤 3-编辑文件 config.toml 输出参考： ``` # vi config.toml ``` ``` # Diff Configuration. ######################### Global config ######################### # 检查数据的线程数量，上下游数据库的连接数会略大于该值 check-thread-count = 4 # 如果开启，若表存在不一致，则输出用于修复的 SQL 语句。 export-fix-sql = true # 只对比表结构而不对比数据 check-struct-only = false ######################### Datasource config ######################### [data-sources] [data-sources.mysql] host = "127.0.0.1" port = 3306 user = "root" password = "mysql" #（可选）使用映射规则来匹配上游多个分表，其中 rule1 和 rule2 在下面 Routes 配置栏中定义 route-rules = ["rule1"] [data-sources.tidb] host = "127.0.0.1" port = 4000 user = "root" password = "tidb" ########################### Routes ########################### # 如果需要对比大量的不同库名或者表名的表的数据，或者用于校验上游多个分表与下游总表的数据，可以通过 table-rule 来设置映射关系 # 可以只配置 schema 或者 table 的映射关系，也可以都配置 [routes] [routes.rule1] # rule1 是该配置的唯一标识的自定义 id，用于上面 data-sources.route-rules 中 schema-pattern = "test1" # 匹配数据源的库名，支持通配符 "*" 和 "?" target-schema = "test" # 目标库名 ######################### Task config ######################### # 配置需要对比的*目标数据库*中的表 [task] output-dir = "./output" source-instances = ["mysql"] target-instance = "tidb" target-check-tables = ["test.*"] ``` -----------------------------------------------