How can the Database Specialist accomplish this?

By:
On:
In: DBS-C01
Tagged:
With: 0 Comments
The Development team recently executed a database script containing several data definition language (DDL) and data manipulation language (DML) statements on an Amazon Aurora MySQL DB cluster.The release accidentally deleted thousands of rows from an important table and broke some application functionality.This was discovered 4 hours after the release.Upon investigation, a Database Specialist tracked the issue to a DELETE command in the script with an incorrectWHERE clause filtering the wrong set of rows.The Aurora DB cluster has Backtrack enabled with an 8-hour backtrack window.The Database Administrator also took a manual snapshot of the DB cluster before the release started.The database needs to be returned to the correct state as quickly as possible to resume full application functionality.Data loss must be minimal.

How can the Database Specialist accomplish this?

Quickly rewind the DB cluster to a point in time before the release using Backtrack.

Perform a point-in-time recovery (PITR) of the DB cluster to a time before the release and copy the deleted rows from the restored database to the original database.

Restore the DB cluster using the manual backup snapshot created before the release and change the application configuration settings to point to the new DB cluster.

Create a clone of the DB cluster with Backtrack enabled. Rewind the cloned cluster to a point in time before the release. Copy deleted rows from the clone to the original database.

Explanations:

Backtrack allows for rewinding to a specific point in time, but it only applies to Aurora MySQL. It can’t undo DDL/DML changes like DELETE statements.

Point-in-time recovery (PITR) restores the database to a specific moment before the release. The deleted rows can then be copied from the restored database.

Restoring from a snapshot reverts the entire database to the state at the time of the snapshot, causing a potential loss of any data added after the snapshot.

While cloning and using Backtrack could theoretically restore a database state, Backtrack can’t undo DML changes (like DELETE), making it ineffective here.

Leave a Reply

Your email address will not be published. Required fields are marked *

five × five =