Skip to content

Latest commit

 

History

History
175 lines (126 loc) · 5.98 KB

sql-statement-flashback-cluster.md

File metadata and controls

175 lines (126 loc) · 5.98 KB
title summary aliases
FLASHBACK CLUSTER
Learn the usage of FLASHBACK CLUSTER in TiDB databases.
/tidb/v6.5/sql-statement-flashback-to-timestamp

FLASHBACK CLUSTER

TiDB v6.4.0 introduces the FLASHBACK CLUSTER TO TIMESTAMP syntax. You can use it to restore a cluster to a specific point in time. When specifying the timestamp, you can either set a datetime value or use a time function. The format of datetime is like '2016-10-08 16:45:26.999', with millisecond as the minimum time unit. But in most cases, specifying the timestamp with second as the time unit is sufficient, for example, '2016-10-08 16:45:26'.

Starting from v6.5.6, TiDB introduces the FLASHBACK CLUSTER TO TSO syntax. This syntax enables you to use TSO to specify a more precise recovery point in time, thereby enhancing flexibility in data recovery.

Warning:

The FLASHBACK CLUSTER TO [TIMESTAMP|TSO] syntax is not applicable to TiDB Serverless clusters. Do not execute this statement on TiDB Serverless clusters to avoid unexpected results.

Warning:

When specifying a recovery point in time, make sure to check the validity of your target timestamp or TSO and avoid specifying a future time that exceeds the maximum TSO currently allocated by PD (see Current TSO on the Grafana PD panel). Otherwise, concurrent processing linear consistency and transaction isolation levels might be violated, leading to serious data correctness issues.

Note:

The working principle of FLASHBACK CLUSTER TO [TIMESTAMP|TSO] is to write the old data of a specific point in time with the latest timestamp, and will not delete the current data. So before using this feature, you need to ensure that there is enough storage space for the old data and the current data.

Syntax

FLASHBACK CLUSTER TO TIMESTAMP '2022-09-21 16:02:50';
FLASHBACK CLUSTER TO TSO 445494839813079041;

Synopsis

FlashbackToTimestampStmt
         ::= 'FLASHBACK' 'CLUSTER' 'TO' 'TIMESTAMP' stringLit
           | 'FLASHBACK' 'CLUSTER' 'TO' 'TSO' LengthNum

Notes

  • The time specified in the FLASHBACK statement must be within the Garbage Collection (GC) lifetime. The system variable tidb_gc_life_time (default: 10m0s) defines the retention time of earlier versions of rows. The current safePoint of where garbage collection has been performed up to can be obtained with the following query:

    SELECT * FROM mysql.tidb WHERE variable_name = 'tikv_gc_safe_point';

  • Only a user with the SUPER privilege can execute the FLASHBACK CLUSTER SQL statement.

  • From the time specified in the FLASHBACK statement to the time when the FLASHBACK is executed, there cannot be a DDL statement that changes the related table structure. If such a DDL exists, TiDB will reject it.

  • Before executing FLASHBACK CLUSTER, TiDB disconnects all related connections and prohibits read and write operations on these tables until the FLASHBACK statement is completed.

  • The FLASHBACK CLUSTER statement cannot be canceled after being executed. TiDB will keep retrying until it succeeds.

Example

The following example shows how to flashback a cluster to a specific timestamp to restore newly inserted data:

mysql> CREATE TABLE t(a INT);
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT * FROM t;
Empty set (0.01 sec)

mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2022-09-28 17:24:16 |
+---------------------+
1 row in set (0.02 sec)

mysql> INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM t;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> FLASHBACK CLUSTER TO TIMESTAMP '2022-09-28 17:24:16';
Query OK, 0 rows affected (0.20 sec)

mysql> SELECT * FROM t;
Empty set (0.00 sec)

The following example shows how to flashback a cluster to a specific TSO to precisely restore mistakenly deleted data:

mysql> INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM t;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)


mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tidb_current_ts;  -- Get the current TSO
+--------------------+
| @@tidb_current_ts  |
+--------------------+
| 446113975683252225 |
+--------------------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)


mysql> DELETE FROM t;
Query OK, 1 rows affected (0.00 sec)


mysql> FLASHBACK CLUSTER TO TSO 446113975683252225;
Query OK, 0 rows affected (0.20 sec)

mysql> SELECT * FROM t;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

If there is a DDL statement that changes the table structure from the time specified in the FLASHBACK statement to the time when the FLASHBACK is executed, the FLASHBACK statement fails:

mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2022-10-09 16:40:51 |
+---------------------+
1 row in set (0.01 sec)

mysql> CREATE TABLE t(a int);
Query OK, 0 rows affected (0.12 sec)

mysql> FLASHBACK CLUSTER TO TIMESTAMP '2022-10-09 16:40:51';
ERROR 1105 (HY000): Detected schema change due to another DDL job during [2022-10-09 16:40:51 +0800 CST, now), can't do flashback

The FLASHBACK execution progress can be viewed via the Raft admin > Peer in Flashback State metrics and the log. The following log is an example:

Through the log, you can obtain the execution progress of FLASHBACK. The following is an example:

[2022/10/09 17:25:59.316 +08:00] [INFO] [cluster.go:463] ["flashback cluster stats"] ["complete regions"=9] ["total regions"=10] []

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.