Port Forward to RDS

Port forwarding to RDS database in AWS Private Subnet

There are a few strategies for accessing an RDS (AWS managed) database instance in a AWS Private Subnet.  According to AWS documentation there are 3 ways to do this. VPC Peering, creating a bastion host, and using Public accessibility.  

We will walk through creating a bastion host with port forwarding, so jdbc connections can be made from external clients to your RDS instance.

What we will do:

1. Create a VPC
2. Create a Public Subnet
3. Create 2 private subnets for our RDS instance.  RDS requires at least 2 subnets, for failover purposes.
4. Create an internet gateway, Configure ACL, Configure security groups.
5. Launch an RDS database in our private subnets.
6. Launch an EC2 instance in our public subnet.
7. Install and configure HAproxy on our EC2 instance.

Create a VPC.  From the AWS dashboard search or navigate to VPC.  This will bring you to the VPC dashboard.

Select ‘VPCs’.  This will take you to view your current VPCs available.

Note: If you select ‘Create VPC’ here, it will launch the VPC wizard.  This would create a lot of components for us and not be as fun.

Select ‘Create VPC’.

Enter a name tag, I’ll call mine ‘MyRDSvpc.  Enter a CIDR block range. I use ‘10.0.0.0/16’, which will give me the most IPs available.  I also select ‘Amazon provided IPv6 CIDR block’ to give more options later on. Click ‘Yes, Create’ when done.

First we will create our Public Subnet.  Servers in our public subnet will have public IP addresses, and access to the outside internet.

Select ‘Subnets’ on the left hand side.  Select ‘Create Subnet’.

Enter a Name tag.  I use the CIDR block range, and availability zone in my name ‘10.0.1.0 – us-east1a’.  This was how I learned it from ACloud.guru and it has stuck with me.

Select ‘MyRDSvpc’.  Enter CIDR block range ‘10.0.1.0/24’ and availability zone ‘us-east1’. Click ‘Create’.

Now since this is going to be our public subnet, let’s have it automatically assign public IPs to instances created here.  This allows access from outside our VPC.

Select our subnet, click ‘Actions->Modify auto-assign IP settings’.  Check the ‘Auto-assign IPv4’ box.

Now let’s create our 2 private subnets.  Click ‘Create Subnet’. Enter the following information, and click ‘Create’.

Now let’s create one for RDS failover.

Create an internet gateway.  This will give access out to the internet.  Select ‘Internet Gateway’ from the left-hand side. Select ‘Create internet gateway’.

Give it a name.

Attach Internet Gateway.

Now let us create a route table.  This route table will be used for our public subnet to give it a route out to the internet.  You will notice you have a route table created by default.  So let’s just use that.

Select ‘Route Tables’ on the left hand side.   Give this route table a meaningful name.  

 

Click the ‘Routes’ tab in the bottom pain. Click ‘Edit’ and add 2 rules.

0.0.0.0/0 with the target being your Internet Gateway.  Then do the IPv6 version ::/0.  This route table allows traffic out to any internet address.  This is useful for software installs and such.

Now we need to attach this our public subnet to this route table.  Select ‘Subnet Associations’ tab.

Now Check the box next to 10.0.1.0 – us-east1a’ or what ever your public subnet is named.

 

Notice a Network ACL is already create for us.  Nothing to do here, just pointing this out.

 

Let’s create a security groups for our bastion host.  Here we will allow SSH traffic, so we can access this server.  We will allow HTTP and HTTPS, so we can download and install software from the internet.  We will allow MySQL traffic, so applications can connect to our HA proxy and that MySQL traffic can be forwarded to our private subnet.

Click ‘Security Groups’ on the menu to the left.  Click ‘Create Security Group’ at the top of the screen.

Name the Security Group.  I’ll call mine ‘MyWebDMZ’.  Since this will be used for Bastion servers and proxy servers.  Give it a group name and description.  Select the VPC you just created in the bottom drop down box.

Select ‘MyWebDMZ’, select the tab below ‘Inbound Rules’, click add another rule.

Use the drop down menu under ‘Type’ and select SSH. Optionally you can just type 22 into Port Range.  For Source type in the IP address to your machine.

This will allow SSH access to port 22 on the EC2 instance associated with this security group from your machine.  Note:  You will still need credentials, this just allows access to the port.

Populate the rest of the inbound rules like the following.  Where the IPs are blacked out, use the IP to your machine.  We are basically allowing all HTTP(s) traffic from outside our VPC to the machine we will attach to this security group.  SSH and MYSQL traffic will only be allowed from our machines.  In a production environment, if other individuals needed access to the MySQL DB, we would add rules for their machines.

Now let’s create a security group for our RDS server.  This will allow connections only from our public subnet, and not the outside world.

Notice the source below points to our Public Subnet.  So we are only allowing MySQL traffic in from our public subnet.

 

Now let’s just create a subnet group that can be used during our RDS setup.

Navigate to RDS.

 

 

 

 

Select ‘Subnet groups’ on the left, then ‘Create DB Subnet Group’ at the top.

Scroll Down and add the subsets 10.0.2.0 and 10.0.3.0.  Select ‘Availability zone’ us-east-1c and ‘Subnet’ 10.0.3.0/24, then click Add Subnet.  Do the same for us-east-1b and 10.0.2.0/24.

Click ‘Create’.

Ok Now for the fun stuff.

 

Let’s launch an RDS server in our private subnet group.  This will be the server that we wish to connect to from our client machine.

I’m only checking a connection here, so I used a really small machine.

 

Scroll down.

Now let’s launch our EC2 instance.

 

Let’s rename the instance and Copy the public IP.

Let’s SSH into the instance.  Replace <public ip> with the IP address copied from above.

>ssh ec2-user@<public ip> -i ~/Downloads/MyPOCKeyPair.pem.txt

>sudo yum update
>sudo yum install haproxy

Update the HA proxy config file (/etc/haproxy/haproxy.cfg) to add an entry for our port forwarding.  The last line is the Endpoint of our RDS server.

Note: You can also use HAProxy to distribute traffic to different servers.  As of the time of this writing AWS Elastic Load Balancer does not support load balancing to RDS.  You can get more details here Scaling RDS Applications and Digital Ocean.

>cd /etc/haproxy/
>vi haproxy.cfg

Add to the bottom:

listen MySQL
        bind 0.0.0.0:3306
        timeout connect 10s
        timeout client 1m
        timeout server 1m
        mode tcp
        server singleMySQL movies-db-mysql.c2vb6mgdcjk2.us-east-1.rds.amazonaws.com:3306

Here is where to find your RDS endpoint (i.e., movies-db-mysql.c2vb6mgdcjk2.us-east-1.rds.amazonaws.com:3306 from above).

Restart the service.

Note:  This service does not automatically restart if the server is bounced.  You will need to script that yourself.

>sudo service haproxy restart

Now let’s install mysql to see if we can connect from this server.

>sudo yum install mysql

>mysql -u root -h movies-db-mysql.c0vb5mgkcjf2.us-east-1.rds.amazonaws.com -p

So now we confirmed we can connect from our bastion/proxy server to our Mysql instance.  Let’s check if the port forwarding is working.

Set details for any client application that would connect to a MySQL database.  Different BI tools would be a good test. I am just going to use MySQL workbench.

Enter in the details like you normally would, except the IP address should be the public IP of the bastion/proxy server we set up in our public subnet.