Dumpling介绍和使用

最新推荐文章于 2024-07-18 04:03:46 发布

老王笔记

最新推荐文章于 2024-07-18 04:03:46 发布

阅读量1.6k

点赞数

分类专栏： TiDB 文章标签： TiDB逻辑备份

本文链接：https://blog.csdn.net/JSWANGCHANG/article/details/122886250

版权

TiDB 专栏收录该内容

46 篇文章 3 订阅

订阅专栏

Dumpling架构与特点

支持多种数据形式，SQL/CSV
逻辑导出
支持表过滤，数据行过滤；
支持Amazon S3云盘
针对TiDB进行优化：支持内存限制，调整GC时间，使用隐藏列优化导出性能；

Dumpling适用场景

导出数据量较小，十几G最佳；
需要导出SQL或者CSV场景，可以在异构数据库进行数据迁移
对于导出效率要求不高，由于数是从TiDB节点导出，需要数据读取和转换，比物理导出效率低下；

Dumpling不适用场景

需要直接导出SST（KEY-VALUE）
增量备份：目前只支持全量，不支持增量；
数据量较大场景

部署

tiup install dumpling 或者 tidb-toolkit包

权限

SELECT
RELOAD
LOCK TABLES
REPLICATION CLIENT

参数介绍

Dumpling导出sql
dumpling -u user -P 4000 -h 127.0.0.1 
	--filetype csv 指定文件类型，sql/csv
	--threads 32 	并发数
	-o /export/servers/TiDB/backup   存储目录
	-r 200000 	导出时，单个文件的最大行数
	-F 256MiB	单个文件的最大大小，KB,MiB,GiB；
	--where "id < 10000"	#导出的所有的表都会进行id筛选，不适合全库； 
	--filter "employees.*"	#导出指定库
	--filter "*.order"		#导出所有库下的order表；

注意

Dummpling全库备份的时候会自动排除系统库

一致性保证

Dumpling通过 --consistency <consistency level>标志控制导出数据“一致性保证”的方式

flush：备份前会执行flush table with read lock，整个集群能读不能写；FTRL，温备
snapshot：默认值，可以指定备份的时间戳，要求备份的所有数据是该时间点的数据版本；
lock：只锁定备份的表
none：不锁定；数据会实时变化
auto：会根据目标数据库类型选择默认方式，tidb会选snapshot，mysql会用flush

Dumpling备份

dumpling]# dumpling -h XX.XX.XX.XX -P4000 -u root --password '' --filetype sql  --threads 2 -o /export/servers/TiDB/backup/dumpling -r 10000 
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

[2022/02/11 18:08:54.706 +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"]
[2022/02/11 18:08:54.709 +08:00] [INFO] [config.go:599] ["detect server type"] [type=TiDB]
[2022/02/11 18:08:54.709 +08:00] [INFO] [config.go:618] ["detect server version"] [version=5.3.0]
[2022/02/11 18:08:54.720 +08:00] [INFO] [client.go:193] ["[pd] create pd client with endpoints"] [pd-address="[XX.XX.XX.XX:2379,XX.XX.XX.XX:2379,XX.XX.XX.XX:2379]"]
[2022/02/11 18:08:54.724 +08:00] [INFO] [base_client.go:296] ["[pd] update member urls"] [old-urls="[http://XX.XX.XX.XX:2379,http://XX.XX.XX.XX:2379,http://XX.XX.XX.XX:2379]"] [new-urls="[http://XX.XX.XX.XX:2379,http://XX.XX.XX.XX:2379,http://XX.XX.XX.XX:2379]"]
[2022/02/11 18:08:54.724 +08:00] [INFO] [base_client.go:308] ["[pd] switch leader"] [new-leader=http://XX.XX.XX.XX:2379] [old-leader=]
[2022/02/11 18:08:54.724 +08:00] [INFO] [base_client.go:112] ["[pd] init cluster id"] [cluster-id=7058541070281534107]
[2022/02/11 18:08:54.725 +08:00] [INFO] [dump.go:936] ["generate dumpling gc safePoint id"] [id=dumpling_1644574134725790545]
[2022/02/11 18:08:54.730 +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\":\"XX.XX.XX.XX\",\"Port\":4000,\"Threads\":2,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/export/servers/TiDB/backup/dumpling\",\"StatusAddr\":\":8281\",\"Snapshot\":\"431115241965748232\",\"Consistency\":\"snapshot\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[],\"Where\":\"\",\"FileType\":\"sql\",\"ServerInfo\":{\"ServerType\":3,\"ServerVersion\":\"5.3.0\"},\"Rows\":10000,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":0,\"StatementSize\":1000000,\"SessionParams\":{\"tidb_snapshot\":\"431115241965748232\"},\"Tables\":null}"]
[2022/02/11 18:08:55.041 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=tony] [table=hero] [chunkIdx=0]
[2022/02/11 18:08:55.198 +08:00] [WARN] [writer.go:230] ["no data written in table chunk"] [database=tony] [table=hero2] [chunkIdx=0]
[2022/02/11 18:08:55.586 +08:00] [INFO] [collector.go:212] ["backup Success summary: total backup ranges: 7, total success: 7, total failed: 0, total take(backup time): 823.020183ms, total take(real time): 823.083425ms, total size(MB): 5.18, avg speed(MB/s): 6.30, total rows: 262153"]
[2022/02/11 18:08:55.586 +08:00] [INFO] [main.go:81] ["dump data successfully, dumpling will exit now"]

Dumpling备份文件说明

.
|-- metadata					//元数据文件,含有备份时间，BINLOG POS，GTID等
|-- tony.hero.000000001.sql		//数据文件
|-- tony.hero2.000000001.sql	
|-- tony.hero2-schema.sql		//建表语句
|-- tony.hero-schema.sql		
`-- tony-schema-create.sql		//建库语句

其他备份方式

备份指定库：

dumpling -h XX.XX.XX.XX -P4000 -u root --password 'xxxxxx' --filetype sql -B tony --threads 2 -o /export/servers/TiDB/backup/dumpling -r 10000

备份特定表：

dumpling -h XX.XX.XX.XX -P4000 -u root --password 'xxxxxx' --filetype sql -T tony.hero2 --threads 2 -o /export/servers/TiDB/backup/dumpling -r 10000

只备份表名含有hero的

dumpling -h XX.XX.XX.XX -P4000 -u root --password 'xxxxxx' --filetype sql -f "*.hero*" --threads 2 -o /export/servers/TiDB/backup/dumpling -r 10000

过滤部分数据行

dumpling -h XX.XX.XX.XX -P4000 -u root --password 'xxxxxx' --filetype sql -f "*.hero*" --where "id < 10" --threads 2 -o /export/servers/TiDB/backup/dumpling -r 10000