Point-In-TIme data restore on Amazon RDS
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.
Answer
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.