# Migrating Data Using TiDB Data Migration ### 概要 + In these exercises, you migrate data using the `TiDB Data Migration` tool. + The following shows the deployment topology diagram of TiDB cluster. ![60](https://pingcap-edu.s3.us-west-2.amazonaws.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://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/DMLabToplogy_aa_005_20220626.png) ### 注意 + Click the private key file [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download it. It is required to log into the Control Machine for this demo. + Commands and SQL statements presented on this page are clickable for easy copy and paste. + The required IP addresses for specific commands will be substituted with the actual ones automatically once you have set up the lab environment after Step 1. + Your environment may be different with these exercises, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide. + The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt. ### 前提条件 + You have downloaded and secured the TiUP Control Machine private key: `chmod 400 [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)` + The `ssh-agent` is running on your laptop. Windows users please refer to this [guide](https://github.com/pingcap/tidb-course-201-lab/blob/master/README_303_v65_en_class_setup_guide_for_student.md). + You have added identity to authentication agent: `ssh-add [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)` ## 演習 1 ### Deploying `TiDB Data Migration` Cluster ### 概要 + In this exercise, you deploy a `TiDB Data Migration` cluster with TiUP. ### 所要時間 + This exercise should take you approximately 10 minutes to complete. ### タスク + Login as user `ec2-user` on Control Machine (monitor1_address). + Install `TiDB Data Migration` component. + Deploy and start a `TiDB Data Migration` cluster using a topology file. + Install the `dmctl` tool. ### 手順 1. Login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. The returned message may be slightly different from what is displayed here: ```command $ ssh -A ec2-user@monitor1_address ``` ```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. Install `TiDB Data Migration` component. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup install dm dmctl:v6.5.1 ``` 3. Use a text editor to edit the `dm-topology.yaml` file. Modify `dm-topology.yaml` file to contain settings similar to those that follow (replace the IP addresses with the correct ones assigned to you). Or, you can simply copy from the solution file `solution-dm-topology-six-nodes.yaml` which had been already provisioned for you: ```command $ vi dm-topology.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: - host: - host: worker_servers: - host: - host: - host: monitoring_servers: - host: grafana_servers: - host: alertmanager_servers: - host: ``` 4. Verify that the new version of `TiDB Data Migration` is installed on your host. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup list dm-master ``` ```output Available versions for dm-master: Version Installed Release Platforms ------- --------- ------- --------- build-debug-mode 2022-06-10T14:27:38+08:00 linux/amd64,linux/arm64 ... ``` 5. Deploy a `TiDB Data Migration` cluster with name `dm-test` using the `dm-topology.yaml` file. Enter the following command at the terminal prompt and receive the results shown. Or, you can simply use the solution file `solution-dm-topology-six-nodes.yaml` which is already provisioned for you: ```command $ tiup dm deploy dm-test v6.5.1 ./dm-topology.yaml --yes ``` ```output ... Cluster `dm-test` deployed successfully, you can start it with command: `tiup dm start dm-test` ``` 6. Start the `TiDB Data Migration` cluster. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dm start dm-test ``` ```output ... Started cluster `dm-test` successfully ``` 7. Check the `TiDB Data Migration` cluster managed by TiUP. Enter the following command at the terminal prompt and receive the results shown: ```command $ 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 v6.5.1 /home/ec2-user/.tiup/storage/dm/clusters/dm-test /home/ec2-user/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa ``` 8. Check the status of the deployed `TiDB Data Migration` cluster. Enter the following command at the terminal prompt and receive the results shown: ```command $ 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: v6.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 ### Migrating data From Two MySQL Instances ### 概要 + In this exercise, you migrate data from the upstream database MySQL to the downstream database TiDB. ### The migration rules used in this exercise: + Rule 1: Migrate all tables in the `user` database in upstream MySQL instance on `PD1` with port 3306 to `user_north` database in the downstream TiDB instance. Migrate all tables in `user` database in the upstream MySQL instance on `PD2` with port 3306 to `user_east` database in the downstream TiDB instance. + 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. + 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. + 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. + Rule 5: The `log` databases in any upstream MySQL instance will not be replicated to the downstream TiDB instance. ### 所要時間 + This exercise should take you approximately 30 minutes to complete. ### タスク + Setup source MySQL instance on PD1 + Setup source MySQL instance on PD2 + Import data into two MySQL instances + Setup target TiDB cluster schemas + Create the DM source configurations + Create the DM task configuration + Start the DM task + Verify the DM task ### 手順 #### Setup source MySQL instance on PD1 1. Login as user ec2-user on pd1_address. Enter the following command at the terminal prompt and receive the results shown. The returned message may be slightly different from what is displayed here: ```command $ ssh -A ec2-user@pd1_address ``` ```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. Start the MySQL instance on `PD1`: ```command $ sudo service mysqld start ``` ```output Redirecting to /bin/systemctl start mysqld.service ``` 3. Connect to MySQL server on PD1 using user `root@'localhost'`: ```command $ mysql -h localhost -P 3306 -uroot -pq1w2e3R4_ ``` ```output Enter password: ... Server version: 5.7.41-log ... mysql> ``` 4. Create a new user `dm_user@'%'`, and grant all privileges to the user: ```command CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; ``` ```output mysql> CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; Query OK, 0 rows affected (0.00 sec) ``` ```command GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; ``` ```output mysql> GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; Query OK, 0 rows affected (0.00 sec) ``` 5. Turn on `show_compatibility_56`: ```command SET @@global.show_compatibility_56=ON; ``` ```output mysql> SET @@global.show_compatibility_56=ON; Query OK, 0 rows affected (0.00 sec) ``` 6. Exit to terminal: ```command EXIT ``` ```output mysql> EXIT Bye ``` #### Setup source MySQL instance on PD2 1. Login as user ec2-user on pd2_address. Enter the following command at the terminal prompt and receive the results shown. The returned message may be slightly different from what is displayed here: ```command $ ssh -A ec2-user@pd2_address ``` ```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. Start the MySQL instance on `PD2`: ```command $ sudo service mysqld start ``` ```output Redirecting to /bin/systemctl start mysqld.service ``` 3. Connect to MySQL server on PD2 using user `root@'localhost'`: ```command $ mysql -h localhost -P 3306 -uroot -pq1w2e3R4_ ``` ```output Enter password: ... Server version: 5.7.41-log ... mysql> ``` 4. Create a new user `dm_user@'%'`, and grant all privileges to the user: ```command CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; ``` ```output mysql> CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; Query OK, 0 rows affected (0.00 sec) ``` ```command GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; ``` ```output mysql> GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; Query OK, 0 rows affected (0.00 sec) ``` 5. Turn on `show_compatibility_56`: ```command SET @@global.show_compatibility_56=ON; ``` ```output mysql> SET @@global.show_compatibility_56=ON; Query OK, 0 rows affected (0.00 sec) ``` 6. Exit to terminal: ```command EXIT ``` ```output mysql> EXIT Bye ``` #### Import data into two MySQL instances 1. Login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. The returned message may be slightly different from what is displayed here: ```command $ ssh -A ec2-user@monitor1_address ``` ```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. Make sure the sample is extract. ```command $ unzip -u stage/tidb-admin-dataset.zip -d stage/ ``` 3. Import the `user`, `store`, `log` and `salesdb` databases into MySQL on PD1: ```command $ mysql -udm_user -pq1w2e3R4_ -h ${HOST_PD1_PRIVATE_IP} -P3306 < stage/TiDB-Administration-exercise-data/exercise-12/3306db.sql ``` 4. Import the `user`, `store`, `log` and `salesdb` databases into MySQL on PD2: ```command $ mysql -udm_user -pq1w2e3R4_ -h ${HOST_PD2_PRIVATE_IP} -P3306 < stage/TiDB-Administration-exercise-data/exercise-12/3307db.sql ``` #### Setup target TiDB cluster schemas 1. Connect to the TiDB: ```command $ 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-v6.5.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible ... mysql> ``` 2. Create new user called `dm_user`, and grant all privileges to the user: ```command CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; ``` ```output mysql> CREATE USER dm_user@'%' identified by 'q1w2e3R4_'; Query OK, 0 rows affected (0.04 sec) ``` ```command GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; ``` ```output mysql> GRANT ALL PRIVILEGES ON *.* TO dm_user@'%'; Query OK, 0 rows affected (0.02 sec) ``` 3. 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`. Enter the following command at the terminal prompt and receive the results shown: ```command CREATE DATABASE user_north; ``` ```output mysql> CREATE DATABASE user_north; Query OK, 0 rows affected (0.08 sec) ``` ```command CREATE TABLE user_north.information(id INT PRIMARY KEY, info VARCHAR(64)); ``` ```output mysql> CREATE TABLE user_north.information(id INT PRIMARY KEY, info VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) ``` ```command CREATE TABLE user_north.trace(id INT PRIMARY KEY, content VARCHAR(64)); ``` ```output mysql> CREATE TABLE user_north.trace(id INT PRIMARY KEY, content VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) ``` 4. 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`. Enter the following command at the terminal prompt and receive the results shown: ```command CREATE DATABASE user_east; ``` ```output mysql> CREATE DATABASE user_east; Query OK, 0 rows affected (0.08 sec) ``` ```command CREATE TABLE user_east.information(id INT PRIMARY KEY, info VARCHAR(64)); ``` ```output mysql> CREATE TABLE user_east.information(id INT PRIMARY KEY, info VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) ``` ```command CREATE TABLE user_east.trace(id INT PRIMARY KEY, content VARCHAR(64)); ``` ```output mysql> CREATE TABLE user_east.trace(id INT PRIMARY KEY, content VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) ``` 5. 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`. Enter the following command at the terminal prompt and receive the results shown: ```command CREATE DATABASE store; ``` ```output mysql> CREATE DATABASE store; Query OK, 0 rows affected (0.09 sec) ``` ```command CREATE TABLE store.store_bj(id INT PRIMARY KEY, pname VARCHAR(64)); ``` ```output mysql> CREATE TABLE store.store_bj(id INT PRIMARY KEY, pname VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) ``` ```command CREATE TABLE store.store_tj(id INT PRIMARY KEY, pname VARCHAR(64)); ``` ```output mysql> CREATE TABLE store.store_tj(id INT PRIMARY KEY, pname VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) ``` ```command CREATE TABLE store.store_sh(id INT PRIMARY KEY, pname VARCHAR(64)); ``` ```output mysql> CREATE TABLE store.store_sh(id INT PRIMARY KEY, pname VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) ``` ```command CREATE TABLE store.store_suzhou(id INT PRIMARY KEY, pname VARCHAR(64)); ``` ```output mysql> CREATE TABLE store.store_suzhou(id INT PRIMARY KEY, pname VARCHAR(64)); Query OK, 0 rows affected (0.09 sec) ``` 6. 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`. Enter the following command at the terminal prompt and receive the results shown: ```command CREATE DATABASE salesdb; ``` ```output mysql> CREATE DATABASE salesdb; Query OK, 0 rows affected (0.09 sec) ``` ```command CREATE TABLE salesdb.sales(id INT PRIMARY KEY, pname VARCHAR(20), cnt int); ``` ```output mysql> CREATE TABLE sales.sales(id INT PRIMARY KEY, pname VARCHAR(20), cnt int); Query OK, 0 rows affected (0.09 sec) ``` 7. 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. Enter the following command at the terminal prompt and receive the results shown: ```command CREATE DATABASE log; ``` ```output mysql> CREATE DATABASE log; Query OK, 0 rows affected (0.08 sec) ``` ```command CREATE TABLE log.messages(id INT PRIMARY KEY, msg VARCHAR(64)); ``` ```output mysql> CREATE TABLE log.messages(id INT PRIMARY KEY, msg VARCHAR(64)); Query OK, 0 rows affected (0.08 sec) ``` ```command EXIT ``` ```output mysql> EXIT Bye ``` #### Create the DM source configurations 1. Execute the `tiup dmctl:v6.5.1 --encrypt` statements to encrypt the password of the upstream database MySQL. Enter the following command at the terminal prompt and receive the results shown. Copy and save the encrypted password to a temporary file, you will need it in the subsequent steps: ```command $ tiup dmctl:v6.5.1 --encrypt 'q1w2e3R4_' ``` ``` output tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.5.1/dmctl/dmctl --encrypt mysql DMJtTSNCJbEoxUn1RVUZCUD9D3L4Fw== ``` 2. 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 IP addresses with the correct ones assigned to you): ```command $ vi mysql-source-conf1.yaml ``` ```output source-id: "mysql-replica-01" from: host: "" port: 3306 user: "dm_user" password: "" ``` 3. 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 IP addresses with the correct ones assigned to you): ```command $ vi mysql-source-conf2.yaml ``` ```output source-id: "mysql-replica-02" from: host: "" port: 3306 user: "dm_user" password: "" ``` 4. Execute the `tiup dmctl:v6.5.1` statement to load the configuration file for MySQL instance on PD1 as the upstream database. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dmctl:v6.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source create mysql-source-conf1.yaml ``` ```output tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.5.1/dmctl/dmctl --master-addr=172.31.0.232:8261 operate-source create mysql-source-conf1.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-172.31.0.232-8262" } ] } ``` 5. Execute the `tiup dmctl:v6.5.1` statements to load the configuration file for MySQL instance on PD2 as the upstream database. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dmctl:v6.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source create mysql-source-conf2.yaml ``` ```output tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.5.1/dmctl/dmctl --master-addr=172.31.0.232:8261 operate-source create mysql-source-conf2.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-172.31.0.233-8262" } ] } ``` 6. View the content of the loaded configuration named `mysql-replica-01` and `mysql-replica-02`. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dmctl:v6.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 get-config source mysql-replica-01 ``` ```output tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.5.1/dmctl/dmctl --master-addr=172.31.0.232:8261 get-config upstream databasemysql-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: 172.31.0.135\n port: 3306\n user: root\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: 429542057\ntracer: {}\ncase-sensitive: false\nfilters: []\n" } ``` ```command $ tiup dmctl:v6.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 get-config source mysql-replica-02 ``` ```output tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.5.1/dmctl/dmctl --master-addr=172.31.0.232:8261 get-config upstream databasemysql-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: 172.31.0.135\n port: 3307\n user: root\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: 429507194\ntracer: {}\ncase-sensitive: false\nfilters: []\n" } ``` 7. View the added data source and the corresponding `DM-worker`. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dmctl:v6.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source show ``` ```output tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.5.1/dmctl/dmctl --master-addr=172.31.0.232:8261 operate-source databaseshow { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-172.31.0.232-8262" }, { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-172.31.0.233-8262" } ] } ``` #### Create the DM task configuration 1. 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 two items, `` and ``, in the yaml file, you need to replace them with the private IP address of TiDB server and the encrypted password from the previous section respectively: ```command 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: "" port: 4000 user: "dm_user" password: "" 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 ``` 2. Execute the `check-task` command to run a data migration task smoothly. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dmctl:v6.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/v6.5.1/dmctl/dmctl --master-addr=172.31.0.232:8261 check-task dm-task.yaml { "result": true, "msg": "pre-check is passed. " } ``` #### Start the DM task 1. Start a migration task by using `tiup dmctl:v6.5.1` command. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dmctl:v6.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 start-task dm-task.yaml ``` ```output tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.5.1/dmctl/dmctl --master-addr=172.31.0.232:8261 start-task dm-task.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "source": "mysql-replica-01", "worker": "dm-172.31.0.232-8262" }, { "result": true, "msg": "", "source": "mysql-replica-02", "worker": "dm-172.31.0.233-8262" } ], "checkResult": "pre-check is passed. " } ``` 2. View the status of the task. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dmctl:v6.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 query-status dm-task.yaml ``` ```output tiup is checking updates for component dmctl ... Starting component `dmctl`: /root/.tiup/components/dmctl/v6.5.1/dmctl/dmctl --master-addr=172.31.0.232:8261 query-status dm-task.yaml { "result": true, "msg": "", "sources": [ { "result": true, "msg": "", "sourceStatus": { "source": "mysql-replica-01", "worker": "dm-172.31.0.232-8262", "result": null, "relayStatus": null }, "subTaskStatus": [ { "name": "dm-task2", "stage": "Running", "unit": "Sync", "result": null, ... "unresolvedDDLLockID": "", "sync": { "totalEvents": "4", "totalTps": "0", "recentTps": "0", "masterBinlog": "(mysql-bin.000002, 154)", "masterBinlogGtid": "", "syncerBinlog": "(mysql-bin.000002, 154)", "syncerBinlogGtid": "", "blockingDDLs": [ ], "unresolvedGroups": [ ], "synced": true, "binlogType": "remote", "secondsBehindMaster": "0", "blockDDLOwner": "", "conflictMsg": "" } } ] } ] } ``` #### Verify the DM task 1. Verify that the downstream database TiDB contains the full data from the migration task. Enter the following command at the terminal prompt and receive the results shown: ```command $ mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot ``` ```command USE user_east; ``` ```command SHOW TABLES; ``` ```command SELECT * FROM information; ``` ```command SELECT * FROM trace; ``` ```command USE user_north; ``` ```command SHOW TABLES; ``` ```command SELECT * FROM information; ``` ```command SELECT * FROM trace; ``` ```command USE store; ``` ```command SHOW TABLES; ``` ```command SELECT * FROM store_bj; ``` ```command SELECT * FROM store_tj; ``` ```command SELECT * FROM store_suzhou; ``` ```command SELECT * FROM store_sh; ``` ```command USE salesdb; ``` ```command SELECT * FROM sales; ``` ```command EXIT; ``` ```output $ mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot << EOF > 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; > EOF Tables_in_user_east information trace id info 1 andy 2 candy ... id pname cnt 1 book 100 2 cup 200 ``` 2. Verify that after inserting data into the upstream database, the downstream database can also receive the value. On the `PD1`: ```command $ mysql -u root -p -h 127.0.0.1 -P 3306 -pq1w2e3R4_ ``` ```command 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) ``` ```command 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) ``` ```command EXIT ``` ```output mysql> EXIT; Bye ``` On the `Monitor`: ```command $ mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot ``` ```command 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) ``` ```command EXIT ``` ```output mysql> EXIT Bye ``` 3. You can also design your own experiments to verify that the `log` databases in any upstream MySQL instance will not be replicated to the downstream TiDB instance. ## 演習 3 ### Scale-in and scale-out `TiDB Data Migration` Cluster ### 概要 + In this exercise, you remove the `TiDB Data Migration` cluster. ### 所要時間 + This exercise should take you approximately 10 minutes to complete. ### タスク + Scale in `TiDB DM` cluster. + Scale out `TiDB DM` cluster. + Stop and delete `TiDB DM` cluster. ### 手順 1. On the `Control Machine`, check the status of the deployed `TiDB Data Migration` cluster and see how many free nodes of `dm-master` in the cluster. Enter the following command at the terminal prompt and receive the results shown: ```command $ 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: v6.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. 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. Enter the following command at the terminal prompt and receive the results shown: ```command $ 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. Check the status of the deployed `TiDB Data Migration` cluster again, verify that the node with free states is offline. Enter the following command at the terminal prompt and receive the results shown: ```command $ 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: v6.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. Use a text editor to edit the `dm-scale.yaml` file. Modify `dm-scale.yaml` file to contain settings similar to those that follow (replace the IP addresses with the correct ones assigned to you): ```command $ vi dm-scale.yaml ``` ```output worker_servers: - host: ``` 5. Run the `tiup dm scale-out` command to scale out `TiDB Data Migration` cluster. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dm 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. Check the status of the `TiDB Data Migration` cluster, and verify that the new `dm-worker` with `KV server 1` IP address is online. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dm display dm-test ``` 7. Execute the `stop-task` command to stop data migration tasks. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dmctl:v6.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/v6.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. Verify that the `TiDB Data Migration` cluster data migration has stopped. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dmctl:v6.5.1 --master-addr=${HOST_PD3_PRIVATE_IP}:8261 query-status dm-task.yaml ``` ```output Starting component `dmctl`: /home/ec2-user/.tiup/components/dmctl/v6.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. Execute `tiup dm stop` command to stop all the services of the `TiDB Data Migration` cluster. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dm stop dm-test ``` ```output ... Do you want to continue? [y/N]:(default=N) y ... Stopped cluster `dm-test` successfully ``` 10. Check the status of the `TiDB Data Migration` cluster. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dm display dm-test ``` 11. Destroy your DM cluster ```command $ tiup dm destroy dm-test ``` 12. Log out of the client and close all open terminal windows. ## 演習終わり