DEV Community

loading...

AWS RDS dump / restore / view progress

piczmar_0 profile image Marcin Piczkowski ・2 min read

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 noone should be able to 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:

  • 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 ec2-user@bastion.host  \
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.

  • 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 an the bastion server will be used to tunnel the traffic.
ssh -i ssh_key.pem -N -L LOCAL_PORT:rds.host:RDS_PORT ec2-user@bastion.host

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

Example connection configuration from Sequel Pro

  • 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 with which you can track progress.

-- 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:

Pipe Viewer in action


Thanks for reading!

You can connect with me on Twitter or subscribe to my mailing list. I will occasionally update you about my recent work.

Subscribe

Discussion (0)

pic
Editor guide