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，配置 column mapping 规则 如下：

    1.   column-mappings:
    2.     instance-1-sale:
    3.       schema-pattern: "store_*"
    4.       table-pattern: "sale_*"
    5.       expression: "partition id"
    6.       source-column: "id"
    7.       target-column: "id"
    8.       arguments: ["1", "store", "sale", "_"]
    9.     instance-2-sale:
    10.       schema-pattern: "store_*"
    11.       table-pattern: "sale_*"
    12.       expression: "partition id"
    13.       source-column: "id"
    14.       target-column: "id"
    15.       arguments: ["2", "store", "sale", "_"]
    16.     instance-3-sale:
    17.       schema-pattern: "store_*"
    18.       table-pattern: "sale_*"
    19.       expression: "partition id"
    20.       source-column: "id"
    21.       target-column: "id"
    22.       arguments: ["3", "store", "sale", "_"]

同步任务配置

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

  1. name: "shard_merge"
  2. task-mode: all
  3. meta-schema: "dm_meta"
  4. remove-meta: false
  6. target-database:
  7.   host: "192.168.0.1"
  8.   port: 4000
  9.   user: "root"
  10.   password: ""
  12. mysql-instances:
  13.   -
  14.     source-id: "instance-1"
  15.     route-rules: ["user-route-rule", "store-route-rule", "sale-route-rule"]
  16.     filter-rules: ["user-filter-rule", "store-filter-rule", "sale-filter-rule"]
  17.     column-mapping-rules: ["instance-1-sale"]
  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.     column-mapping-rules: ["instance-2-sale"]
  28.     black-white-list:  "log-bak-ignored"
  29.     mydumper-config-name: "global"
  30.     loader-config-name: "global"
  31.     syncer-config-name: "global"
  32.   -
  33.     source-id: "instance-3"
  34.     route-rules: ["user-route-rule", "store-route-rule", "sale-route-rule"]
  35.     filter-rules: ["user-filter-rule", "store-filter-rule", "sale-filter-rule"]
  36.     column-mapping-rules: ["instance-3-sale"]
  37.     black-white-list:  "log-bak-ignored"
  38.     mydumper-config-name: "global"
  39.     loader-config-name: "global"
  40.     syncer-config-name: "global"
  42. # 所有实例共享的其他通用配置
  44. routes:
  45.   user-route-rule:
  46.     schema-pattern: "user"
  47.     target-schema: "user"
  48.   store-route-rule:
  49.     schema-pattern: "store_*"
  50.     target-schema: "store"
  51.   sale-route-rule:
  52.     schema-pattern: "store_*"
  53.     table-pattern: "sale_*"
  54.     target-schema: "store"
  55.     target-table:  "sale"
  57. filters:
  58.   user-filter-rule:
  59.     schema-pattern: "user"
  60.     events: ["truncate table", "drop table", "delete", "drop database"]
  61.     action: Ignore
  62.   sale-filter-rule:
  63.     schema-pattern: "store_*"
  64.     table-pattern: "sale_*"
  65.     events: ["truncate table", "drop table", "delete"]
  66.     action: Ignore
  67.   store-filter-rule:
  68.     schema-pattern: "store_*"
  69.     events: ["drop database"]
  70.     action: Ignore
  72. black-white-list:
  73.   log-bak-ignored:
  74.     ignore-tables:
  75.     - db-name: "user"
  76.       tbl-name: "log_bak"
  78. column-mappings:
  79.   instance-1-sale:
  80.     schema-pattern: "store_*"
  81.     table-pattern: "sale_*"
  82.     expression: "partition id"
  83.     source-column: "id"
  84.     target-column: "id"
  85.     arguments: ["1", "store", "sale", "_"]
  86.   instance-2-sale:
  87.     schema-pattern: "store_*"
  88.     table-pattern: "sale_*"
  89.     expression: "partition id"
  90.     source-column: "id"
  91.     target-column: "id"
  92.     arguments: ["2", "store", "sale", "_"]
  93.   instance-3-sale:
  94.     schema-pattern: "store_*"
  95.     table-pattern: "sale_*"
  96.     expression: "partition id"
  97.     source-column: "id"
  98.     target-column: "id"
  99.     arguments: ["3", "store", "sale", "_"]
  101. mydumpers:
  102.   global:
  103.     threads: 4
  104.     chunk-filesize: 64
  105.     skip-tz-utc: true
  107. loaders:
  108.   global:
  109.     pool-size: 16
  110.     dir: "./dumped_data"
  112. syncers:
  113.   global:
  114.     worker-count: 16
  115.     batch: 100
  116.     max-retry: 100