# Lab 9: Using sync-diff-inspector for Data Check

### Overview
In these exercises, you compare data stored in the databases with the MySQL and TiDB by using `sync-diff-inspector`. The following shows the deployment topology diagram of TiDB cluster.
   ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/TiDBClusterDeploymentTopologyExercisewithSync_aa_003_20230301.png)

### Notes
+ Click the private key file [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download it. It is required to log into the Control Machine for this lab.
+ Commands and SQL statements presented on this page are clickable for easy copy and paste.
+ Your environment may be different with these exercises, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide.
+ The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt.

### Assumptions
+ You have downloaded and secured the TiUP Control Machine private key (for example the file is saved under `~/.ssh/` folder locally): [chmod 400 ~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)
+ Windows users please refer to this [guide](https://github.com/pingcap-inc/tidb-course-labs/blob/master/README_303_class_setup_guide_for_student_lab_control_en.md) for private key preparation.

## Exercise 1
### Subject: Deploying the sync-diff-inspector Tool

### Overview
+ In this exercise, you download and install the `sync-diff-inspector` tool.

### Duration
+ This exercise should take you approximately 15 minutes to complete.

### Tasks
+ Install `sync-diff-inspector`

### Solution

1. Login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. If you do not possess the credentials to connect, kindly review the <kbd>Assumptions</kbd> section under the <kbd>OVERVIEW</kbd> tab. If prompted, answer `yes`.
   ```command
   $ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@monitor1_address
   ```
   ```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. The `sync-diff-inspector` tool is included in the `TiDB Toolkit`. Enter the following command in the terminal to download the `TiDB Toolkit`. 
   ```command
   $ 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]
   ```

3. Extract the `TiDB Toolkit` binary archive from `tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz`. Enter the following command at the terminal prompt and receive the results shown: 
   ```command
   $ 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
   ```

4. Confirm the `sync-diff-inspector` has been installed.
   ```command
   $ 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*
   ```

## Exercise 2
### Subject: Checking Data for TiDB Upstream and Downstream Clusters

### Overview
+ In this exercise, you compare data stored in the databases with MySQL and TiDB by using `sync-diff-inspector`.

### Duration
+ This exercise should take you approximately 25 minutes to complete.

### Tasks
+ Prepare the schema and data for the upstream MySQL server instance on PD3.
+ Prepare the schema and data for downstream TiDB server. 
+ Compare the schema and data using `sync-diff-inspector`.

### Solution
1. SSH to PD3 from `monitor1`:
   ```command
   $ source hosts-env.sh
   ```
   ```command
   $ ./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. Start MySQL server instance, if you have no MySQL instance running on PD3:
   ```command
   $ sudo service mysqld start
   ```

3. Login MySQL server on port 3306 as `root@'localhost'` with the password `q1w2e3R4_`. Enter the following command at the terminal prompt and receive the results shown:
   ```command
   $ mysql -u root -p -h 127.0.0.1 -P 3306 -pq1w2e3R4_
   ```

4. Create a user for `sync-diff-inspector` task:
   ```command
   CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_';
   ```
   ```output
   mysql> CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_';
   Query OK, 0 rows affected (0.00 sec)
   ```

   ```command
   GRANT ALL PRIVILEGES on *.* to ins@'%';
   ```
   ```output
   mysql> GRANT ALL PRIVILEGES on *.* to ins@'%';
   Query OK, 0 rows affected (0.00 sec)
   ```

5. Create dummy data in MySQL database. Enter the following command at the terminal prompt and receive the results shown:.
   ```command
   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. Return to `monitor1` by exit from MySQL client and then the SSH terminal:
   ```command
   EXIT
   ```
   ```output
   mysql> EXIT;
   Bye
   ```
   ```command
   exit
   ```
   ```output
   logout
   Connection to XX.XX.XX.XX closed.
   ```

7.  Create a user on TiDB for `sync-diff-inspector` task:
   ```command
   $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
   ```
   ```command
   CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_';
   ```
   ```output
   mysql> CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_';
   Query OK, 0 rows affected (0.06 sec)
   ```

   ```command
   GRANT ALL PRIVILEGES on *.* to ins@'%';
   ```
   ```output
   mysql> GRANT ALL PRIVILEGES on *.* to ins@'%';
   Query OK, 0 rows affected (0.04 sec)
   ```

8.  Create dummy data in TiDB cluster. Enter the following command at the terminal prompt and receive the results shown:
    ```command
    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)
    ```

    ```command
    EXIT
     ```
    ```output
    mysql> EXIT;
    Bye
    ```

9. Read the file `solution-sync-diff-config.toml`. The IP addresses shown below:
   ```command
   $  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. Compare the table structures and table data using `sync-diff-inspector`. Enter the following command at the terminal prompt and receive the results shown: 
    ```command
    $ source hosts-env.sh
    ```
    ```command
    $ ./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. View the contents of the `output/summary.txt` file. Verify that tables `T1`, `T2`, `T3` and `T4` contain the data from MySQL and TiDB.
    ```command
    $ 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. Connect to the MySQL running on port 3306 as `root`. `INSERT`, `DELETE`, `UPDATE` on `T1`, `T2`, `T3` and `T4` tables. Enter the following command at the terminal prompt and receive the results shown:
    ```command
    $ source hosts-env.sh
    ```
    ```command
    $ mysql -u ins -h ${HOST_PD3_PRIVATE_IP} -P 3306 -pq1w2e3R4_
    ```
    ```command
    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. Compare the table structures and table data using `sync-diff-inspector`. Enter the following command at the terminal prompt and receive the results shown:
    ```command
    $ source hosts-env.sh
    ```
    ```command
    $ ./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. View the contents of the `output/summary.txt` file. Verify that tables `T1`, `T2`, `T3` and `T4` contain the data from MySQL and TiDB.
    ```command
    $ 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
    ``` 

15. Close all terminal windows. 

## End of Exercises