Restore mysql dump to rds

Tips on dumping RDS database, in particular MySQL to localhost

I bet you had a situation when you had to dump production database for some investigation, testing or even development.

Off topic:
I won’t mention here about the sensitive data and data obfuscation as this is out of scope of this post, but just wanted to remind you that you’re dealing with real people data and should keep in mind some regulations like GDPR or be careful not to send emails to real users if you are testing your apps using production DB.
In any case, it’s good to replace real users’ sensitive data with some dummy values.

Usually the production DB is secured in AWS VPC (virtual private cloud) and nobody should connect directly without VPN. Often it happens that the DB is only accessible from a server inside VPC which serves as a “bastion” server so that you can access other servers inside VPC from this server but not from public.

Below are a few tips which you may find useful in these circumstances:

1. Dump MySQL file from RDS database straight on your localhost.
   This means you do not have to dump the file on “bastion” server and then copy it to your local, because you can create the dump straight on your local

ssh -i ssh_key.pem  \
mysqldump -P 3306 -h rds.host -u dbuser — password=dbpassword dbname > dumpfile

This command will ssh to the bastion server and execute mysqldump command there but the result is redirected to your localhost file dumpfile.

2. If you’re not brave enough to dump DB from command line and you prefer to use some graphic tools like Sequel Pro you can still do it using ssh reverse tunnel.
It means that you will map remote port of your RDS host to a localhost port and the bastion server will be used to tunnel the traffic.

ssh -i ssh_key.pem -N -L LOCAL_PORT:rds.host:RDS_PORT 

When it runs you should be able to connect with your favourite SQL client, e.g.:

3. If you dump or restore large DBs it can be tricky to see progress. Usually the command looks like hanging, e.g. for restore:

mysql -P DB_PORT -u dbuser — password=dbpassword dbname < dumpfile

There is a nice but not so well known tool on *nix systems called Pipe Viewer which can help in tracking progress. Here is an example usage:

• for dump:

mysqldump -P 3306 -h rds.host -u dbuser — password=dbpassword dbname | pv -W > dumpfile

• for restore:

pv dumpfile | mysql -P DB_PORT -h db.host -u dbuser — password=dbpassword dbname

Then you see a nice progress bar from Pipe Viewer:

Thanks for reading! Feel free to hit the recommend button below if you found this piece helpful.

You can connect with me on Twitter or subscribe to my mailing list if you want to get occasional info about my recent work.

How do I restore a MySQL dump file?

How do I import a dump file into AWS RDS?

How do I restore AWS RDS MySQL database?

How do I restore a MySQL database dump file in Linux?