# Lab 7: Exporting Data Using Dumpling ### Overview In these exercises, you export data stored in TiDB/MySQL as SQL or CSV data files and can be used to make a logical full backup or export. The following shows the deployment topology diagram of TiDB cluster. ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/TiDBClusterDeploymentTopologyExercisewithDumpling_aa_003_20230301.png) ### Notes + 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 lab. + Commands and SQL statements presented on this page are clickable for easy copy and paste. + 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. ### Assumptions + You have downloaded and secured the TiUP Control Machine private key (for example the file is saved under `~/.ssh/` folder locally): [chmod 400 ~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) + Windows users please refer to this [guide](https://github.com/pingcap-inc/tidb-course-labs/blob/master/README_303_class_setup_guide_for_student_lab_control_en.md) for private key preparation. ## Exercise 1 ### Subject: Deploying the Dumpling Data Export Tool ### Overview + In this exercise, you download and install the Dumpling Data Export Tool. You will perform all tasks on the Control Machine (monitor1_address). ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks + Install `Dumpling` using `TiUP`. + Download and prepare the dataset. ### Solution 1. Login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. If you do not possess the credentials to connect, kindly review the Assumptions section under the OVERVIEW tab. If prompted, answer `yes`. ```command $ ssh -i ~/.ssh/pe-class-key-oregon.pem 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 `Dumpling` using `TiUP`. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup install dumpling:v7.5.1 ``` ```output component dumpling version v7.5.1 is already installed ``` 3. Extract the data directories archive from `tidb-admin-dataset-v2.zip` to `./stage/`. Enter the following command at the terminal prompt and receive the results shown: ```command $ unzip -u stage/tidb-admin-dataset-v2.zip -d stage/ ``` ```output Archive: stage/tidb-admin-dataset-v2.zip ``` ## Exercise 2 ### Subject: Importing the Data into TiDB Using TiDB Lightning ### Overview + In this exercise, you export data for exercise. ### Duration + This exercise should take you approximately 15 minutes to complete. You will perform all tasks on the `Control Machine`. ### Tasks + Importing the Data into TiDB Using `TiDB Lightning` ### Solution 1. List the contents of the data files directory. ```command $ ls ./stage/TiDB-Administration-exercise-data-v2/exercise-dumpling ``` ```output emp.dep.0000000010000.sql emp.emp-schema.sql emp.sal.0000000030000.sql emp.sal-schema.sql emp.t-schema.sql emp.dep-schema.sql emp.sal.0000000010000.sql emp.sal.0000000040000.sql emp-schema-create.sql metadata emp.emp.0000000010000.sql emp.sal.0000000020000.sql emp.sal.0000000050000.sql emp.t.0000000010000.sql ``` 2. Connect to the TiDB and drop the `emp` database. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown: ```command $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```command SHOW DATABASES; ``` ```output mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | INFORMATION_SCHEMA | | METRICS_SCHEMA | | PERFORMANCE_SCHEMA | | emp | | mysql | | test | | tidb | +--------------------+ 7 rows in set (0.00 sec) ``` ```command DROP DATABASE IF EXISTS emp; ``` ```output mysql> DROP DATABASE IF EXISTS emp; Query OK, 0 rows affected (0.21 sec) ``` ```command EXIT ``` ```output mysql> EXIT Bye ``` 3. Read `solution-lightning-sql.toml`. The IP addresses shown below will be different from yours. ```command $ cat solution-lightning-sql.toml ``` ```output [lightning] # Logging level = "info" file = "tidb-lightning.log" [tikv-importer] # Sets the backend to the "local" mode. backend = "local" # Sets the directory of temporary local storage. sorted-kv-dir = "/tmp/sorted-kv-dir" [mydumper] # Local source data directory data-source-dir = "/home/ec2-user/stage/TiDB-Administration-exercise-data-v2/exercise-dumpling" # Configures the wildcard rule. By default, all tables in the mysql, sys, INFORMATION_SCHEMA, PERFORMANCE_SCHEMA, METRICS_SCHEMA, and INSPECTION_SCHEMA system databases are filtered. # If this item is not configured, the "cannot find schema" error occurs when system tables are imported. filter = ['*.*', '!mysql.*', '!sys.*', '!INFORMATION_SCHEMA.*', '!PERFORMANCE_SCHEMA.*', '!METRICS_SCHEMA.*', '!INSPECTION_SCHEMA.*'] [tidb] # Configuration of any TiDB server from the cluster. host = "10.90.2.124" port = 4000 user = "ltask" password = "q1w2e3R4_" # Table schema information is fetched from TiDB via this status-port. status-port = 10080 # Address of any PD server from the cluster. pd-addr = "10.90.2.84:2379" ``` 4. Use a `nohup` command to start the `tidb-lightning` process (press Enter to make `tidb-lighting` work in the background). Enter the following command at the terminal prompt and receive the results shown: ```command $ nohup tiup tidb-lightning:v7.5.1 -config solution-lightning-sql.toml > nohup.out & ``` ```output [1] 13135 $ nohup: ignoring input and redirecting stderr to stdout ... [1]+ Done nohup tidb-lightning:v7.5.1 -config solution-lightning-sql.toml > nohup.out ``` 5. View the contents of the `nohup.out` file to verify that `TiDB Lightning` is exited. Enter the following command at the terminal prompt and receive the results shown: ```command $ cat nohup.out ``` ```output Verbose debug logs will be written to tidb-lightning.log +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | # | CHECK ITEM | TYPE | PASSED | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 1 | Source csv files size is proper | performance | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ ... +-----------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 8 | Cluster available is rich, available is 51.79GiB, we need 1.007GiB | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 9 | Cluster doesn't have too many empty regions | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 10 | Cluster region distribution is balanced | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ tidb lightning exit successfully ``` ## Exercise 3 ### Subject: Exporting the Data from TiDB Using Dumpling ### Overview + In this exercise, you export data stored in TiDB. ### Duration + This exercise should take you approximately 20 minutes to complete. You will perform all tasks on the Control Machine (monitor1_address). ### Tasks + Export the `dept` table in the `emp` database from TiDB using `Dumpling`. + Export the `emp` database from TiDB using `Dumpling`. ### Solution 1. Connect to the TiDB and review the tables in the `emp` database. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Then exit the clients. Enter the following command at the terminal prompt and receive the results shown: ```command $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```command SHOW DATABASES; ``` ```output mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | INFORMATION_SCHEMA | | METRICS_SCHEMA | | PERFORMANCE_SCHEMA | | emp | | mysql | | test | | tidb | +--------------------+ 7 rows in set (0.00 sec) ``` ```command USE emp; ``` ```output mysql> USE emp; Database changed ``` ```command SHOW TABLES; ``` ```output mysql> SHOW TABLES; +---------------+ | Tables_in_emp | +---------------+ | dep | | emp | | sal | | t | +---------------+ 4 rows in set (0.00 sec) ``` ```command EXIT ``` ```output mysql> EXIT Bye ``` 2. Export the `dept` table in the `emp` database from TiDB. The explanation of each parameter is as follows: + `-uroot`: user is root. + `-P4000`: port is 4000. + `-h ${HOST_DB1_PRIVATE_IP}`: IP address of TiDB is `${HOST_DB1_PRIVATE_IP}`. + `--filetype sql`: Export data to SQL files. + `-t 8`: uses 8 threads to export at the same time. + `-o /tmp/dept`: option `/tmp/dept` as the export directory. + `-r 200000`: specifies 200000 as maximum number of rows in a single file. + `-F 256MiB`: specify 256 MiB as maximum size of a single file (the unit here is MiB; inputs like 5GiB or 8KB are also acceptable). + Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dumpling:v7.5.1 -uroot -P4000 -h ${HOST_DB1_PRIVATE_IP} --filetype sql -t 8 -o /tmp/dep -r 200000 -F 256MiB -T emp.dep ``` ```output Release version: v7.5.1 Git commit hash: 1a89decdb192cbdce6a7b0020d71128bc964d30f Git branch: heads/refs/tags/v7.5.1 Build timestamp: 2022-06-05 05:09:15Z Go version: go version go1.18.2 linux/amd64 [2022/11/08 15:09:37.456 +08:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v7.5.1] ["Git Commit Hash"=1a89decdb192cbdce6a7b0020d71128bc964d30f] ["Git Branch"=heads/refs/tags/v7.5.1] ["Build timestamp"="2022-06-05 05:09:15"] ["Go Version"="go version go1.18.2 linux/amd64"] ... [2022/11/08 15:09:37.496 +08:00] [INFO] [main.go:80] ["dump data successfully, dumpling will exit now"] ``` 3. Check the contents under `/tmp/dep` directory. Enter the following command at the terminal prompt and receive the results shown: ```command $ ls /tmp/dep ``` ```output emp.dep.0000000010000.sql emp.dep-schema.sql emp-schema-create.sql metadata ``` 4. View the details of `emp-schema-create.sql` script for creating the database. Enter the following command at the terminal prompt and receive the results shown: ```command $ cat /tmp/dep/emp-schema-create.sql ``` ```output /*!40101 SET NAMES binary*/; CREATE DATABASE `emp` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; ``` 5. View the details of `emp.dept-schema.sql` script for creating the table. Enter the following command at the terminal prompt and receive the results shown: ```command $ cat /tmp/dep/emp.dep-schema.sql ``` ```output /*!40101 SET NAMES binary*/; CREATE TABLE `dep` ( `dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL, PRIMARY KEY (`dept_no`) /*T![clustered_index] NONCLUSTERED */, UNIQUE KEY `dept_name` (`dept_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; ``` 6. View the details of `emp.dept.0000000010000.sql` script for data. Enter the following command at the terminal prompt and receive the results shown: ```command $ cat /tmp/dep/emp.dep.0000000010000.sql ``` ```output /*!40101 SET NAMES binary*/; INSERT INTO `dep` VALUES ('d001','Marketing'), ('d002','Finance'), ('d003','Human Resources'), ('d004','Production'), ('d005','Development'), ('d006','Quality Management'), ('d007','Sales'), ('d008','Research'), ('d009','Customer Service'); ``` 7. View the contents of the `metadata` data files. Enter the following command at the terminal prompt and receive the results shown: ```command $ cat /tmp/dep/metadata ``` ```output Started dump at: 2022-06-25 15:44:18 SHOW MASTER STATUS: Log: tidb-binlog Pos: 434147965850615813 GTID: Finished dump at: 2022-06-25 15:44:20 ``` 8. Export the `emp` database from the TiDB. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dumpling:v7.5.1 -uroot -P4000 -h ${HOST_DB1_PRIVATE_IP} --filetype sql -t 8 -o /tmp/emp -r 200000 -F 256MiB -B emp ``` ```output Release version: v7.5.1 Git commit hash: 1a89decdb192cbdce6a7b0020d71128bc964d30f Git branch: heads/refs/tags/v7.5.1 Build timestamp: 2022-06-05 05:09:15Z Go version: go version go1.18.2 linux/amd64 [2022/11/08 15:18:17.316 +08:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v7.5.1] ["Git Commit Hash"=1a89decdb192cbdce6a7b0020d71128bc964d30f] ["Git Branch"=heads/refs/tags/v7.5.1] ["Build timestamp"="2022-06-05 05:09:15"] ["Go Version"="go version go1.18.2 linux/amd64"] ... [2022/11/08 15:18:19.398 +08:00] [INFO] [main.go:80] ["dump data successfully, dumpling will exit now"] ``` 9. View the contents of the `/tmp/emp` directory. Enter the following command at the terminal prompt and receive the results shown: ```command $ ls /tmp/emp ``` ```output emp.dep.0000000010000.sql emp.emp.0000000010000.sql emp.sal.0000000010000.sql emp.sal.0000000030000.sql emp.sal-schema.sql emp.t.0000000010000.sql metadata emp.dep-schema.sql emp.emp-schema.sql emp.sal.0000000020000.sql emp.sal.0000000040000.sql emp-schema-create.sql emp.t-schema.sql ``` ## Exercise 4 ### Subject: Exporting the Data from MySQL Using Dumpling ### Overview + In this exercise, you export data stored in MySQL. You will perform all tasks on the Control Machine (monitor1_address). ### Duration + This exercise should take you approximately 20 minutes to complete. ### Tasks + Start a MySQL server instance on PD3, and import data into it. + Export the `emp` database from MySQL. ### Solution Steps 1. SSH to `PD3`: ```command $ ./ssh-to-pd3.sh ``` ```output The authenticity of host '10.80.0.215 (10.80.0.215)' can't be established. ... __| __|_ ) _| ( / Amazon Linux 2 AMI ___|\___|___| https://aws.amazon.com/amazon-linux-2/ ``` 2. Start MySQL server instance: ```command $ sudo service mysqld start ``` 3. Unzip the data on PD3. ```command unzip -u stage/tidb-admin-dataset-v2.zip -d stage/ ``` 4. Login MySQL server on port 3306 as `root@'localhost'` with the password `q1w2e3R4_`. Enter the following command at the terminal prompt and receive the results shown: ```command $ mysql -u root -p -h 127.0.0.1 -P 3306 -pq1w2e3R4_ ``` 5. Create a user for export task: ```command CREATE USER exp@'%' IDENTIFIED BY 'q1w2e3R4_'; ``` ```output mysql> CREATE USER exp@'%' IDENTIFIED BY 'q1w2e3R4_'; Query OK, 0 rows affected (0.00 sec) ``` ```command GRANT ALL PRIVILEGES on *.* to exp@'%'; ``` ```output mysql> GRANT ALL PRIVILEGES on *.* to exp@'%'; Query OK, 0 rows affected (0.00 sec) ``` 6. Import data into MySQL. Enter the following command at the terminal prompt and receive the results shown: ```command SOURCE ./stage/TiDB-Administration-exercise-data-v2/exercise-dumpling/emp-schema-create.sql ``` ```command USE emp; ``` ```output mysql> USE emp; Database changed ``` ```command SOURCE ./stage/TiDB-Administration-exercise-data-v2/exercise-dumpling/emp.dep-schema.sql SOURCE ./stage/TiDB-Administration-exercise-data-v2/exercise-dumpling/emp.dep.0000000010000.sql SOURCE ./stage/TiDB-Administration-exercise-data-v2/exercise-dumpling/emp.t-schema.sql SOURCE ./stage/TiDB-Administration-exercise-data-v2/exercise-dumpling/emp.t.0000000010000.sql SOURCE ./stage/TiDB-Administration-exercise-data-v2/exercise-dumpling/emp.emp-schema.sql SOURCE ./stage/TiDB-Administration-exercise-data-v2/exercise-dumpling/emp.emp.0000000010000.sql ``` ```output ... Query OK, 16843 rows affected (0.14 sec) Records: 16843 Duplicates: 0 Warnings: 0 Query OK, 12161 rows affected (0.10 sec) Records: 12161 Duplicates: 0 Warnings: 0 ``` 7. Review the tables in the `emp` database from MySQL. Then exit the clients. Enter the following command at the terminal prompt and receive the results shown: ```command SHOW TABLES; ``` ```output mysql> SHOW TABLES; +---------------+ | Tables_in_emp | +---------------+ | dep | | emp | | t | +---------------+ 3 rows in set (0.00 sec) ``` 8. Return to `monitor1` by exit from MySQL client and then the SSH terminal: ```command EXIT ``` ```output mysql> EXIT; Bye ``` ```command exit ``` ```output logout Connection to 10.90.1.78 closed. ``` 9. Export the `emp` database from MySQL with port 3306. Enter the following command at the terminal prompt and receive the results shown: ```command $ tiup dumpling:v7.5.1 -uexp -P 3306 -h ${HOST_PD3_PRIVATE_IP} -pq1w2e3R4_ --filetype sql -t 8 -o /tmp/empmysql -r 200000 -F 256MiB -B emp ``` ```output Release version: v7.5.1 Git commit hash: 1a89decdb192cbdce6a7b0020d71128bc964d30f Git branch: heads/refs/tags/v7.5.1 Build timestamp: 2022-06-05 05:09:15Z Go version: go version go1.18.2 linux/amd64 [2022/11/08 15:18:17.316 +08:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v7.5.1] ["Git Commit Hash"=1a89decdb192cbdce6a7b0020d71128bc964d30f] ["Git Branch"=heads/refs/tags/v7.5.1] ["Build timestamp"="2022-06-05 05:09:15"] ["Go Version"="go version go1.18.2 linux/amd64"] ... [2022/11/08 15:18:19.398 +08:00] [INFO] [main.go:80] ["dump data successfully, dumpling will exit now"] ``` 10. View the contents of the `/tmp/empmysql` directory. Enter the following command at the terminal prompt and receive the results shown: ```command $ ls /tmp/empmysql ``` ```output emp.dep.0000000010000.sql emp.emp-schema.sql emp.sal.0000000030000.sql emp-schema-create.sql metadata emp.dep-schema.sql emp.sal.0000000010000.sql emp.sal.0000000040000.sql emp.t.0000000010000.sql emp.emp.0000000010000.sql emp.sal.0000000020000.sql emp.sal-schema.sql emp.t-schema.sql ``` 11. Close all open terminal windows. ## End of Exercises