概述
TiDB导出工具 Dumpling，可以把存储在 TiDB 或 MySQL 中的数据导出为 SQL 或 CSV 格式，用于逻辑全量备份。
安装
因为Dumpling和BR相同，都在tidb-toolkit包中，安装请参考上一篇BR的部署。
导出TiDB数据库数据
单库导出成SQL文件
Dumpling导出工具不适合数据量很大的库或者表，如果库超过10G以上建议使用其他工具，单库导出命令：dumpling -uroot -P4000 -h 192.168.59.27 --filetype sql -t 8 -o /tmp/chnbs -r 200000 -F 256MiB -B chnbs
dumpling命令参数详见Dumpling 使用文档 | PingCAP Docs
[root@node1 ~]# dumpling -uroot -P4000 -h 192.168.59.27 --filetype sql -t 8 -o /tmp/chnbs -r 200000 -F 256MiB -B chnbs
Release version: v5.0.1
Git commit hash: 4cb115746bb658b6d1a12c0e49932bfd3a08afac
Git branch: heads/refs/tags/v5.0.1
Build timestamp: 2021-04-23 06:01:59Z
Go version: go version go1.13 linux/amd64
[2021/11/08 12:17:33.878 +08:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v5.0.1] ["Git Commit Hash"=4cb115746bb658b6d1a12c0e49932bfd3a08afac] ["Git Branch"=heads/refs/tags/v5.0.1] ["Build timestamp"="2021-04-23 06:01:59"] ["Go Version"="go version go1.13 linux/amd64"]
[2021/11/08 12:17:33.881 +08:00] [INFO] [config.go:599] ["detect server type"] [type=TiDB]
[2021/11/08 12:17:33.881 +08:00] [INFO] [config.go:618] ["detect server version"] [version=5.2.1]
[2021/11/08 12:17:33.899 +08:00] [INFO] [client.go:193] ["[pd] create pd client with endpoints"] [pd-address="[192.168.59.29:2379,192.168.59.28:2379,192.168.59.27:2379]"]
[2021/11/08 12:17:33.903 +08:00] [INFO] [base_client.go:296] ["[pd] update member urls"] [old-urls="[http://192.168.59.29:2379,http://192.168.59.28:2379,http://192.168.59.27:2379]"] [new-urls="[http://192.168.59.27:2379,http://192.168.59.28:2379,http://192.168.59.29:2379]"]
[2021/11/08 12:17:33.903 +08:00] [INFO] [base_client.go:308] ["[pd] switch leader"] [new-leader=http://192.168.59.28:2379] [old-leader=]
[2021/11/08 12:17:33.903 +08:00] [INFO] [base_client.go:112] ["[pd] init cluster id"] [cluster-id=7021398084574750333]
[2021/11/08 12:17:33.904 +08:00] [INFO] [dump.go:936] ["generate dumpling gc safePoint id"] [id=dumpling_1636345053904353027]
[2021/11/08 12:17:33.914 +08:00] [INFO] [dump.go:83] ["begin to run Dump"] [conf="{\"s3\":{\"endpoint\":\"\",\"region\":\"\",\"storage-class\":\"\",\"sse\":\"\",\"sse-kms-key-id\":\"\",\"acl\":\"\",\"access-key\":\"\",\"secret-access-key\":\"\",\"provider\":\"\",\"force-path-style\":true,\"use-accelerate-endpoint\":false},\"gcs\":{\"endpoint\":\"\",\"storage-class\":\"\",\"predefined-acl\":\"\",\"credentials-file\":\"\"},\"AllowCleartextPasswords\":false,\"SortByPk\":true,\"NoViews\":true,\"NoHeader\":false,\"NoSchemas\":false,\"NoData\":false,\"CompleteInsert\":false,\"TransactionalConsistency\":true,\"EscapeBackslash\":true,\"DumpEmptyDatabase\":true,\"PosAfterConnect\":false,\"CompressType\":0,\"Host\":\"192.168.59.27\",\"Port\":4000,\"Threads\":8,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/tmp/chnbs\",\"StatusAddr\":\":8281\",\"Snapshot\":\"428958037799337989\",\"Consistency\":\"snapshot\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[\"chnbs\"],\"Where\":\"\",\"FileType\":\"sql\",\"ServerInfo\":{\"ServerType\":3,\"ServerVersion\":\"5.2.1\"},\"Rows\":200000,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":268435456,\"StatementSize\":1000000,\"SessionParams\":{\"tidb_snapshot\":\"428958037799337989\"},\"Tables\":null}"]
[2021/11/08 12:17:34.364 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=chnbs] [table=f_industry] [chunkIdx=0]
[2021/11/08 12:17:34.449 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=chnbs] [table=f_order] [chunkIdx=0]
[2021/11/08 12:17:34.528 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=chnbs] [table=a] [chunkIdx=0]
[2021/11/08 12:17:34.606 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=chnbs] [table=f_config] [chunkIdx=0]
[2021/11/08 12:17:34.686 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=chnbs] [table=f_comment] [chunkIdx=0]
[2021/11/08 12:17:34.765 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=chnbs] [table=f_notification_badge] [chunkIdx=0]
[2021/11/08 12:17:34.846 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=chnbs] [table=f_job_intention] [chunkIdx=0]
[2021/11/08 12:17:34.926 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=chnbs] [table=f_notification] [chunkIdx=0]
[2021/11/08 12:17:35.009 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=chnbs] [table=f_enterprise] [chunkIdx=0]
[2021/11/08 12:17:35.009 +08:00] [INFO] [collector.go:212] ["backup Success summary: total backup ranges: 28, total success: 28, total failed: 0, total take(backup time): 1.044577872s, total take(real time): 1.044612942s, total size(Byte): 22053, avg speed(Byte/s): 21111.88, total rows: 233"]
[2021/11/08 12:17:35.010 +08:00] [INFO] [main.go:81] ["dump data successfully, dumpling will exit now"] 以上可以看出已经备份成功，查看备份文件：
输出的文件格式：
metadata：此文件包含导出的起始时间，以及 master binary log 的位置。
{schema}-schema-create.sql：创建 schema 的 SQL 文件。
{schema}.{table}-schema.sql：创建 table 的 SQL 文件。
schema}.{table}.{0001}.{sql|csv}：数据源文件。
*-schema-view.sql、*-schema-trigger.sql、*-schema-post.sql：其他导出文件。
单库导出成CSV文件
命令：dumpling -uroot -P4000 -h 192.168.59.27 --filetype csv -t 8 -o /tmp/chnbs -B chnbs，此导出唯一差异是存放数据的文件变为csv格式，其他文件及结构与SQL导出相同。
[root@node1 chnbs]# dumpling -uroot -P4000 -h 192.168.59.27 --filetype csv -t 8 -o /tmp/chnbs -B chnbs
Release version: v5.0.1
Git commit hash: 4cb115746bb658b6d1a12c0e49932bfd3a08afac
Git branch: heads/refs/tags/v5.0.1
Build timestamp: 2021-04-23 06:01:59Z
Go version: go version go1.13 linux/amd64
[2021/11/08 12:25:54.723 +08:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v5.0.1] ["Git Commit Hash"=4cb115746bb658b6d1a12c0e49932bfd3a08afac] ["Git Branch"=heads/refs/tags/v5.0.1] ["Build timestamp"="2021-04-23 06:01:59"] ["Go Version"="go version go1.13 linux/amd64"]
[2021/11/08 12:25:54.725 +08:00] [INFO] [config.go:599] ["detect server type"] [type=TiDB]
[2021/11/08 12:25:54.725 +08:00] [INFO] [config.go:618] ["detect server version"] [version=5.2.1]
[2021/11/08 12:25:54.735 +08:00] [INFO] [client.go:193] ["[pd] create pd client with endpoints"] [pd-address="[192.168.59.29:2379,192.168.59.28:2379,192.168.59.27:2379]"]
[2021/11/08 12:25:54.738 +08:00] [INFO] [base_client.go:296] ["[pd] update member urls"] [old-urls="[http://192.168.59.29:2379,http://192.168.59.28:2379,http://192.168.59.27:2379]"] [new-urls="[http://192.168.59.27:2379,http://192.168.59.28:2379,http://192.168.59.29:2379]"]
[2021/11/08 12:25:54.738 +08:00] [INFO] [base_client.go:308] ["[pd] switch leader"] [new-leader=http://192.168.59.28:2379] [old-leader=]
[2021/11/08 12:25:54.738 +08:00] [INFO] [base_client.go:112] ["[pd] init cluster id"] [cluster-id=7021398084574750333]
[2021/11/08 12:25:54.739 +08:00] [INFO] [dump.go:936] ["generate dumpling gc safePoint id"] [id=dumpling_1636345554739396496]
[2021/11/08 12:25:54.745 +08:00] [INFO] [dump.go:83] ["begin to run Dump"] [conf="{\"s3\":{\"endpoint\":\"\",\"region\":\"\",\"storage-class\":\"\",\"sse\":\"\",\"sse-kms-key-id\":\"\",\"acl\":\"\",\"access-key\":\"\",\"secret-access-key\":\"\",\"provider\":\"\",\"force-path-style\":true,\"use-accelerate-endpoint\":false},\"gcs\":{\"endpoint\":\"\",\"storage-class\":\"\",\"predefined-acl\":\"\",\"credentials-file\":\"\"},\"AllowCleartextPasswords\":false,\"SortByPk\":true,\"NoViews\":true,\"NoHeader\":false,\"NoSchemas\":false,\"NoData\":false,\"CompleteInsert\":false,\"TransactionalConsistency\":true,\"EscapeBackslash\":true,\"DumpEmptyDatabase\":true,\"PosAfterConnect\":false,\"CompressType\":0,\"Host\":\"192.168.59.27\",\"Port\":4000,\"Threads\":8,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/tmp/chnbs\",\"StatusAddr\":\":8281\",\"Snapshot\":\"428958169093898246\",\"Consistency\":\"snapshot\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[\"chnbs\"],\"Where\":\"\",\"FileType\":\"csv\",\"ServerInfo\":{\"ServerType\":3,\"ServerVersion\":\"5.2.1\"},\"Rows\":0,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":0,\"StatementSize\":1000000,\"SessionParams\":{\"tidb_snapshot\":\"428958169093898246\"},\"Tables\":null}"]
[2021/11/08 12:25:55.903 +08:00] [INFO] [collector.go:212] ["backup Success summary: total backup ranges: 19, total success: 19, total failed: 0, total take(backup time): 1.118167806s, total take(real time): 1.11821236s, total size(Byte): 20796, avg speed(Byte/s): 18598.28, total rows: 233"]
[2021/11/08 12:25:55.904 +08:00] [INFO] [main.go:81] ["dump data successfully, dumpling will exit now"]
[root@node1 chnbs]# ls
chnbs.a.000000000.csv chnbs.f_config-schema.sql chnbs.f_job_intention.000000000.csv chnbs.f_notification-schema.sql
chnbs.a-schema.sql chnbs.f_enterprise.000000000.csv chnbs.f_job_intention-schema.sql chnbs.f_order.000000000.csv
chnbs.f_comment.000000000.csv chnbs.f_enterprise-schema.sql chnbs.f_notification.000000000.csv chnbs.f_order-schema.sql
chnbs.f_comment-schema.sql chnbs.f_industry.000000000.csv chnbs.f_notification_badge.000000000.csv chnbs-schema-create.sql
chnbs.f_config.000000000.csv chnbs.f_industry-schema.sql chnbs.f_notification_badge-schema.sql metadata
导出MySQL数据库数据
连接MySQL数据库查看数据
[root@node1 chnbs]# mysql -uroot -h 192.168.59.72 -P3306 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5335
Server version: 5.6.16-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lottery |
| modoer |
| mysql |
| oauth |
| open |
| performance_schema |
| spider |
| test |
| ucenter |
+--------------------+
10 rows in set (0.00 sec)
mysql> use spider;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------+
| Tables_in_spider |
+---------------------+
| sp_analyze_conf |
| sp_area_code |
| sp_article_category |
| sp_business_code |
| sp_classify_result |
| sp_file_cache |
| sp_gather_conf |
| sp_meta_basic |
| sp_meta_detail |
| sp_parameter |
| sp_path_regex |
| sp_process_queue |
| sp_resource_file |
+---------------------+
13 rows in set (0.01 sec)
mysql> select count(*) from sp_analyze_conf;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.04 sec)导出spider数据库
[root@node1 tmp]# dumpling -uroot -p111111 -P3306 -h 192.168.59.72 --filetype sql -t 8 -o /tmp/spider -B spider
Release version: v5.0.1
Git commit hash: 4cb115746bb658b6d1a12c0e49932bfd3a08afac
Git branch: heads/refs/tags/v5.0.1
Build timestamp: 2021-04-23 06:01:59Z
Go version: go version go1.13 linux/amd64
[2021/11/08 12:34:42.232 +08:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v5.0.1] ["Git Commit Hash"=4cb115746bb658b6d1a12c0e49932bfd3a08afac] ["Git Branch"=heads/refs/tags/v5.0.1] ["Build timestamp"="2021-04-23 06:01:59"] ["Go Version"="go version go1.13 linux/amd64"]
[2021/11/08 12:34:42.234 +08:00] [INFO] [config.go:599] ["detect server type"] [type=MySQL]
[2021/11/08 12:34:42.234 +08:00] [INFO] [config.go:618] ["detect server version"] [version=5.6.16-enterprise-commercial-advanced]
[2021/11/08 12:34:42.235 +08:00] [INFO] [dump.go:83] ["begin to run Dump"] [conf="{\"s3\":{\"endpoint\":\"\",\"region\":\"\",\"storage-class\":\"\",\"sse\":\"\",\"sse-kms-key-id\":\"\",\"acl\":\"\",\"access-key\":\"\",\"secret-access-key\":\"\",\"provider\":\"\",\"force-path-style\":true,\"use-accelerate-endpoint\":false},\"gcs\":{\"endpoint\":\"\",\"storage-class\":\"\",\"predefined-acl\":\"\",\"credentials-file\":\"\"},\"AllowCleartextPasswords\":false,\"SortByPk\":true,\"NoViews\":true,\"NoHeader\":false,\"NoSchemas\":false,\"NoData\":false,\"CompleteInsert\":false,\"TransactionalConsistency\":true,\"EscapeBackslash\":true,\"DumpEmptyDatabase\":true,\"PosAfterConnect\":false,\"CompressType\":0,\"Host\":\"192.168.59.72\",\"Port\":3306,\"Threads\":8,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/tmp/spider\",\"StatusAddr\":\":8281\",\"Snapshot\":\"\",\"Consistency\":\"flush\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[\"spider\"],\"Where\":\"\",\"FileType\":\"sql\",\"ServerInfo\":{\"ServerType\":1,\"ServerVersion\":\"5.6.16-enterprise-commercial-advanced\"},\"Rows\":0,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":0,\"StatementSize\":1000000,\"SessionParams\":{},\"Tables\":null}"]
[2021/11/08 12:34:42.619 +08:00] [INFO] [dump.go:178] ["All the dumping transactions have started. Start to unlock tables"]
[2021/11/08 12:34:43.182 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=spider] [table=sp_resource_file] [chunkIdx=0]
[2021/11/08 12:35:01.218 +08:00] [INFO] [collector.go:212] ["backup Success summary: total backup ranges: 27, total success: 27, total failed: 0, total take(backup time): 18.597701063s, total take(real time): 18.59775185s, total size(MB): 264.88, avg speed(MB/s): 14.24, total rows: 26920"]
[2021/11/08 12:35:01.218 +08:00] [INFO] [main.go:81] ["dump data successfully, dumpling will exit now"]
[root@node1 tmp]# cd spider/
[root@node1 spider]# ls
metadata spider.sp_article_category-schema.sql spider.sp_gather_conf.000000000.sql spider.sp_parameter-schema.sql
spider-schema-create.sql spider.sp_business_code.000000000.sql spider.sp_gather_conf-schema.sql spider.sp_path_regex.000000000.sql
spider.sp_analyze_conf.000000000.sql spider.sp_business_code-schema.sql spider.sp_meta_basic.000000000.sql spider.sp_path_regex-schema.sql
spider.sp_analyze_conf-schema.sql spider.sp_classify_result.000000000.sql spider.sp_meta_basic-schema.sql spider.sp_process_queue.000000000.sql
spider.sp_area_code.000000000.sql spider.sp_classify_result-schema.sql spider.sp_meta_detail.000000000.sql spider.sp_process_queue-schema.sql
spider.sp_area_code-schema.sql spider.sp_file_cache.000000000.sql spider.sp_meta_detail-schema.sql spider.sp_resource_file-schema.sql
spider.sp_article_category.000000000.sql spider.sp_file_cache-schema.sql spider.sp_parameter.000000000.sql
[root@node1 spider]# cat metadata
Started dump at: 2021-11-08 12:34:42
Finished dump at: 2021-11-08 12:35:01
[root@node1 spider]# cat spider.sp_parameter.000000000.sql
/*!40101 SET NAMES binary*/;
INSERT INTO `sp_parameter` VALUES
(10000,'svm_train','0','0：根据语料训练模型 1：预测文本所属分类','xyuser','2015-11-05 17:55:31');
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
