Data Migration 简单使用场景

本文介绍了 DM 工具的一个简单使用场景(非分库分表合并场景):将三个上游 MySQL 实例的数据同步到一个下游 TiDB 集群中。

上游实例

假设上游结构为:

  • 实例 1
Schema Tables
user information, log
store store_bj, store_tj
log messages
  • 实例 2
Schema Tables
user information, log
store store_sh, store_sz
log messages
  • 实例 3
Schema Tables
user information, log
store store_gz, store_sz
log messages

同步要求

  1. 不合并 user 库。

    1. 将实例 1 中的 user 库同步到下游 TiDB 的 user_north 库中。

    2. 将实例 2 中的 user 库同步到下游 TiDB 的 user_east 库中。

    3. 将实例 3 中的 user 库同步到下游 TiDB 的 user_south 库中。

    4. 任何情况下都不删除 log 表的任何数据。

  2. 将上游 store 库同步到下游 store 库中,且同步过程中不合并表。

    1. 实例 2 和实例 3 中都存在 store_sz 表,且这两个 store_sz 表分别被同步到下游的 store_suzhou 表和 store_shenzhen 表中。

    2. 任何情况下都不删除 store 库的任何数据。

  3. log 库需要被过滤掉。

下游实例

假设下游结构为:

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

同步方案

  • 为了满足同步要求中第一点的前三条要求,需要配置以下 table routing 规则

    1. routes:
    2. ...
    3. instance-1-user-rule:
    4. schema-pattern: "user"
    5. target-schema: "user_north"
    6. instance-2-user-rule:
    7. schema-pattern: "user"
    8. target-schema: "user_east"
    9. instance-3-user-rule:
    10. schema-pattern: "user"
    11. target-schema: "user_south"
  • 为了满足同步要求中第二点的第一条要求,需要配置以下 table routing 规则

    1. routes:
    2. ...
    3. instance-2-store-rule:
    4. schema-pattern: "store"
    5. table-pattern: "store_sz"
    6. target-schema: "store"
    7. target-table: "store_suzhou"
    8. instance-3-store-rule:
    9. schema-pattern: "store"
    10. table-pattern: "store_sz"
    11. target-schema: "store"
    12. target-table: "store_shenzhen"
  • 为了满足同步要求中第一点的第四条要求,需要配置以下 binlog event filter 规则

    1. filters:
    2. ...
    3. log-filter-rule:
    4. schema-pattern: "user"
    5. table-pattern: "log"
    6. events: ["truncate table", "drop table", "delete"]
    7. action: Ignore
    8. user-filter-rule:
    9. schema-pattern: "user"
    10. events: ["drop database"]
    11. action: Ignore
  • 为了满足同步要求中第二点的第二条要求,需要配置以下 binlog event filter 规则

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

    注意:

    store-filter-rule 不同于 log-filter-ruleuser-filter-rulestore-filter-rule 是针对整个 store 库的规则,而 log-filter-ruleuser-filter-rule 是针对 user 库中 log 表的规则。

  • 为了满足同步要求中的第三点要求,需要配置以下 black & white table lists 规则

    1. black-white-list:
    2. log-ignored:
    3. ignore-dbs: ["log"]

同步任务配置

以下是完整的同步任务配置,详见配置介绍

  1. name: "one-tidb-slave"
  2. task-mode: all
  3. meta-schema: "dm_meta"
  4. remove-meta: false
  5. target-database:
  6. host: "192.168.0.1"
  7. port: 4000
  8. user: "root"
  9. password: ""
  10. mysql-instances:
  11. -
  12. source-id: "instance-1"
  13. route-rules: ["instance-1-user-rule"]
  14. filter-rules: ["log-filter-rule", "user-filter-rule" , "store-filter-rule"]
  15. black-white-list: "log-ignored"
  16. mydumper-config-name: "global"
  17. loader-config-name: "global"
  18. syncer-config-name: "global"
  19. -
  20. source-id: "instance-2"
  21. route-rules: ["instance-2-user-rule", instance-2-store-rule]
  22. filter-rules: ["log-filter-rule", "user-filter-rule" , "store-filter-rule"]
  23. black-white-list: "log-ignored"
  24. mydumper-config-name: "global"
  25. loader-config-name: "global"
  26. syncer-config-name: "global"
  27. -
  28. source-id: "instance-3"
  29. route-rules: ["instance-3-user-rule", instance-3-store-rule]
  30. filter-rules: ["log-filter-rule", "user-filter-rule" , "store-filter-rule"]
  31. black-white-list: "log-ignored"
  32. mydumper-config-name: "global"
  33. loader-config-name: "global"
  34. syncer-config-name: "global"
  35. # 所有实例的共有配置
  36. routes:
  37. instance-1-user-rule:
  38. schema-pattern: "user"
  39. target-schema: "user_north"
  40. instance-2-user-rule:
  41. schema-pattern: "user"
  42. target-schema: "user_east"
  43. instance-3-user-rule:
  44. schema-pattern: "user"
  45. target-schema: "user_south"
  46. instance-2-store-rule:
  47. schema-pattern: "store"
  48. table-pattern: "store_sz"
  49. target-schema: "store"
  50. target-table: "store_suzhou"
  51. instance-3-store-rule:
  52. schema-pattern: "store"
  53. table-pattern: "store_sz"
  54. target-schema: "store"
  55. target-table: "store_shenzhen"
  56. filters:
  57. log-filter-rule:
  58. schema-pattern: "user"
  59. table-pattern: "log"
  60. events: ["truncate table", "drop table", "delete"]
  61. action: Ignore
  62. user-filter-rule:
  63. schema-pattern: "user"
  64. events: ["drop database"]
  65. action: Ignore
  66. store-filter-rule:
  67. schema-pattern: "store"
  68. events: ["drop database", "truncate table", "drop table", "delete"]
  69. action: Ignore
  70. black-white-list:
  71. log-ignored:
  72. ignore-dbs: ["log"]
  73. mydumpers:
  74. global:
  75. threads: 4
  76. chunk-filesize: 64
  77. skip-tz-utc: true
  78. loaders:
  79. global:
  80. pool-size: 16
  81. dir: "./dumped_data"
  82. syncers:
  83. global:
  84. worker-count: 16
  85. batch: 100
  86. max-retry: 100