Using Migrate Data from Multiple Data Sources to TiDB

This document shows how to use Data Migration (DM) in a simple data migration scenario where the data of three data source MySQL instances needs to be migrated to a downstream TiDB cluster (no sharding data).

Data source instances

Assume that the data sources are as follows:

Instance 1
Schema Tables
user information, log
store store_bj, store_tj
log messages
Instance 2
Schema Tables
user information, log
store store_sh, store_sz
log messages
Instance 3
Schema Tables
user information, log
store store_gz, store_sz
log messages

Schema	Tables
user	information, log
store	store_bj, store_tj
log	messages

Schema	Tables
user	information, log
store	store_sh, store_sz
log	messages

Schema	Tables
user	information, log
store	store_gz, store_sz
log	messages

Migration requirements

Do not merge the user schema.
1. Migrate the user schema of instance 1 to the user_north of TiDB.
2. Migrate the user schema of instance 2 to the user_east of TiDB.
3. Migrate the user schema of instance 3 to the user_south of TiDB.
4. Never delete the table log.
Migrate the upstream store schema to the downstream store schema without merging tables.
1. store_sz exists in both instances 2 and 3, which is migrated to store_suzhou and store_shenzhen respectively.
2. Never delete store.
The log schema needs to be filtered out.

Downstream instances

Assume that the schemas migrated to the downstream are as follows:

Schema	Tables
user_north	information, log
user_east	information, log
user_south	information, log
store	store_bj, store_tj, store_sh, store_suzhou, store_gz, store_shenzhen

Migration solution

To satisfy migration Requirements #1-i, #1-ii and #1-iii, configure the table routing rules as follows:

routes:
  ...
  instance-1-user-rule:
    schema-pattern: "user"
    target-schema: "user_north"
  instance-2-user-rule:
    schema-pattern: "user"
    target-schema: "user_east"
  instance-3-user-rule:
    schema-pattern: "user"
    target-schema: "user_south"

To satisfy the migration Requirement #2-i, configure the table routing rules as follows:

routes:
  ...
  instance-2-store-rule:
    schema-pattern: "store"
    table-pattern: "store_sz"
    target-schema: "store"
    target-table:  "store_suzhou"
  instance-3-store-rule:
    schema-pattern: "store"
    table-pattern: "store_sz"
    target-schema: "store"
    target-table:  "store_shenzhen"

To satisfy the migration Requirement #1-iv, configure the binlog filtering rules as follows:

filters:
  ...
  log-filter-rule:
    schema-pattern: "user"
    table-pattern: "log"
    events: ["truncate table", "drop table", "delete"]
    action: Ignore
  user-filter-rule:
    schema-pattern: "user"
    events: ["drop database"]
    action: Ignore

To satisfy the migration Requirement #2-ii, configure the binlog filtering rule as follows:
```
filters:
  ...
  store-filter-rule:
    schema-pattern: "store"
    events: ["drop database", "truncate table", "drop table", "delete"]
    action: Ignore
```
Note
store-filter-rule is different from log-filter-rule & user-filter-rule. store-filter-rule is a rule for the whole store schema, while log-filter-rule and user-filter-rule are rules for the log table in the user schema.

To satisfy the migration Requirement #3, configure the block and allow lists as follows:

block-allow-list:  # Use black-white-list if the DM version is earlier than or equal to v2.0.0-beta.2.
  log-ignored:
    ignore-dbs: ["log"]

Migration task configuration

The complete migration task configuration is shown below. For more details, see data migration task configuration guide.

name: "one-tidb-secondary"
task-mode: all
meta-schema: "dm_meta"

target-database:
  host: "192.168.0.1"
  port: 4000
  user: "root"
  password: ""

mysql-instances:
  -
    source-id: "instance-1"
    route-rules: ["instance-1-user-rule"]
    filter-rules: ["log-filter-rule", "user-filter-rule", "store-filter-rule"]
    block-allow-list:  "log-ignored"  # Use black-white-list if the DM version is earlier than or equal to v2.0.0-beta.2.
    mydumper-config-name: "global"
    loader-config-name: "global"
    syncer-config-name: "global"
  -
    source-id: "instance-2"
    route-rules: ["instance-2-user-rule", instance-2-store-rule]
    filter-rules: ["log-filter-rule", "user-filter-rule", "store-filter-rule"]
    block-allow-list:  "log-ignored"  # Use black-white-list if the DM version is earlier than or equal to v2.0.0-beta.2.
    mydumper-config-name: "global"
    loader-config-name: "global"
    syncer-config-name: "global"
  -
    source-id: "instance-3"
    route-rules: ["instance-3-user-rule", instance-3-store-rule]
    filter-rules: ["log-filter-rule", "user-filter-rule", "store-filter-rule"]
    block-allow-list:  "log-ignored"  # Use black-white-list if the DM version is earlier than or equal to v2.0.0-beta.2.
    mydumper-config-name: "global"
    loader-config-name: "global"
    syncer-config-name: "global"

# other common configs shared by all instances

routes:
  instance-1-user-rule:
    schema-pattern: "user"
    target-schema: "user_north"
  instance-2-user-rule:
    schema-pattern: "user"
    target-schema: "user_east"
  instance-3-user-rule:
    schema-pattern: "user"
    target-schema: "user_south"
  instance-2-store-rule:
    schema-pattern: "store"
    table-pattern: "store_sz"
    target-schema: "store"
    target-table:  "store_suzhou"
  instance-3-store-rule:
    schema-pattern: "store"
    table-pattern: "store_sz"
    target-schema: "store"
    target-table:  "store_shenzhen"

filters:
  log-filter-rule:
    schema-pattern: "user"
    table-pattern: "log"
    events: ["truncate table", "drop table", "delete"]
    action: Ignore
  user-filter-rule:
    schema-pattern: "user"
    events: ["drop database"]
    action: Ignore
  store-filter-rule:
    schema-pattern: "store"
    events: ["drop database", "truncate table", "drop table", "delete"]
    action: Ignore

block-allow-list:  # Use black-white-list if the DM version is earlier than or equal to v2.0.0-beta.2.
  log-ignored:
    ignore-dbs: ["log"]

mydumpers:
  global:
    threads: 4
    chunk-filesize: 64

loaders:
  global:
    pool-size: 16
    dir: "./dumped_data"

syncers:
  global:
    worker-count: 16
    batch: 100
    max-retry: 100