July 13, 2016 · Code Snippets AWS

Get mysqldump from Amazon RDS to local machine

I had tried many combinations to finally get this working. This might seem very straightforward and obvious for most, but I had to change and test so many parameters, I thought I'd write how I did it (also as a reference for myself). Be warned, this is a very "dirty" way to do it. But I just wanted to get a quick mysqldump to check the raw data locally.

I opened the AWS Console and opened the RDS DB instance. In the configuration details I have DB Name and Username. If you forgot the master password, we can change it.

But first, in the DB instance configuration details, click on one of the Security Groups and it opens the Security Group list view. Choose Create a Security Group. Call it something like "temp-access-for-mysqldump" or so. On the settings below, click on Inbound and add the rule for Type MYSQL/Aurora and Source to My IP.

Inbound Rule

After you've saved it, go back to the RDS AWS Console, right-click on the RDS instance line and choose Modify. Add the selection to include the newly created security group under Network & Security. Also select Publicly Accessible to Yes. (And set a new master password, in case you don't know the old one.) Check Apply Immediately at the bottom, and then Continue and Modify DB instance. It might take a minute or two for the changes to take effect.

Then, from your local machine, do

mysqldump -h abcd1234567890.abcdef123456.eu-central-1.rds.amazonaws.com -u username -p -P 3306 mydbname > dump.sql

where the host (-h) is the endpoint. DB Name and Username should be visible in the configuration details. Optional is --compress, which might save a bit of time in large dumps.

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket
Comments powered by Disqus