# TiDB User Guide

TiDB (/'taɪdiːbi:/, where "Ti" stands for Titanium) is an open-source, cloud-native, distributed SQL database built for high availability, horizontal and vertical scalability, strong consistency, and high performance. It speaks the MySQL 8.0 protocol, so you connect to it with the MySQL clients, drivers, and ORMs you already use — while it transparently shards, replicates, and rebalances your data across a cluster.

This guide shows you how to get a TiDB cluster running, connect to it, operate the built-in command-line servers (`tidb-server`), and use the data-movement tools that ship in this repository: **BR** (backup & restore), **TiDB Lightning** (fast bulk import), and **Dumpling** (logical export). Everything documented here is derived directly from the TiDB source code at commit `5e0c630`.

> [!NOTE]
> This repository (`pingcap/tidb`) contains the **TiDB SQL layer** and its ecosystem tools. A full production cluster also runs **PD** (Placement Driver) and **TiKV** (the distributed key-value storage engine), and optionally **TiFlash** (a columnar engine for analytics). PD, TiKV, and TiFlash are separate projects — TiDB connects to them over gRPC. You do **not** need to build them from this repo; deployment tooling (TiUP, TiDB Operator, TiDB Cloud) provisions them for you.

---

## Table of Contents

1. [What You Get](#1-what-you-get)
2. [Key Features](#2-key-features)
3. [Quick Start](#3-quick-start)
4. [Installation](#4-installation)
5. [Connecting to TiDB](#5-connecting-to-tidb)
6. [Running `tidb-server`](#6-running-tidb-server)
7. [Everyday SQL Usage](#7-everyday-sql-usage)
8. [Backup & Restore with BR](#8-backup--restore-with-br)
9. [Bulk Import with TiDB Lightning](#9-bulk-import-with-tidb-lightning)
10. [Logical Export with Dumpling](#10-logical-export-with-dumpling)
11. [Importing Data with `IMPORT INTO`](#11-importing-data-with-import-into)
12. [Observability & the Status API](#12-observability--the-status-api)
13. [Common Tasks (Recipes)](#13-common-tasks-recipes)
14. [Troubleshooting](#14-troubleshooting)
15. [FAQ](#15-faq)
16. [Command & Flag Reference](#16-command--flag-reference)

---

## 1. What You Get

Building this repository (or downloading a release) produces several binaries. The ones you interact with directly are:

| Binary | Source | Purpose |
|--------|--------|---------|
| `tidb-server` | `cmd/tidb-server` | The stateless SQL layer. Accepts MySQL-protocol connections, parses/plans/executes SQL, and reads/writes data through TiKV (or an embedded store). |
| `br` | `br/cmd/br` | Backup & Restore CLI for a TiDB/TiKV cluster: full/database/table snapshots, raw/txn KV ranges, and point-in-time recovery via log backup. |
| `tidb-lightning` | `lightning/cmd/tidb-lightning` | High-speed bulk importer that loads large SQL/CSV/Parquet datasets into a TiDB cluster. |
| `tidb-lightning-ctl` | `lightning/cmd/tidb-lightning-ctl` | Control/inspection companion for Lightning (checkpoint management, cleanup). |
| `dumpling` | `dumpling/cmd/dumpling` | Logical export tool — dumps MySQL/TiDB data to SQL or CSV files. |

> [!TIP]
> If you only want to **try TiDB SQL on your laptop**, you do not need TiKV or PD at all. `tidb-server` defaults to an embedded storage engine called **UniStore**, so a single process gives you a working MySQL-compatible database. See [Quick Start](#3-quick-start). For anything beyond experimentation, deploy a real cluster (TiUP/Kubernetes/Cloud).

---

## 2. Key Features

These are the capabilities TiDB advertises, each backed by mechanisms in this codebase.

- **Distributed transactions** — TiDB uses a two-phase commit protocol to provide ACID guarantees. Transactions can span many nodes and remain correct across node failures and network partitions. Both **optimistic** and **pessimistic** transaction modes are supported (the pessimistic path is configurable under `[pessimistic-txn]`).
- **Horizontal and vertical scalability** — Compute (`tidb-server`) and storage (TiKV) scale independently. Add `tidb-server` instances behind a load balancer to add SQL throughput; add TiKV nodes to add storage and I/O — all online.
- **High availability** — Data is replicated by the Raft consensus protocol (in TiKV). A write commits only after a majority of replicas acknowledge it, so the cluster survives the loss of a minority of replicas.
- **Hybrid Transactional/Analytical Processing (HTAP)** — Two storage engines are available: **TiKV** (row-based, for OLTP) and **TiFlash** (columnar, for OLAP). TiFlash replicates from TiKV in real time. The optimizer can route a query to whichever engine is faster, controlled by the `tidb_isolation_read_engines` setting and the `[isolation-read]` config section.
- **Cloud-native** — Deployable on public clouds, on-premises, or in Kubernetes via TiDB Operator. A managed offering (TiDB Cloud) is also available.
- **MySQL 8.0 compatibility** — TiDB speaks the MySQL wire protocol and emulates MySQL system variables, error codes, `INFORMATION_SCHEMA`, and `PERFORMANCE_SCHEMA`. Most applications migrate with no code changes.

---

## 3. Quick Start

The fastest way to a working SQL prompt is a single standalone `tidb-server` using the embedded UniStore engine.

=== "Run the binary directly"

    ```bash
    # Start a standalone TiDB on the default port 4000 using the embedded store.
    ./bin/tidb-server
    ```

    ```text
    Welcome to the TiDB.
    Release Version: ...
    ... ["server is running MySQL protocol"] [addr=0.0.0.0:4000]
    ... ["start status server"] [addr=0.0.0.0:10080]
    ```

=== "Build from source first"

    ```bash
    git clone https://github.com/pingcap/tidb.git
    cd tidb
    make server           # builds ./bin/tidb-server
    ./bin/tidb-server
    ```

Connect from another terminal with any MySQL client:

```bash
mysql --host 127.0.0.1 --port 4000 -u root
```

```text
Welcome to the MySQL monitor.
mysql>
```

Run your first statements:

```sql
CREATE DATABASE app;
USE app;
CREATE TABLE users (id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64));
INSERT INTO users (name) VALUES ('Ada'), ('Linus');
SELECT * FROM users;
```

```text
+----+-------+
| id | name  |
+----+-------+
|  1 | Ada   |
|  2 | Linus |
+----+-------+
2 rows in set (0.00 sec)
```

> [!IMPORTANT]
> The standalone UniStore engine is intended for **development and testing only**. It is an in-process store and does not provide the replication, durability, or scalability of a real TiKV-backed cluster. For staging or production, deploy a real cluster.

For a real (but local) multi-node cluster, the recommended path is the **TiUP playground**, which downloads and wires up PD, TiKV, and TiDB for you:

```bash
# Install TiUP, then:
tiup playground
```

```text
... PD endpoints: 127.0.0.1:2379
... TiDB endpoints: 127.0.0.1:4000
```

---

## 4. Installation

### 4.1 Requirements

`tidb-server` itself is a single static Go binary with no runtime dependencies. To **build** from source you need:

| Requirement | Detail |
|-------------|--------|
| Go | `go 1.25.10` (see `go.mod`). The SQL parser submodule (`pkg/parser`) targets `go 1.25`. |
| `make` | Build targets such as `make server`, `make build_for_br_integration_test`. |
| OS | Linux and macOS are fully supported. On Windows, secure-bootstrap mode is unavailable (see note below). |

> [!CAUTION]
> Secure bootstrap (`--initialize-secure`) relies on Unix-socket authentication, which is **not supported on Windows**. `tidb-server` exits with `the option -initialize-secure is not supported on Windows` if you try it there. Use insecure bootstrap on Windows or deploy on Linux.

### 4.2 Deployment options

| Method | Best for | How |
|--------|----------|-----|
| **TiUP playground** | Local testing, demos | `tiup playground` spins up PD + TiKV + TiDB locally. |
| **TiUP cluster** | Self-managed production on bare metal / VMs | Declarative topology file, then `tiup cluster deploy`. |
| **Kubernetes (TiDB Operator)** | Containerized / cloud-native deployments | TiDB Operator automates cluster lifecycle. |
| **TiDB Cloud** | Fully managed (no ops) | Provision a cluster from the web console. |
| **Standalone binary** | SQL experimentation only | `./bin/tidb-server` (UniStore). |

The integration-test harness in this repo documents the components a complete local cluster expects, which is a useful checklist when assembling one by hand:

```text
bin/tidb-server   bin/tikv-server   bin/pd-server
bin/pd-ctl        bin/tiflash       bin/cdc
```

These binaries must be version `≥ 2.1.0` and matched to each other. In practice, let TiUP or the Operator manage versions rather than collecting binaries manually.

---

## 5. Connecting to TiDB

TiDB listens for client connections on the **MySQL protocol**. Defaults from the source:

| Property | Default | Set with |
|----------|---------|----------|
| Host (bind address) | `0.0.0.0` | `--host` / `[host]` |
| SQL port | `4000` | `-P` / `[port]` |
| Unix socket | `/tmp/tidb-{Port}.sock` | `--socket` / `[socket]` |
| Status (HTTP) host | `0.0.0.0` | `--status-host` / `[status.status-host]` |
| Status (HTTP) port | `10080` | `--status` / `[status.status-port]` |

Connect with the MySQL CLI, any MySQL driver, or an ORM:

=== "MySQL CLI"

    ```bash
    mysql --host 127.0.0.1 --port 4000 -u root -p
    ```

=== "Go (go-sql-driver/mysql)"

    ```go
    db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:4000)/app")
    ```

=== "Python (PyMySQL)"

    ```python
    import pymysql
    conn = pymysql.connect(host="127.0.0.1", port=4000, user="root", database="app")
    ```

=== "Unix socket"

    ```bash
    mysql --socket /tmp/tidb-4000.sock -u root
    ```

> [!NOTE]
> A freshly bootstrapped cluster has a `root` user with **no password** by default (insecure bootstrap). Set a password immediately with `SET PASSWORD FOR 'root'@'%' = '...';` or bootstrap in secure mode. See [Security in the Admin Guide](pingcap__tidb__AdminGuide.md).

### Behind a proxy / load balancer

TiDB supports the PROXY protocol so it can recover the real client IP when sitting behind an L4 load balancer:

```bash
tidb-server --proxy-protocol-networks="10.0.0.0/8,192.168.0.0/16"
```

Set `--proxy-protocol-networks="*"` to accept the PROXY header from any source, or leave it empty to disable PROXY-protocol support entirely.

---

## 6. Running `tidb-server`

`tidb-server` is configured by a TOML config file (`--config`), command-line flags, or both — flags override the matching config keys. The most common startup flags:

```bash
tidb-server \
  --store=tikv \
  --path="pd-host-1:2379,pd-host-2:2379,pd-host-3:2379" \
  --host=0.0.0.0 \
  --port=4000 \
  --status=10080 \
  --config=/etc/tidb/tidb.toml \
  --log-file=/var/log/tidb/tidb.log
```

### 6.1 Storage backend selection (`--store`)

| `--store` value | Meaning |
|-----------------|---------|
| `unistore` | Embedded in-process store (default). Standalone, for testing. |
| `tikv` | Connect to a real TiKV cluster. `--path` must list the PD endpoints. |
| `mocktikv` | A mock TiKV used mainly by tests. |

For `--store=tikv`, `--path` is the **comma-separated list of PD endpoints**, not a filesystem path. For `--store=unistore`, `--path` is the local storage directory (default `/tmp/tidb`).

### 6.2 Config validation

You can check a config file without starting the server:

```bash
tidb-server --config=/etc/tidb/tidb.toml --config-check
```

```text
config file /etc/tidb/tidb.toml contained valid configuration
```

Add `--config-strict` to make TiDB **refuse to start** if the config file contains unknown keys, rather than warning and continuing.

### 6.3 Version and help

```bash
tidb-server -V        # print version information and exit
tidb-server --help    # show all flags and exit
```

A full flag table is in the [Command & Flag Reference](#16-command--flag-reference). Deep configuration (every TOML key, security, performance tuning) lives in the **Admin Guide**.

> [!WARNING]
> Do **not** change `--lease` (schema lease, default `45s`) unless you fully understand schema-version synchronization. The source comments call this "very dangerous to change only if you know what you do." A wrong value can break online DDL safety across the cluster.

### 6.4 Graceful shutdown

`tidb-server` installs signal handlers and shuts down cleanly on `SIGTERM`/`SIGINT`: it stops accepting connections, drains in-flight work, and syncs logs before exiting. To drain client connections before shutdown, set `graceful-wait-before-shutdown` (seconds) in the config so the status endpoint reports unhealthy and the load balancer stops routing new connections first.

---

## 7. Everyday SQL Usage

Because TiDB is MySQL-8.0-compatible, day-to-day SQL is what you already know. A few TiDB-specific conveniences are worth calling out.

### 7.1 Standard DDL/DML

```sql
CREATE TABLE orders (
  id    BIGINT PRIMARY KEY AUTO_INCREMENT,
  user  BIGINT NOT NULL,
  total DECIMAL(10,2),
  KEY idx_user (user)
);
INSERT INTO orders (user, total) VALUES (1, 19.99);
UPDATE orders SET total = total * 1.1 WHERE user = 1;
SELECT user, SUM(total) FROM orders GROUP BY user;
```

> [!NOTE]
> TiDB executes DDL **online** and **asynchronously** through a job queue and a cluster-wide schema-version protocol — a long `ADD INDEX` does not lock the table against reads and writes. You can observe progress with `ADMIN SHOW DDL JOBS;`.

### 7.2 `AUTO_RANDOM` for write-hotspot avoidance

A monotonically increasing `AUTO_INCREMENT` primary key concentrates writes on one region ("hotspot"). TiDB offers `AUTO_RANDOM` to scatter inserts:

```sql
CREATE TABLE events (
  id  BIGINT PRIMARY KEY AUTO_RANDOM,
  ts  DATETIME,
  payload JSON
);
```

### 7.3 TiFlash replicas for analytics

If your cluster has TiFlash, mark a table for columnar replication and analytical queries automatically benefit:

```sql
ALTER TABLE orders SET TIFLASH REPLICA 1;
```

```text
Query OK, 0 rows affected
-- The replica becomes queryable once at least one TiFlash replica is available.
```

> [!TIP]
> When you `SET TIFLASH REPLICA`, TiDB notes that the change "will take effect after at least one TiFlash replica is set for the table." Use `SELECT ... ` with the optimizer routing to TiFlash, or hint engine isolation via `tidb_isolation_read_engines`.

### 7.4 Inspecting the cluster from SQL

TiDB exposes cluster internals as queryable tables:

```sql
SHOW DATABASES;
ADMIN SHOW DDL;                 -- current DDL owner & state
ADMIN SHOW DDL JOBS;           -- running/recent schema-change jobs
SELECT * FROM information_schema.cluster_info;   -- all nodes in the cluster
SELECT * FROM information_schema.tidb_indexes;   -- index metadata
```

### 7.5 `ADMIN CHECK` for integrity

```sql
ADMIN CHECK TABLE orders;
```

```text
Query OK, 0 rows affected
```

If this returns an inconsistency error (`admin:8223 data inconsistency in table ...`), stop writing to the table and consult the [Troubleshooting](#14-troubleshooting) section — it signals a record/index mismatch that needs investigation.

---

## 8. Backup & Restore with BR

`br` is the cluster-level backup and restore CLI. It backs up directly from TiKV to external storage (local filesystem, Amazon S3, Google Cloud Storage, Azure Blob) and restores it back. The command tree, taken from the source:

```text
br
├── backup   full | db | table | raw | txn
├── restore  full | db | table | raw | txn | point
├── abort    restore (full | db | table | point)
├── log      start | stop | pause | resume | status | truncate | metadata | advancer
├── operator <subcommand>   (for tidb-operator; hidden)
└── debug    <subcommand>   (checksum/backupmeta/decode/encode/...; hidden)
```

### 8.1 Full backup

```bash
br backup full \
  --pd "pd-host:2379" \
  --storage "s3://my-bucket/backup-2026-06-05/" \
  --log-file backup.log
```

```text
Full Backup <---------------------------------------------> 100%
Checksum <------------------------------------------------> 100%
["Full Backup success summary"] [total-take=4m12s] [total-kv-size=...] 
```

### 8.2 Database / table backup

```bash
br backup db    --pd "pd-host:2379" --db app    --storage "s3://my-bucket/app-db/"
br backup table --pd "pd-host:2379" --db app --table orders --storage "local:///mnt/backup/orders/"
```

### 8.3 Restore

```bash
br restore full  --pd "pd-host:2379" --storage "s3://my-bucket/backup-2026-06-05/"
br restore db    --pd "pd-host:2379" --db app --storage "s3://my-bucket/app-db/"
br restore table --pd "pd-host:2379" --db app --table orders --storage "local:///mnt/backup/orders/"
```

> [!IMPORTANT]
> Restore a full backup into an **empty or non-conflicting** cluster. By default, full restore filters out internal schemas (`mysql.*`, `INFORMATION_SCHEMA`, etc.) but keeps user/privilege and SQL-binding tables, so grants and bindings come back with your data.

### 8.4 Point-in-time recovery (log backup)

PITR combines a periodic full snapshot with a continuous **log backup**. Start the log backup task once; it streams changes to storage continuously:

```bash
# Start continuous log backup
br log start --task-name pitr --pd "pd-host:2379" --storage "s3://my-bucket/log-backup/"

# Check status
br log status --task-name pitr --pd "pd-host:2379"

# Pause / resume / stop
br log pause  --task-name pitr --pd "pd-host:2379"
br log resume --task-name pitr --pd "pd-host:2379"
br log stop   --task-name pitr --pd "pd-host:2379"
```

To recover to a specific timestamp, restore a base snapshot then replay the log up to the target point:

```bash
br restore point \
  --pd "pd-host:2379" \
  --storage "s3://my-bucket/log-backup/" \
  --full-backup-storage "s3://my-bucket/backup-2026-06-05/" \
  --restored-ts "2026-06-05 12:30:00+08:00"
```

### 8.5 Filtering what gets backed up/restored

All BR commands accept table-filter expressions (`-f` / `--filter`) using the TiDB ecosystem filter syntax — wildcards, blocklists, regex, and file includes:

```bash
br backup full --pd "pd-host:2379" --storage "local:///mnt/bk" \
  -f 'app.*' -f '!app.audit_*'
```

The filter grammar (shared by BR, Lightning, and Dumpling) is:

| Pattern | Meaning |
|---------|---------|
| `db.table` | Exact match |
| `*` | Zero or more characters; `?` one character |
| `[a-z]` / `[!a-z]` | Character class / negated class |
| `!pattern` | Exclude (blocklist) — must follow an include like `*.*` |
| `/regex/./regex/` | Go-dialect regular expression per name part |
| `@file.txt` | Read rules from a file (one per line; `#` comments) |

> [!CAUTION]
> A filter list defaults to **reject** when nothing matches. To build a blocklist you must start with an explicit `*.*` include, e.g. `-f '*.*' -f '!*.secrets'`. A lone `-f '!*.secrets'` matches nothing and backs up nothing.

### 8.6 Encryption & verification

- Snapshot backups can be encrypted with a cipher key; restore must supply the same key.
- `br debug checksum` and `br debug backupmeta validate` verify backup integrity (these subcommands are hidden but available). A checksum mismatch raises `BR:Backup:ErrBackupChecksumMismatch`.

---

## 9. Bulk Import with TiDB Lightning

`tidb-lightning` loads large datasets (the output of Dumpling, CSV, or Parquet) into a TiDB cluster far faster than running `INSERT`s. It is driven by a **TOML config file**, not by many flags:

```bash
tidb-lightning --config /etc/lightning/tidb-lightning.toml
```

```text
Verbose debug logs will be written to /var/log/lightning/lightning.log

... ["the whole procedure completed"] [takeTime=18m02s]
tidb lightning exit successfully
```

### 9.1 Backends

Lightning has two import backends, selected by `[tikv-importer] backend` in the config:

| Backend | Behavior | Use when |
|---------|----------|----------|
| `local` | Encodes source data into sorted SST files and ingests them directly into TiKV. Fastest; the target table should be empty. | Initial bulk load of a large dataset. |
| `tidb` | Replays the data as ordinary SQL `INSERT`s through a `tidb-server`. Slower but works on a live, non-empty cluster. | Incremental loads into a table already serving traffic. |

> [!NOTE]
> With the `local` backend, Lightning raises `GOGC` to 500 by default to cut GC overhead during the heavy encode-and-write phase (memory usage rises in exchange for speed). The `tidb` backend keeps the standard GC behavior.

### 9.2 Minimal config

```toml
[lightning]
level = "info"
file  = "/var/log/lightning/lightning.log"

[tikv-importer]
backend = "local"
sorted-kv-dir = "/mnt/ssd/sorted-kv"

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

[tidb]
host = "tidb-host"
port = 4000
user = "root"
```

### 9.3 Server mode

Run Lightning as a long-lived service that accepts import tasks over HTTP instead of importing once and exiting:

```bash
tidb-lightning --config lightning.toml --server-mode --status-addr ":8289"
```

When `--status-addr` is set, Lightning exposes live progress and a metrics endpoint.

### 9.4 Control & cleanup with `tidb-lightning-ctl`

`tidb-lightning-ctl` manages checkpoints and post-import cleanup — for example resetting a failed import's checkpoint so you can retry from scratch, or clearing leftover metadata. Use it when an import is interrupted and Lightning refuses to restart cleanly.

---

## 10. Logical Export with Dumpling

`dumpling` exports MySQL or TiDB data to SQL or CSV files. It is flag-driven (`pflag`), so options are passed on the command line:

```bash
dumpling \
  --host 127.0.0.1 --port 4000 --user root \
  --database app \
  --output /mnt/data/dump \
  --threads 8
```

```text
dump data successfully, dumpling will exit now
```

Common options:

| Flag | Purpose |
|------|---------|
| `--host`, `--port`, `--user`, `--password` | Source connection |
| `--database` | Restrict to specific schema(s) |
| `-f`, `--filter` | Table-filter expression (same grammar as BR §8.5) |
| `--output` (`-o`) | Output directory |
| `--threads` | Export concurrency |
| `--filetype` | `sql` (default) or `csv` |
| `--version` (`-V`) | Print version and exit |

> [!TIP]
> Dumpling output pairs naturally with Lightning: dump from a source database with `dumpling`, then bulk-load into TiDB with `tidb-lightning --backend local`. This is the standard migration path into TiDB.

---

## 11. Importing Data with `IMPORT INTO`

Besides the standalone Lightning binary, TiDB has a built-in SQL statement, `IMPORT INTO`, that runs a distributed import across the whole cluster using TiDB's Distributed eXecution Framework (DXF). You issue it like any other SQL statement:

```sql
IMPORT INTO orders
FROM 's3://my-bucket/orders/*.csv'
WITH skip_rows = 1, thread = 8;
```

```text
+----------+--------+...
| Job_ID   | Status |
+----------+--------+...
```

Because the work is distributed across TiDB nodes, large imports parallelize automatically. Job lifecycle and history are tracked in system tables and via the cluster's task endpoints.

> [!NOTE]
> On a global-sort import, if too many duplicate keys are detected, TiDB writes the conflicting rows to a separate `conflicted-rows/<task-id>/...` location that is **deliberately preserved** after the task's normal cleanup, so you can inspect what failed. If conflicts exceed an in-memory bound, the final checksum verification is skipped — re-run a verifying check afterward if you need a guarantee.

---

## 12. Observability & the Status API

Every `tidb-server` runs an HTTP **status server** (default port `10080`) alongside the SQL port. It exposes health, metrics, schema introspection, and operational endpoints. A selection from the source (`pkg/server/http_status.go`):

| Endpoint | Purpose |
|----------|---------|
| `GET /status` | Liveness/health and version info |
| `GET /metrics` | Prometheus metrics |
| `GET /schema` | All databases; `/schema/{db}`, `/schema/{db}/{table}` drill down |
| `GET /info` | This server's info; `/info/all` for the whole cluster |
| `GET /ddl/history` | Recent DDL jobs |
| `GET /config` | The effective server configuration |
| `GET /tables/{db}/{table}/regions` | Region distribution for a table |
| `GET /regions/hot` | Current hot regions |
| `GET /debug/pprof/...` | Go pprof CPU/heap/profile endpoints |
| `GET /debug/zip` | Bundle of diagnostics for support tickets |

Quick health check:

```bash
curl http://tidb-host:10080/status
```

```text
{"connections":3,"version":"8.0.11-TiDB-...","git_hash":"5e0c630"}
```

> [!WARNING]
> The status port exposes profiling and diagnostic endpoints (`/debug/pprof/*`, `/debug/zip`). Do **not** expose port `10080` to untrusted networks. Bind it to an internal interface or firewall it. Configuration details are in the Admin Guide.

Metrics can also be **pushed** to a Prometheus Pushgateway:

```bash
tidb-server --metrics-addr "pushgateway:9091" --metrics-interval 15
```

Set `--metrics-interval 0` (or an empty `--metrics-addr`) to disable the push client.

---

## 13. Common Tasks (Recipes)

### Migrate a MySQL database into TiDB

```bash
# 1. Export from MySQL/TiDB source
dumpling --host src-mysql --port 3306 --user root -p --database app --output /data/dump --filetype sql

# 2. Bulk-load into TiDB
tidb-lightning --config lightning.toml   # points data-source-dir=/data/dump, backend="local"
```

### Take a one-off snapshot before a risky migration

```bash
br backup full --pd "pd-host:2379" --storage "local:///mnt/snap" --log-file snap.log
# ... run your migration ...
# If it goes wrong, restore into a fresh cluster:
br restore full --pd "pd-host:2379" --storage "local:///mnt/snap"
```

### Continuous protection + point-in-time recovery

```bash
br log start --task-name pitr --pd "pd-host:2379" --storage "s3://bk/log/"
br backup full --pd "pd-host:2379" --storage "s3://bk/base-$(date +%F)/"
# Later, recover to a moment just before an accidental DELETE:
br restore point --pd "pd-host:2379" \
  --full-backup-storage "s3://bk/base-2026-06-05/" \
  --storage "s3://bk/log/" \
  --restored-ts "2026-06-05 09:14:59+00:00"
```

### Run an ad-hoc analytical query on TiFlash

```sql
ALTER TABLE sales SET TIFLASH REPLICA 1;          -- once
-- wait for replication, then:
SELECT region, SUM(amount) FROM sales GROUP BY region;  -- optimizer routes to TiFlash
```

### Find and inspect a slow query

```sql
SELECT query, query_time, mem_max
FROM information_schema.slow_query
ORDER BY query_time DESC
LIMIT 10;
```

---

## 14. Troubleshooting

TiDB reports errors with **MySQL-compatible error codes**, so existing MySQL knowledge applies. Format below: the message you see → cause → solution.

### Error: `ERROR 1045 (28000): Access denied for user 'root'@'...' (using password: YES)`

- **Cause:** Wrong password, or the user/host combination is not granted.
- **Solution:** Verify the account exists with `SELECT user, host FROM mysql.user;`. On a freshly bootstrapped insecure cluster, `root` has an empty password — connect without `-p`, then set one.
- **Prevention:** Bootstrap in secure mode and manage credentials explicitly.

### Error: `ERROR 1049 (42000): Unknown database 'xxx'`

- **Cause:** The schema does not exist on this cluster.
- **Solution:** `SHOW DATABASES;` to confirm; `CREATE DATABASE xxx;` if needed. After a restore, confirm the restore targeted the right tables/filter.

### Error: `ERROR 1062 (23000): Duplicate entry '...' for key '...'`

- **Cause:** A unique/primary key collision.
- **Solution:** Resolve the conflicting row, or use `INSERT ... ON DUPLICATE KEY UPDATE` / `REPLACE`. During a Lightning/`IMPORT INTO` load, inspect the preserved `conflicted-rows/...` files.

### Error: `ERROR 1064 (42000): You have an error in your SQL syntax`

- **Cause:** A statement TiDB's parser rejected. TiDB targets MySQL 8.0 syntax; a few MySQL features are unsupported or `noop`.
- **Solution:** Check the statement against MySQL 8.0 syntax. If it's a function with "only noop implementation in tidb" (`1235`), enable `tidb_enable_noop_functions` only if you understand it does nothing.

### Error: `ERROR 1105 (HY000): Unknown system variable '...'` / `variable:1193`

- **Cause:** Referencing a MySQL variable TiDB doesn't implement, or a typo.
- **Solution:** `SHOW VARIABLES LIKE '%name%';`. TiDB-specific variables are prefixed `tidb_`.

### Error: `admin:8223 data inconsistency in table: ..., index: ...`

- **Cause:** `ADMIN CHECK` found a record that disagrees with its index — a serious data-integrity signal.
- **Solution:** Stop writes to the affected table, capture a `/debug/zip` bundle, and engage support. Do not "fix" by re-creating the index blindly.
- **Prevention:** Keep the cluster on a supported version; run `ADMIN CHECK TABLE` periodically on critical tables.

### Error: `BR:Backup:ErrBackupChecksumMismatch` / `backup checksum mismatch`

- **Cause:** Backup data was altered or corrupted in storage.
- **Solution:** Re-run the backup; validate with `br debug backupmeta validate`. Don't restore from a backup that fails checksum.

### Error: `BR:Common:ErrFailedToConnect` / `failed to make gRPC channels`

- **Cause:** BR cannot reach PD/TiKV (wrong `--pd`, firewall, or TLS mismatch).
- **Solution:** Verify `--pd` endpoints are reachable; if the cluster uses TLS, pass `--ca`, `--cert`, `--key`.

### Lightning import fails partway and won't restart

- **Cause:** A checkpoint from the failed run blocks a clean restart.
- **Solution:** Use `tidb-lightning-ctl` to inspect/reset the checkpoint, fix the underlying issue, then re-run.

### `tidb-server` exits at startup: `the option -initialize-secure is not supported on Windows`

- **Cause:** Secure bootstrap requires Unix-socket auth, unavailable on Windows.
- **Solution:** Use insecure bootstrap on Windows, or deploy on Linux.

### `tidb-server` exits: `value of [tmp-storage-quota] (...) exceeds the capacity of the [...] directory`

- **Cause:** The configured temp-storage quota for spill-to-disk is larger than the actual free space on the temp directory.
- **Solution:** Lower `tmp-storage-quota`, point `tmp-storage-path` at a larger volume, or free space.

---

## 15. FAQ

**Q: Is TiDB really a drop-in MySQL replacement?**
For most applications, yes — TiDB implements the MySQL 8.0 wire protocol, error codes, system variables, and metadata schemas. Some MySQL features are unsupported or are `noop` placeholders; validate edge cases (stored procedures, certain functions) against your workload.

**Q: Do I need TiKV and PD to try TiDB?**
No. A single `tidb-server` with the default `unistore` engine gives you a working SQL database for testing. For anything real, deploy a cluster with PD + TiKV.

**Q: What ports does TiDB use?**
SQL `4000`, status/HTTP `10080`. A full cluster also uses PD `2379`/`2380` and TiKV `20160`. All are configurable.

**Q: How do I back up just one table?**
`br backup table --db <db> --table <tbl> --storage <url>`. Restore with `br restore table ...`.

**Q: What's the difference between Lightning's `local` and `tidb` backends?**
`local` writes sorted SSTs straight into TiKV (fastest, target should be empty). `tidb` replays inserts through a live `tidb-server` (works on a populated cluster, slower). See §9.1.

**Q: How do I import a CSV without writing INSERTs?**
Either `tidb-lightning` (standalone, config-driven) or the in-cluster `IMPORT INTO ... FROM '...csv'` SQL statement (distributed). See §9 and §11.

**Q: How is TiDB different for analytics?**
Mark tables with `ALTER TABLE ... SET TIFLASH REPLICA n` to enable the columnar TiFlash engine; the optimizer then routes suitable analytical queries to it (HTAP).

**Q: How do I scale write throughput?**
Add more `tidb-server` instances behind a load balancer for SQL/compute, and add TiKV nodes for storage/I/O. Both scale independently and online.

**Q: How do I avoid write hotspots on an auto-increment key?**
Use `AUTO_RANDOM` instead of `AUTO_INCREMENT` for the primary key to scatter inserts across regions.

**Q: Can I run a backup while the cluster is serving traffic?**
Yes — `br backup` is designed for online clusters. For continuous protection, run `br log start` (log backup) in addition to periodic full backups.

**Q: Where are slow queries recorded?**
In the `information_schema.slow_query` table and the slow-query log file (`--log-slow-query`). Tune the threshold with `tidb_slow_log_threshold`.

**Q: How do I check cluster health quickly?**
`curl http://<tidb-host>:10080/status`, query `information_schema.cluster_info`, and check `/metrics` in Prometheus.

---

## 16. Command & Flag Reference

### 16.1 `tidb-server` flags

| Flag | Default | Description |
|------|---------|-------------|
| `-V` | — | Print version information and exit |
| `--config` | — | Config file path (TOML) |
| `--config-check` | false | Check config validity and exit |
| `--config-strict` | false | Reject unknown config keys |
| `--store` | `unistore` | Storage engine: `unistore`, `tikv`, `mocktikv` |
| `--path` | `/tmp/tidb` | Storage path, or PD endpoints when `--store=tikv` |
| `--host` | `0.0.0.0` | SQL server bind host |
| `--advertise-address` | — | Address advertised to other nodes |
| `-P` | `4000` | SQL server port |
| `--socket` | `/tmp/tidb-{Port}.sock` | Unix socket path |
| `--cors` | — | Allowed CORS origin for the status server |
| `--run-ddl` | true | Whether this instance runs the DDL worker |
| `--lease` | `45s` | Schema lease duration (**do not change casually**) |
| `--token-limit` | `1000` | Max concurrently executing sessions |
| `--temp-dir` | `/tmp/tidb` | Temporary directory |
| `--L` | `info` | Log level: `debug`/`info`/`warn`/`error`/`fatal` |
| `--log-file` | — | Log file path |
| `--log-slow-query` | — | Slow-query log file path |
| `--log-general` | — | General (all-statements) log file path |
| `--report-status` | true | Enable the status HTTP server |
| `--status-host` | `0.0.0.0` | Status server host |
| `--status` | `10080` | Status server port |
| `--metrics-addr` | — | Prometheus Pushgateway address (empty disables push) |
| `--metrics-interval` | `15` | Push interval in seconds (0 disables) |
| `--proxy-protocol-networks` | — | Allowed PROXY-protocol source networks (`*` = any) |
| `--plugin-dir` | `/data/deploy/plugin` | Plugin directory |
| `--plugin-load` | — | Comma-separated plugins to load |
| `--affinity-cpus` | — | Pin to CPUs (comma-separated indices) |
| `--initialize-secure` | false | Bootstrap in secure mode (not on Windows) |
| `--initialize-insecure` | true | Bootstrap in insecure mode |
| `--initialize-sql-file` | — | SQL file run once on first bootstrap |
| `--keyspace-name` | — | Keyspace name (next-gen multi-tenant deployments) |
| `--cluster-ca` / `--cluster-cert` / `--cluster-key` | — | TLS for intra-cluster (TiKV/PD) connections |
| `--sql-ca` / `--sql-cert` / `--sql-key` | — | TLS for client SQL connections |

> Flags override the corresponding TOML config keys. The complete TOML reference (every section and key, with defaults) is in the **Admin Guide**.

### 16.2 `br` command tree

| Command | Subcommands | Purpose |
|---------|-------------|---------|
| `br backup` | `full`, `db`, `table`, `raw`, `txn` | Snapshot backup to external storage |
| `br restore` | `full`, `db`, `table`, `raw`, `txn`, `point` | Restore from backup (`point` = PITR) |
| `br abort` | `restore full/db/table/point` | Abort an in-progress restore |
| `br log` | `start`, `stop`, `pause`, `resume`, `status`, `truncate`, `metadata`, `advancer` | Continuous log backup control |
| `br operator` | (operator utilities) | For tidb-operator; hidden |
| `br debug` | `checksum`, `backupmeta validate`, `decode`, `encode`, … | Inspect/verify backup data; hidden |

Common BR flags: `--pd` (PD endpoints), `--storage` (`s3://`, `gcs://`, `azure://`, `local://`), `-f`/`--filter` (table filter), `--ca`/`--cert`/`--key` (TLS), `-L`/`--log-level`, `--log-file`, `--redact-info-log`.

### 16.3 Tool one-liners

| Tool | Invocation |
|------|-----------|
| Standalone SQL | `tidb-server` |
| Connect | `mysql -h 127.0.0.1 -P 4000 -u root` |
| Full backup | `br backup full --pd <pd> --storage <url>` |
| Full restore | `br restore full --pd <pd> --storage <url>` |
| Bulk import | `tidb-lightning --config <file>` |
| Logical export | `dumpling --host <h> --port 4000 -u root -o <dir>` |
| SQL import | `IMPORT INTO <tbl> FROM '<url>'` |

---

_Generated June 2026 from pingcap/tidb at 5e0c630. AI-generated documentation. The library is checked by sampling (three repositories so far); this guide was not individually reviewed. Verify against the project before relying on it. Unofficial; not affiliated with or reviewed by the project._