• DM 分库分表合并场景
    • 上游实例
    • 同步需求
    • 下游实例
    • 同步方案
    • 同步任务配置

    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