When you deploy a production AWS RDS SQL database, it is common practice that the RDS should be inside a VPC private subnet group. In this tutorial, I will show you How to connect to AWS RDS MySQL on a VPC private subnet.
As you can see from the picture above, this is the cloud architecture we will build for our AWS RDS database. We will have an RDS instance inside a private subnet and an EC2 instance inside the public subnet.
1# Create VPC:
Open the AWS management console and create the VPC or use the existing VPC for our RDS. This tutorial will create VPC in 2 available zones (AZ) with two private and two public subnets.
After that, click “Create VPC.”
2# Create a security group:
The next step is to create a new security group to attach to any server that wants to connect to our database.
From the VPC dashboard, go to the security group and create a new group and do the following:
- Call this group “RDS security group.”
- Attach the VPC we create on the previous steps to the new security group.
- Allow your IP to access through the SSH tunnel.
3# Create Mysql Database Instance
Now it is time to create a new database instance. For this example, we going to create a MYSQL database.
Go to the RDS dashboard and create a new database.
Instance settings should be as the following:
- Engine options: choose MySQL database engine type.
- Templates: Choose a free tier or development database
- Availability and durability: Chose a Multi-AZ DB cluster for high availability and Read replica (Ignore if you choose the free tier).
- Credentials Settings: We will use password authentication. Thus, you need to add a master username and password.
- DB instance class: Default (Or anything else you need)
- Storage Type: Default (Or anything else you need)
On your connectivity section, choose the right VPC and make public access to “NO”.
Finally, click create database button.
4# Create EC2 Instance
Create a new EC2 Ubuntu server, and choose your instance type. We are going to use this EC2 server to connect to our database server.
Then create your key pair and download it or choose the existing key pair:
The most important section is the network settings. You need to make sure you did the following steps:
- Choose the correct VPC that is associated with your RDS
- Make the EC2 inside the public subnet
- Assign public IP to allow access to the server
- Choose the correct security group we just created
- Click launch instance
5# Edit Security Group Inbound Rules
EC2 Security Group
Go to your EC2 Instance, and from the security tab, click security group. Then down below, click edit inbound rules.
Finally, add the SSH connection rule to your IP address.
MySQL RDS Security Group
Go to your database instance and click the connectivity & security tab, then go to security and click on the VPC security groups link.
Choose your RDS connection security group.
Then go to inbound rules and click edit inbound rules. Add your new rule by adding the id of your EC2 security group and then click save rules.
6# Test connection
The last step is to test the connection to your MySQL DB instance using MySQL workbench.
Add your connection information to MySQL workbench:
- Connection Method: Standard TCP/IP over SSH
- SSH Hostname: Your EC2 Hostname + post number. Example: URL:22
- SSH Username: Your EC2 Server username
- SSH Password: Empty
- SSH Key File: Use the PEM file that is associated with your EC2 Instance
- MySQL Hostname: MySQL instance hostname
- MySQL Server Port: 3306 The default or any custom port number.
- Username: DB master username
- Password: DB master Password.
Finally, Test your connection.
1. What is AWS RDS MySQL, and why is it important?
Amazon RDS ( Amazon Relational Database Service) for MySQL is a cloud-based relational database that is easy to set up, operate, and scale. It offers cost-efficient and resizable capacity while managing time-consuming database administration tasks, freeing you to focus on your applications and business.
2. How can I ensure the security of my AWS RDS MySQL database?
Securing your AWS RDS MySQL instance is critical. Here are some best practices:
- Use IAM roles and policies: Use AWS Identity and Access Management (IAM) to control who can manage AWS RDS resources.
- Enable encryption: AWS RDS supports using SSL to secure data on the network connecting your application and databases.
- Implement VPC: Use Amazon Virtual Private Cloud (VPC) to isolate your DB instances in your virtual network and connect to your existing IT infrastructure through an industry-standard encrypted IPsec VPN.
3. How can I improve the availability of my AWS RDS MySQL database?
To improve the availability of your AWS RDS MySQL database, consider the following measures:
- Multi-AZ deployments: You can have a standby replica of your production database in a different Availability Zone. AWS automatically fails over to standby during planned database maintenance or unplanned service disruption.
- Read Replicas: This feature allows you to have a read-only copy of your production database. This is useful for serving read traffic and reducing the load on your production database.
4. What are some fault-tolerant settings for my AWS RDS MySQL?
Fault tolerance in AWS RDS MySQL can be achieved through the following:
- Automated backups: This feature allows point-in-time recovery for your database. AWS backs up your database and transaction logs and stores them for a user-specified retention period.
- Database snapshots: You can create a manual snapshot of your database anytime. Unlike automated backups, manual snapshots are not deleted after the retention period.
5. How To Connect To AWS RDS MySQL?
To connect to AWS RDS MySQL on a VPC private subnet, you need to follow these steps:
- Create a new Amazon RDS DB instance in the VPC.
- Launch an EC2 instance in the same VPC.
- Allow SSH connection from the EC2 security group to the MySQL security group.
- Use the MySQL Workbench to connect to the RDS DB instance.
Remember, the key to successfully leveraging AWS RDS MySQL lies in understanding and implementing these best practices. Doing so ensures your data remains secure, available, and resilient to faults. Happy databasing!