Skip to content

Latest commit

 

History

History
218 lines (147 loc) · 8.29 KB

sql-statement-backup.md

File metadata and controls

218 lines (147 loc) · 8.29 KB
Raw
title summary aliases
BACKUP | TiDB SQL Statement Reference
An overview of the usage of BACKUP for the TiDB database.
/docs/dev/sql-statements/sql-statement-backup/

BACKUP

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

Warning:

  • This feature is experimental. It is not recommended that you use it in the production environment. This feature might be changed or removed without prior notice. If you find a bug, you can report an issue on GitHub.
  • This feature is not available on TiDB Cloud Serverless clusters.

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 ::=
    "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

{{< copyable "sql" >}}

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

{{< copyable "sql" >}}

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

{{< copyable "sql" >}}

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

Back up the entire cluster

{{< copyable "sql" >}}

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:

{{< copyable "sql" >}}

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:

{{< copyable "sql" >}}

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.

{{< copyable "sql" >}}

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.

{{< copyable "sql" >}}

-- 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.

{{< copyable "sql" >}}

-- 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