# 实验 9：使用 sync-diff-inspector 进行数据检查 ## 概述 在本实验中，你将使用 `sync-diff-inspector` 工具比较存储在 MySQL 和 TiDB 数据库中的数据。以下展示了 TiDB 集群的部署拓扑图。 ![60%](https://lab-static.pingcap.com/quick-demo/TiDBClusterDeploymentTopologyExercisewithSync_aa_003_20230301.png) > [!TIP] > 点击 **开始** 启动实验环境。 > 环境准备就绪后，点击底部工具栏中的 **前进** 进入 **模块 1**。 > 然后，按照实验指南的步骤连接到实践环境并开始你的任务。 ## 模块 1：部署 sync-diff-inspector 工具 ### 步骤 1：登录远程 Linux + 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。 + 登录后显示的横幅： ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### 步骤 2. 安装 `sync-diff-inspector` + `sync-diff-inspector` 工具包含在 `TiDB Toolkit` 中。在[终端 1](ssh://monitor1-ssh#1) 中输入以下命令下载 `TiDB Toolkit`. ```bash wget https://download.pingcap.org/tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz ``` ```output --2023-02-08 03:57:06-- https://download.pingcap.org/tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz Resolving download.pingcap.org (download.pingcap.org)... 156.251.65.6, 156.251.65.9 Connecting to download.pingcap.org (download.pingcap.org)|156.251.65.6|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 958570823 (914M) [application/x-compressed] Saving to: ‘tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz’ 100%[=============================================================================================>] 958,570,823 35.3MB/s in 27s 2023-02-08 03:57:34 (33.3 MB/s) - ‘tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz’ saved [958570823/958570823] ``` + 从 `tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz` 中解压出 `TiDB Toolkit` 二进制归档文件。 ```bash tar xvf tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz ``` ```output tidb-community-toolkit-v7.5.1-linux-amd64/ tidb-community-toolkit-v7.5.1-linux-amd64/keys/ ... tidb-community-toolkit-v7.5.1-linux-amd64/sync_diff_inspector tidb-community-toolkit-v7.5.1-linux-amd64/reparo tidb-community-toolkit-v7.5.1-linux-amd64/arbiter tidb-community-toolkit-v7.5.1-linux-amd64/tidb-lightning-ctl tidb-community-toolkit-v7.5.1-linux-amd64/mydumper tidb-community-toolkit-v7.5.1-linux-amd64/etcdctl ``` ### 步骤 3. 确认 `sync-diff-inspector` 已安装 ```bash ls -lF tidb-community-toolkit-v7.5.1-linux-amd64/sync_diff_inspector ``` ```output -rwxr-xr-x 1 ec2-user ec2-user 111859344 Mar 10 05:45 tidb-community-toolkit-v7.5.1-linux-amd64/sync_diff_inspector* ``` ## 模块 2：检查 TiDB 上下游集群的数据 ### 步骤 1. 通过 SSH 连接到 PD3 ```bash ./ssh-to-pd3.sh ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### 步骤 2. 启动 MySQL server 实例 ```bash sudo service mysqld start ``` ### 步骤 3. 登录 MySQL server ```bash mysql -u root -p -h 127.0.0.1 -P 3306 -pq1w2e3R4_ ``` ### 步骤 4. 为 `sync-diff-inspector` 任务创建一个用户 ```sql CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_'; ``` ```output mysql> CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_'; Query OK, 0 rows affected (0.00 sec) ``` ```sql GRANT ALL PRIVILEGES on *.* to ins@'%'; ``` ```output mysql> GRANT ALL PRIVILEGES on *.* to ins@'%'; Query OK, 0 rows affected (0.00 sec) ``` ### 步骤 5. 向 MySQL 数据库中插入示例数据 ```sql CREATE DATABASE test1; USE test1; CREATE TABLE T1(id INT(11) NOT NULL PRIMARY KEY, name VARCHAR(32)); CREATE TABLE T2(id INT(11), name VARCHAR(32)); CREATE TABLE T3(id INT(11) NOT NULL PRIMARY KEY, name VARCHAR(32)); CREATE TABLE T4(id INT(11), name VARCHAR(32)); INSERT INTO T1 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony'); INSERT INTO T2 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony'); INSERT INTO T3 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony'); INSERT INTO T4 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony'); DESC T1; DESC T2; DESC T3; DESC T4; SELECT * FROM T1; SELECT * FROM T2; SELECT * FROM T3; SELECT * FROM T4; ``` ```output mysql> CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_'; Query OK, 0 rows affected (0.06 sec) ... mysql> SELECT * FROM T4; +------+-------+ | id | name | +------+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | | 4 | Tony | +------+-------+ 4 rows in set (0.00 sec) ``` ### 步骤 6. 返回 `monitor1` ```sql EXIT ``` ```output mysql> EXIT; Bye ``` ```bash exit ``` ```output logout Connection to XX.XX.XX.XX closed. ``` ### 步骤 7. 在 TiDB 上为 `sync-diff-inspector` 任务创建一个用户： ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_'; ``` ```output mysql> CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_'; Query OK, 0 rows affected (0.06 sec) ``` ```sql GRANT ALL PRIVILEGES on *.* to ins@'%'; ``` ```output mysql> GRANT ALL PRIVILEGES on *.* to ins@'%'; Query OK, 0 rows affected (0.04 sec) ``` ### 步骤 8. 向 TiDB 集群中插入示例数据 ```sql CREATE DATABASE test1; USE test1; CREATE TABLE T1(id INT(11) NOT NULL PRIMARY KEY, name VARCHAR(32)); CREATE TABLE T2(id INT(11), name VARCHAR(32)); CREATE TABLE T3(id INT(11) NOT NULL PRIMARY KEY, name VARCHAR(32)); CREATE TABLE T4(id INT(11), name VARCHAR(32)); INSERT INTO T1 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony'); INSERT INTO T2 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony'); INSERT INTO T3 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony'); INSERT INTO T4 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony'); DESC T1; DESC T2; DESC T3; DESC T4; SELECT * FROM T1; SELECT * FROM T2; SELECT * FROM T3; SELECT * FROM T4; ``` ```output mysql> CREATE DATABASE test1; Query OK, 1 row affected (0.00 sec) ... mysql> SELECT * FROM T4; +------+-------+ | id | name | +------+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | | 4 | Tony | +------+-------+ 4 rows in set (0.00 sec) ``` ```sql EXIT ``` ```output mysql> EXIT; Bye ``` ### 步骤 9. 查看 `solution-sync-diff-config.toml` 文件 ```bash cat solution-sync-diff-config.toml ``` ```output # Diff Configuration. #####################Global config ######################### # The number of threads that check the data. The number of connections to the upstream and downstream databases will be slightly larger than this value. check-thread-count = 4 # When on, if there is an inconsistency in the table, the SQL statement used to fix it is output. export-fix-sql = true # Comparing only table schema without comparing data check-struct-only = false #####################Datasource config ######################### [data-sources] [data-sources.mysql] host = "10.90.2.214" port = 3306 user = "ins" password = "q1w2e3R4_" #(Optional) Use mapping rules to match multiple shards upstream, where rule1 and rule2 are defined in the Routes configuration column below route-rules = ["rule1"] [data-sources.tidb] host = "10.90.2.124" port = 4000 user = "ins" password = "q1w2e3R4_" #######################Routes ########################### # If You Need to Compare the Data of a Large Number of Tables with Different Library Names or Table Names, or to Verify the Data of Multiple Upstream Sharded Sharded Table and Downstream Combined Table, You Can Use Table-rule to Set the Mapping Relationship # You Can Configure Only Schema or Table Mapping Relationships, or Both [routes] [routes.rule1] # rule1 is a custom id that uniquely identifies this configuration and is used in data-sources.route-rules above schema-pattern = "test1" # Match the library name of the data source. Wildcards "*" and "?" are supported target-schema = "test1" # Target library name #####################Task config ######################### # Configuration Table in *Target Database* That Needs to Be Compared [task] output-dir = "./output" source-instances = ["mysql"] target-instance = "tidb" target-check-tables = ["test1.*"] ``` ### 步骤 10. 比较表结构和表数据 ```bash ./tidb-community-toolkit-v7.5.1-linux-amd64/sync_diff_inspector --config=./solution-sync-diff-config.toml ``` ```output 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' ``` ### 步骤 11. 查看 `output/summary.txt` 文件的内容 ```bash cat ./output/summary.txt ``` ```output ... 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 ``` ### 步骤 12. 更新 MySQL 中的数据 ```bash source hosts-env.sh ``` ```bash mysql -u ins -h ${HOST_PD3_PRIVATE_IP} -P 3306 -pq1w2e3R4_ ``` ```bash USE test1; INSERT INTO T1 SELECT 5, 'Candy'; INSERT INTO T2 SELECT 5, 'Candy'; INSERT INTO T3 SELECT 5, 'Candy'; INSERT INTO T4 SELECT 5, 'Candy'; DELETE FROM T1 WHERE id=4; DELETE FROM T2 WHERE id=4; DELETE FROM T3 WHERE id=4; DELETE FROM T4 WHERE id=4; UPDATE T1 SET NAME='Andy' WHERE id=1; UPDATE T2 SET NAME='Andy' WHERE id=1; UPDATE T3 SET NAME='Andy' WHERE id=1; UPDATE T4 SET NAME='Andy' WHERE id=1; ``` ```output mysql> USE test1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed ... mysql> UPDATE T4 SET NAME='Andy' WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 ``` ### 步骤 13. 再次比较表结构和表数据 ```bash ./tidb-community-toolkit-v7.5.1-linux-amd64/sync_diff_inspector --config=./solution-sync-diff-config.toml ``` ```output 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' ``` ### 步骤 14. 查看 `output/summary.txt` 文件的内容 ```bash cat ./output/summary.txt ``` ```output 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 ``` ## 实验结束