TiDB Practical Chapter – Data Export Tool Dumpling

Introduction

Briefly use the data export tool.

Introduction

Features

Applicable scenarios

Deployment

Download

TiDB Community Edition | PingCAP

Install

#Unzip the downloaded installation package
tar -zxvf tidb-community-toolkit-v6.5.0-linux-amd64.tar.gz
# After entering the folder, unzip the dumpling
cd tidb-community-toolkit-v6.5.0-linux-amd64
tar -zxvf dumping-v6.5.0-linux-amd64.tar.gz

vi /etc/profile.d/my.sh
#TOOLKIT_HOME
export TOOLKIT_HOME=/root/tidb-community-toolkit-v6.5.0-linux-amd64
export PATH=$PATH:$TOOLKIT_HOME
source /etc/profile.d/my.sh

Print

[root@master ~]# dumping
Release version: v6.5.0
Git commit hash: 706c3fa3c526cdba5b3e9f066b1a568fb96c56e3
Git branch: heads/refs/tags/v6.5.0
Build timestamp: 2022-12-27 03:43:05Z
Go version: go version go1.19.3 linux/amd64

[2023/04/16 11:33:01.563 + 08:00] [INFO] [versions.go:54] ["Welcome to dumping"] ["Release Version"=v6.5.0] [" Git Commit Hash"=706c3fa3c526cdba5b3e9f066b1a568fb96c56e3] ["Git Branch"=heads/refs/tags/v6.5.0] ["Build timestamp"="2022-12-27 03:43:05"] [ "Go Version"="go version go1.19.3 linux/amd64"]
[2023/04/16 11:33:01.565 + 08:00] [WARN] [version.go:316] ["select tidb_version() failed, will fallback to 'select version();'"] [error="Error 1045: Access denied for user 'root'@'127.0.0.1' (using password: NO)"]

create dumper failed: sql: SELECT version();: Error 1045: Access denied for user 'root'@'127.0.0.1' (using password: NO)

Export overview

Minimal permissions to use

Export SQL file

dumpling -u root -P 4000 -h 127.0.0.1 --filetype sql -t 8 -o /tmp/test -r 200000 -F256MiB
#Indicates that the exported file is sql
--filetype sql
#Indicates the number of concurrent threads exported
-t 8
#Exported file location
-o /tmp/test
# How many lines to create a new file
-r 200000
#Reach 256M and create a new file
-F256MiB

Export CSV file

./dumpling -u root -P 4000 -h 127.0.0.1 -o /tmp/test --filetype csv -r 200000 -F256MiB

For export data filtering

If the type of the exported file is not specified, the default is sql.

Database filtering of exported data

Export actual combat

Prerequisites

#Log in to tidb to view the data
mysql -h127.0.0.1 -P4000 -uroot -ptidb

use test;

mysql> select * from emp;
 + ------+
| id |
 + ------+
| 1 |
 + ------+
1 row in set (0.00 sec)

Export SQL file

dumpling -uroot -P4000 -h127.0.0.1 -ptidb --filetype sql -t 8 -o /tmp/test/sql -r 200000 -F256MiB -B test

Exported file

[root@master ~]# cd /tmp/test/sql
[root@master sql]# ll
total 16
-rw-r--r-- 1 root root 146 Apr 16 12:06 metadata
-rw-r--r-- 1 root root 59 Apr 16 12:06 test.emp.0000000010000.sql
-rw-r--r-- 1 root root 139 Apr 16 12:06 test.emp-schema.sql
-rw-r--r-- 1 root root 95 Apr 16 12:06 test-schema-create.sql

metadata

[root@master sql]# cat metadata
Started dump at: 2023-04-16 12:06:17
SHOW MASTER STATUS:
Log: tidb-binlog
Pos: 440826063176335367
GTID:

Finished dump at: 2023-04-16 12:06:17

test-schema-create.sql

[root@master sql]# cat cat test-schema-create.sql
cat: cat: No such file or directory
/*!40101 SET NAMES binary*/;
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

test.emp-schema.sql 

[root@master sql]# cat test.emp-schema.sql
/*!40101 SET NAMES binary*/;
CREATE TABLE `emp` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

test.emp.0000000010000.sql

[root@master sql]# cat test.emp.0000000010000.sql
/*!40101 SET NAMES binary*/;
INSERT INTO `emp` VALUES
(1);

Export csv file

dumpling -uroot -P4000 -h127.0.0.1 -ptidb --filetype csv -t 8 -o /tmp/test/csv -r 200000 -F256MiB -B test

If you want to export a separate table, then use -T test.emp

[root@master csv]# ll
total 16
-rw-r--r-- 1 root root 146 Apr 16 12:59 metadata
-rw-r--r-- 1 root root 9 Apr 16 12:59 test.emp.0000000010000.csv
-rw-r--r-- 1 root root 139 Apr 16 12:59 test.emp-schema.sql
-rw-r--r-- 1 root root 95 Apr 16 12:59 test-schema-create.sql

test.emp.0000000010000.csv

[root@master csv]# cat test.emp.0000000010000.csv
"id"
1

Consistency of exported data

  • Snapshot #The MVCC mechanism is used. If the export time is 10 o’clock, then even if the data is deleted at 10:10 o’clock, all the data at 10 o’clock will still be exported
  • None #The MVCC mechanism will not be used. If some data is deleted during export, the deleted part of the data will not be exported.
  • Flush #Add a read-only lock to the database.
  • Lock #Which table is exported will lock that table.
  • Auto #If it is tidb, then it is Flush, if it is TiDB, then it is Snapshot.

Performance optimization