Connect and Dump SQL from AWS RDS MySQL Server of a Vapor Project (The Easy Way Using HeidiSQL — Windows 11)

As usual, this is not me trying to teach you the correct way of doing anything. I’m just sharing a knowledge I have which I think can be useful. On the other side, it’s also helping me keep records of what I know to work in case I have to use it in the future.

For me connecting to the AWS RDS of a Vapor project is a bit challenging task since you have to connect it through an SSH tunnel. Although how Laravel has developed actually enables you to use seeder or even connect to the RDS directly using connection config. Still, there are cases where you need to dump the DB to your localhost because of many subjective reasons. Those specific cases are what this story tries to help.

Installing HeidiSQL and Putty

This story won’t explain in a more detailed way how and why you should HeidiSQL and Putty Client. In a short term, HeidiSQL uses plink.exe to connect through SSH tunnel, and plink.exe will be easily installed and put to PATH if you install it by installing Putty SSH client.

(Note: you can try to install the 2 software from any source you’d prefer, the links I share here is the ones that worked for me)

• Putty Client: Download here
• HeidiSQL: Download here

Create A Laravel Vapor Jumpbox

Now, go to your Laravel Vapor Dashboard (login) and select the Vapor project you want to work on. Go to the “Networks” from the left sidebar, then pick the network that you want to connect to or create a new one if you don’t have one.

Select a network on Laravel Vapor

Scroll down to the “Jumpboxes” section, there will be a button on the right that allows you to create a new Jumpbox. Click the button, give it a name that resounds to a remote database (I found this a bit important since you can’t use the same private key for the instance), and store the private key in a safe place.

Create a Vapor Jumpbox

Create a Connection Session on HeidiSQL

Now open up HeidiSQL and set a new connection session. Select “MariaDB or MySQL (TCP/IP) as the “Network Type”. There is no preferable method actually.

(Note: You don’t need to set the Network to use SSH Tunnel although the official document requires you to do so)

In order to get the RDS credential, you will need to have Laravel Vapor CLI installed in your system. You can follow the official doc here, it’s pretty straightforward. Once Laravel Vapor CLI is installed, go to your project directory using the terminal (it would be a lot easier using VSCode) and run the below command

vapor env:pull production 

This command will get you the .env file for production (if you would like to get the DB information for the staging server, replace “production” with “staging” in the command). Go to the “Databases” dashboard from Vapor’s left sidebar and select which database you would like to connect to and just click it.

Now open the “Settings” tab on HeidiSQL and enter the details needed as below (check screenshot for better understanding).

1. Host
2. Username: vapor
3. Password (from the .env.production file)
4. Databases (from the .env.production file)

New Connection in HeidiSQL

Save your newly made connection session in HeidiSQL and click “Open”. You should be connected to your RDS server now.

Dump SQL To The Localhost DB

Now comes the fun part, I found this a very interesting feature from HeidiSQL. We can actually dump the DB SQL directly as a new DB. More to clone it to our localhost, which I think is very useful since we need to ensure data integrity.

1. Right-click on the DB you want to dump.
2. Select “Export Database to SQL”.
3. Now you can use the setting I have in the below screenshot (Screenshot #3) or you can play around a bit to match your need. The important thing is, you must select the Output to your localhost server connection session.

Screenshot #2

Screenshot #3

Now, just wait until the process is completed and you’re good to go.

Hope this story brings a bit of benefit to you all who read it. Thank you and see you soon!