# 实验 7：使用 Dumpling 导出数据 ## 概述 在本实验中，你将把存储在 TiDB/MySQL 中的数据导出为 SQL 或 CSV 数据文件，这些文件可用于进行逻辑全量备份或导出。以下展示了 TiDB 集群的部署拓扑图。 ![60%](https://lab-static.pingcap.com/quick-demo/TiDBClusterDeploymentTopologyExercisewithDumpling_aa_003_20230301.png) > [!TIP] > 点击 **开始** 启动实验环境。 > 环境准备就绪后，点击底部工具栏中的 **前进** 进入 **模块 1**。 > 然后，按照实验指南的步骤连接到实践环境并开始你的任务。 ## 模块 1：部署 `Dumpling` ### 步骤 1：登录远程 Linux + 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。 + 登录后显示的横幅： ```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. 安装 `Dumpling` ```bash tiup install dumpling:v7.5.1 ``` ```output component dumpling version v7.5.1 is already installed ``` ### 步骤 3. 解压数据 ```bash unzip -u stage/tidb-admin-dataset-v2.zip -d stage/ ``` ```output Archive: stage/tidb-admin-dataset-v2.zip ``` ## 模块 2：使用 TiDB Lightning 将数据导入 TiDB ### 步骤 1. 列出数据文件目录的文件 ```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 ``` ### 步骤 2. 连接到 TiDB 并删除 `emp` 数据库。 ```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 ``` ### 步骤 3. 查看 `solution-lightning-sql.toml` + 以下显示的 IP 地址可能会与你的不同。 ```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" ``` ### 步骤 4. 将数据导入 TiDB + 使用 `nohup` 命令启动 `tidb-lightning` 进程（按 Enter 让 `tidb-lighting` 在后台运行）。 ```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 ``` ### 步骤 5. 查看 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 ``` ## 模块 3：使用 Dumpling 从 TiDB 导出数据 ### 步骤 1. 连接到 TiDB 并查看 `emp` 数据库中的表 ```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 ``` ### 步骤 2. 从 TiDB 导出 `emp` 数据库中的 `dept` 表 + 以下是参数说明： + `-uroot`: 用户为 root。 + `-P4000`: 端口为 4000。 + `-h ${HOST_DB1_PRIVATE_IP}`: TiDB 的 IP 地址为 `${HOST_DB1_PRIVATE_IP}`。 + `--filetype sql`: 将数据导出到 SQL 文件。 + `-t 8`: 使用 8 个线程同时导出。 + `-o /tmp/dept`: 指定 `/tmp/dept` 为导出目录。 + `-r 200000`: 指定单个文件中的最大行数为 200000。 + `-F 256MiB`: 指定单个文件的最大大小为 256 MiB（此处单位为 MiB；也接受 5GiB 或 8KB 等输入） ```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"] ``` ### 步骤 3. 检查 `/tmp/dep` 目录下的内容 ```bash ls /tmp/dep ``` ```output emp.dep.0000000010000.sql emp.dep-schema.sql emp-schema-create.sql metadata ``` ### 步骤 4. 检查 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 ``` ### 步骤 5. 从 TiDB 导出 `emp` 数据库 ```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"] ``` ### 步骤 6. 查看 `/tmp/emp` 目录的内容 ```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 ``` ## 模块 4：使用 Dumpling 从 MySQL 导出数据 ### 步骤 1. 通过 SSH 连接到 `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/ ``` ### 步骤 2. 启动 MySQL server 实例 ```bash sudo service mysqld start ``` ### 步骤 3. 在 PD3 上解压数据 ```bash unzip -u stage/tidb-admin-dataset-v2.zip -d stage/ ``` ### 步骤 4. 登录 MySQL Server ```bash mysql -u root -p -h 127.0.0.1 -P 3306 -pq1w2e3R4_ ``` ### 步骤 5. 为导出任务创建一个用户 ```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) ``` ### 步骤 6. 将数据导入 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 ``` ### 步骤 7. 查看 MySQL 中 `emp` 数据库的表 ```sql SHOW TABLES; ``` ```output mysql> SHOW TABLES; +---------------+ | Tables_in_emp | +---------------+ | dep | | emp | | t | +---------------+ 3 rows in set (0.00 sec) ``` ### 步骤 8. 返回 `monitor1` ```sql EXIT ``` ```output mysql> EXIT; Bye ``` ```bash exit ``` ```output logout Connection to 10.90.1.78 closed. ``` ### 步骤 9. 从 MySQL 导出 `emp` 数据库 ```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"] ``` ### 步骤 10. 查看输出目录中的文件 ```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 ``` ## 实验结束