Point-In-TIme data restore on Amazon RDS

Question

I have hosted my MySql database on Amazon RDS, and It has last automated snapshot. (e.g. yesterday midnight). Now situation is, I have deleted some of records from a very important table accidentally, and would like to recovery it. I have no additional backup since yesterday midnight. (as mention earlier). Now How should I recover data without taking any downtime? How do I use point-in-time data recovery? If someone need more information let me know and sorry for my poor explanation.

Stiltner

What ever you will point in time you will recover will be from yesterday midnight. Those records you deleted today will not be recovered. – Lala

@error2007s, if I understand what you are saying, then your assertion isn't correct. Any point in time during your backup retention window can be selected, even if after the last snapshot. Deleted rows will be recovered if they existed at the point-in-time selected. That's the idea behind restoring to a specific point in time. – Woolly

I was talking about the record that were created after yesterday midnight – Lala

Right, but everything that happened up until the selected point in time will appear on the new instance, even if the selected time is after the most recent snapshot. Only things after the selected point in time -- not the snapshot time -- will be absent. You can recover the database to any specific point in time within the number of days in your configured backup retention window, up to about 5 minutes before the time you request the restore. The most recent ~5 minutes aren't available for selection because of the way RDS manages log rotation. – Woolly

That I know I was under impression that user deleted the records created after yesterday midnight. – Lala

Answer 1

Point in time recovery allows you to create an additional RDS instance, based on the data as it existed on your instance at any specific point in time you choose between the oldest available automated backup and approximately 5 minutes ago. All you have to do is select what date and time you need.

There is no disruption or change to your running instance.

The process creates a new instance, which you connect to, collect the data you need in order to get your production system back the way it should be, and then destroy the new instance. Or, depending on what went wrong for you, you could also switch your application to this new instance and destroy the old one, though it seems unlikely that this is what you would want to do. But you can do either.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html

I have no additional backup since yesterday midnight.

Point in time recovery doesn't care. RDS preserves the snapshots as well as a complete, timestamped log of everything that changed between the snapshots. These logs are archived in an area that is not accessible to you... but they are there. RDS will automatically load the most recent snapshot that is earlier than the point-in-time you select, and then use the logs to roll the new instance's data forward in time to the target time. When the process is complete, your new instance will contain exactly the data that was present on the old instance at the point in time that you selected.

---

lets assume I have one table contain 10 records at midnight. which is exists in backup/snapshot.

Stop thinking about what is in the snapshot. It doesn't matter.

Next day morning I have added 5 another records at 10:00pm. After half an hour (at 10:30) I have deleted 2 records from them antecedently.

Perform a point in time recovery, selecting any point between 10:00 and 10:30 -- a point in time when the records were in your database.

Point in time recovery creates a new instance, which contains all your data exactly as it existed at the time you selected. Connect to that new instance manually, fetch the missing rows, insert them back into your live/main/production database, and then the newly-created instance can be destroyed because it is no longer needed.

Do not assume this process is complicated or difficult.

Woolly