Mysql – download RDS snapshot

We also run several RDS instances, in addition to MySQL on some machines that we manage ourselves. I can't comment specifically, as I'm not an Amazon engineer, but several things I've learned that might explain what you're seeing:

• Although Amazon does not share the backend details 100%, we strongly suspect that they are using their EBS system to back RDS databases.

• This article helps explain EBS limitations and snapshot functionality http://blog.rightscale.com/2008/08/20/amazon-ebs-explained/ Again, while it's not explicit, it would make sense for Amazon to be using this infrastructure to provide RDS services.

• Typically, a MySQL backup, in contrast to a snapshot, involves using a tool like mysqldump to create a file of SQL statements that will then reproduce the database. The database does not need to be frozen to do this. With an EBS backend, the best practice is to freeze the database (pause all transactions) while you are snapshotting to avoid data corruption.

• The spikes you're seeing at the ends of the backup window. If replication is paused by Amazon during the snapshot of your replica, the replica would then need to "catch up" on transactions when the snapshot was complete. This would cause a latency spike.

• Replication across a multi-AZ deployment is inherently slower then a single AZ deployment. The price you pay for better redundancy.

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.