What would be your reaction when your junior comes and says that i have dropped the wrong table or accidentally dropped the entire DB in prod instead of doing it in a test environment !!

This has happened to us, and we have successfully done the recovery for many of our clients. However, the major question comes from the client how long will it take to recover and also recovery point .

In the world of databases, the ability to recover from accidental data loss or corruption is crucial. TiDB, a distributed SQL database, offers a powerful feature called Flashback that allows you to restore data to a specific point in time. This feature is particularly useful for scenarios such as accidental deletions, updates, or data corruption.

Flashback

Flashback in TiDB is a feature that enables you to restore tables, databases, or even the entire cluster to a previous state without the need for a full backup and restore.

Understanding Garbage Collection LifeTime (GCLT) in TiDB

The key to Flashback's functionality is the Garbage Collection LifeTime (GCLT) mechanism. GCLT determines how long the historical data versions are retained in the storage. The system variable tidb_gc_life_time (default: 10m0s) controls the retention time of these earlier versions of rows​.

How Flashback Works: Recovering Tables, Databases, and Clusters

To check your current value of GC window use the following:

Query:

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

Output:

 
mysql> SELECT * FROM mysql.tidb WHERE variable_name = 'tikv_gc_safe_point';
+--------------------+-----------------------------+--------------------------------------------------------------+
| VARIABLE_NAME      | VARIABLE_VALUE              | COMMENT                                                      |
+--------------------+-----------------------------+--------------------------------------------------------------+
| tikv_gc_safe_point | 20240405-12:02:56.278 +0000 | All versions after safe point can be accessed. (DO NOT EDIT) |
+--------------------+-----------------------------+--------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-04-05 14:04:31 |
+---------------------+
1 row in set (0.01 sec)
	

The above output shows that you can recover after 12:02:56 to the current time 14:04:31 near to 10 mins (approx).

If the table drop or deletion occurred before the GC window, Flashback won't be able to recover it. You'll receive an error message like:

 
Can't find dropped / truncated table 'tbl name' in GC safe point 2024-04-05 12:02:56 +0000 UTC
	

 
ERROR 1105 (HY000): Can't find dropped database 'test' in GC safe point 2024-04-05 12:02:56 +0000 UTC
	

When you perform a Flashback operation, you specify the target point in time(the exact time you want to restore the data to) for the restoration.

Flashback Syntax and Use Cases

• Flashback Table: To Restore a table dropped by the DROP or TRUNCATE operation within the GC lifetime.

Drop table

 
FLASHBACK TABLE table_name;
	

Truncate table:

 
FLASHBACK TABLE table_name to other_table_name;
	

Since a TRUNCATE TABLE removes the data but preserves the table structure within the database, the table ID cannot be duplicated in the cluster. Therefore, when recovering a truncated table using Flashback, it will be restored with a different name.

• Flashback Database: Restore a database and its data deleted by the DROP statement within the GC lifetime.

 
FLASHBACK DATABASE DBName [TO newDBName];
	

• Flashback Cluster: Restore the entire cluster to a specific point in time with in GC life time or a TSO value within the GC life time

 
FLASHBACK CLUSTER TO TIMESTAMP '2024-04-05 14:02:50'; FLASHBACK CLUSTER TO TSO 445494839813079041;
	

Lets see how the truncate is recovered ie., FLASHBACK TABLE t TO t1;

• TiDB initiates the Flashback process by scanning through the recent history of Data Definition Language (DDL) operations. It specifically looks for the occurrence of TRUNCATE TABLE operation on the table ‘t’. If TiDB is unable to find such an operation within the historical window of GC lifetime, it returns an error, indicating that the table cannot be restored using Flashback.
• Once a relevant DDL operation is identified, TiDB then verifies the timestamp of the operation against the tidb_gc_safe_point, Assuming the DDL operation is within the GC safe window, TiDB uses the timestamp of this operation as a reference point (snapshot) to access historical data. It retrieves the table metadata as it existed at that moment in time
• Next, TiDB proceeds to remove any GC tasks associated with table t from the mysql.gc_delete_range system table. This step ensures that the data for table t is no longer marked for deletion by the GC process.
• Finally, TiDB creates a new table using the retrieved metadata, but with a crucial modification: the table's name is changed to t1, while the table ID remains unchanged. This means that the new table t1 is essentially a restored version of the previously dropped table t, complete with the same table ID and historical data up to the point of the DDL operation.

You can also manually adjust the GC lifetime as needed to match your desired recovery window. However, keep in mind that this will require additional disk space to store the snapshots and may have a slight impact on database performance.

 
SET GLOBAL tidb_gc_life_time = '24h';
	

Limitations and Considerations for Flashback

• The time specified in the Flashback statement must be within the Garbage Collection lifetime.
• Only users with the SUPER privilege can execute Flashback SQL statements.
• Flashback does not support rolling back DDL statements that modify PD-related information such as placement-rule changes and others
• TiDB disconnects all related connections and prohibits read and write operations on these tables until the FLASHBACK CLUSTER statement is completed.

Use Cases of Flashback

• Accidental Data Deletion: Quickly restore lost data due to accidental DROP or TRUNCATE operations.
• Data Corruption Recovery: Roll back to a state before the data corruption occurred.
• Point-in-Time Recovery: Restore the database to a specific point in time for audit or analysis purposes

Flashback in TiDB provides a flexible and efficient way to recover data, offering a safety net for database administrators and users. By understanding and utilizing the Garbage Collection Life Time mechanism, you can leverage this powerful feature to ensure data integrity and minimize downtime in case of unexpected data loss or corruption. We can also increase the GC window, but consider the storage and performance implication comes along with that.

Don’t leave your data vulnerable! Contact Mydbops today for comprehensive TiDB Consulting and Remote DBA Services, including Flashback implementation, performance optimization, and 24/7 support.

{{cta}}