## Module 3: Migrating Data From Two MySQL Instances (DM Configuration)

### Step 1. Create the DM source configurations

+ Execute the `tiup dmctl:v7.5.1 --encrypt` statements to encrypt the password of the upstream database MySQL. Copy and save the encrypted password to a temporary file, you will need it in the subsequent steps.

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

+ Use a text editor to edit the `mysql-source-conf1.yaml` file for MySQL instance on PD1. Modify `mysql-source-conf1.yaml` file to contain settings similar to those that follow (replace the encrypted password):

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

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

+ Use a text editor to edit the `mysql-source-conf2.yaml` file for MySQL instance on PD2. Modify `mysql-source-conf2.yaml` file to contain settings similar to those that follow (replace the encrypted password):

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

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

+ Execute the `tiup dmctl:v7.5.1` statement to load the configuration file for MySQL instance on PD1 as the upstream database.

  ```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"
           }
        ]
  }
  ```

+ Execute the `tiup dmctl:v7.5.1` statements to load the configuration file for MySQL instance on PD2 as the upstream database.

  ```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"
           }
        ]
  }
  ```

+ View the content of the loaded configuration named `mysql-replica-01` and `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: '******'\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: '******'\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"
  }
  ```

+ Confirm the added data source and the corresponding `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"
          }
      ]
  }
  ```

### Step 2. Create the DM task configuration

+ Create a `TiDB Data Migration` task configuration file named `dm-task.yaml`, use a text editor to edit the `dm-task.yaml`. The `dm-task.yaml` file should now contain settings similar to those that follow. Pay attention to `${encrypted_password_in_previous_step}` in the yaml file, you need to replace them with the encrypted password from the previous section. The solution yaml, `solution-dm-task.yaml` is also provided, please note you are required to replace the `${encrypted_password_in_previous_step}` even in the solution yaml - `solution-dm-task.yaml`.

  ```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: "${LAB: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
  ```

+ Execute the `check-task` command to verify the `dm-task.yaml` configuration. Message `pre-check is passed` is expected.

  ```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. "
  }
  ```