## モジュール 2: MySQL と TiDB 間のデータチェック

### 手順 1. PD3 に SSH 接続

  ```bash
  ./ssh-to-pd3.sh
  ```

### 手順 2. MySQL サーバーインスタンスを開始する

  ```bash
  sudo service mysqld start
  ```

### 手順 3. MySQL サーバーにログイン

  ```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
  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
  ```

  ```sql
  EXIT;
  ```

### 手順 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
  ```