DM 分库分表合并场景

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

上游实例

假设上游库结构如下：

实例 1

实例 2

实例 3

同步需求

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

下游实例

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

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

同步方案

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

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

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

      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"

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

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

注意：

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

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

      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

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

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

要满足同步需求 #8，配置 column mapping 规则如下：

      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", "_"]

同步任务配置

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

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"
# 所有实例共享的其他通用配置
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-tables:
    - 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

阅读全文

看完两件小事

如果你觉得这篇文章对你挺有启发，我想请你帮我两个小忙：

关注我们的 GitHub 博客，让我们成为长期关系
把这篇文章分享给你的朋友 / 交流群，让更多的人看到，一起进步，一起成长！
关注公众号 「方志朋」，公众号后台回复「资源」免费领取我精心整理的前端进阶资源教程

JS中文网是中国领先的新一代开发者社区和专业的技术媒体,一个帮助开发者成长的社区，目前已经覆盖和服务了超过 300 万开发者，你每天都可以在这里找到技术世界的头条内容。欢迎热爱技术的你一起加入交流与学习,JS中文网的使命是帮助开发者用代码改变世界

分库分表合并场景