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
- BRIETables
- BackupOption
- Boolean
- BackupTSO
BackupStmt ::=
"BACKUP" BRIETables "TO" stringLit BackupOption*
BRIETables ::=
"DATABASE" ( '*' | DBName (',' DBName)* )
| "TABLE" TableNameList
BackupOption ::=
"CHECKSUM" '='? Boolean
| "CHECKSUM_CONCURRENCY" '='? LengthNum
| "COMPRESSION_LEVEL" '='? LengthNum
| "COMPRESSION_TYPE" '='? stringLit
| "CONCURRENCY" '='? LengthNum
| "IGNORE_STATS" '='? Boolean
| "LAST_BACKUP" '='? BackupTSO
| "RATE_LIMIT" '='? LengthNum "MB" '/' "SECOND"
| "SEND_CREDENTIALS_TO_TIKV" '='? Boolean
| "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:
Column | Description |
---|---|
Destination | The destination URL |
Size | The total size of the backup archive, in bytes |
BackupTS | The TSO of the snapshot when the backup is created (useful for incremental backup) |
Queue Time | The timestamp (in current time zone) when the BACKUP task is queued. |
Execution Time | The timestamp (in current time zone) when the BACKUP task starts to run. |
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 URI Formats of External Storage Services.
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 by default. The default concurrency for checksum tasks on a single table is 4, which you can adjust using the CHECKSUM_CONCURRENCY
parameter. If you are confident that the data 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.
Statistics are not backed up by default. To back up statistics, you need to set the IGNORE_STATS
parameter to FALSE
.
By default, the SST files generated by backup use the zstd
compression algorithm. If needed, you can specify a different compression algorithm using the COMPRESSION_TYPE
parameter. Supported algorithms include lz4
, zstd
, and snappy
. You can also adjust the compression level with the COMPRESSION_LEVEL
parameter; the higher the level number, the higher the compression ratio, but the higher the CPU consumption.
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.