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.:
- 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:
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.
Top comments (0)