How to take backup of a database through SSH tunneling

Step by step guide on how to take a database backup through an ssh tunnel

There are situations where we have to take backup of databases that are only accessible via ssh and that can be a challenge if you don’t know the steps. One example of that can be a database only accessible via a jump box (think AWS’s RDS in a VPC only accessible via an ec2 instance, where this ec2 instance has its port 22 open to the world)

For this tutorial my example setup is like this -

The Database is AWS’s RDS (Relational database system) Aurora within a VPC (virtual private cloud)

Step 1: Open a terminal and connect to your database instance via the ec2 instance (or any other server that you have)

sudo ssh -N -v -L 9001:[replace with host url for the database]:5432 -i key_file.pem [replace with user for the server]@[replace with ec2/server ip/url]

In the above command, we are connecting to the server using the key file and username via port 22 (default), and once that is done, then we are connecting to the database server. In the example above, I am connecting to a Postgres database on port 5432 via my local machine’s port 9001. You can adapt it to the port of your database (3306 for MySQL and so on… ) and any other available port on your local machine.

Step 2: Open a new tab on the same terminal and run your db backup command over port 9001

pg_dump -U [replace with you db username] -v --format=c -h localhost -p 9001 --file=db_backup.psql [replace with your database name]

Now you are running commands on your connected database server (and it’s not just limited to taking backups, you can do whatever you are allowed with your database user).

Once this is complete you will have a backup file on your local machine 🙌 🎆 🎉