Connect to Private MySQL RDS Instances using MySQL Workbench

In this blog post We have explained how to connect to Private MySQL RDS Instances using MySQL workbench.

It is always recommended to host RDS instances (Databases) in private subnet as they should be more secure and also it doesn’t need to be hosted in public facing subnets.

Connecting RDS instances in the private subnet can be established in two ways.

• Setup SSH tunneling using public EC2 Instance (As bastion Server)

• Using Client VPN endpoint solution

In this blog , We have explained on connecting with private RDS instances using SSH tunneling.

Steps Involved

To connect with the RDS instances in the private subnet from local machine using MySQL workbench we have to execute the below steps.

• Create an EC2 Instance in the Public subnet and configure networking

• Configure RDS instance security group so that the EC2 instance can connect with it.

• Establish SSH tunneling with EC2 and RDS Instances details from the local system

• Install MySQL workbench and connect to RDS instances

What is SSH Tunneling?

SSH tunneling is a method of transporting arbitrary networking data over an encrypted SSH connection.

It also provides a way to secure the data traffic of any given application using port forwarding, basically tunneling any TCP/IP port over SSH.

PreRequisites

As we are in the requirement of creating EC2 instance (bastion server) in the Public and the RDS instances in the private subnet.

Check out this article , To create Custom VPC with Public and private subnets , if you don’t have one already.

Step 1 : Creating EC2 Instance

I have published an separate article and explained in details on

How to create Linux EC2 Instances , Check here And also how to establish SSH connection to EC2 instances using SSH client.

By default , port 22 is open to all , We can restrict to local public IP address for security purpose.

Step 2 : Configuring RDS Instance Security Group.

Next step is configure the security group of the RDS instance in such a way that the EC2 instance is able to connect with it.

If you havn’t created an RDS instance yet , Check this blog. Always make sure to create the RDS instance in the Private subnet and Allow Database port only for the certain IP addresses.

To configure the security group , Login to RDS console.

In the navigation pane , Choose Databases , Select the RDS Instance.

Choose the Connectivity & Security tab. Under Security , Click the VPC security groups.

Then click the Inbound rules, Click Edit to allow a new inbound rule for EC2 instance.

Click Add rule , For port range , Enter the database port Eg: 3306 for MySQL

For the Source , Enter the private IP address of the EC2 instance.

and click Save rules.

The configuration which we have added in the security group will allow traffic from the EC2 instance’s private IP address.

Step 3 : SSH Tunneling

To establish the SSH tunneling , We need the following details.

Public IP Address of EC2 Instance , .pem file of the EC2 Instance , RDS instance Endpoint and Port.

ssh -i testinstance.pem -4 -N -L randomport:DB-endpoint:DB-port [email protected]

In the above command ,

testinstance.pem – is the pem file of the public EC2 instance

randomport – we need to provide a port It can be any port number

DB-endpoint – Enter the endpoint of the private RDS instance

DB-port – Port of the Database instance

username – username of the EC2 Instance , For eg: Amazon Linux OS (ec2-user) , Ubuntu OS (ubuntu)

PublicIpAddress – Public IP address of the EC2 Instance.

Once the command is successfully executed , We can connect to RDS instances in the private subnet using MySQL Workbench from the Local machine.

Note : In the below step , I will explain how we can setup tunneling using MySQL Workbench.

Step 4 : Connecting to RDS Instance using MySQL Workbench

If you havn’t installed MySQL Workbench yet , Check this article to Install MySQL Workbench on Ubuntu systems.

Also I have explained in detail how to create and manage connection of database servers using Workbench.

Depending on the operating system , You can install the MySQL Workbench client.

Once the installation is completed.

Launch the MySQL Workbench. To create MySQL Connections , Click + icon

You should see the following screen.

Provide a Connection Name.

The connection method should be Standard TCP/IP over SSH

For SSH Hostname , Enter the public IP address of EC2 Instance

For SSH username , Enter the username of the EC2 Instance

For SSH Key File , Choose the .pem file of the EC2 Instance.

For MySQL Hostname , Enter the RDS DB instance Endpoint.

For MySQL Server Port , Type 3306 , If you have configured mySQL with custom port , We can specify it there.

Provide the Username and Database of the RDS DB Instance.

Once the details are added , Click Test Connection

After the connection is Successful , Now You are successfully connected to the RDS instance in the private subnet.

Conclusion

We have successfully configured a setup to connect with Private RDS DB instance using SSH tunneling.

Hope you find it helpful.

Also Don’t forgot to check out the below articles.