--- title: "Migrating Data Using TiDB Data Migration" version: "v2" status: "GA" description: "In this hands-on lab, you will learn how to migrate data using TiDB Data Migration. Specifically, we will cover how to deploy TiCDC, create a replication task, and remove a TiCDC cluster." --- # Lab 10: Migrating Data Using TiDB Data Migration ## Overview >[!NOTE] > This hands-on lab is also included in the [TiDB Certification Training: TiDB Administration Using TiUP](https://www.pingcap.com/education/course-tidb-database-administration-for-self-hosted-deployments/) course. In this lab, you migrate data using the `TiDB Data Migration` tool. The following shows the deployment topology diagram of TiDB cluster. ![60%](https://lab-static.pingcap.com/quick-demo/TiDBClusterDeploymentTopologyExercisewithDataMigration_aa_005_20230301.png) + The `TiDB Data Migration` is used for supporting the full data migration and the incremental data replication from the upstream MySQL instances into downstream TiDB instance. The following shows the topology diagram of `TiDB Data Migration` cluster. ![60%](https://lab-static.pingcap.com/quick-demo/DMLabToplogy_aa_005_20220626.png) > [!TIP] > Click **Start** to launch the lab environment. > Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**. > And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks. ## Module 1: Deploying TiDB Data Migration Cluster ### Step 1. Log into the remote Linux + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner after you logged in: ```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/ ``` ### Step 2. Install `TiDB Data Migration` ```bash tiup install dm dmctl:v7.5.1 ``` ### Step 3. Read the file `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 ``` ### Step 4. Verify that the `TiDB Data Migration` is installed ```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 ... ``` ### Step 5. Deploy a `TiDB Data Migration` cluster ```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` ``` ### Step 6. Start the `TiDB Data Migration` cluster ```bash tiup dm start dm-test ``` ```output ... Started cluster `dm-test` successfully ``` ### Step 7. Check the `TiDB Data Migration` cluster managed by TiUP ```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 ``` ### Step 8. Check the status of the deployed `TiDB Data Migration` cluster ```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 ``` ## Module 2: Migrating Data From Two MySQL Instances (Schema Preparation) ### Step 1. Setup source MySQL instance on PD1 + Login as user ec2-user on PD1. The returned message may be slightly different from what is displayed here. If prompted, answer `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/ ``` + Start the MySQL instance on `PD1`. ```bash sudo service mysqld start ``` ```output Redirecting to /bin/systemctl start mysqld.service ``` + Connect to MySQL server on PD1 using user `root@'localhost'` ```bash mysql -h localhost -P 3306 -uroot -pq1w2e3R4_ ``` ```output Enter password: ... Server version: 5.7.41-log ... mysql> ``` + Create a new user `dm_user@'%'`, and grant privileges to the 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) ``` + Turn on `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) ``` + Exit to terminal and exit to the Control Machine (monitor1_address): ```sql EXIT ``` ```output mysql> EXIT Bye ``` ```bash exit ``` ```output logout Connection to XX.XX.XX.XX closed. ``` ### Step 2. Setup source MySQL instance on PD2 + Login as user ec2-user on PD2. The returned message may be slightly different from what is displayed here. ```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/ ``` + Start the MySQL instance on `PD2`. ```bash sudo service mysqld start ``` ```output Redirecting to /bin/systemctl start mysqld.service ``` + Connect to MySQL server on PD2 using user `root@'localhost'`. ```bash mysql -h localhost -P 3306 -uroot -pq1w2e3R4_ ``` ```output Enter password: ... Server version: 5.7.41-log ... mysql> ``` + Create a new user `dm_user@'%'`, and grant privileges to the 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) ``` + Turn on `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) ``` + Exit to terminal and exit to the Control Machine. ```sql EXIT ``` ```output mysql> EXIT Bye ``` ```bash exit ``` ```output logout Connection to XX.XX.XX.XX closed. ``` ### Step 3. Import data into two MySQL instances + Make sure the sample is extract. ```bash unzip -u stage/tidb-admin-dataset-v2.zip -d stage/ ``` + Import the `user`, `store`, `log` and `salesdb` databases into MySQL on PD1. ```bash mysql -udm_user -pq1w2e3R4_ -h ${HOST_PD1_PRIVATE_IP} -P3306 < stage/TiDB-Administration-exercise-data-v2/exercise-dm/3306db.sql ``` + Import the `user`, `store`, `log` and `salesdb` databases into MySQL on PD2. ```bash mysql -udm_user -pq1w2e3R4_ -h ${HOST_PD2_PRIVATE_IP} -P3306 < stage/TiDB-Administration-exercise-data-v2/exercise-dm/3307db.sql ``` ### Step 4. Setup target TiDB cluster schemas + Connect to the 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> ``` + Create new user called `dm_user`, and grant privileges to the 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) ``` + Create a database on the TiDB called `user_north`, with table called `information` and `trace`. The `information` table should have a primary key column called `id` and a `VARCHAR(64)` column called `info`. The `trace` table should have a primary key column called `id` and a `VARCHAR(64)` column called `content`. ```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) ``` + Create a database on the TiDB called `user_east`, with tables called `information` and `trace`. The `information` table should have a primary key column called `id` and a `VARCHAR(64)` column called `info`. The `trace` table should have a primary key column called `id` and a `VARCHAR(64)` column called `content`. ```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) + Create a database on the TiDB called `store`, with tables called `store_bj`, `store_tj`, `store_sh` and `store_suzhou`. The `store_bj` table should have a primary key column called `id` and a `VARCHAR(64)` column called `pname`. The `store_tj` table should have a primary key column called `id` and a `VARCHAR(64)` column called `pname`. The `store_sh` table should have a primary key column called `id` and a `VARCHAR(64)` column called `pname`. The `store_suzhou` table should have a primary key column called `id` and a `VARCHAR(64)` column called `pname`. ```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) ``` + Create a database on the TiDB called `salesdb`, with a table called `sales`. The `sales` table should have a primary key column called `id`, a `VARCHAR(20)` column called `pname` and an `int` column called `cnt`. ```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) ``` + Create a database on the TiDB called `log`, with a table called `messages`. The `messages` table should have a primary key column called `id`, and a `VARCHAR(64)` column called `msg`. Then exit the session. ```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 ``` ## Module 3: Migrating Data From Two MySQL Instances (DM Configuration) ### Step 1. Create the DM source configurations + Execute the `tiup dmctl:v7.5.1 --encrypt` statements to encrypt the password of the upstream database MySQL. Copy and save the encrypted password to a temporary file, you will need it in the subsequent steps. ```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== ``` + Use a text editor to edit the `mysql-source-conf1.yaml` file for MySQL instance on PD1. Modify `mysql-source-conf1.yaml` file to contain settings similar to those that follow (replace the encrypted password): ```bash vi mysql-source-conf1.yaml ``` ```bash source-id: "mysql-replica-01" from: host: "${LAB:HOST_PD1_PRIVATE_IP}" port: 3306 user: "dm_user" password: "${encrypted_password_in_previous_step}" ``` + Use a text editor to edit the `mysql-source-conf2.yaml` file for MySQL instance on PD2. Modify `mysql-source-conf2.yaml` file to contain settings similar to those that follow (replace the encrypted password): ```bash vi mysql-source-conf2.yaml ``` ```bash source-id: "mysql-replica-02" from: host: "${LAB:HOST_PD2_PRIVATE_IP}" port: 3306 user: "dm_user" password: "${encrypted_password_in_previous_step}" ``` + Execute the `tiup dmctl:v7.5.1` statement to load the configuration file for MySQL instance on PD1 as the upstream database. ```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" } ] } ``` + Execute the `tiup dmctl:v7.5.1` statements to load the configuration file for MySQL instance on PD2 as the upstream database. ```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" } ] } ``` + View the content of the loaded configuration named `mysql-replica-01` and `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" } ``` + Confirm the added data source and the corresponding `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.1.161-8262" }, { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-10.90.1.139-8262" } ] } ``` ### Step 2. Create the DM task configuration + Create a `TiDB Data Migration` task configuration file named `dm-task.yaml`, use a text editor to edit the `dm-task.yaml`. The `dm-task.yaml` file should now contain settings similar to those that follow. Pay attention to `${encrypted_password_in_previous_step}` in the yaml file, you need to replace them with the encrypted password from the previous section. The solution yaml, `solution-dm-task.yaml` is also provided, please note you are required to replace the `${encrypted_password_in_previous_step}` even in the solution yaml - `solution-dm-task.yaml`. ```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: "${LAB: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 ``` + Execute the `check-task` command to verify the `dm-task.yaml` configuration. Message `pre-check is passed` is expected. ```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. " } ``` ## Module 4: Run the DM Task and Verify the Migration Result ### Step 1. Start the TiDB Data Migration task + Start a migration task by using `tiup dmctl:v7.5.1` command. ```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. " } ``` + View the status of the task. ```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": [ { ... } ] } ] } ``` ### Step 2. Verify the result of TiDB Data Migration task + Verify that the downstream database TiDB contains the full data from the migration task. ```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) ``` + Exit to the shell terminal. ```bash EXIT; ``` ### Step 3. Insert new values in the upsteam database + Verify that after inserting data into the upstream database, the downstream database can also receive the value. + On `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. ``` + On the `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 ``` ## Module 5: Scale-in and Scale-out the TiDB Data Migration Cluster ### Step 1. Check the status of the deployed `TiDB Data Migration` cluster ```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 ``` ### Step 2. Scale in the DM cluster + Run the `tiup dm scale-in` command to scale in the `TiDB Data Migration` cluster, and take the `DM-worker` node with `KV server 1` IP address offline. ```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 ``` ### Step 3. Check the status of the deployed `TiDB Data Migration` cluster again ```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 ``` ### Step 4. Prepare the `dm-scale.yaml` file + Check the contents in the `dm-scale.yaml` file. ```bash cat dm-scale.yaml ``` ```output worker_servers: - host: ${LAB:HOST_KV1_PRIVATE_IP} ``` ### Step 5. Scale out the DM cluster ```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 ``` ### Step 6. Check the status of the deployed `TiDB Data Migration` cluster again ```bash tiup dm display dm-test ``` ### Step 7. Execute the `stop-task` command to stop data migration tasks ```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" } ] } ``` ### Step 8. Verify that the DM cluster data migration has stopped ```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": [ ] } ``` ### Step 9. Stop the DM cluster ```bash tiup dm stop dm-test ``` ```output ... Do you want to continue? [y/N]:(default=N) y ... Stopped cluster `dm-test` successfully ``` ### Step 10. Check the status of the `TiDB Data Migration` cluster ```bash tiup dm display dm-test ``` ### Step 11. Destroy your DM cluster ```bash tiup dm destroy dm-test ``` ## End of Exercises