020、数据库管理之数据导出工具(Dumpling)

dumpling详解

dumpling介绍

数据导出工具dumpling 可以把存储在TiDB/MySQL中的数据导出为SQL或者csv格式，可以用于未完成逻辑上的全量备份或导出。

dumpling架构与特点

• 支持导出多种数据形式，包括SQL/CSV
• 逻辑导出
• 支持表过滤和数据过滤

dumpling 适用场景

• 导数数据量小。
• 需要导出SQL语句或者csv，可以在异构数据库进行迁移
• 比物理导出效率低
• 不能做增量备份
• 数据量较大不适用

dumpling的部署

• 使用TiUP

tiup install dumpling

• tidb-toolkit安装包

dumpling 所需权限

select、reload、lock tables、replication client

dumpling 导出SQL文件

dumpling -uroot -P 4000 -h localhost --filetype sql --threads 32 -o /tmp/test -r 200000 -F 256MiB

-r : 单词表最大行数，开启这个参数，会使用并行
-F：备份文件的单个文件大小

dumpling 导出csv文件

dumpling -uroot -P 4000 -h localhost -o /tmp/test --filetype csv

对导出数据进行筛选

使用–where 选项筛选数据

dumpling -uroot -P 4000 -h localhost -o /tmp/test --where "id < 100"

所有的表id<100,dump默认会对系统库排除。 另外这个只针对csv文件可以 使用–where

使用–filter 选项筛选数据

dumpling -uroot -P 4000 -h localhost -o /tmp/test -r 200000 --filter "employees.*" --filter "*.workorder"

使用-B 或-T选项 筛选数据
-B empoyeees : 导出employees数据库
-T employees.workorder : 导出employees.workorder 表

导出数据的格式

metadata: 此文件包含导出的起始时间，以及master binary log 的位置

包含创建schema的SQL文件
test-schema-create.sql: 创建test库的SQL文件

包含创建table的SQL文件
test.t1-schema.sql: 创建表的SQL文件

包含数据源文件
{schema}.{table}.{0001}.{sql|csv} : 数据源文件

导出数据的一致性

dumpling 通过–consistency 标志控制导出数据一致性。

• flush :FLUSH 方式就是FTRL,备份期间只能读 flush table with read lock
• snapshot :基于指定时间点做的备份，使用MVCC机制
• lock :备份哪些对象，就锁哪些对象
• none : 不会用，备份的数据不一致
• auto :如果tidb 选用snapshot 如果是mysql 则是flush

./dumpling --snapshot 417773951312461825
./dumpling --snapshot “2022-01-02 03:04:05”

dumpling 性能优化

-t: 用于指定导出的线程数
-r: 选项用于指定单个文件的最大行数

实验

导出TiDB 数据库中的单张表和单个库

1、查看当前的表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| afs_test       |
| t1             |
| t2             |
+----------------+
3 rows in set (0.00 sec)

2、导出数据

[root@tiup tidb-toolkit]# ./dumpling -uroot -P4000 -h192.168.16.10 --filetype sql -t 2 -o /tmp/test/table -r 1000 -F 1MiB  -T test.afs_test 
-r : 每个导出文件最大容纳的行数
-F ： 每个导出文件最大的容量
-t ： 指定多少个线程同时导出。


Release version: v6.0.0
Git commit hash: 36a9810441ca0e496cbd22064af274b3be771081
Git branch:      heads/refs/tags/v6.0.0
Build timestamp: 2022-03-31 10:27:29Z
Go version:      go version go1.18 linux/amd64

