Daily automatic backup of mysql database Amazon RDS snapshot to an via SSH

Question

I would like to copy/clone the the database inside the newest RDS snapshot on Amazon RDS to a specific server outside of Amazon. I'm looking for a way to backup the mySQL database of it. I would like also to use a daily cron job that triggers a mysqldump from the newest RDS snapshot directly and copy this mySQL dump to a location on a different server via SSH or FTP.

At the moment the steps are way too time consuming. You need to replicate the snapshot to a new RDS instance, access the database via SSH, dump the database on the local PC and then upload it on another server.

Is there perhaps a different approach or alternative? Thanks already for any good hint, advice and help!

Answer 1

RDS snapshots are an AWS specific thing.

What you are describing is traditional database snapshots, which is pretty much your only option since you cannot download an RDS snapshot. There are offerings to do this, but what you are doing is pretty much the most common way. The only other option is to use an AWS instance to do this on a cron.

If you really want the data from an RDS snapshot, it will add time, but you could clone the RDS snapshot into a temporary instance and back that up to a file.

Answer 2

There is no way to do a mysqldump directly from an RDS snapshot. You have to restore the snapshot to a new server instance and then take a mysqldump of that new server instance.

It sounds like you need to forget about the RDS snapshots and just do daily mysqldumps directly from the database server, via a cron job.