Skip to content

Latest commit

 

History

History
113 lines (78 loc) · 3.4 KB

sql-statement-show-backups.md

File metadata and controls

113 lines (78 loc) · 3.4 KB
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.

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