It happens to all of us. New developers and experienced ones alike. The need to restore from your database backups.
I found myself in this position recently, and was disheartened to find the records I’d hoped to retrieve existed only in between daily snapshots of the database. The data was created after the second most recent backup, and was deleted before the most recent backup was taken.
It turns out that if you’re hosted on Amazon Web Services’ RDS database service, using MySQL, and with InnoDB database – there’s a solution. It’s called their post in time restore. It allows you to restore to any time within your backup retention period (the earliest available automated backup).
It does this using the MySQL binary log (aka the transaction log).
Using Point in Time Restore in the AWS Console
- Log into your AWS RDS Console.
- From the Database tab, select the radio button next to the RDS instance you’d like to restore from. From the “Actions” dropdown, choose the “Restore to point in time” option.
- The next screen will allow you to choose the time that you’d like to restore from. This is accurate to the minute.
- Give a name to the new RDS instance, and adjust any other instance settings for the new instance.
When you click on the “Restore to point in time” button at the bottom of the screen, AWS will create a new RDS instance with the data from the specific time you requested.
Using Point in Time Restore with the AWS CLI or API
You can also run a point in time restore using the AWS CLI or the RDS API. See the full instructions about the point in time restore feature in Amazon’s documentation.
What if I’m not on using AWS RDS?
If you’re using MySQL, but not on AWS RDS, you can also restore using the transaction log. See Point in Time Recovery Using Binary Log in the official MySQL documentation.