--- title: "Migrate from MySQL Using TiDB Data Migration" version: "v2" status: "GA" description: "This hands-on lab demonstrates how to migrate from MySQL to TiDB using TiDB Data Migration. TiDB Data Migration is a powerful tool that simplifies the migration process by providing a unified interface for migrating data from MySQL to TiDB. This lab shows you how to migrate from MySQL to TiDB using TiDB Data Migration, including setting up the migration task and verifying the migration result." --- # Migrate from MySQL Using TiDB Data Migration ## Overview In this lab, you migrate data from MySQL to TiDB using the `TiDB Data Migration` tool, it is used for supporting the full data migration and the incremental data replication from the upstream MySQL instances into downstream TiDB instance. ### Duration + This lab should take you approximately 90 minutes to complete. ### Tasks + Install `TiDB Data Migration` component. + Deploy and start a `TiDB Data Migration` cluster using a topology file. + Install `dmctl`. + Create the TiDB Data Migration source configurations. + Enable the MySQL upstream. + Create the TiDB Data Migration task configuration and start the migration task. + Verify the result of the TiDB Data Migration task. > [!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: Connect To A MySQL Server And Run Queries To Verify The Source Schema ### Step 1. Connect to a remote Linux - the TiUP control machine + 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. Connect to MySQL server instance + Connect to MySQL server node. If prompted, answer `yes`. ```bash ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP} ``` ```output Last login: Thu Aug 1 11:58:47 2024 from 10.90.4.90 [rocky@ip-10-90-1-235 ~]$ ``` + Connect to MySQL server as database user `root`. ```bash mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password ``` ```output mysql> ``` ### Step 3. View the existing schema from MySQL + Verify the tables and data in `sbtest` database are already exist. ```sql SHOW DATABASES; ``` ```output +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sbtest | | sys | +--------------------+ 5 rows in set (0.01 sec) ``` ```sql SHOW TABLES FROM sbtest; ``` ```output +------------------+ | Tables_in_sbtest | +------------------+ | sbtest1 | | sbtest2 | | sbtest3 | | sbtest4 | | sbtest5 | | sbtest6 | +------------------+ 6 rows in set (0.00 sec) ``` + Review the schema of table `sbtest.sbtest1`. ```sql DESC sbtest.sbtest1; ``` ```output +-------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | k | int | NO | MUL | 0 | | | c | char(120) | NO | | | | | pad | char(60) | NO | | | | +-------+-----------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) ``` + Review the row count of table `sbtest.sbtest1`. It should take you several seconds. (note that you are connecting to MySQL right now) ```sql SELECT COUNT(id) FROM sbtest.sbtest1; ``` ```output +-----------+ | COUNT(id) | +-----------+ | 10000000 | +-----------+ 1 row in set (29.75 sec) ``` ### Step 4. Understand the data volume in the source MySQL server + Execute `ANALYZE` command on database `sbtest`. Besides `sbtest1`, the database also contains `sbtest2`, `sbtest3`, `sbtest4`, `sbtest5`, and `sbtest6`. ```sql ANALYZE TABLE sbtest.sbtest1, sbtest.sbtest2, sbtest.sbtest3, sbtest.sbtest4, sbtest.sbtest5, sbtest.sbtest6; ``` ```output +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | sbtest.sbtest1 | analyze | status | OK | | sbtest.sbtest2 | analyze | status | OK | | sbtest.sbtest3 | analyze | status | OK | | sbtest.sbtest4 | analyze | status | OK | | sbtest.sbtest5 | analyze | status | OK | | sbtest.sbtest6 | analyze | status | OK | +----------------+---------+----------+----------+ 6 rows in set (34.08 sec) ``` + Get the data volume of `sbtest` database, it should be around `13.43GB`. ```sql SELECT CONCAT(ROUND(SUM((data_length + index_length)/1024/1024/1024),2), 'GB') AS data FROM information_schema.tables WHERE table_schema='sbtest'; ``` ```output +---------+ | data | +---------+ | 13.43GB | +---------+ 1 row in set (0.00 sec) ``` + Exit to OS prompt. ```sql EXIT ``` ```output bye ``` + Keep the remote terminal connected to MySQL server (the prompt you observe should be starting with `[rocky@`) and proceed to the next practice. ## Module 2: Migrate The Sample Database From MySQL To TiDB ### Step 1. Create a user in MySQL for migration purpose + On the [终端 1](ssh://monitor1-ssh#1), connect to MySQL server via it's private IP address. And then, create a dedicated user `dm_user` on MySQL for the migration purpose. ```bash X=q1w2e3R4_ mysql -h localhost -P 3306 -uroot -p$X --connect-expired-password << EOF DROP USER IF EXISTS dm_user@'%'; CREATE USER dm_user@'%' IDENTIFIED BY 'q1w2e3R4_'; GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; EXIT EOF ``` ```output mysql: [Warning] Using a password on the command line interface can be insecure. ``` + Exit to the remote TiUP control machine. ```bash exit ``` ```output logout Connection to 10.90.1.38 closed. ``` ### Step 2. Check the status of TiDB cluster + Verify that the TiDB cluster is already up and running. Check the TiDB cluster status. Note that there is one TiDB server and three TiKV servers in `tidb-demo` TiDB cluster, and the `TiProxy` is deployed on the same node with `Alert Manager`, `Grafana`, and `Prometheus`. ```bash ~/.tiup/bin/tiup cluster display tidb-demo ``` ```output Cluster type: tidb Cluster name: tidb-demo Cluster version: v6.5.9 Deploy user: ec2-user SSH type: builtin Dashboard URL: http://34.221.120.15:2379/dashboard Grafana URL: http://10.90.4.90:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.90:9093 alertmanager 10.90.4.90 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.90:3000 grafana 10.90.4.90 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.2.11:2379 pd 10.90.2.11 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.90:9090 prometheus 10.90.4.90 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.1.173:4000 tidb 10.90.1.173 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.1.233:20160 tikv 10.90.1.233 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.1.242:20160 tikv 10.90.1.242 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.1.79:20160 tikv 10.90.1.79 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.4.90:6000 tiproxy 10.90.4.90 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 9 ``` ### Step 3. Create a user in TiDB for migration purpose + Create a dedicated user `dm_user` in TiDB. ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot << 'EOF' DROP USER IF EXISTS dm_user@'%'; CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; EXIT EOF ``` ### Step 4. Create a target database named `sbtest` with empty tables + Note that column `id` is set to `AUTO_RANDOM` instead of `AUTO_INCREMENT`. `AUTO_RANDOM` is a TiDB specific feature to avoid the hotspot performance issues, to learn more about `AUTO_RANDOM` please use the `Ask AI` feature located at the bottom right. ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot << 'EOF' CREATE DATABASE sbtest; CREATE TABLE `sbtest`.`sbtest1` ( `id` bigint NOT NULL AUTO_RANDOM, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `sbtest`.`sbtest2` ( `id` bigint NOT NULL AUTO_RANDOM, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `sbtest`.`sbtest3` ( `id` bigint NOT NULL AUTO_RANDOM, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `sbtest`.`sbtest4` ( `id` bigint NOT NULL AUTO_RANDOM, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `sbtest`.`sbtest5` ( `id` bigint NOT NULL AUTO_RANDOM, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `sbtest`.`sbtest6` ( `id` bigint NOT NULL AUTO_RANDOM, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; SET GLOBAL allow_auto_random_explicit_insert = true; USE sbtest; SHOW TABLES; EXIT EOF ``` ```output Tables_in_sbtest sbtest1 sbtest2 sbtest3 sbtest4 sbtest5 sbtest6 ``` ### Step 5. Install TiDB Data Migration utility ```bash ~/.tiup/bin/tiup install dm dmctl:v${LAB:TIDB_VERSION} dm-master:v${LAB:TIDB_VERSION} dm-worker:v${LAB:TIDB_VERSION} ``` ```bash ~/.tiup/bin/tiup list --verbose | grep dm ``` ```output dm pingcap v1.15.1,v1.16.0 linux/amd64 Data Migration Platform manager dm-master pingcap v8.0.0,v${LAB:TIDB_VERSION} linux/amd64 dm-master component of Data Migration Platform. dm-worker pingcap v8.0.0,v${LAB:TIDB_VERSION} linux/amd64 dm-worker component of Data Migration Platform. dmctl pingcap v6.5.1,v6.5.9,v7.5.0,v${LAB:TIDB_VERSION} linux/amd64 dmctl component of Data Migration Platform. ``` ### Step 6. Review the TiDB Data Migration cluster topology + The TiDB Data Migration cluster topology configuration file has already provisioned for you. Review the configuration, no changes are needed. ```bash cat dm-topology.yaml ``` ```output global: user: "ec2-user" ssh_port: 22 deploy_dir: "/tidb-deploy" data_dir: "/tidb-data" arch: "amd64" master_servers: - host: 10.90.4.191 worker_servers: - host: 10.90.3.49 - host: 10.90.3.71 - host: 10.90.3.135 monitoring_servers: - host: 10.90.1.7 grafana_servers: - host: 10.90.1.7 alertmanager_servers: - host: 10.90.1.7 ``` ### Step 7. Deploy and start the TiDB Data Migration cluster + Deploy TiDB Data Migration cluster - `dm-test` ```bash ~/.tiup/bin/tiup dm deploy dm-test v${LAB:TIDB_VERSION} ./dm-topology.yaml \ --user ec2-user \ -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \ --yes ``` ```output Starting component dm: /home/ec2-user/.tiup/components/dm/v1.16.0/tiup-dm deploy dm-test v${LAB:TIDB_VERSION} ./dm-topology.yaml --user ec2-user -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem --yes + Detect CPU Arch Name - Detecting node 10.90.4.191 Arch info ... Done - Detecting node 10.90.3.49 Arch info ... Done - Detecting node 10.90.3.71 Arch info ... Done - Detecting node 10.90.3.135 Arch info ... Done - Detecting node 10.90.1.7 Arch info ... Done . . . Cluster `dm-test` deployed successfully, you can start it with command: `tiup dm start dm-test` ``` + Start the TiDB Data Migration cluster - `dm-test`. ```bash ~/.tiup/bin/tiup dm start dm-test ``` ```output Starting component dm: /home/ec2-user/.tiup/components/dm/v1.16.0/tiup-dm start dm-test Starting cluster dm-test... + [ Serial ] - SSHKeySet: privateKey=/home/ec2-user/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa, publicKey=/home/ec2-user/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa.pub + [Parallel] - UserSSH: user=ec2-user, host=10.90.1.7 + [Parallel] - UserSSH: user=ec2-user, host=10.90.3.71 + [Parallel] - UserSSH: user=ec2-user, host=10.90.1.7 + [Parallel] - UserSSH: user=ec2-user, host=10.90.4.191 + [Parallel] - UserSSH: user=ec2-user, host=10.90.1.7 + [Parallel] - UserSSH: user=ec2-user, host=10.90.3.49 + [Parallel] - UserSSH: user=ec2-user, host=10.90.3.135 + [ Serial ] - StartCluster Starting component dm-master Starting instance 10.90.4.191:8261 Start instance 10.90.4.191:8261 success Starting component dm-worker Starting instance 10.90.3.135:8262 Starting instance 10.90.3.49:8262 Starting instance 10.90.3.71:8262 Start instance 10.90.3.135:8262 success Start instance 10.90.3.71:8262 success Start instance 10.90.3.49:8262 success Starting component prometheus Starting instance 10.90.1.7:9090 Start instance 10.90.1.7:9090 success Starting component grafana Starting instance 10.90.1.7:3000 Start instance 10.90.1.7:3000 success Starting component alertmanager Starting instance 10.90.1.7:9093 Start instance 10.90.1.7:9093 success Started cluster `dm-test` successfully ``` ### Step 8. Check the status of TiDB Data Migration cluster + Confirm the status of TiDB Data Migration cluster is up and running. ```bash tiup dm display dm-test ``` ```output Starting component dm: /home/ec2-user/.tiup/components/dm/v1.16.0/tiup-dm display dm-test Cluster type: dm Cluster name: dm-test Cluster version: v${LAB:TIDB_VERSION} Deploy user: ec2-user SSH type: builtin Grafana URL: http://10.90.3.188:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.3.188:9093 alertmanager 10.90.3.188 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.196:8261 dm-master 10.90.4.196 8261/8291 linux/x86_64 Healthy|L /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261 10.90.4.196:8262 dm-worker 10.90.4.196 8262 linux/x86_64 Free /tidb-data/dm-worker-8262 /tidb-deploy/dm-worker-8262 10.90.3.188:3000 grafana 10.90.3.188 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.3.188:9090 prometheus 10.90.3.188 9090 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 Total nodes: 5 ``` ### Step 9. Set up the migration task source configuration ```bash cat << EOF > mysql-source-conf1.yaml source-id: "mysql-replica-01" from: host: "${HOST_MYSQL1_PRIVATE_IP}" port: 3306 user: "dm_user" password: "q1w2e3R4_" EOF cat mysql-source-conf1.yaml ``` ```output source-id: "mysql-replica-01" from: host: "10.90.1.135" port: 3306 user: "dm_user" password: "q1w2e3R4_" ``` ### Step 10. Create the migration source + Set the MySQL instance as the migration source, and verify that the source can be recognized by TiDB Data Migration cluster. ```bash ~/.tiup/bin/tiup dmctl:v${LAB:TIDB_VERSION} \ --master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 operate-source create mysql-source-conf1.yaml ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v${LAB:TIDB_VERSION}/dmctl/dmctl --master-addr=10.90.4.66:8261 operate-source create mysql-source-conf1.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-10.90.3.187-8262" } ] } ``` ### Step 11. Review the migration source configuration ```bash ~/.tiup/bin/tiup dmctl:v${LAB:TIDB_VERSION} \ --master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 get-config source mysql-replica-01 ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v${LAB:TIDB_VERSION}/dmctl/dmctl --master-addr=10.90.4.66: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.1.135\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: 429530419\ntracer: {}\ncase-sensitive: false\nfilters: []\n" } ``` ### Step 12. Check the status of the migration source ```bash ~/.tiup/bin/tiup dmctl:v${LAB:TIDB_VERSION} \ --master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 operate-source show ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v${LAB:TIDB_VERSION}/dmctl/dmctl --master-addr=10.90.4.66:8261 operate-source show { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-10.90.3.187-8262" } ] } ``` ### Step 13. Create a migration task + Generate the migration task configuration. ```bash cat << EOF > dm-task.yaml name: "dm-task" task-mode: all ignore-checking-items: ["auto_increment_ID"] target-database: host: "${HOST_DB1_PRIVATE_IP}" port: 4000 user: "dm_user" password: "q1w2e3R4_" mysql-instances: - source-id: "mysql-replica-01" route-rules: ["instance-1-user-schema-rule"] block-allow-list: "rule_1" mydumper-config-name: "global" loader-config-name: "global" routes: instance-1-user-schema-rule: schema-pattern: "sbtest" target-schema: "sbtest" block-allow-list: rule_1: do-dbs: ["sbtest"] mydumpers: global: threads: 32 chunk-filesize: 64 loaders: global: pool-size: 8 import-mode: "physical" pd-addr: "${HOST_PD1_PRIVATE_IP}:2379" EOF cat dm-task.yaml ``` ```output name: "dm-task" task-mode: all ignore-checking-items: ["auto_increment_ID"] . . . loaders: global: pool-size: 32 import-mode: "physical" ``` + Pre-check the migration task. The message should be `pre-check is passed`. ```bash ~/.tiup/bin/tiup dmctl:v${LAB:TIDB_VERSION} --master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 check-task ./dm-task.yaml ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v${LAB:TIDB_VERSION}/dmctl/dmctl --master-addr=10.90.4.235:8261 check-task ./dm-task.yaml { "result": true, "msg": "pre-check is passed. " } ``` ### Step 14. Start the migration task + As per the configuration, the migration task contains 3 sub-task units: `Dump`, `Load`, and `Sync`. Start the migration task. ```bash ~/.tiup/bin/tiup dmctl:v${LAB:TIDB_VERSION} \ --master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 start-task ./dm-task.yaml ``` ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v${LAB:TIDB_VERSION}/dmctl/dmctl --master-addr=10.90.4.66:8261 start-task ./dm-task.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-10.90.3.187-8262" } ], "checkResult": "pre-check is passed. " } ``` ### Step 15. Check the migration progress by using `query-status` action + Note the `stage`, `unit` and `progress` attributes in the `subTaskStatus`. + Use the `watch` command to monitor the progress of the whole task. Base on the lab environment configuration (since it is not a PoC, we use low-end servers for hands-on labs), it may take around 8 minutes (Dump: 4min, Load: 4min ) to keep the MySQL and TiDB synced. Time to grab a cup of coffee, read through the following additional information and observe the migration progress on the screen. ```bash watch -n 1 -d ./.tiup/bin/tiup dmctl:v${LAB:TIDB_VERSION} --master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 query-status ./dm-task.yaml ``` + Unit `Dump`, stage `Running`: ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v${LAB:TIDB_VERSION}/dmctl/dmctl --master-addr=10.90.4.235:8261 query-status ./dm-task.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "sourceStatus": { "source": "mysql-replica-01", "worker": "dm-10.90.1.147-8262", "result": null, "relayStatus": null }, "subTaskStatus": [ { "name": "dm-task", "stage": "Running", "unit": "Dump", "result": null, "unresolvedDDLLockID": "", "dump": { "totalTables": "6", "completedTables": 3, "finishedBytes": 7949200595, "finishedRows": 39359277, "estimateTotalRows": 59224808, "bps": "57472325", "progress": "64.63 %" }, "validation": null } ] } ] } ``` + Unit `Load`, stage `Running`: ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v${LAB:TIDB_VERSION}/dmctl/dmctl --master-addr=10.90.4.196:8261 query-status ./dm-task.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "sourceStatus": { "source": "mysql-replica-01", "worker": "dm-10.90.4.196-8262", "result": null, "relayStatus": null }, "subTaskStatus": [ { "name": "dm-task", "stage": "Running", "unit": "Load", "result": null, "unresolvedDDLLockID": "", "load": { "finishedBytes": "5307344924", "totalBytes": "8161057816", "progress": "65.03 %", "metaBinlog": "(binlog.000008, 157240317)", "metaBinlogGTID": "", "bps": "62882221" }, "validation": null } ] } ] } ``` + Unit `Sync`, stage `Running`. Note the target TiDB and the source MySQL are synced. ```output Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/v${LAB:TIDB_VERSION}/dmctl/dmctl --master-addr=10.90.4.196:8261 query-status ./dm-task.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "sourceStatus": { "source": "mysql-replica-01", "worker": "dm-10.90.4.196-8262", "result": null, "relayStatus": null }, "subTaskStatus": [ { "name": "dm-task", "stage": "Running", "unit": "Sync", "result": null, "unresolvedDDLLockID": "", "sync": { "totalEvents": "19767046", "totalTps": "21118", "recentTps": "0", "masterBinlog": "(binlog.000011, 706201359)", "masterBinlogGtid": "", "syncerBinlog": "(binlog.000011, 706201359)", "syncerBinlogGtid": "00000000-0000-0000-0000-000000000000:0", "blockingDDLs": [ ], "unresolvedGroups": [ ], "synced": true, "binlogType": "remote", "secondsBehindMaster": "0", "blockDDLOwner": "", "conflictMsg": "", "totalRows": "19767046", "totalRps": "21118", "recentRps": "0" }, "validation": null } ] } ] } ``` + Wait and confirm that the migration task had reached the `"stage": "Running" and "unit": "Sync"` status. Keep the `watch` command running and proceed to the next practice. ## Module 3: Make Some Changes From The Source MySQL ### Step 1. Connect to the TiUP control machine, and then the MySQL server + Click here - [Terminal 2](ssh://monitor1-ssh#2) 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/ ``` + Connect to MySQL server node. ```bash ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP} ``` ```output Last login: Thu Aug 1 11:58:47 2024 from 10.90.4.90 [rocky@ip-10-90-1-235 ~]$ ``` + Connect to MySQL server with a database session. ```bash mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password ``` ```output mysql> ``` ### Step 2. Execute UPDATE from MySQL + On [Terminal 2](ssh://monitor1-ssh#2) (the MySQL side), set column `c` to `Changed on MySQL` for a specific row. ```sql SELECT * FROM sbtest.sbtest1 WHERE id=1; UPDATE sbtest.sbtest1 SET c='Changed on MySQL' WHERE id=1; SELECT * FROM sbtest.sbtest1 WHERE id=1; ``` ```output mysql> SELECT * FROM sbtest.sbtest1 WHERE id=1; +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 5014614 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 | +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> UPDATE sbtest.sbtest1 SET c='Changed on MySQL' WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM sbtest.sbtest1 WHERE id=1; +----+---------+------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+---------+------------------+-------------------------------------------------------------+ | 1 | 5014614 | Changed on MySQL | 22195207048-70116052123-74140395089-76317954521-98694025897 | +----+---------+------------------+-------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ### Step 3. Connect to the TiUP control machine, and then the TiDB instance + Click here - [Terminal 3](ssh://monitor1-ssh#3) 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/ ``` + Connect to TiDB cluster. ```bash export MYSQL_PS1="tidb:db1> " mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output tidb:db1> ``` ### Step 4. Obverse the UPDATE operation result from TiDB + On [Terminal 3](ssh://monitor1-ssh#3) (the TiDB side), observe the `UPDATE` result from TiDB side, the change had been already synced from MySQL. ```sql SELECT * FROM sbtest.sbtest1 WHERE id=1; ``` ```output tidb:db1> SELECT * FROM sbtest.sbtest1 WHERE id=1; +----+---------+------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+---------+------------------+-------------------------------------------------------------+ | 1 | 5014614 | Changed on MySQL | 22195207048-70116052123-74140395089-76317954521-98694025897 | +----+---------+------------------+-------------------------------------------------------------+ 1 row in set (0.01 sec) ``` ### Step 5. Execute the INSERT operation in MySQL + On [Terminal 2](ssh://monitor1-ssh#2) (the MySQL side), insert a new row. ```sql INSERT INTO sbtest.sbtest1 (k, c, pad) VALUES (102, 'Insert on MySQL', 'Insert on MySQL'); SELECT * FROM sbtest.sbtest1 WHERE k=102; ``` ```output mysql> INSERT INTO sbtest.sbtest1 (k, c, pad) VALUES (102, 'Insert on MySQL', 'Insert on MySQL'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM sbtest.sbtest1 WHERE k=102; +----------+-----+-----------------+-----------------+ | id | k | c | pad | +----------+-----+-----------------+-----------------+ | 10000002 | 102 | Insert on MySQL | Insert on MySQL | +----------+-----+-----------------+-----------------+ 1 row in set (0.00 sec) ``` ### Step 6. Observe the INSERT operation result from TiDB + On [Terminal 3](ssh://monitor1-ssh#3) (the TIDB side), observe the `INSERT` result from TiDB side, the change had been already synced from MySQL. ```sql SELECT * FROM sbtest.sbtest1 WHERE k=102; ``` ```output tidb:db1> SELECT * from sbtest.sbtest1 where k=102; +----------+-----+-----------------+-----------------+ | id | k | c | pad | +----------+-----+-----------------+-----------------+ | 10000002 | 102 | Insert on MySQL | Insert on MySQL | +----------+-----+-----------------+-----------------+ 1 row in set (0.00 sec) ``` + If time allows, try more modifications from MySQL side and observe the result from TiDB side. ## Conclusion Among TiDB's suite of tools, TiDB Data Migration (DM) plays a pivotal role in simplifying data migration tasks. More than just a utility, TiDB DM is an integrated platform designed to handle both full data migration and incremental data replication from MySQL-compatible databases, including MySQL, MariaDB, and Amazon Aurora, into TiDB. TiDB DM also supports merging and migrating multiple MySQL shards into a single TiDB database. This makes it an ideal choice for organizations managing vast amounts of fragmented data across different storage systems, effectively reducing the complexity of data management. With centralized management capabilities, TiDB DM can handle thousands of nodes within a cluster and manage numerous migration tasks concurrently, underscoring its capacity for handling large-scale operations. Moreover, its high availability ensures that migration tasks are resilient to node failures, providing a stable and reliable platform for data operations. ```hbsptform { "ui": { "titleLevel": 3 }, "portalId": "4466002", "formId": "89fa8368-eba1-4b84-b91e-eb9eb6613ddf", "region": "na1", "sfdcCampaignId": "701RC00000VqCODYA3" } ```