How to Access an AWS RDS MySQL Database Using MySQL Workbench via EC2 and Private Key

Accessing an AWS RDS MySQL database from MySQL Workbench can sometimes be tricky, especially when the RDS instance is not publicly accessible. One common solution is to use an EC2 instance as a jump host or bastion host. In this guide, we’ll walk you through how to securely access your RDS MySQL database using MySQL Workbench, leveraging a private key for SSH tunneling via your EC2 instance.

Prerequisites

1. RDS MySQL Instance: An AWS RDS MySQL instance should already be set up.
2. EC2 Instance: An EC2 instance acting as a jump host.
3. MySQL Workbench: Installed on your local machine.
4. Private Key: The private SSH key (id_rsa) used for accessing the EC2 instance.

Step 1: Set Up Your EC2 Instance (Jump Host)

Before you can access your RDS MySQL database via MySQL Workbench, ensure that:

1. Your EC2 instance has access to your RDS MySQL instance (i.e., the EC2 instance should be in the same VPC or have network access to the RDS instance).
2. The security group associated with the EC2 instance allows inbound SSH (port 22) from your IP.
3. The security group of the RDS instance allows inbound MySQL traffic (port 3306) from the EC2 instance’s security group.

Step 2: Download and Install MySQL Workbench

If you haven’t already, download MySQL Workbench from the official site: MySQL Workbench Downloads.

• Windows: Download and run the MSI installer.
• macOS: Download the DMG file and follow the instructions.
• Linux: Use your package manager to install MySQL Workbench.

Step 3: Set Up MySQL Workbench to Use SSH Tunneling

• Launch MySQL Workbench on your machine.
• Click on the “+” icon next to MySQL Connections to create a new connection.
• In the Connection Method dropdown, select Standard TCP/IP over SSH.
• Now, fill in the following details:

i. Connection Name: Give the connection a name, e.g., RDS-via-EC2.

ii. SSH Hostname: The public IP address of your EC2 instance.

iii. SSH Port: Typically, this is 22.

iv. SSH Username: Use the username for your EC2 instance, such as ec2-user (for Amazon Linux) or ubuntu (for Ubuntu).

v. SSH Key File: Browse to and select your private key file (id_rsa) that allows SSH access to the EC2 instance.

vi. MySQL Hostname: This is the RDS endpoint (without http:// or https://), which you can find in the RDS Dashboard. For example, mydbinstance.xxxxxxx.us-east-1.rds.amazonaws.com.

vii. MySQL Port: The default MySQL port is 3306.

viii. MySQL Username: Enter your RDS MySQL username (the one you used when setting up the RDS instance, such as admin or root).

ix. MySQL Password: Click on Store in Vault, and enter the password for the MySQL user.

Step 4: Test the Connection

Once you’ve entered all the necessary information, click on the Test Connection button. If everything is set up correctly, you should see a Connection Successful message.

If the connection fails, double-check:

• The private key file is correct and matches the EC2 instance.
• The security group settings for both EC2 and RDS.
• The MySQL credentials.

Step 5: Save and Connect

After the connection test is successful, click OK to save the connection. You can now double-click the new connection you just created to access the RDS MySQL database.

Step 6: Troubleshooting

If you encounter issues, here are a few things to check:

• SSH Access to EC2: Ensure you can SSH into the EC2 instance directly using the same private key.
• RDS Security Group: Verify that the RDS security group allows inbound traffic from the EC2 instance’s security group on port 3306.
• Network Configuration: Make sure the EC2 instance and RDS are in the same VPC or can route to each other through a VPC Peering or VPN.

Conclusion

By using an EC2 instance as a jump host, you can securely access your AWS RDS MySQL database through an SSH tunnel with MySQL Workbench. This method is particularly useful when your RDS instance is not publicly accessible and you need an intermediary server (like EC2) to establish the connection.

Following this process will allow you to work with your RDS instance just like any local MySQL database, all while maintaining security and ensuring that sensitive data is not exposed to the public internet.

Need help setting up or troubleshooting your MySQL Workbench connection to AWS RDS via EC2 ? Our expert AWS management services team is here to assist you with secure, reliable, and hassle-free database access. Contact us today to get professional support and streamline your cloud operations.