## モジュール 3: 二つの MySQL インスタンスからのデータ移行 (TiDB Data Migration 設定)

### 手順 1. TiDB Data Migration ソース設定を作成する

+ `tiup dmctl:v7.5.1 --encrypt` 文を実行して、上流データベースMySQLのパスワードを暗号化します。暗号化されたパスワードを一時ファイルにコピーして保存してください。この後のステップで必要になります。

  ```bash
  tiup dmctl:v7.5.1 --encrypt 'q1w2e3R4_'
  ```

  ```output
  tiup is checking updates for component dmctl ...
  Starting component `dmctl`: /root/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --encrypt mysql
  DMJtTSNCJbEoxUn1RVUZCUD9D3L4Fw==
  ```

+ テキストエディタを使って `mysql-source-conf1.yaml` ファイルをPD1上のMySQLインスタンス用に編集します。`mysql-source-conf1.yaml` ファイルを次のような設定に変更します（割り当てられた正しいIPアドレスで置き換えてください）。

  ```bash
  vi mysql-source-conf1.yaml
  ```

  ```bash
  source-id: "mysql-replica-01"
  from:
     host: "${HOST_PD1_PRIVATE_IP}"
     port: 3306
     user: "dm_user"
     password: "${encrypted_password_in_previous_step}"
  ```

+ テキストエディタを使って `mysql-source-conf2.yaml` ファイルをPD2上のMySQLインスタンス用に編集します。`mysql-source-conf2.yaml` ファイルを次のような設定に変更します（割り当てられた正しいIPアドレスで置き換えてください）。

  ```bash
  vi mysql-source-conf2.yaml
  ```

  ```bash
  source-id: "mysql-replica-02"
  from:
     host: "${HOST_PD2_PRIVATE_IP}"
     port: 3306
     user: "dm_user"
     password: "${encrypted_password_in_previous_step}"
  ```

+ `tiup dmctl:v7.5.1` 文を実行して、PD1上のMySQLインスタンス用の構成ファイルを上流データベースとしてロードします。

  ```bash
  tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source create mysql-source-conf1.yaml
  ```

  ```output
  Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 operate-source create mysql-source-conf1.yaml
  {
        "result": true,
        "msg": "",
        "sources": [
           {
              "result": true,
              "msg": "",
              "source": "mysql-replica-01",
              "worker": "dm-10.90.3.231-8262"
           }
        ]
  }
  ```

+ `tiup dmctl:v7.5.1` 文を実行して、PD2上のMySQLインスタンス用の構成ファイルを上流データベースとしてロードします。

  ```bash
  tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source create mysql-source-conf2.yaml
  ```

  ```output
  Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 operate-source create mysql-source-conf2.yaml
  {
        "result": true,
        ``msg": "",
        "sources": [
           {
              ``result": true,
              "msg": "",
              "source": "mysql-replica-02",
              "worker": "dm-10.90.3.7-8262"
           }
        ]
  }
  ```

+ ロードされた構成`mysql-replica-01`と`mysql-replica-02`の内容を表示します。

  ```bash
  tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 get-config source mysql-replica-01
  ```

  ```output
  Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 get-config source mysql-replica-01
  {
     "result": true,
     "msg": "",
     "cfg": "enable: true\nenable-gtid: false\nauto-fix-gtid: false\nrelay-dir: relay-dir\nmeta-dir: \"\"\nflavor: mysql\ncharset: \"\"\nenable-relay: false\nrelay-binlog-name: \"\"\nrelay-binlog-gtid: \"\"\nsource-id: mysql-replica-01\nfrom:\n  host: 10.90.3.251\n  port: 3306\n  user: dm_user\n  password: '\u003_ENCODED'\n  max-allowed-packet: null\n  session: {}\n  security: null\npurge:\n  interval: 3600\n  expires: 0\n  remain-space: 15\nchecker:\n  check-enable: true\n  backoff-rollback: 5m0s\n  backoff-max: 5m0s\n  check-interval: 5s\n  backoff-min: 1s\n  backoff-jitter: true\n  backoff-factor: 2\nserver-id: 429532589\ntracer: {}\ncase-sensitive: false\nfilters: []\n"
  }
  ```

  ```bash
  tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 get-config source mysql-replica-02
  ```

  ```output
  Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 get-config source mysql-replica-02
  {
     "result": true,
     "msg": "",
     "cfg": "enable: true\nenable-gtid: false\nauto-fix-gtid: false\nrelay-dir: relay-dir\nmeta-dir: \"\"\nflavor: mysql\ncharset: \"\"\nenable-relay: false\nrelay-binlog-name: \"\"\nrelay-binlog-gtid: \"\"\nsource-id: mysql-replica-02\nfrom:\n  host: 10.90.3.59\n  port: 3306\n  user: dm_user\n  password: '\u003_ENCODED'\n  max-allowed-packet: null\n  session: {}\n  security: null\npurge:\n  interval: 3600\n  expires: 0\n  remain-space: 15\nchecker:\n  check-enable: true\n  backoff-rollback: 5m0s\n  backoff-max: 5m0s\n  check-interval: 5s\n  backoff-min: 1s\n  backoff-jitter: true\n  backoff-factor: 2\nserver-id: 429553756\ntracer: {}\ncase-sensitive: false\nfilters: []\n"
  }
  ```