[2023/06/28 05:50:45.160 -04:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v6.0.0] ["Git Commit Hash"=36a9810441ca0e496cbd22064af274b3be771081] ["Git Branch"=heads/refs/tags/v6.0.0] ["Build timestamp"="2022-03-31 10:27:29"] ["Go Version"="go version go1.18 linux/amd64"]
[2023/06/28 05:50:45.610 -04:00] [INFO] [version.go:362] ["detect server version"] [type=TiDB] [version=6.1.6]
[2023/06/28 05:50:45.928 -04:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[192.168.16.10:2379]"]
[2023/06/28 05:50:45.936 -04:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://192.168.16.10:2379] [old-leader=]
[2023/06/28 05:50:45.936 -04:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7249668463048054636]
[2023/06/28 05:50:45.937 -04:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global]
[2023/06/28 05:50:45.944 -04:00] [INFO] [dump.go:1384] ["generate dumpling gc safePoint id"] [id=dumpling_1687945845944772357]
[2023/06/28 05:50:46.050 -04:00] [INFO] [dump.go:103] ["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\":\"\"},\"azblob\":{\"endpoint\":\"\",\"account-name\":\"\",\"account-key\":\"\",\"access-tier\":\"\"},\"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.16.10\",\"Port\":4000,\"Threads\":2,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/tmp/test/table\",\"StatusAddr\":\":8281\",\"Snapshot\":\"442484875827347459\",\"Consistency\":\"snapshot\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[],\"Where\":\"\",\"FileType\":\"sql\",\"ServerInfo\":{\"ServerType\":3,\"ServerVersion\":\"6.1.6\",\"HasTiKV\":true},\"Rows\":1000,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":1048576,\"StatementSize\":1000000,\"SessionParams\":{\"tidb_snapshot\":\"442484875827347459\"},\"Tables\":null,\"CollationCompatible\":\"loose\"}"]
[2023/06/28 05:50:46.468 -04:00] [INFO] [writer.go:242] ["no data written in table chunk"] [database=test] [table=afs_test] [chunkIdx=0]
[2023/06/28 05:50:46.593 -04:00] [INFO] [collector.go:237] ["backup success summary"] [total-ranges=4] [ranges-succeed=4] [ranges-failed=0] [total-take=280.28639ms] [total-kv-size=432.2kB] [average-speed=1.542MB/s] [total-rows=10842]
[2023/06/28 05:50:46.594 -04:00] [INFO] [main.go:80] ["dump data successfully, dumpling will exit now"]

3、 进入导出目录，查看导出文件

[root@tiup tidb-toolkit]# cd /tmp/test/table/
[root@tiup table]# ls
metadata                         test.afs_test-schema.sql
test.afs_test.0000000010000.sql  test-schema-create.sql

建库语句

[root@tiup table]# more test-schema-create.sql 
/*!40101 SET NAMES binary*/;
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

建表语句

[root@tiup table]# more test.afs_test-schema.sql 
/*!40101 SET NAMES binary*/;
CREATE TABLE `afs_test` (
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

数据插入脚本

[root@tiup table]# more test.afs_test.0000000010000.sql 
/*!40101 SET NAMES binary*/;
INSERT INTO `afs_test` VALUES
('HELLO1',1,'2023-06-28 05:40:34'),
('HELLO2',2,'2023-06-28 05:40:35'),

元数据信息

[root@tiup table]# more metadata 
Started dump at: 2023-06-28 05:50:46
SHOW MASTER STATUS:
	Log: tidb-binlog
	Pos: 442484875827347459
	GTID:

Finished dump at: 2023-06-28 05:50:46

4、导出整库

[root@tiup tidb-toolkit]# ./dumpling -uroot -P4000 -h192.168.16.10 --filetype sql -t 2 -o /tmp/test -r 1000 -F 1MiB  -B test
-B : 指定导出的数据库
Release version: v6.0.0
Git commit hash: 36a9810441ca0e496cbd22064af274b3be771081
Git branch:      heads/refs/tags/v6.0.0
Build timestamp: 2022-03-31 10:27:29Z
Go version:      go version go1.18 linux/amd64

[2023/06/28 05:56:51.130 -04:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v6.0.0] ["Git Commit Hash"=36a9810441ca0e496cbd22064af274b3be771081] ["Git Branch"=heads/refs/tags/v6.0.0] ["Build timestamp"="2022-03-31 10:27:29"] ["Go Version"="go version go1.18 linux/amd64"]
[2023/06/28 05:56:51.279 -04:00] [INFO] [version.go:362] ["detect server version"] [type=TiDB] [version=6.1.6]
[2023/06/28 05:56:51.449 -04:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[192.168.16.10:2379]"]
[2023/06/28 05:56:51.456 -04:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://192.168.16.10:2379] [old-leader=]
[2023/06/28 05:56:51.456 -04:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7249668463048054636]
[2023/06/28 05:56:51.456 -04:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global]
[2023/06/28 05:56:51.460 -04:00] [INFO] [dump.go:1384] ["generate dumpling gc safePoint id"] [id=dumpling_1687946211460417963]
[2023/06/28 05:56:51.778 -04:00] [INFO] [dump.go:103] ["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\":\"\"},\"azblob\":{\"endpoint\":\"\",\"account-name\":\"\",\"account-key\":\"\",\"access-tier\":\"\"},\"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.16.10\",\"Port\":4000,\"Threads\":2,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/tmp/test\",\"StatusAddr\":\":8281\",\"Snapshot\":\"442484971653824513\",\"Consistency\":\"snapshot\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[\"test\"],\"Where\":\"\",\"FileType\":\"sql\",\"ServerInfo\":{\"ServerType\":3,\"ServerVersion\":\"6.1.6\",\"HasTiKV\":true},\"Rows\":1000,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":1048576,\"StatementSize\":1000000,\"SessionParams\":{\"tidb_snapshot\":\"442484971653824513\"},\"Tables\":null,\"CollationCompatible\":\"loose\"}"]
[2023/06/28 05:56:52.012 -04:00] [INFO] [writer.go:242] ["no data written in table chunk"] [database=test] [table=afs_test] [chunkIdx=0]
[2023/06/28 05:56:52.118 -04:00] [INFO] [writer.go:242] ["no data written in table chunk"] [database=test] [table=t1] [chunkIdx=0]
[2023/06/28 05:56:52.165 -04:00] [INFO] [writer.go:242] ["no data written in table chunk"] [database=test] [table=t2] [chunkIdx=0]
[2023/06/28 05:56:52.232 -04:00] [INFO] [collector.go:237] ["backup success summary"] [total-ranges=10] [ranges-succeed=10] [ranges-failed=0] [total-take=266.067582ms] [total-kv-size=432.4kB] [average-speed=1.625MB/s] [total-rows=10846]
[2023/06/28 05:56:52.233 -04:00] [INFO] [main.go:80] ["dump data successfully, dumpling will exit now"]

可以看到目录当中是各个表的SQL导出文件

[root@tiup tidb-toolkit]# cd /tmp/test/
[root@tiup test]# ls
metadata                         test.afs_test-schema.sql   test.t1-schema.sql
test-schema-create.sql     test.t2.0000000010000.sql
test.afs_test.0000000010000.sql  test.t1.0000000010000.sql  test.t2-schema.sql

导出mysql数据库

./dumpling -uroot -P3306 -hlocalhost -p’Pingcap’ --filetype sql -t 8 -o /tmp/mysql -r 10000 -F 256MiB -B test