# 实验 10：使用 TiDB Data Migration 工具进行数据迁移 ## 概述 在本实验中，你将使用 `TiDB Data Migration` 工具进行数据迁移。以下展示了 TiDB 集群的部署拓扑图。 ![60%](https://lab-static.pingcap.com/quick-demo/TiDBClusterDeploymentTopologyExercisewithDataMigration_aa_005_20230301.png) + `TiDB Data Migration` 工具用于支持将上游 MySQL 实例中的全量数据迁移和增量数据复制到下游 TiDB 实例。以下展示了 `TiDB Data Migration` 集群的拓扑图。 ![60%](https://lab-static.pingcap.com/quick-demo/DMLabToplogy_aa_005_20220626.png) > [!TIP] > 点击 **开始** 启动实验环境。 > 环境准备就绪后，点击底部工具栏中的 **前进** 进入 **模块 1**。 > 然后，按照实验指南的步骤连接到实践环境并开始你的任务。 ## 模块 1：部署 TiDB Data Migration 集群 ### 步骤 1：登录远程 Linux + 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。 + 登录后显示的横幅： ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### 步骤 2. 安装 `TiDB Data Migration` 工具 ```bash tiup install dm dmctl:v7.5.1 ``` ### 步骤 3. 查看 `solution-dm-topology-six-nodes.yaml` 文件 ```bash cat solution-dm-topology-six-nodes.yaml ``` ```output # The topology template is used deploy a minimal DM cluster, which suitable # for scenarios with only three machines. The minimal cluster contains # - 3 master nodes # - 3 worker nodes # You can change the hosts according your environment --- global: user: "tidb" ssh_port: 22 deploy_dir: "/tidb-deploy" data_dir: "/tidb-data" arch: "amd64" master_servers: - host: 10.90.2.84 - host: 10.90.2.134 - host: 10.90.2.214 worker_servers: - host: 10.90.3.239 - host: 10.90.3.119 - host: 10.90.3.181 monitoring_servers: - host: 10.90.2.84 grafana_servers: - host: 10.90.2.84 alertmanager_servers: - host: 10.90.2.84 ``` ### 步骤 4. 验证 `TiDB Data Migration` 工具已安装 ```bash tiup list dm-master ``` ```output Available versions for dm-master: Version Installed Release Platforms ------- --------- ------- --------- v7.5.1 2022-06-10T14:27:38+08:00 linux/amd64,linux/arm64 ... ``` ### 步骤 5. 部署一个 `TiDB Data Migration` 集群 ```bash tiup dm deploy dm-test v7.5.1 --user ec2-user -i ~/.ssh/pe-class-key-oregon.pem ./solution-dm-topology-six-nodes.yaml --yes ``` ```output ... Cluster `dm-test` deployed successfully, you can start it with command: `tiup dm start dm-test` ``` ### 步骤 6. 启动 `TiDB Data Migration` 集群 ```bash tiup dm start dm-test ``` ```output ... Started cluster `dm-test` successfully ``` ### 步骤 7. 检查由 TiUP 管理的 `TiDB Data Migration` 集群 ```bash tiup dm list ``` ```output tiup is checking updates for component dm ... Starting component `dm`: /home/ec2-user/.tiup/components/dm/v1.12.1/tiup-dm list Name User Version Path PrivateKey ---- ---- ------- ---- ---------- dm-test tidb v7.5.1 /home/ec2-user/.tiup/storage/dm/clusters/dm-test /home/ec2-user/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa ``` ### 步骤 8. 检查已部署的 `TiDB Data Migration` 集群的状态 ```bash tiup dm display dm-test ``` ```output tiup is checking updates for component dm ... Starting component `dm`: /home/ec2-user/.tiup/components/dm/v1.12.1/tiup-dm display dm-test Cluster type: dm Cluster name: dm-test Cluster version: v7.5.1 Deploy user: tidb SSH type: builtin Grafana URL: http://10.90.4.231:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.231:9093 alertmanager 10.90.4.231 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.1.34:8261 dm-master 10.90.1.34 8261/8291 linux/x86_64 Healthy|L /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261 10.90.2.38:8261 dm-master 10.90.2.38 8261/8291 linux/x86_64 Healthy /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261 10.90.3.188:8261 dm-master 10.90.3.188 8261/8291 linux/x86_64 Healthy /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261 10.90.1.162:8262 dm-worker 10.90.1.162 8262 linux/x86_64 Free /tidb-data/dm-worker-8262 /tidb-deploy/dm-worker-8262 10.90.2.108:8262 dm-worker 10.90.2.108 8262 linux/x86_64 Free /tidb-data/dm-worker-8262 /tidb-deploy/dm-worker-8262 10.90.3.12:8262 dm-worker 10.90.3.12 8262 linux/x86_64 Free /tidb-data/dm-worker-8262 /tidb-deploy/dm-worker-8262 10.90.4.231:3000 grafana 10.90.4.231 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.4.231:9090 prometheus 10.90.4.231 9090 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 Total nodes: 9 ``` ## 模块 2：从两个 MySQL 实例迁移数据 (数据准备) ### 步骤 1. 在 PD1 上设置数据源 MySQL 实例 + 以 ec2-user 用户身份登录到 PD1。返回的消息可能与这里显示的略有不同。如果有提示，回答 `yes`。 ```bash ./ssh-to-pd1.sh ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + 在 `PD1` 上启动 MySQL 实例。 ```bash sudo service mysqld start ``` ```output Redirecting to /bin/systemctl start mysqld.service ``` + 使用 `root@'localhost'` 用户连接到 PD1 上的 MySQL server。 ```bash mysql -h localhost -P 3306 -uroot -pq1w2e3R4_ ``` ```output Enter password: ... Server version: 5.7.41-log ... mysql> ``` + 创建一个新用户 `dm_user@'%'`，并授予该用户权限： ```sql CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; ``` ```output mysql> CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; Query OK, 0 rows affected (0.00 sec) ``` ```sql GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; ``` ```output mysql> GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; Query OK, 0 rows affected (0.00 sec) ``` + 开启 `show_compatibility_56`： ```sql SET @@global.show_compatibility_56=ON; ``` ```output mysql> SET @@global.show_compatibility_56=ON; Query OK, 0 rows affected (0.00 sec) ``` + 退出终端并返回控制机 (monitor1_address)： ```sql EXIT ``` ```output mysql> EXIT Bye ``` ```bash exit ``` ```output logout Connection to XX.XX.XX.XX closed. ``` ### 步骤 2. 在 PD2 上设置数据源 MySQL 实例 + 以 ec2-user 用户身份登录到 PD2。返回的消息可能与这里显示的略有不同。 ```bash ./ssh-to-pd2.sh ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + 在 `PD2` 上启动 MySQL 实例。 ```bash sudo service mysqld start ``` ```output Redirecting to /bin/systemctl start mysqld.service ``` + 使用 `root@'localhost'` 用户连接到 PD2 上的 MySQL server。 ```bash mysql -h localhost -P 3306 -uroot -pq1w2e3R4_ ``` ```output Enter password: ... Server version: 5.7.41-log ... mysql> ``` + 创建一个新用户 `dm_user@'%'`，并授予该用户权限。 ```sql CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; ``` ```output mysql> CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; Query OK, 0 rows affected (0.00 sec) ``` ```sql GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; ``` ```output mysql> GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; Query OK, 0 rows affected (0.00 sec) ``` + 开启 `show_compatibility_56`。 ```sql SET @@global.show_compatibility_56=ON; ``` ```output mysql> SET @@global.show_compatibility_56=ON; Query OK, 0 rows affected (0.00 sec) ``` + 退出终端并返回控制机。 ```sql EXIT ``` ```output mysql> EXIT Bye ``` ```bash exit ``` ```output logout Connection to XX.XX.XX.XX closed. ``` ### 步骤 3. 向两个 MySQL 实例导入数据 + 确保示例数据已解压。 ```bash unzip -u stage/tidb-admin-dataset-v2.zip -d stage/ ``` + 将 `user`、 `store`、 `log` 和 `salesdb` 数据库导入到 PD1 的 MySQL 中。 ```bash mysql -udm_user -pq1w2e3R4_ -h ${HOST_PD1_PRIVATE_IP} -P3306 < stage/TiDB-Administration-exercise-data-v2/exercise-dm/3306db.sql ``` + 将 `user`、 `store`、 `log` 和 `salesdb` 数据库导入到 PD2 的 MySQL 中。 ```bash mysql -udm_user -pq1w2e3R4_ -h ${HOST_PD2_PRIVATE_IP} -P3306 < stage/TiDB-Administration-exercise-data-v2/exercise-dm/3307db.sql ``` ### 步骤 4. 设置目标 TiDB 集群数据 + 连接到 TiDB： ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot ``` ```output Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 405 Server version: 5.7.25-TiDB-v7.5.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible ... mysql> ``` + 创建一个名为 `dm_user` 的新用户，并授予该用户权限。 ```sql CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; ``` ```output mysql> CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; Query OK, 0 rows affected (0.04 sec) ``` ```sql GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; ``` ```output mysql> GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; Query OK, 0 rows affected (0.02 sec) ``` + 在 TiDB 上创建一个名为 `user_north` 的数据库，其包含名为 `information` 和 `trace` 的表。`information` 表应具有一个名为 `id` 的主键列和一个名为 `info` 的 `VARCHAR(64)` 列。 `trace` 表应具有一个名为 `id` 的主键列和一个名为 `content` 的 `VARCHAR(64)` 列。 ```sql CREATE DATABASE user_north; CREATE TABLE user_north.information(id INT PRIMARY KEY, info VARCHAR(64)); CREATE TABLE user_north.trace(id INT PRIMARY KEY, content VARCHAR(64)); ``` ```output mysql> CREATE DATABASE user_north; Query OK, 0 rows affected (0.08 sec) ... mysql> CREATE TABLE user_north.trace(id INT PRIMARY KEY, content VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) ``` + 在 TiDB 上创建一个名为 `user_east`的数据库，其包含名为 `information` 和 `trace` 的表。`information` 表应具有一个名为 `id` 的主键列和一个名为 `info` 的 `VARCHAR(64)` 列。 `trace` 表应具有一个名为 `id` 的主键列和一个名为 `content` 的 `VARCHAR(64)` 列。 ```sql CREATE DATABASE user_east; CREATE TABLE user_east.information(id INT PRIMARY KEY, info VARCHAR(64)); CREATE TABLE user_east.trace(id INT PRIMARY KEY, content VARCHAR(64)); ``` ```output mysql> CREATE DATABASE user_east; Query OK, 0 rows affected (0.08 sec) ... mysql> CREATE TABLE user_east.trace(id INT PRIMARY KEY, content VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) + 在 TiDB 上创建一个名为 `store` 的数据库，其包含名为 `store_bj`, `store_tj`, `store_sh` 和 `store_suzhou` 的表。`store_bj` 表应具有一个名为 `id`的主键列和一个名为 `pname` 的 `VARCHAR(64)` 列。`store_tj` 表应具有一个名为 `id` 的主键列和一个名为 `pname` 的 `VARCHAR(64)` 列。`store_sh` 表应具有一个名为 `id` 的主键列和一个名为 `pname` 的 `VARCHAR(64)` 列。`store_suzhou`表应具有一个名为 `id` 的主键列和一个名为 `pname` 的`VARCHAR(64)` 列。 ```sql CREATE DATABASE store; CREATE TABLE store.store_bj(id INT PRIMARY KEY, pname VARCHAR(64)); CREATE TABLE store.store_tj(id INT PRIMARY KEY, pname VARCHAR(64)); CREATE TABLE store.store_sh(id INT PRIMARY KEY, pname VARCHAR(64)); CREATE TABLE store.store_suzhou(id INT PRIMARY KEY, pname VARCHAR(64)); ``` ```output mysql> CREATE DATABASE store; Query OK, 0 rows affected (0.09 sec) ... mysql> CREATE TABLE store.store_suzhou(id INT PRIMARY KEY, pname VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) ``` + 在 TiDB 上创建一个名为 `salesdb`的数据库，其包含一个名为 `sales`的表。`sales` 表应具有一个名为 `id` 的主键列，一个名为 `pname` 的 `VARCHAR(20)`列和一个名为 `cnt` 的 `int` 列。 ```sql CREATE DATABASE salesdb; CREATE TABLE salesdb.sales(id INT PRIMARY KEY, pname VARCHAR(20), cnt int); ``` ```output mysql> CREATE DATABASE salesdb; Query OK, 0 rows affected (0.09 sec) mysql> CREATE TABLE sales.sales(id INT PRIMARY KEY, pname VARCHAR(20), cnt int); Query OK, 0 rows affected (0.09 sec) ``` + 在 TiDB 上创建一个名为 `log` 的数据库，包含一个名为 `messages` 的表。 `messages` 表应具有一个名为 `id` 的主键列和一个名为 `msg` 的 `VARCHAR(64)` 列。然后退出会话。 ```bash CREATE DATABASE log; CREATE TABLE log.messages(id INT PRIMARY KEY, msg VARCHAR(64)); ``` ```output mysql> CREATE DATABASE log; Query OK, 0 rows affected (0.08 sec) mysql> CREATE TABLE log.messages(id INT PRIMARY KEY, msg VARCHAR(64)); Query OK, 0 rows affected (0.08 sec) ``` ```sql EXIT ``` ```output mysql> EXIT Bye ``` ## 模块 3：从两个 MySQL 实例迁移数据（DM 配置） ### 步骤 1. 创建 DM 源配置 + 执行 `tiup dmctl:v7.5.1 --encrypt` 语句对上游数据库 MySQL 的密码进行加密。将加密后的密码复制并保存到一个临时文件中，后续步骤会用到。 ```bash tiup dmctl:v7.5.1 --encrypt 'q1w2e3R4_' ``` ```output tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --encrypt mysql DMJtTSNCJbEoxUn1RVUZCUD9D3L4Fw== ``` + 使用文本编辑器编辑 PD1 上 MySQL 实例的 `mysql-source-conf1.yaml` 文件。修改 `mysql-source-conf1.yaml` 文件，使其包含类似以下的设置（将 IP 地址替换为分配给你的正确地址）： ```bash vi mysql-source-conf1.yaml ``` ```bash source-id: "mysql-replica-01" from: host: "${HOST_PD1_PRIVATE_IP}" port: 3306 user: "dm_user" password: "${encrypted_password_in_previous_step}" ``` + 使用文本编辑器编辑 PD2 上 MySQL 实例的 `mysql-source-conf2.yaml` 文件。修改 `mysql-source-conf2.yaml` 文件，使其包含类似以下的设置（将 IP 地址替换为分配给你的正确地址）： ```bash vi mysql-source-conf2.yaml ``` ```bash source-id: "mysql-replica-02" from: host: "${HOST_PD2_PRIVATE_IP}" port: 3306 user: "dm_user" password: "${encrypted_password_in_previous_step}" ``` + 执行 `tiup dmctl:v7.5.1` 语句，将 PD1 上 MySQL 实例的配置文件加载为上游数据库。 ```bash tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source create mysql-source-conf1.yaml ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 operate-source create mysql-source-conf1.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-10.90.3.231-8262" } ] } ``` + 执行 `tiup dmctl:v7.5.1` 语句，将 PD2 上 MySQL 实例的配置文件加载为上游数据库。 ```bash tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source create mysql-source-conf2.yaml ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 operate-source create mysql-source-conf2.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-10.90.3.7-8262" } ] } ``` + 查看名为 `mysql-replica-01` 和 `mysql-replica-02` 的已加载配置的内容。 ```bash tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 get-config source mysql-replica-01 ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 get-config source mysql-replica-01 { "result": true, "msg": "", "cfg": "enable: true\nenable-gtid: false\nauto-fix-gtid: false\nrelay-dir: relay-dir\nmeta-dir: \"\"\nflavor: mysql\ncharset: \"\"\nenable-relay: false\nrelay-binlog-name: \"\"\nrelay-binlog-gtid: \"\"\nsource-id: mysql-replica-01\nfrom:\n host: 10.90.3.251\n port: 3306\n user: dm_user\n password: '******'\n max-allowed-packet: null\n session: {}\n security: null\npurge:\n interval: 3600\n expires: 0\n remain-space: 15\nchecker:\n check-enable: true\n backoff-rollback: 5m0s\n backoff-max: 5m0s\n check-interval: 5s\n backoff-min: 1s\n backoff-jitter: true\n backoff-factor: 2\nserver-id: 429532589\ntracer: {}\ncase-sensitive: false\nfilters: []\n" } ``` ```bash tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 get-config source mysql-replica-02 ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 get-config source mysql-replica-02 { "result": true, "msg": "", "cfg": "enable: true\nenable-gtid: false\nauto-fix-gtid: false\nrelay-dir: relay-dir\nmeta-dir: \"\"\nflavor: mysql\ncharset: \"\"\nenable-relay: false\nrelay-binlog-name: \"\"\nrelay-binlog-gtid: \"\"\nsource-id: mysql-replica-02\nfrom:\n host: 10.90.3.59\n port: 3306\n user: dm_user\n password: '******'\n max-allowed-packet: null\n session: {}\n security: null\npurge:\n interval: 3600\n expires: 0\n remain-space: 15\nchecker:\n check-enable: true\n backoff-rollback: 5m0s\n backoff-max: 5m0s\n check-interval: 5s\n backoff-min: 1s\n backoff-jitter: true\n backoff-factor: 2\nserver-id: 429553756\ntracer: {}\ncase-sensitive: false\nfilters: []\n" } ``` + 查看添加的数据源和相应的 `DM-worker`. ```bash tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source show ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 operate-source show { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-10.90.3.231-8262" }, { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-10.90.3.7-8262" } ] } ``` ### 步骤 2. 创建 DM 任务配置 + 创建一个名为 `dm-task.yaml` 的 `TiDB Data Migration` 任务配置文件，使用文本编辑器编辑 `dm-task.yaml`。 `dm-task.yaml` 文件现在应包含类似以下的设置。 注意 yaml 文件中的两个项 `${HOST_DB1_PRIVATE_IP}` 和 `${encrypted_password_in_previous_step}`，你需要分别用 TiDB server 的私有 IP 地址和上一节中的加密密码替换它们。这里也提供了示例 yaml 文件 `solution-dm-task.yaml`，请注意即使在 `solution-dm-task.yaml` 中，你也需要替换 `${encrypted_password_in_previous_step}`。 ```bash vi dm-task.yaml ``` ```bash name: "dm-task" # The name of the task is dm-task. task-mode: all # The task mode is set to full (only migrates full data). ignore-checking-items: ["auto_increment_ID"] # Ignorable checking auto_increment_ID. target-database: # set the host, port, user and password of the downstream database TiDB. host: "${HOST_DB1_PRIVATE_IP}" port: 4000 user: "dm_user" password: "${encrypted_password_in_previous_step}" mysql-instances: # configure data sources that need to be migrated for the data migration task. - source-id: "mysql-replica-01" route-rules: ["instance-1-user-schema-rule","salesdb-schema-route-rule"] filter-rules: ["trace-table-filter-rule", "user-table-filter-rule" , "store-table-filter-rule"] block-allow-list: "log-ignored" mydumper-config-name: "global" - source-id: "mysql-replica-02" route-rules: ["instance-2-user-schema-rule", "instance-2-store-schema-rule","salesdb-schema-route-rule"] filter-rules: ["trace-table-filter-rule", "user-table-filter-rule" , "store-table-filter-rule"] block-allow-list: "log-ignored" mydumper-config-name: "global" routes: # Rule 1: Migrate all tables in the user database in upstream MySQL instance on PD1 to user_north database in the downstream TiDB instance. Migrate all tables in the `user` database in upstream MySQL instance on PD2 to user_east database in the downstream TiDB instance. instance-1-user-schema-rule: # The first of the routing mapping rule. schema-pattern: "user" target-schema: "user_north" instance-2-user-schema-rule: schema-pattern: "user" target-schema: "user_east" # Rule 2: Migrate all tables in store database in upstream MySQL instance to store database in the downstream TiDB instance, except the table store.store_sz in upstream MySQL instances on PD2 to the table store.store_suzhou in the downstream TiDB instance. instance-2-store-schema-rule: schema-pattern: "store" table-pattern: "store_sz" target-schema: "store" target-table: "store_suzhou" # Rule 3: Migrate the table salesdb.sales which is sharded schemas in upstream MySQL instances on PD1 and PD2 in the two upstream MySQL instances to the salesdb.sales tables in the downstream TiDB instance. salesdb-schema-route-rule: schema-pattern: "salesdb" target-schema: "salesdb" # Rule 4: Any delete DML event on user.*, any drop table, truncate table DDL event on table user.trace and any delete, truncate table, drop table DDL event on store database in any upstream MySQL instance will not be replicated to the downstream TiDB instance. filters: trace-table-filter-rule: schema-pattern: "user" table-pattern: "trace" events: ["truncate table", "DROP TABLE", "delete"] action: Ignore user-table-filter-rule: schema-pattern: "user" events: ["drop database"] action: Ignore store-table-filter-rule: schema-pattern: "store" events: ["drop database", "truncate table", "DROP TABLE", "delete"] action: Ignore # Rule 5: The log databases in any upstream MySQL instance will not be replicated to the downstream TiDB instance. block-allow-list: log-ignored: ignore-dbs: ["log"] mydumpers: global: threads: 4 chunk-filesize: 64 ``` + 执行 `check-task` 命令验证 `dm-task.yaml` 配置。预期会收到 `pre-check is passed` 消息。 ```bash tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 check-task dm-task.yaml ``` ```output tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=172.31.0.232:8261 check-task dm-task.yaml { "result": true, "msg": "pre-check is passed. " } ``` ## 模块 4：运行 DM 任务并验证迁移结果 ### 步骤 1. 启动 TiDB Data Migration 任务 + 使用 `tiup dmctl:v7.5.1` 命令启动迁移任务。 ```sql tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 start-task dm-task.yaml ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 start-task dm-task.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-10.90.3.231-8262" }, { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-10.90.3.7-8262" } ], "checkResult": "pre-check is passed. " } ``` + 查看任务状态。 ```bash tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 query-status dm-task.yaml ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.3.251:8261 query-status dm-task.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "sourceStatus": { "source": "mysql-replica-01", "worker": "dm-10.90.3.231-8262", "result": null, "relayStatus": null }, "subTaskStatus": [ { ... } ] }, { "result": true, "msg": "", "sourceStatus": { "source": "mysql-replica-02", "worker": "dm-10.90.3.7-8262", "result": null, "relayStatus": null }, "subTaskStatus": [ { ... } ] } ] } ``` ### 步骤 2. 验证 TiDB Data Migration 任务的结果 + 验证下游数据库 TiDB 包含来自迁移任务的完整数据。 ```bash mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot ``` ```sql USE user_east; SHOW TABLES; SELECT * FROM information; SELECT * FROM trace; USE user_north; SHOW TABLES; SELECT * FROM information; SELECT * FROM trace; USE store; SHOW TABLES; SELECT * FROM store_bj; SELECT * FROM store_tj; SELECT * FROM store_suzhou; SELECT * FROM store_sh; USE salesdb; SELECT * FROM sales; ``` ```output mysql> USE user_east; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +---------------------+ | Tables_in_user_east | +---------------------+ | information | | trace | +---------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM information; +----+-------+ | id | info | +----+-------+ | 1 | andy | | 2 | candy | +----+-------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM trace; +----+---------+ | id | content | +----+---------+ | 1 | error | +----+---------+ 1 row in set (0.00 sec) ... mysql> USE salesdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT * FROM sales; +----+-------+------+ | id | pname | cnt | +----+-------+------+ | 1 | book | 100 | | 2 | cup | 200 | +----+-------+------+ 2 rows in set (0.00 sec) ``` + 退出到 shell 终端。 ```bash EXIT; ``` ### 步骤 3. 在上游数据库中插入新值 + 验证在上游数据库中插入数据后，下游数据库也能接收到该值。 + 在 `PD1` 上 ```bash ./ssh-to-pd1.sh ``` ```bash mysql -u root -p -h 127.0.0.1 -P 3306 -pq1w2e3R4_ ``` ```sql INSERT INTO salesdb.sales VALUES(3,'shoes',120); ``` ```output mysql> INSERT INTO salesdb.sales VALUES(3,'shoes',120); Query OK, 1 row affected (0.01 sec) ``` ```sql SELECT * FROM salesdb.sales; ``` ```output mysql> SELECT * FROM salesdb.sales; +----+-------+------+ | id | pname | cnt | +----+-------+------+ | 1 | book | 100 | | 3 | shoes | 120 | +----+-------+------+ 2 rows in set (0.00 sec) ``` ```sql EXIT ``` ```output mysql> EXIT; Bye ``` ```bash exit ``` ```output logout Connection to XX.XX.XX.XX closed. ``` + 在 `Control Machine` 上 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot ``` ```sql SELECT * FROM salesdb.sales; ``` ```output mysql> SELECT * FROM salesdb.sales; +----+-------+------+ | id | pname | cnt | +----+-------+------+ | 1 | book | 100 | | 2 | cup | 200 | | 3 | shoes | 120 | +----+-------+------+ 3 rows in set (0.00 sec) ``` ```sql EXIT ``` ```output mysql> EXIT Bye ``` ## 模块 5：对 TiDB Data Migration 集群进行缩容与扩容 ### 步骤 1. 检查已部署的 `TiDB Data Migration` 集群的状态 ```bash tiup dm display dm-test ``` ```output tiup is checking updates for component dm ... Starting component `dm`: /home/ec2-user/.tiup/components/dm/v1.12.1/tiup-dm display dm-test Cluster type: dm Cluster name: dm-test Cluster version: v7.5.1 Deploy user: tidb SSH type: builtin Grafana URL: http://10.90.4.231:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.231:9093 alertmanager 10.90.4.231 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.1.34:8261 dm-master 10.90.1.34 8261/8291 linux/x86_64 Healthy|L /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261 10.90.2.38:8261 dm-master 10.90.2.38 8261/8291 linux/x86_64 Healthy /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261 10.90.3.188:8261 dm-master 10.90.3.188 8261/8291 linux/x86_64 Healthy /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261 10.90.1.162:8262 dm-worker 10.90.1.162 8262 linux/x86_64 Bound /tidb-data/dm-worker-8262 /tidb-deploy/dm-worker-8262 10.90.2.108:8262 dm-worker 10.90.2.108 8262 linux/x86_64 Bound /tidb-data/dm-worker-8262 /tidb-deploy/dm-worker-8262 10.90.3.12:8262 dm-worker 10.90.3.12 8262 linux/x86_64 Free /tidb-data/dm-worker-8262 /tidb-deploy/dm-worker-8262 10.90.4.231:3000 grafana 10.90.4.231 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.4.231:9090 prometheus 10.90.4.231 9090 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 Total nodes: 9 ``` ### 步骤 2. 对 DM 集群进行缩容 + 运行 `tiup dm scale-in` 命令对 `TiDB Data Migration` 集群进行缩容，使具有 `KV server 1` IP 地址的 `DM-worker` 节点离线。 ```bash tiup dm scale-in dm-test -N ${HOST_KV1_PRIVATE_IP}:8262 ``` ```output ... Do you want to continue? [y/N]:(default=N) y ... Scaled cluster `dm-test` in successfully ``` ### 步骤 3. 再次检查已部署的 `TiDB Data Migration` 集群的状态 ```bash tiup dm display dm-test ``` ```output tiup is checking updates for component dm ... Starting component `dm`: /home/ec2-user/.tiup/components/dm/v1.12.1/tiup-dm display dm-test Cluster type: dm Cluster name: dm-test Cluster version: v7.5.1 Deploy user: tidb SSH type: builtin Grafana URL: http://10.90.4.231:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.231:9093 alertmanager 10.90.4.231 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.1.34:8261 dm-master 10.90.1.34 8261/8291 linux/x86_64 Healthy|L /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261 10.90.2.38:8261 dm-master 10.90.2.38 8261/8291 linux/x86_64 Healthy /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261 10.90.3.188:8261 dm-master 10.90.3.188 8261/8291 linux/x86_64 Healthy /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261 10.90.1.162:8262 dm-worker 10.90.1.162 8262 linux/x86_64 Bound /tidb-data/dm-worker-8262 /tidb-deploy/dm-worker-8262 10.90.3.12:8262 dm-worker 10.90.3.12 8262 linux/x86_64 Bound /tidb-data/dm-worker-8262 /tidb-deploy/dm-worker-8262 10.90.4.231:3000 grafana 10.90.4.231 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.4.231:9090 prometheus 10.90.4.231 9090 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 Total nodes: 8 ``` ### 步骤 4. 准备 `dm-scale.yaml` 文件 + 使用文本编辑器编辑 `dm-scale.yaml` 文件。修改 `dm-scale.yaml` 文件，使其包含类似以下的设置（将 IP 地址替换为分配给你的正确地址）。 ```bash vi dm-scale.yaml ``` ```bash worker_servers: - host: ${HOST_KV1_PRIVATE_IP} ``` ### 步骤 5. 对 DM 集群进行扩容 ```bash tiup dm --user ec2-user -i ~/.ssh/pe-class-key-oregon.pem scale-out dm-test dm-scale.yaml ``` ```output ... Do you want to continue? [y/N]: (default=N) y ... Scaled cluster `dm-test` out successfully ``` ### 步骤 6. 再次检查已部署的 `TiDB Data Migration` 集群的状态 ```bash tiup dm display dm-test ``` ### 步骤 7. 执行 `stop-task` 命令停止数据迁移任务 ```bash tiup dmctl:v7.5.1 --master-addr=${HOST_PD3_PRIVATE_IP}:8261 stop-task dm-task.yaml ``` ```output tiup is checking updates for component dmctl ... Starting component `dmctl`: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.2.38:8261 stop-task dm-task.yaml { "op": "Delete", "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-10.90.3.12-8262" }, { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-10.90.1.162-8262" } ] } ``` ### 步骤 8. 验证 DM 集群数据迁移已停止 ```bash tiup dmctl:v7.5.1 --master-addr=${HOST_PD3_PRIVATE_IP}:8261 query-status dm-task.yaml ``` ```output Starting component `dmctl`: /home/ec2-user/.tiup/components/dmctl/v7.5.1/dmctl/dmctl --master-addr=10.90.2.38:8261 query-status dm-task.yaml { "result": false, "msg": "task dm-task has no source or not exist", "sources": [ ] } ``` ### 步骤 9. 停止 DM 集群 ```bash tiup dm stop dm-test ``` ```output ... Do you want to continue? [y/N]:(default=N) y ... Stopped cluster `dm-test` successfully ``` ### 步骤 10. 检查 `TiDB Data Migration` 集群的状态 ```bash tiup dm display dm-test ``` ### 步骤 11. 销毁你的 DM 集群 ```bash tiup dm destroy dm-test ``` ## 实验结束