Data Migration Shard Merge Scenario

This document shows how to use Data Migration (DM) in the shard merge scenario where the sharded schemas and sharded tables data of three upstream MySQL instances need to be synchronized to a downstream TiDB cluster.

Upstream instances

Assume that the upstream schemas are as follows:

• Instance 1

  Schema	Tables
  user	information, log_north, log_bak
  store_01	sale_01, sale_02
  store_02	sale_01, sale_02

• Instance 2

  Schema	Tables
  user	information, log_east, log_bak
  store_01	sale_01, sale_02
  store_02	sale_01, sale_02

• Instance 3

  Schema	Tables
  user	information, log_south, log_bak
  store_01	sale_01, sale_02
  store_02	sale_01, sale_02

Synchronization requirements

1. Merge the user.information table of three upstream instances to the downstream user.information table in TiDB.
2. Merge the user.log_{north|south|east} table of three upstream instances to the downstream user.log_{north|south|east} table in TiDB.
3. Merge the store_{01|02}.sale_{01|02} table of three upstream instances to the downstream store.sale table in TiDB.
4. Filter out all the deletion operations in the user.log_{north|south|east} table of three upstream instances.
5. Filter out all the deletion operations in the user.information table of three upstream instances.
6. Filter out all the deletion operations in the store_{01|02}.sale_{01|02} table of three upstream instances.
7. Filter out the user.log_bak table of three upstream instances.
8. Because the store_{01|02}.sale_{01|02} tables have auto-increment primary keys of the bigint type, the conflict occurs when these tables are merged into TiDB. So you need to modify the auto-increment primary keys to avoid the conflict.

Downstream instances

Assume that the downstream schema after synchronization is as follows:

Synchronization solution

• To satisfy the synchronization Requirements #1 and #2, configure the table routing rule as follows:

  routes:
    ...
    user-route-rule:
      schema-pattern: "user"
      target-schema: "user"

• To satisfy the synchronization Requirement #3, configure the table routing rule as follows:

  routes:
    ...
    store-route-rule:
      schema-pattern: "store_*"
      target-schema: "store"
    sale-route-rule:
      schema-pattern: "store_*"
      table-pattern: "sale_*"
      target-schema: "store"
      target-table:  "sale"

• To satisfy the synchronization Requirements #4 and #5, configure the binlog event filtering rule as follows:

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

  Note: The synchronization Requirements #4, #5 and #7 indicate that all the deletion operations in the user schema are filtered out, so a schema level filtering rule is configured here. However, the deletion operations of future tables in the user schema will also be filtered out.

• To satisfy the synchronization Requirement #6, configure the binlog event filter rule as follows:

  filters:
    ...
    sale-filter-rule:
      schema-pattern: "store_*"
      table-pattern: "sale_*"
      events: ["truncate table", "drop table", "delete"]
      action: Ignore
    store-filter-rule:
      schema-pattern: "store_*"
      events: ["drop database"]
      action: Ignore

• To satisfy the synchronization Requirement #7, configure the black and white table lists as follows:

  black-white-list:
    log-bak-ignored:
      ignore-tales:
      - db-name: "user"
        tbl-name: "log_bak"

• To satisfy the synchronization Requirement #8, configure the column mapping rule as follows:

  column-mappings:
    instance-1-sale:
      schema-pattern: "store_*"
      table-pattern: "sale_*"
      expression: "partition id"
      source-column: "id"
      target-column: "id"
      arguments: ["1", "store_", "sale_"]
    instance-2-sale:
      schema-pattern: "store_*"
      table-pattern: "sale_*"
      expression: "partition id"
      source-column: "id"
      target-column: "id"
      arguments: ["2", "store_", "sale_"]
    instance-3-sale:
      schema-pattern: "store_*"
      table-pattern: "sale_*"
      expression: "partition id"
      source-column: "id"
      target-column: "id"
      arguments: ["3", "store_", "sale_"]

Synchronization task configuration

The complete configuration of the synchronization task is shown as below. For more details, see Data Migration Task Configuration File.

name: "shard_merge"
task-mode: all
meta-schema: "dm_meta"
remove-meta: false

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

mysql-instances:
  -
    source-id: "instance-1"
    route-rules: ["user-route-rule", "store-route-rule", "sale-route-rule"]
    filter-rules: ["user-filter-rule", "store-filter-rule" , "sale-filter-rule"]
    column-mapping-rules: ["instance-1-sale"]
    black-white-list:  "log-bak-ignored"
    mydumper-config-name: "global"
    loader-config-name: "global"
    syncer-config-name: "global"

  -
    source-id: "instance-2"
    route-rules: ["user-route-rule", "store-route-rule", "sale-route-rule"]
    filter-rules: ["user-filter-rule", "store-filter-rule" , "sale-filter-rule"]
    column-mapping-rules: ["instance-2-sale"]
    black-white-list:  "log-bak-ignored"
    mydumper-config-name: "global"
    loader-config-name: "global"
    syncer-config-name: "global"
  -
    source-id: "instance-3"
    route-rules: ["user-route-rule", "store-route-rule", "sale-route-rule"]
    filter-rules: ["user-filter-rule", "store-filter-rule" , "sale-filter-rule"]
    column-mapping-rules: ["instance-3-sale"]
    black-white-list:  "log-bak-ignored"
    mydumper-config-name: "global"
    loader-config-name: "global"
    syncer-config-name: "global"

# Other common configs shared by all instances.

routes:
  user-route-rule:
    schema-pattern: "user"
    target-schema: "user"
  store-route-rule:
    schema-pattern: "store_*"
    target-schema: "store"
  sale-route-rule:
    schema-pattern: "store_*"
    table-pattern: "sale_*"
    target-schema: "store"
    target-table:  "sale"

filters:
  user-filter-rule:
    schema-pattern: "user"
    events: ["truncate table", "drop table", "delete"， "drop database"]
    action: Ignore
  sale-filter-rule:
    schema-pattern: "store_*"
    table-pattern: "sale_*"
    events: ["truncate table", "drop table", "delete"]
    action: Ignore
  store-filter-rule:
    schema-pattern: "store_*"
    events: ["drop database"]
    action: Ignore

black-white-list:
  log-bak-ignored:
    ignore-tales:
    - db-name: "user"
      tbl-name: "log_bak"

column-mappings:
  instance-1-sale:
    schema-pattern: "store_*"
    table-pattern: "sale_*"
    expression: "partition id"
    source-column: "id"
    target-column: "id"
    arguments: ["1", "store_", "sale_"]
  instance-2-sale:
    schema-pattern: "store_*"
    table-pattern: "sale_*"
    expression: "partition id"
    source-column: "id"
    target-column: "id"
    arguments: ["2", "store_", "sale_"]
  instance-3-sale:
    schema-pattern: "store_*"
    table-pattern: "sale_*"
    expression: "partition id"
    source-column: "id"
    target-column: "id"
    arguments: ["3", "store_", "sale_"]

mydumpers:
  global:
    threads: 4
    chunk-filesize: 64
    skip-tz-utc: true

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

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