Skip to content

Latest commit

 

History

History
117 lines (81 loc) · 3.56 KB

sql-statement-show-backups.md

File metadata and controls

117 lines (81 loc) · 3.56 KB
Raw
title summary aliases
SHOW [BACKUPS|RESTORES] | TiDB SQL Statement Reference
An overview of the usage of SHOW [BACKUPS|RESTORES] for the TiDB database.
/docs/dev/sql-statements/sql-statement-show-backups/

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.

Note:

This feature is not available on TiDB Cloud Serverless clusters.

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

Synopsis

ShowBRIEStmt ::=
    "SHOW" ("BACKUPS" | "RESTORES") ShowLikeOrWhere?

ShowLikeOrWhere ::=
    "LIKE" SimpleExpr
|   "WHERE" Expression

Examples

In one connection, execute the following statement:

{{< copyable "sql" >}}

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

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

{{< copyable "sql" >}}

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

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

Column Description
Destination The destination URL (with all parameters stripped to avoid leaking secret keys)
State State of the task
Progress Estimated progress in the current state as a percentage
Queue_time When the task was queued
Execution_time When the task was started; the value is 0000-00-00 00:00:00 for queueing tasks
Finish_time The timestamp when the task finished; the value is 0000-00-00 00:00:00 for queueing and running tasks
Connection Connection ID running this task
Message Message with details

The possible states are:

State Description
Backup Making a backup
Wait Waiting for execution
Checksum Running a checksum operation

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

{{< copyable "sql" >}}

KILL TIDB QUERY 4;
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.

{{< copyable "sql" >}}

SHOW BACKUPS LIKE 's3://%';

Use the WHERE clause to filter by columns.

{{< copyable "sql" >}}

SHOW BACKUPS WHERE `Progress` < 25.0;

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also