When managing large-scale databases, efficient and reliable data import is essential, especially during initial setup or migration from legacy systems. Traditional import tools for MySQL, such as LOAD DATA INFILE or mysqldump, are insufficient for TiDB’s distributed architecture.

TiDB Lightning is a high-performance data import tool designed specifically for TiDB clusters. It accelerates large-scale data migration while ensuring consistency, integrity, and minimal downtime. In this guide, you’ll learn about TiDB Lightning architecture, import modes, limitations, and how it compares to the SQL-based IMPORT INTO method.

For assistance in planning or executing your TiDB import strategy, Mafiree’s consulting team provides architecture planning, optimization, and hands-on support.

Why Large-Scale Data Import in TiDB Requires Different Tools Than MySQL

In MySQL, large data imports are mostly linear and executed on a single-node database. Tools like mysqldump, LOAD DATA INFILE, or custom scripts are sufficient for moderate datasets. However, TiDB is a distributed SQL database, which introduces complexities that require a different approach:

• Data Distribution: Data is spread across multiple TiKV nodes.
• Consistency: Writes must be consistent across replicas.
• Performance Bottlenecks: Network and storage throughput can limit SQL-based imports.
• Cluster Load: Large imports via SQL can overload the cluster and impact production applications.

To address these challenges, TiDB provides specialized tools:

• TiDB Lightning – for high-speed bulk import.
• IMPORT INTO – SQL-based import for moderate datasets.
• Dumpling – for data export.

TiDB Lightning is designed to parallelize ingestion, optimize KV encoding, balance node load, and ensure data consistency—unlike MySQL’s mostly sequential import process. Choosing the wrong import method can cause cluster instability, slow performance, or failed imports, so careful planning is crucial. MaFiree’s team can help assess your data migration strategy and select the best approach.

TiDB Lightning Import Modes

TiDB Lightning supports two import modes, determined by the backend:

1. Physical Import Mode (backend = local)

• Encodes data into key-value pairs.
• Writes directly into TiKV via RocksDB.
• Best for initial full imports into empty tables.

Pros:

• Extremely fast (100–500 GiB/hour)
• Parallel ingestion

Cons:

• High resource usage
• Limited cluster availability

2. Logical Import Mode (backend = tidb)

• Converts data into SQL statements and executes via TiDB.
• Best for production clusters or tables with existing data.

Pros:

• ACID compliant
• Minimal disruption

Cons:

• Slower (10–50 GiB/hour)

Performance Comparison

TiDB Lightning Architecture

TiDB Lightning processes data in multiple stages:

1. Reads source files.
2. Converts into KV pairs (physical mode) or SQL (logical mode).
3. Distributes data across TiKV nodes.
4. Performs ingestion into RocksDB.

Complete Import Workflow: Dumpling Export to Lightning Import

Export Data using Dumpling:

tiup dumpling -u root -P 4000 -h host -o /data/export

Prepare Data:

• Validate files
• Organize schema and table structure

Configure TiDB Lightning:

Create tidb-lightning.toml:

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


[tikv-importer]
backend = "local"
sorted-kv-dir = "/data/tidb-tmp/"


[mydumper]
data-source-dir = "/data/export"
filter = ['*.*', '!mysql.*', '!sys.*', '!INFORMATION_SCHEMA.*']


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

Run TiDB Lightning:

tiup tidb-lightning -config lightning.toml > lightning.out

If successful, output shows “tidb lightning exit successfully”.

For additional support during large-scale imports or cluster setup, Mafiree’s TiDB Administration & Maintenance team can assist in configuration, monitoring, and troubleshooting.

IMPORT INTO: Replaces Lightning with a Single SQL Statement

IMPORT INTO is a simpler SQL-based import option:

IMPORT INTO table_name FROM 's3://bucket/data.csv' WITH format = 'csv';

Benefits:

• No external tools required
• Fully integrated with TiDB SQL layer
• Supports cloud storage directly
• Easier automation

Limitations:

• Slower than Lightning physical mode
• Not ideal for TB-scale imports
• Dependent on SQL execution performance

IMPORT INTO vs TiDB Lightning