89

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?

William
  • 1,295
  • 1
  • 10
  • 19

7 Answers7

74

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).

datasage
  • 19,153
  • 2
  • 48
  • 54
  • 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! – William Feb 17 '13 at 03:11
  • 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. – datasage Feb 17 '13 at 04:32
  • 7
    How do you connect to a snapshot? Can it be done without restoring that snapshot to the db? – ScotterC Aug 19 '13 at 20:27
  • 13
    @ScotterC, you need to create a new instance with the snapshot. – datasage Aug 19 '13 at 20:34
  • 6
    @datasage an example of this would have been good. Pretty lazy answer – hmedia1 Oct 03 '18 at 13:08
  • @datasage - How to connect RDS snapshot to take database dump? – AnkurVyas Jun 26 '19 at 13:27
  • 7
    This seems way more complicated than it should be. – user2588667 Feb 02 '20 at 00:40
  • This is way more complicated than it should be. I wrote up a blog post to remind my future-self how to do this: http://mikebian.co/dumping-a-aws-rds-database-to-your-local-machine/ – iloveitaly Nov 29 '20 at 23:51
  • 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. – Mike 'Pomax' Kamermans Apr 10 '23 at 22:12
  • 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. – tread May 19 '23 at 10:59
46

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.

AWS RDS Console with Export to S3

rfreytag
  • 1,161
  • 10
  • 16
  • This certainly looks nice, but AWS just says "Internal Server Error" when attempting this. Hopefully they iron out the bugs soon. – user2588667 Feb 02 '20 at 00:38
  • 1
    as a clarification : this is only true in a selected list of regions, not available everywhere: https://aws.amazon.com/about-aws/whats-new/2020/01/announcing-amazon-relational-database-service-snapshot-export-to-s3/ – Vincent Duprez Mar 04 '20 at 09:33
  • 2
    I'm getting "Missing required key 'KmsKeyId' in params" every time I attempt to export a snapshot. Are we forced to use encryption? – BenMorel Jan 21 '21 at 14:02
  • 1
    for some reason my option to Export to S3 is greyed out :/ – Casey L Feb 10 '21 at 20:42
  • 1
    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? – Tom Raganowicz Mar 18 '21 at 05:51
  • 6
    This exports in Parquet format, which may not be what you want. – Mr. S Aug 12 '21 at 19:55
  • This doesn't cover all the cases, the "Export to Amazon S3" isn't available to me. – Alynva Mar 19 '22 at 19:08
13

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.

Community
  • 1
  • 1
abguy
  • 943
  • 10
  • 12
5

I use:

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

Édipo Féderle
  • 4,169
  • 5
  • 31
  • 35
  • 2
    Thank you for your answer - although code/config snippets might provide some limited short-term help, a proper explanation [would greatly improve](https://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) 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. – kenny_k Oct 07 '19 at 13:40
  • Short versions of flags are for writing. Long versions are for reading – Ben Longo Aug 10 '23 at 22:11
3

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
Pang
  • 9,564
  • 146
  • 81
  • 122
Kaz
  • 99
  • 1
  • 1
  • 6
  • 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. – toon81 Mar 26 '19 at 11:48
  • @toon81 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. – Bogdan Mart Jun 22 '19 at 18:28
  • @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. – toon81 Jun 23 '19 at 21:55
1

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.

David Dehghan
  • 22,159
  • 10
  • 107
  • 95
1

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.

Java Main
  • 1,521
  • 14
  • 18