simple-synchronization-scenario.md

title	summary	category
Data Migration Simple Usage Scenario	Learn how to use Data Migration to synchronize data in a simple scenario.	tools

Data Migration Simple Usage Scenario

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

Upstream instances

Assume that the upstream schemas 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

Synchronization requirements

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

Downstream instances

Assume that the schemas synchronized 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

Synchronization solution

• To satisfy synchronization 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 synchronization 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 synchronization 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 synchronization 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 synchronization Requirement #3, configure the black and white lists as follows:

  black-white-list:
    log-ignored:
      ignore-dbs: ["log"]

Synchronization task configuration

The complete synchronization task configuration is shown below. For more details, see configuration explanations.

name: "one-tidb-slave"
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: ["instance-1-user-rule"]
    filter-rules: ["log-filter-rule", "user-filter-rule" , "store-filter-rule"]
    black-white-list:  "log-ignored"
    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"]
    black-white-list:  "log-ignored"
    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"]
    black-white-list:  "log-ignored"
    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

black-white-list:
  log-ignored:
    ignore-dbs: ["log"]

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