BACKUP

This statement is used to perform a distributed backup of the TiDB cluster.

The BACKUP statement uses the same engine as the BR tool does, except that the backup process is driven by TiDB itself rather than a separate BR tool. All benefits and warnings of BR also apply to this statement.

Executing BACKUP requires either the BACKUP_ADMIN or SUPER privilege. Additionally, both the TiDB node executing the backup and all TiKV nodes in the cluster must have read or write permission to the destination. Local storage (storage paths starting with local://) is not permitted when Security Enhanced Mode is enabled.

The BACKUP statement is blocked until the entire backup task is finished, failed, or canceled. A long-lasting connection should be prepared for executing BACKUP. The task can be canceled using the KILL TIDB QUERY statement.

Only one BACKUP and RESTORE task can be executed at a time. If a BACKUP or RESTORE statement is already being executed on the same TiDB server, the new BACKUP execution will wait until all previous tasks are finished.

BACKUP can only be used with "tikv" storage engine. Using BACKUP with the "unistore" engine will fail.

Synopsis

BackupStmt ::=
    "BACKUP" BRIETables "TO" stringLit BackupOption*

BRIETables ::=
    "DATABASE" ( '*' | DBName (',' DBName)* )
|   "TABLE" TableNameList

BackupOption ::=
    "RATE_LIMIT" '='? LengthNum "MB" '/' "SECOND"
|   "CONCURRENCY" '='? LengthNum
|   "CHECKSUM" '='? Boolean
|   "SEND_CREDENTIALS_TO_TIKV" '='? Boolean
|   "LAST_BACKUP" '='? BackupTSO
|   "SNAPSHOT" '='? ( BackupTSO | LengthNum TimestampUnit "AGO" )

Boolean ::=
    NUM | "TRUE" | "FALSE"

BackupTSO ::=
    LengthNum | stringLit

Examples

Back up databases

BACKUP DATABASE `test` TO 'local:///mnt/backup/2020/04/';

+------------------------------+-----------+-----------------+---------------------+---------------------+
| Destination                  | Size      | BackupTS        | Queue Time          | Execution Time      |
+------------------------------+-----------+-----------------+---------------------+---------------------+
| local:///mnt/backup/2020/04/ | 248665063 | 416099531454472 | 2020-04-12 23:09:48 | 2020-04-12 23:09:48 |
+------------------------------+-----------+-----------------+---------------------+---------------------+
1 row in set (58.453 sec)

In the example above, the test database is backed up into the local filesystem. The data is saved as SST files in the /mnt/backup/2020/04/ directories distributed among all TiDB and TiKV nodes.

The first row of the result above is described as follows:

Back up tables

BACKUP TABLE `test`.`sbtest01` TO 'local:///mnt/backup/sbtest01/';

BACKUP TABLE sbtest02, sbtest03, sbtest04 TO 'local:///mnt/backup/sbtest/';

Back up the entire cluster

BACKUP DATABASE * TO 'local:///mnt/backup/full/';

Note that the system tables (mysql.*, INFORMATION_SCHEMA.*, PERFORMANCE_SCHEMA.*, …) will not be included into the backup.

External storages

BR supports backing up data to S3 or GCS:

BACKUP DATABASE `test` TO 's3://example-bucket-2020/backup-05/?access-key={YOUR_ACCESS_KEY}&secret-access-key={YOUR_SECRET_KEY}';

The URL syntax is further explained in external storage URI.

When running on cloud environment where credentials should not be distributed, set the SEND_CREDENTIALS_TO_TIKV option to FALSE:

BACKUP DATABASE `test` TO 's3://example-bucket-2020/backup-05/'
    SEND_CREDENTIALS_TO_TIKV = FALSE;

Performance fine-tuning

Use RATE_LIMIT to limit the average upload speed per TiKV node to reduce network bandwidth.

Before the backup is completed, BACKUP would perform a checksum against the data on the cluster to verify correctness. If you are confident that this verification is unnecessary, you can disable the check by setting the CHECKSUM parameter to FALSE.

To specify the number of concurrent tasks that BR can execute for backing up tables and indices, use the CONCURRENCY parameter. This parameter controls the thread pool size within BR, optimizing the performance and efficiency of backup operations.

One task represents one table range or one index range, according to the backup schemas. For one table with one index, two tasks are used to back up this table. The default value of CONCURRENCY is 4. If you need to back up lots of tables or indices, increase its value.

BACKUP DATABASE `test` TO 's3://example-bucket-2020/backup-06/'
    RATE_LIMIT = 120 MB/SECOND
    CONCURRENCY = 8
    CHECKSUM = FALSE;

Snapshot

Specify a timestamp, TSO or relative time to backup historical data.

-- relative time
BACKUP DATABASE `test` TO 'local:///mnt/backup/hist01'
    SNAPSHOT = 36 HOUR AGO;

-- timestamp (in current time zone)
BACKUP DATABASE `test` TO 'local:///mnt/backup/hist02'
    SNAPSHOT = '2020-04-01 12:00:00';

-- timestamp oracle
BACKUP DATABASE `test` TO 'local:///mnt/backup/hist03'
    SNAPSHOT = 415685305958400;

The supported units for relative time are:

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK

Note that, following SQL standard, the units are always singular.

Incremental backup

Supply the LAST_BACKUP option to only backup the changes between the last backup to the current snapshot.

-- timestamp (in current time zone)
BACKUP DATABASE `test` TO 'local:///mnt/backup/hist02'
    LAST_BACKUP = '2020-04-01 12:00:00';

-- timestamp oracle
BACKUP DATABASE `test` TO 'local:///mnt/backup/hist03'
    LAST_BACKUP = 415685305958400;

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also

• RESTORE
• SHOW BACKUPS