+ 追加されたデータソースと対応する`DM-worker`を確認します。

  ```bash
  tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source show
  ```

  ```output
  Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 operate-source show
  {
      "result": true,
      "msg": "",
      "sources": [
          {
              "result": true,
              "msg": "",
              "source": "mysql-replica-01",
              "worker": "dm-10.90.1.161-8262"
          },
          {
              "result": true,
              "msg": "",
              "source": "mysql-replica-02",
              "worker": "dm-10.90.1.139-8262"
          }
      ]
  }
  ```

### 手順 2. TiDB Data Migration タスク設定を作成する

+ `TiDB Data Migration` タスク設定ファイル `dm-task.yaml` を作成し、テキストエディタを使用して `dm-task.yaml` を編集します。`dm-task.yaml` ファイルは次のような設定を含む必要があります。二つの項目に注意を払い、yamlファイルの `${HOST_DB1_PRIVATE_IP}` と `${encrypted_password_in_previous_step}` をそれぞれTiDBサーバーのプライベートIPアドレスと前セクションの暗号化されたパスワードで置き換える必要があります。ソリューションyaml `solution-dm-task.yaml` も提供されていますが、`solution-dm-task.yaml`でも `${encrypted_password_in_previous_step}` を置き換える必要があります。

   ```bash
   vi dm-task.yaml
   ```

  ```bash
  name: "dm-task"  # The name of the task is dm-task.
  task-mode: all    # The task mode is set to full (only migrates full data).
  ignore-checking-items: ["auto_increment_ID"] # Ignorable checking auto_increment_ID.

  target-database:  # set the host, port, user and password of the downstream database TiDB.
   host: "${HOST_DB1_PRIVATE_IP}"
   port: 4000
   user: "dm_user"
   password: "${encrypted_password_in_previous_step}"

  mysql-instances:  # configure data sources that need to be migrated for the data migration task.
   - source-id: "mysql-replica-01"
     route-rules: ["instance-1-user-schema-rule","salesdb-schema-route-rule"]
     filter-rules: ["trace-table-filter-rule", "user-table-filter-rule" , "store-table-filter-rule"]
     block-allow-list: "log-ignored"
     mydumper-config-name: "global"
   - source-id: "mysql-replica-02"
     route-rules: ["instance-2-user-schema-rule", "instance-2-store-schema-rule","salesdb-schema-route-rule"]
     filter-rules: ["trace-table-filter-rule", "user-table-filter-rule" , "store-table-filter-rule"]
     block-allow-list: "log-ignored"
     mydumper-config-name: "global"

  routes:
  # Rule 1: Migrate all tables in the user database in upstream MySQL instance on PD1 to user_north database in the downstream TiDB instance. Migrate all tables in the `user` database in upstream MySQL instance on PD2 to user_east database in the downstream TiDB instance.
   instance-1-user-schema-rule: # The first of the routing mapping rule.
    schema-pattern: "user"
    target-schema: "user_north"
   instance-2-user-schema-rule:
    schema-pattern: "user"
    target-schema: "user_east"

  # Rule 2: Migrate all tables in store database in upstream MySQL instance to store database in the downstream TiDB instance, except the table store.store_sz in upstream MySQL instances on PD2 to the table store.store_suzhou in the downstream TiDB instance.
   instance-2-store-schema-rule:
    schema-pattern: "store"
    table-pattern: "store_sz"
    target-schema: "store"
    target-table: "store_suzhou"

  # Rule 3: Migrate the table salesdb.sales which is sharded schemas in upstream MySQL instances on PD1 and PD2 in the two upstream MySQL instances to the salesdb.sales tables in the downstream TiDB instance.
   salesdb-schema-route-rule:
    schema-pattern: "salesdb"
    target-schema: "salesdb"

  # Rule 4: Any delete DML event on user.*, any drop table, truncate table DDL event on table user.trace and any delete, truncate table, drop table DDL event on store database in any upstream MySQL instance will not be replicated to the downstream TiDB instance.
  filters:
   trace-table-filter-rule:
    schema-pattern: "user"
    table-pattern: "trace"
    events: ["truncate table", "DROP TABLE", "delete"]
    action: Ignore
   user-table-filter-rule:
    schema-pattern: "user"
    events: ["drop database"]
    action: Ignore
   store-table-filter-rule:
    schema-pattern: "store"
    events: ["drop database", "truncate table", "DROP TABLE", "delete"]
    action: Ignore

  # Rule 5: The log databases in any upstream MySQL instance will not be replicated to the downstream TiDB instance.
  block-allow-list:
   log-ignored:
    ignore-dbs: ["log"]
  mydumpers:
   global:
    threads: 4
    chunk-filesize: 64
  ```

+ `dm-task.yaml` 構成を確認するために `check-task` コマンドを実行します。`pre-check is passed` メッセージが期待されます。

  ```bash
  tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 check-task dm-task.yaml
  ```

  ```output
  tiup is checking updates for component dmctl ...
  Starting component `dmctl`: /root/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=172.31.0.232:8261 check-task dm-task.yaml
  {
     "result": true,
     "msg": "pre-check is passed. "
  }
  ```