Connect to Amazon Aurora Serverless MySQL DB cluster from MySQL Workbench

Amazon Aurora Serverless is an on-demand, auto-scaling configuration for Amazon Aurora. It automatically scales compute capacity up or down based on your application's needs.

An Aurora Serverless DB cluster resides in the private subnet of Amazon VPC. You can’t give an Aurora Serverless DB cluster a public IP address. You can only access it from within a VPC.

If you want to access your Amazon Aurora Serverless DB cluster from MySQL Workbench, one way to do this is via an SSH tunnel through a bastion host (jump box).

In this post, I will share the steps for setting up a bastion host using an Amazon EC2 instance. Then use MySQL Workbench to connect to the bastion host via an SSH tunnel.

Solution Overview

The following diagram illustrates the solution architecture:

For this, you will perform the following steps:

1. Setup networking configurations in Amazon VPC
2. Create and launch an Amazon EC2 instance
3. Configure MySQL Workbench

Prerequisites

You should have the following prerequisites:

• Amazon Aurora Serverless DB cluster with MySQL-compatible edition
• MySQL Workbench installed in your local machine

Steps

Step 1: Setup networking configurations in Amazon VPC

1. Open the Amazon VPC console

A. Security Group

1. Create a security group for Amazon EC2 within the same VPC where your Amazon Aurora Serverless DB cluster resides and add the following inbound rule:
   Type: SSH
   Protocol: TCP
   Port Range: 22
   Source: Enter the IP address of your local machine or choose My IP
   

2. Update your DB cluster’s security group and add the following inbound rule:
   Type: MySQL/Aurora
   Protocol: TCP
   Port Range: 3306
   Source: Enter the Amazon EC2 security group ID
   

B. Internet Gateway

1. Create an Internet Gateway and attach it to your VPC

C. Subnet

1. Create subnets in the same Available Zone (AZ) as the subnet group of your DB cluster

D. Route Table

1. Create a routing table
2. Associate the created subnets in the routing table
3. Add a route in the routing table that directs internet-bound traffic to the internet gateway

Note: If a subnet is associated with a route table that has a route to an internet gateway, it is now a public subnet.

Step 2: Create and launch an Amazon EC2 instance

1. Open the Amazon EC2 console and choose Launch instances
2. Select an Amazon Machine Image (AMI). In this example, I will choose an Amazon Linux 2 AMI

3. Choose an Instance Type. For this, I will choose t2.micro
   

4. Choose Next: Configure Instance Details and make sure to use the following settings:
   Network: Choose the VPC that the Amazon Aurora Serverless DB cluster uses
   Subnet: Choose a public subnet (the subnet that has an internet gateway in its routing table)
   Auto-assign Public IP: Enable
   

5. Choose Next: Add Storage and modify storage as needed

6. Choose Next: Add Tags and add tags as needed

7. Choose Next: Configure Security Group and select the security group created earlier for EC2
   

8. Choose Review and Launch

9. Choose Launch

10. Create a new key pair and download it
    

11. After downloading the key pair, click on Launch Instances

12. Navigate to the launched instance and wait until it's in Running state. Take note of the public IP address of the instance
    

Step 3: Configure MySQL Workbench

1. Open MySQL Workbench, and choose the ⊕ sign beside MySQL Connections to set up a new connection
   

2. In Setup New Connection, enter a name for your connection and select Standard TCP/IP over SSH for the Connection Method

3. In the Parameters section, enter the following settings:
   SSH Hostname: Enter the public IP address of your EC2 instance
   SSH Username: Enter the username for your EC2 instance. In our example, we will use "ec2-user" (this is the default username for EC2 Linux machines)
   SSH Key File: Select the private key (.pem file) that was downloaded when the EC2 instance was created
   MySQL Hostname: Enter the Aurora Serverless DB cluster endpoint
   MySQL Server port: Enter 3306
   Username: Enter the username that you use to connect to your of the Aurora Serverless DB cluster
   Password: Enter the user’s password of your Aurora Serverless DB cluster
   

4. Click on Test Connection

5. If this is the first time you are connecting to the EC2 host, you will get the following error message:
   

6. Click OK

7. After the test is successful, choose OK
   
   Note: If you receive an error when you test the connection, check if you configured the network correctly.

8. In the Setup New Connection, choose OK again to save the connection

Congratulations! You are now able to connect to your Amazon Aurora Serverless MySQL DB cluster from MySQL Workbench.