How to Access RDS Database From Local Machine (Easy and Straightforward)
The first step for accessing the RDS database from your local machine is to launch and configure the EC2 instance. Once you do that, you can configure the security groups and connect the RDS DB instance.
You can also use command lines to achieve this if you’re on a Linux or Mac system. You will find more details about both methods in the following sections.
- How to Access RDS via SSH?
- How to Connect to MySQL RDS From EC2 Instance Linux Command
- FAQs (Frequently Asked Questions and Answers)
How to Access RDS via SSH?
The demonstration below shows how to access Amazon RDS for MySQL instances in a VPC.
(A) Launch and Configure Your EC2 Instance
Launching and configuring the EC2 instance for accessing RDS DB from a local machine consists of the following steps.
1. Open the Amazon EC2 console and select Launch instance. Then, choose an AMI and select the instance type.
2. Choose Next: Configure Instance Details and select the VPC associated with your RDS DB instance for the Network field.
3. Choose a subnet with an internet gateway. Enable should be selected for Auto-assign public IP.
4. Modify the storage and add tags as per your requirements.
5. Select Next: Configure Security Group and choose Add Rule. For Type, use the IP address of your local machine. You can leave the source IP address open to all, which is the default selection, or modify the access.
6. Click on Review and Launch and select Launch.
(B) Configure the Security Group of Your RDS DB Instance
Use the following configuration for the security group to allow traffic from the private IP of your instance.
1. From the navigation menu of your Amazon RDS console, select Databases and enter the name of your RDS DB instance or create a new one.
2. Click on the Connectivity & Security option. Then, select the link below the VPS security groups in the Security tab.
3. Click Actions after choosing the security group. Select Edit inbound rules and go to Add Rule.
4. Now, select Enter Custom TCP rule as the type and enter TCP as the protocol. For the Port Range option, input the port of your RDS DB instance. Use the private IP of your instance as the Source.
5. Review the rules and hit Save.
(C) Connect Your Local Machine to the RDS DB Instance
You can use the following example as a reference when using MySQL Workbench to establish the connection.
1. Open a new connection and choose Standard TCP/IP over SSH as the Connection Method.
2. To configure SSH settings, input the following details for the EC2 instance:
For Auto-assign Public IP, ensure the DNS Hostnames option is enabled.
For SSH Hostname, insert the public DNS of the EC2 instance or use its public IP address.
For SSH Username, input the username for your EC2 instance. For instance, for EC2 Linux machines, the username is commonly “EC2-user.”
Choose the private key linked to your EC2 instance for the SSH Key File.
Remember that if the VPC (virtual private cloud) in which the EC2 instance is generated has DNS Hostnames activated, an EC2 instance launched with a public IP address will possess a corresponding public DNS entry.
3. Provide the subsequent information concerning the MySQL instance configuration:
Input the endpoint of the RDS DB instance for MySQL Hostname.
For MySQL Server Port, fill in the port as 3306. Or, if you use a distinct port, you should input the custom port number.
Enter the username associated with the RDS DB instance.
Input the password linked to the RDS DB instance.
4. Click Test Connection to see if it is successful. Choose a name for the connection and save it.
How to Connect to MySQL RDS From EC2 Instance Linux Command
For connecting to MySQL RDS from an EC2 instance, run the following commands.
For SSH configuration:
ssh -i “YOUR_EC2_KEY” -L LOCAL_PORT:RDS_ENDPOINT:REMOTE_PORT EC2_USER@EC2_HOST -N -f
For testing the tunnel:
lsof -i4 -P | grep -i “listen” | grep LOCAL_PORT
nc -zv 127.0.0.1 LOCAL_PORT
For connecting your RDS instance from the local machine:
mySQL -h 127.0.0.1 -P LOCAL_PORT -u RDS_USER -p
FAQs (Frequently Asked Questions and Answers)
Can we connect AWS RDS to the local machine?
It is possible to connect your AWS RDS database to your local machine. Use the Amazon EC2 console and launch an instance with proper TCP/IP settings.
Does RDS run on EC2?
Amazon RDS provides managed database services. In contrast, Amazon EC2 offers virtual servers that you can use to run various types of applications, including databases.
How do I connect to a private RDS instance locally?
Initiate and set up an EC2 instance, adjust its network settings, and arrange the security groups for your RDS DB instance. Lastly, start a connection with the RDS DB instance from your local machine.
The steps for accessing RDS from your local machine can vary depending on the client used. If you’re using MySQL, the recommendation would be to use SSL and establish an encrypted connection between RDS and the client application.