DM 分库分表合并场景

本文介绍如何在分库分表合并场景中使用 Data Migration (DM)。使用场景中，三个上游 MySQL 实例的分库和分表数据需要同步至下游 TiDB 集群。

上游实例

假设上游库结构如下：

  • 实例 1
Schema Tables
user information, log_north, log_bak
store_01 sale_01, sale_02
store_02 sale_01, sale_02
  • 实例 2
Schema Tables
user information, log_east, log_bak
store_01 sale_01, sale_02
store_02 sale_01, sale_02
  • 实例 3
Schema Tables
user information, log_south, log_bak
store_01 sale_01, sale_02
store_02 sale_01, sale_02

同步需求

  1. 合并三个实例中的 user.information 表至下游 TiDB 中的 user.information 表。
  2. 合并三个实例中的 user.log_{north|south|east} 表至下游TiDB中的 user.log_{north|south|east} 表。
  3. 合并三个实例中的 store_{01|02}.sale_{01|02} 表至下游TiDB中的 store.sale 表。
  4. 过滤掉三个实例的 user.log_{north|south|east} 表的所有删除操作。
  5. 过滤掉三个实例的 user.information 表的所有删除操作。
  6. 过滤掉三个实例的 store_{01|02}.sale_{01|02} 表的所有删除操作。
  7. 过滤掉三个实例的 user.log_bak 表。
  8. 因为 store_{01|02}.sale_{01|02} 表带有 bigint 型的自增主键，将其合并至 TiDB 时会引发冲突。你需要有相应的方案来避免冲突。

下游实例

假设同步后下游库结构如下：

Schema Tables
user information, log_north, log_east, log_south
store sale

同步方案

  • 要满足同步需求 #1 和 #2，配置 Table routing 规则 如下：

    1.   routes:
    2.     ...
    3.     user-route-rule:
    4.       schema-pattern: "user"
    5.       target-schema: "user"

  • 要满足同步需求 #3，配置 table routing 规则 如下：

    1.   routes:
    2.     ...
    3.     store-route-rule:
    4.       schema-pattern: "store_*"
    5.       target-schema: "store"
    6.     sale-route-rule:
    7.       schema-pattern: "store_*"
    8.       table-pattern: "sale_*"
    9.       target-schema: "store"
    10.       target-table:  "sale"

  • 要满足同步需求 #4 和 #5，配置 Binlog event filter 规则 如下：

    1.   filters:
    2.     ...
    3.     user-filter-rule:
    4.       schema-pattern: "user"
    5.       events: ["truncate table", "drop table", "delete", "drop database"]
    6.       action: Ignore

    注意：

    同步需求 #4、#5 和 #7 的操作意味着过滤掉所有对 user 库的删除操作，所以此处配置了库级别的过滤规则。但是 user 库以后加入表的删除操作也都会被过滤。

  • 要满足同步需求 #6，配置 Binlog event filter 规则 如下：

    1.   filters:
    2.     ...
    3.     sale-filter-rule:
    4.       schema-pattern: "store_*"
    5.       table-pattern: "sale_*"
    6.       events: ["truncate table", "drop table", "delete"]
    7.       action: Ignore
    8.     store-filter-rule:
    9.       schema-pattern: "store_*"
    10.       events: ["drop database"]
    11.       action: Ignore

  • 要满足同步需求 #7，配置 Black & white table lists 如下：

    1.   black-white-list:
    2.     log-bak-ignored:
    3.       ignore-tables:
    4.       - db-name: "user"
    5.         tbl-name: "log_bak"

  • 要满足同步需求 #8，首先参考自增主键冲突处理来解决冲突，保证在同步到下游时不会因为分表中有相同的主键值而使同步出现异常；然后需要配置 ignore-checking-items 来跳过自增主键冲突的检查：

    1.   ignore-checking-items: ["auto_increment_ID"]

同步任务配置

同步任务的完整配置如下。详情请参阅 Data Migration 任务配置文件。

  1. name: "shard_merge"
  2. task-mode: all
  3. meta-schema: "dm_meta"
  4. remove-meta: false
  5. ignore-checking-items: ["auto_increment_ID"]
  7. target-database:
  8.   host: "192.168.0.1"
  9.   port: 4000
  10.   user: "root"
  11.   password: ""
  13. mysql-instances:
  14.   -
  15.     source-id: "instance-1"
  16.     route-rules: ["user-route-rule", "store-route-rule", "sale-route-rule"]
  17.     filter-rules: ["user-filter-rule", "store-filter-rule", "sale-filter-rule"]
  18.     black-white-list:  "log-bak-ignored"
  19.     mydumper-config-name: "global"
  20.     loader-config-name: "global"
  21.     syncer-config-name: "global"
  23.   -
  24.     source-id: "instance-2"
  25.     route-rules: ["user-route-rule", "store-route-rule", "sale-route-rule"]
  26.     filter-rules: ["user-filter-rule", "store-filter-rule", "sale-filter-rule"]
  27.     black-white-list:  "log-bak-ignored"
  28.     mydumper-config-name: "global"
  29.     loader-config-name: "global"
  30.     syncer-config-name: "global"
  31.   -
  32.     source-id: "instance-3"
  33.     route-rules: ["user-route-rule", "store-route-rule", "sale-route-rule"]
  34.     filter-rules: ["user-filter-rule", "store-filter-rule", "sale-filter-rule"]
  35.     black-white-list:  "log-bak-ignored"
  36.     mydumper-config-name: "global"
  37.     loader-config-name: "global"
  38.     syncer-config-name: "global"
  40. # 所有实例共享的其他通用配置
  42. routes:
  43.   user-route-rule:
  44.     schema-pattern: "user"
  45.     target-schema: "user"
  46.   store-route-rule:
  47.     schema-pattern: "store_*"
  48.     target-schema: "store"
  49.   sale-route-rule:
  50.     schema-pattern: "store_*"
  51.     table-pattern: "sale_*"
  52.     target-schema: "store"
  53.     target-table:  "sale"
  55. filters:
  56.   user-filter-rule:
  57.     schema-pattern: "user"
  58.     events: ["truncate table", "drop table", "delete", "drop database"]
  59.     action: Ignore
  60.   sale-filter-rule:
  61.     schema-pattern: "store_*"
  62.     table-pattern: "sale_*"
  63.     events: ["truncate table", "drop table", "delete"]
  64.     action: Ignore
  65.   store-filter-rule:
  66.     schema-pattern: "store_*"
  67.     events: ["drop database"]
  68.     action: Ignore
  70. black-white-list:
  71.   log-bak-ignored:
  72.     ignore-tables:
  73.     - db-name: "user"
  74.       tbl-name: "log_bak"
  76. mydumpers:
  77.   global:
  78.     threads: 4
  79.     chunk-filesize: 64
  80.     skip-tz-utc: true
  82. loaders:
  83.   global:
  84.     pool-size: 16
  85.     dir: "./dumped_data"
  87. syncers:
  88.   global:
  89.     worker-count: 16
  90.     batch: 100
  91.     max-retry: 100