AWS RDS dump / restore / view progress
Tags: aws, rds, dump, mysql
Published: 2019-03-14 00:20:54

---
title: AWS RDS dump / restore / view progress
published: true
description: Tips on dumping RDS database, in particular MySQL to localhost
tags: AWS, RDS, dump, MySQL
---

![](https://thepracticaldev.s3.amazonaws.com/i/d28w63qeobp8327jfjkc.png)

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](https://www.sequelpro.com) you can still do it using [ssh reverse tunnel](https://www.howtoforge.com/reverse-ssh-tunneling).
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](https://thepracticaldev.s3.amazonaws.com/i/eq3knq8rf2659wsrlcvd.png)

* 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](https://catonmat.net/unix-utilities-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](https://thepracticaldev.s3.amazonaws.com/i/1n0efvrqzzydq2qx3tfg.png)

---

Thanks for reading! 

You can connect with me on [Twitter](https://twitter.com/piczmar) or subscribe to my mailing list. I will occasionally update you about my recent work.

[![Subscribe](https://thepracticaldev.s3.amazonaws.com/i/6wq5xr06op3k7jbn6p1x.png)](http://eepurl.com/ghDSdz)