## Module 2: Migrate The Sample Database From MySQL To TiDB ### Step 1. Create a user in MySQL for migration purpose + On the [Terminal 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 } ] } ] } ``` > [!TIP] > In this hands-on lab, we are using small instances that are much less powerful than production-grade servers. Please be patient and wait a few minutes for the synchronization to complete. + 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.