Import and Export

TiDB supports several methods for importing and exporting data depending on dataset size and use case.

Export
Import

Export with Dumpling

Dumpling is the recommended tool for exporting large datasets. It exports data as SQL dump files or CSV.

# Export entire cluster as SQL
tiup dumpling \
  -u root -P 4000 -h 127.0.0.1 \
  --filetype sql \
  --output ./dump

# Export specific database as CSV
tiup dumpling \
  -u root -P 4000 -h 127.0.0.1 \
  --filter "mydb.*" \
  --filetype csv \
  --csv-separator "," \
  --output ./dump-csv

# Export with compression
tiup dumpling \
  -u root -P 4000 -h 127.0.0.1 \
  --compress gzip \
  --output ./dump-compressed

Dumpling options

Flag	Description	Default
`--filetype`	Output format: `sql` or `csv`	`sql`
`--filter`	Table filter pattern (e.g. `"db.*"`)	all tables
`--threads`	Parallel export threads	`4`
`--rows`	Split tables into chunks of N rows	`200000`
`--compress`	Compression: `gzip`, `zstd`	none
`--output`	Output directory or S3/GCS path	`./export`

Export with SELECT INTO OUTFILE

For small exports from within a SQL session:

SELECT * FROM orders
WHERE created_at >= '2024-01-01'
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

SELECT INTO OUTFILE writes to the TiDB server’s local filesystem. For large datasets, use Dumpling instead.

Import with TiDB Lightning

TiDB Lightning is the fastest way to import large datasets into TiDB.

tidb-lightning.toml

[lightning]
level = "info"
file = "tidb-lightning.log"

[tikv-importer]
# "local" = physical import mode (fastest, for empty tables)
# "tidb"  = logical import mode (works with live tables)
backend = "local"
sorted-kv-dir = "/tmp/sorted-kv-dir"

[mydumper]
data-source-dir = "./dump"

[tidb]
host = "127.0.0.1"
port = 4000
user = "root"
password = ""
status-port = 10080
pd-addr = "127.0.0.1:2379"

tiup tidb-lightning --config tidb-lightning.toml

Import with LOAD DATA

For smaller CSV files, use LOAD DATA directly in SQL:

LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE mydb.orders
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(order_id, customer_id, amount, created_at);

The LOCAL keyword reads from the client machine. Without LOCAL, it reads from the TiDB server’s filesystem.

Import from S3 or GCS

TiDB supports importing directly from cloud storage:

IMPORT INTO orders
FROM 's3://my-bucket/data/orders.csv'
WITH skip_rows=1;

Or with TiDB Lightning pointing to cloud storage:

[mydumper]
data-source-dir = "s3://my-bucket/dump"

Get Started

Core Concepts

Deployment

Data Management

Operations

Import and Export

Export with Dumpling

Dumpling options

Export with SELECT INTO OUTFILE

Import with TiDB Lightning

Import with LOAD DATA

Import from S3 or GCS

​Export with Dumpling

​Dumpling options

​Export with SELECT INTO OUTFILE

​Import with TiDB Lightning

​Import with LOAD DATA

​Import from S3 or GCS

Export with Dumpling

Dumpling options

Export with SELECT INTO OUTFILE

Import with TiDB Lightning

Import with LOAD DATA

Import from S3 or GCS