59

I am using AWS RDS for MySQL. When it comes to backup, I understand that Amazon provides two types of backup - automated backup and database (DB) snapshot. The difference is explained here. However, I am still confused: should I stick to automated backup only or both automated and manual (db snapshots)?

What do you think guys? What's the setup of your own? I heard from others that automated backup is not reliable due to some unrecoverable database when the DB instance is crashed so the DB snapshots are the way to rescue you. If I am to do daily DB snapshots as similar settings to automated backup, I am gonna pay much bunch of bucks.

Hope anyone could enlighten me or advise me the right set up.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
James Wise
  • 1,715
  • 4
  • 16
  • 21

3 Answers3

47

From personal experience, I recommend doing both. I have the automated backup set to 8 days, and then I also have a script that will take a snapshot once per day and delete snapshots older than 7 days. The reason is because from what I understand, there are certain situations where you could not restore from the automated backup. For example, if you accidentally deleted your RDS instance and did not take a final snapshot, you would not be able to access the automated backups that were done. But it is also good to have the automated backups turned on because that will provide you the point-in-time restore.

Hope this helps.

EDIT

To answer your comment, I use a certain naming convention when my script creates the snapshots. Something like:

autosnap-instancename-2012-03-23

When it goes to do the cleanup, it retrieves all the snapshots, looks for that naming convention, parses the date, and deletes any older than a certain date.

I think you could also look at the snapshot creation date, but this is just how I ended up doing it.

BigJoe714
  • 6,732
  • 7
  • 46
  • 50
  • That make sense. Thanks @BigJoe714. Question, how did you manage to clean up your old DB snapshots within 7 days retention period? – James Wise Mar 23 '12 at 01:33
  • I added info about how I clean up the snapshots to the answer – BigJoe714 Mar 23 '12 at 14:18
  • 3
    Would you be willing to provide the script you're using to do the manual snapshot daily? I like the automated backups and the retention period, but I would like to be able to archive backups of the database every X days or X months... e.g., so that I have a snapshot of the database at various points in time. I'm running on an Ubuntu server. Thanks – Travitron Aug 07 '14 at 00:43
41

Just from personal experience, yesterday I accidentally deleted a table and had to restore from an RDS snapshot. The latest snapshot was only 10 minutes old, which was perfect. However, Amazon RDS took about 3 hours to get the snapshot online, during which time, the affected section of our site was completely offline.

So if you need to make a very quick recovery, do NOT depend on RDS backups.

Keep in mind, you can't download your snapshot so that you could view a database dump. Your only option is to wait for it to load in to a new database instance. So if you're only looking to restore a single table, RDS backups can make it a very painful process.

No blame to Amazon on this- they are awesome. But just something to keep in mind when planning, because it was a learning experience for us.

Henry Weber
  • 621
  • 6
  • 10
1

There are some situations where an automated backup does not recover the specific table you want to recover even though it has a point-in-time recovery feature. I am suggesting you enable the Backtracking feature for this kind of recovery and You can use "AWS-Backup" service to manage backups of Amazon RDS DB instances. Backups managed by AWS Backup are considered manual DB snapshots. Also, you will be required to keep automated backup enabled for creating read-replica for DB-instance in order to improve read performance. The retention period for automated backup should be between 1 and 35 so you can keep it a minimum of 1 day.

Vikk_King
  • 81
  • 1
  • 7