download RDS snapshot

Question

I recently downgraded my EC2 instance. I can no longer connect to RDS. I think it might be that the internal IP is different and now the logins are attached to that specific IP. I haven't been able to figure it out. I would like to be able to get a backup from the snapshot. Is there a way to download it through AWS?

Lincolnlincolnshire

Answer 1

You can't download an RDS snapshot. You can however connect to it and export your databases. Downgrading your instance should not affect connectivity unless you had set up your security groups incorrectly (Opening ports to an IP instead of another security group).

Gwennie

I have verified that the EC2 instance can connect by telnet to port 3306 on the RDS endpoint. However my logins do not work. My security group did not even have mysql specified (I think that is for inbound into the EC2 instance anyway). The db security group has the EC2 security group specified which my EC2 instance also has. I believe that is how it is supposed to be set up. That is what worked initially. I just tried an older password than the one that was in my files and it worked. It is good to know for sure that one cannot download an RDS snapshot though. Thank you! – Lincolnlincolnshire

Did you set up grants that specify an ip address? The root account should not have a grant that is ip specific and if needed it can be reset via the RDS console. – Gwennie

How do you connect to a snapshot? Can it be done without restoring that snapshot to the db? – Galloot

@ScotterC, you need to create a new instance with the snapshot. – Gwennie

@Gwennie an example of this would have been good. Pretty lazy answer – Sommer

@Gwennie - How to connect RDS snapshot to take database dump? – Pavkovic

This seems way more complicated than it should be. – Homoeroticism

This is way more complicated than it should be. I wrote up a blog post to remind my future-self how to do this: mikebian.co/dumping-a-aws-rds-database-to-your-local-machine – Bernetta

Note that this answer may have been true in 2013, but has since 2020 been flat out wrong, and really should get updated to point to the right answer instead. – Taam

One can use the "export to s3" action of the snapshot - to export specific relations and they will be stored in s3 as parquet files. – Raucous

Answer 2

The accepted answer is not up-to-date anymore. Instead of using command line tools, you can use the AWS console.

Navigate to RDS -> Snapshots -> Manual/System ->

Select Snapshot -> Actions -> Export to S3

Going through S3 is common in most production environments, as you won't have direct access to the DB instance.

Lindi

This certainly looks nice, but AWS just says "Internal Server Error" when attempting this. Hopefully they iron out the bugs soon. – Homoeroticism

as a clarification : this is only true in a selected list of regions, not available everywhere: aws.amazon.com/about-aws/whats-new/2020/01/… – Woolpack

I'm getting "Missing required key 'KmsKeyId' in params" every time I attempt to export a snapshot. Are we forced to use encryption? – Critique

for some reason my option to Export to S3 is greyed out :/ – Spokeshave

You can export to to S3, from which you could download, but then it will be encrypted using KMS key which you can't download which would make your package useless on any environment remote to AWS. Am I right? – Carriole

This exports in Parquet format, which may not be what you want. – Bradlybradman

This doesn't cover all the cases, the "Export to Amazon S3" isn't available to me. – Cowberry

Answer 3

In addition to datasage answer.

As an option for production instance you can create a readonly replica in RDS and make dumps from this replica. You could avoid freezing of production DB this way.

We use this scheme for PostgreSQL + pg_dump. Hope it will be helpful to somebody else too.

Patriciapatrician

Answer 4

I use:

pg_dump -v -h RDS_URL -Fc -o -U username dbname > your_dump.sql

Tail

Thank you for your answer - although code/config snippets might provide some limited short-term help, a proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you've made. – Mnemosyne

Short versions of flags are for writing. Long versions are for reading – Nutcracker

Answer 5

I also needed to do this so I created a dump of the db (MySQL) by logging into my app server which has permissions to access the db. I then downloaded the dump to my local machine using scp. I used:

mysqldump -uroot -p -h<HOST> --single-transaction <DBNAME> > output.sql

Strohl

This is unhelpful for two reasons: first of all it doesn't answer the question, and second of all this requires connecting with the RDS instance, and OP is asking this question precisely because they can't. – Righteousness

@Righteousness actually he covers connection issue. If you unable to connect from outside, you would be able to connect from EC2, if that's VPC issue. – Brann

@BogdanMart Since downgrading their EC2 instance, OP is unable to connect, suggesting that they were trying to connect from the EC2 instance all along. Anyway, it still doesn't matter because OP isn't asking about how to connect. I think they should be, but the fact remains this question is about something else. – Righteousness

Answer 6

Another option is to share your snapshot if you don't need to download it and just want to share it with a different AWS account ID.

Exserviceman

You will need to share master key as well, or it won't be possible to decrypt the snapshot. – Dicast

Answer 7

It sounds like your RDS is within a VPC inside a private subnet with security group and ACL. The only way to solve your issue is to take a snapshot and cerate a new DB instance out of it within the default VPC where all connections are allowed. After that you take backup classic backup using a db client or CLI.

Pentose

Answer 8

The "Export to S3" option worked for me. I was able to restore only the table I wanted and view it locally using the "Tad" app.

Leucoma