--- title: "Exporting Data Using Dumpling" version: "v2" status: "GA" description: "In this hands-on lab, you will learn how to export data using Dumpling. Specifically, we will cover how to deploy the Dumpling data export tool, import data into TiDB using TiDB Lightning, export the data from TiDB Using Dumpling, exporting data from MySQL using Dumpling. This lab is a practical workshop that allows you to learn how to export data using Dumpling." --- # Lab 7: Exporting Data Using Dumpling ## Overview >[!NOTE] > This hands-on lab is also included in the [TiDB Certification Training: TiDB Administration Using TiUP](https://www.pingcap.com/education/course-tidb-database-administration-for-self-hosted-deployments/) course. In this lab, you 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://lab-static.pingcap.com/quick-demo/TiDBClusterDeploymentTopologyExercisewithDumpling_aa_003_20230301.png) > [!TIP] > Click **Start** to launch the lab environment. > Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**. > And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks. ## Module 1: Deploying the `Dumpling` ### Step 1. Log into the remote Linux + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner after you logged in: ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### Step 2. Install `Dumpling` ```bash tiup install dumpling:v7.5.1 ``` ```output component dumpling version v7.5.1 is already installed ``` ### Step 3. Extract the data ```bash unzip -u stage/tidb-admin-dataset-v2.zip -d stage/ ``` ```output Archive: stage/tidb-admin-dataset-v2.zip ``` ## Module 2: Importing the Data into TiDB Using TiDB Lightning ### Step 1. List the contents of the data files directory ```bash 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 ``` ### Step 2. Connect to the TiDB and drop the `emp` database. ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql SHOW DATABASES; ``` ```output mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | INFORMATION_SCHEMA | | METRICS_SCHEMA | | PERFORMANCE_SCHEMA | | emp | | mysql | | test | | tidb | +--------------------+ 7 rows in set (0.00 sec) ``` ```sql DROP DATABASE IF EXISTS emp; ``` ```output mysql> DROP DATABASE IF EXISTS emp; Query OK, 0 rows affected (0.21 sec) ``` ```sql EXIT ``` ```output mysql> EXIT Bye ``` ### Step 3. Read `solution-lightning-sql.toml` + The IP addresses shown below will be different from yours. ```bash 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" ``` ### Step 4. Import data into TiDB + Use a `nohup` command to start the `tidb-lightning` process (press Enter to make `tidb-lighting` work in the background). ```bash 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 ``` ### Step 5. View the output of Lightning ```bash 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 ``` ## Module 3: Exporting the Data from TiDB Using Dumpling ### Step 1. Connect to the TiDB and review the tables in the `emp` database ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql SHOW DATABASES; ``` ```output mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | INFORMATION_SCHEMA | | METRICS_SCHEMA | | PERFORMANCE_SCHEMA | | emp | | mysql | | test | | tidb | +--------------------+ 7 rows in set (0.00 sec) ``` ```sql USE emp; ``` ```output mysql> USE emp; Database changed ``` ```sql SHOW TABLES; ``` ```output mysql> SHOW TABLES; +---------------+ | Tables_in_emp | +---------------+ | dep | | emp | | sal | | t | +---------------+ 4 rows in set (0.00 sec) ``` ```sql EXIT ``` ```output mysql> EXIT Bye ``` ### Step 2. Export the `dept` table in the `emp` database from TiDB + Here are the explanation of the parameters: + `-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). ```bash 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"] ``` ### Step 3. Check the contents under `/tmp/dep` directory ```bash ls /tmp/dep ``` ```output emp.dep.0000000010000.sql emp.dep-schema.sql emp-schema-create.sql metadata ``` ### Step 4. Check the outputs of Dumpling ```bash cat /tmp/dep/emp-schema-create.sql ``` ```output /*!40101 SET NAMES binary*/; CREATE DATABASE `emp` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; ``` ```bash 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; ``` ```bash 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'); ``` ```bash 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 ``` ### Step 5. Export the `emp` database from the TiDB ```bash 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"] ``` ### Step 6. View the contents of the `/tmp/emp` directory ```bash 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 ``` ## Module 4: Exporting the Data from MySQL Using Dumpling ### Step 1. SSH to `PD3` ```bash ./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/ ``` ### Step 2. Start MySQL server instance ```bash sudo service mysqld start ``` ### Step 3. Unzip the data on PD3 ```bash unzip -u stage/tidb-admin-dataset-v2.zip -d stage/ ``` ### Step 4.. Login MySQL Server ```bash mysql -u root -p -h 127.0.0.1 -P 3306 -pq1w2e3R4_ ``` ### Step 5. Create a user for export task ```sql CREATE USER exp@'%' IDENTIFIED BY 'q1w2e3R4_'; ``` ```output mysql> CREATE USER exp@'%' IDENTIFIED BY 'q1w2e3R4_'; Query OK, 0 rows affected (0.00 sec) ``` ```sql GRANT ALL PRIVILEGES on *.* to exp@'%'; ``` ```output mysql> GRANT ALL PRIVILEGES on *.* to exp@'%'; Query OK, 0 rows affected (0.00 sec) ``` ### Step 6. Import data into MySQL ```sql SOURCE ./stage/TiDB-Administration-exercise-data-v2/exercise-dumpling/emp-schema-create.sql ``` ```sql USE emp; ``` ```output mysql> USE emp; Database changed ``` ```sql 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 ``` ### Step 7. Review the tables in the `emp` database from MySQL ```sql SHOW TABLES; ``` ```output mysql> SHOW TABLES; +---------------+ | Tables_in_emp | +---------------+ | dep | | emp | | t | +---------------+ 3 rows in set (0.00 sec) ``` ### Step 8. Return to `monitor1` ```sql EXIT ``` ```output mysql> EXIT; Bye ``` ```bash exit ``` ```output logout Connection to 10.90.1.78 closed. ``` ### Step 9. Export the `emp` database from MySQL ```bash 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"] ``` ### Step 10. View the contents in the output directory ```bash 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 ``` ## End of Exercises