SHOW [BACKUPS|RESTORES]

These statements show a list of all queued, running and recently finished BACKUP and RESTORE tasks that were executed on a TiDB instance.

Both statements require SUPER privilege to run.

Use SHOW BACKUPS to query BACKUP tasks and use SHOW RESTORES to query RESTORE tasks.

SHOW [BACKUPS|RESTORES] - 图1

Note

This feature is not available on TiDB Serverless clusters.

Backups and restores that were started with the br commandline tool are not shown.

Synopsis

ShowBRIEStmt

SHOW [BACKUPS|RESTORES] - 图2

ShowLikeOrWhere

SHOW [BACKUPS|RESTORES] - 图3

  1. ShowBRIEStmt ::=
  2.     "SHOW" ("BACKUPS" | "RESTORES") ShowLikeOrWhere?
  4. ShowLikeOrWhere ::=
  5.     "LIKE" SimpleExpr
  6. |   "WHERE" Expression

Examples

In one connection, execute the following statement:

  1. BACKUP DATABASE `test` TO 's3://example-bucket/backup-01';

Before the backup completes, run SHOW BACKUPS in a new connection:

  1. SHOW BACKUPS;
  1. +--------------------------------+---------+----------+---------------------+---------------------+-------------+------------+---------+
  2. | Destination                    | State   | Progress | Queue_time          | Execution_time      | Finish_time | Connection | Message |
  3. +--------------------------------+---------+----------+---------------------+---------------------+-------------+------------+---------+
  4. | s3://example-bucket/backup-01/ | Backup  | 98.38    | 2020-04-12 23:09:03 | 2020-04-12 23:09:25 |        NULL |          4 | NULL    |
  5. +--------------------------------+---------+----------+---------------------+---------------------+-------------+------------+---------+
  6. 1 row in set (0.00 sec)

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

ColumnDescription
DestinationThe destination URL (with all parameters stripped to avoid leaking secret keys)
StateState of the task
ProgressEstimated progress in the current state as a percentage
Queue_timeWhen the task was queued
Execution_timeWhen the task was started; the value is 0000-00-00 00:00:00 for queueing tasks
Finish_timeThe timestamp when the task finished; the value is 0000-00-00 00:00:00 for queueing and running tasks
ConnectionConnection ID running this task
MessageMessage with details

The possible states are:

StateDescription
BackupMaking a backup
WaitWaiting for execution
ChecksumRunning a checksum operation

The connection ID can be used to cancel a backup/restore task via the KILL TIDB QUERY statement.

  1. KILL TIDB QUERY 4;
  1. Query OK, 0 rows affected (0.00 sec)

Filtering

Use the LIKE clause to filter out tasks by matching the destination URL against a wildcard expression.

  1. SHOW BACKUPS LIKE 's3://%';

Use the WHERE clause to filter by columns.

  1. SHOW BACKUPS WHERE `Progress` < 25.0;

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also