Importance of Delay Replica in Data Recovery: Recover Deleted Records

What is a Delay Replica and how does it help?

MySQL Replication is useful, easy to set up, and used for very different purposes. For example:

• split reads and writes
• run data mining or reporting processes on them
• disaster recovery

To learn more, check out How Does MySQL Replication Work?

It’s important to mention that a replication server is not a backup by itself. A mistake on the source, for example, a DROP DATABASE or an UPDATE without a WHERE clause will be replicated nearly instantly to all replica servers. Simply having a replica is not going to be helpful here. How can we avoid that kind of mistake? By having a replica server that intentionally lags behind.

We can never avoid human error in database infrastructure operations. But rollback to the last known good condition from delayed Source/Replica is the best thing recommended during the entire database infrastructure corruption scenarios.

Delayed replication can be used for several purposes:

• To test how the system behaves when there is a lag. For example, in an application, a lag might be caused by a heavy load on the replica. However, it can be difficult to generate this load level. Delayed replication can simulate the lag without having to simulate the load. It can also be used to debug conditions related to a lagging replica.
• To inspect what the database looked like in the past, without having to reload a backup. For example, by configuring a replica with a delay of one week, if you then need to see what the database looked like before the last few days’ worth of development, the delayed replica can be inspected.
• In our case, as we have six hours replication delay, we can recover the non-problematic state of the table by going back to six hours delayed replica in case there was the wrong DML on the source.

Testing the Delay Replica which can help us recover the deleted record

Let’s understand the situation first: Someone deleted the data on a Percona XtraDB Cluster 8 (PXC) server accidentally. As per architecture, we always configure a delayed replica. We have stopped the replication on the delayed replica and now we will restore the deleted records.

From the initial investigation, we came to know from the application operation team that the below query was executed.

Almost 20k records were deleted. Let us do our pre-reqs and initial investigation based on the requirements we have.

In this article, PXC-8 is our source and pxc-backup is our delayed backup Replica. Click through to learn about our product Percona XtraDB Cluster and our backup solution Percona XtraBackup.

1. All records for Schema_g2.per_sch were deleted from PXC-8 by mistake.
2. We have stopped the delayed replica pxc-backup to retrieve these records. All deleted records are present here right now. Below are the number of records we have until replication was stopped.

At this point, we have already stopped the backup replica. 

3. After deletion, below are the new records inserted on the PXC-8 source for this table.

4. id column is auto_inc so new rows inserted have next values.

5. Records on the backup server:

6. A few records between 178631 and 178852 are missing on the backup node and the replica was stopped in between.
7. Exact position before the drop was executed on source: (Application team pinged this, we verified it as an UPDATE query before the DELETE).

From binlog we see below:

 

Steps to restore

1. Start replication on the backup server until the DROP statement.

 Verify binlog position and that it is caught up till above and verify new record count on the backup server.

 Verify replication is stopped again.

-Verify last id is < 178852

 Use –where clause IF we notice any duplicate duplicates rows same as PXC-8 on the backup server.

2. Take a backup from the backup server with the below options to avoid dropping the newly added rows.

Verify no drops/created are present in the backup file.

3. Restore backup on source PXC-8:

Verify it completes ok and records are added back on source:

Verify records < 178852 and above also exist on the source.

4. Start replication on the backup server normally.

With loading the record on the source, it will get replicated to delay replica and other replicas and the data will be in sync again.

Conclusion

It is always recommended to have a Delay Replica in your architecture to avoid and quickly resolve such data losses.