We are working on Amazon (AWS) Cloud service and attempting to setup a new MySQL instance that we can connect to remotely. This empowers the DBA to use their local desktop tools (IDE) like MySQL Workbench or DBeaver to create and manage the databases in the RDS Cloud.
Setup Summary:
- Create database instance
- Choose “Public Access”
- Use Master username and password (not IAM)
- Modify the “VPC security groups”, Edit “Inbound Rules” to allow local IP & port
- Use the Endpoint, Port, Master username/password in IDE
- Leave the “Database” field blank in IDE settings
- Test connection, or use the MySQL CLI
mysql -h mydb333.cxxxxvps.us-east-1.rds.amazonaws.com -P 3306 -u dbadmin -p > use sys;
> select * from sys_config;
> exitAWS Database Free Tier
These settings and examples are configured in the Free Tier offered by Amazon. Developers and DBA’s can learn the system using this tier but features are limited. We will be creating a single MySQL instance with the minimum allowed storage and connecting using one master username and password. All of this falls under the Free Tier so you should not see any charges.
AWS RDS MySQL
To begin, login to your AWS account and navigate to the Managed Relational Database Services (RDS).
AWS -> Services -> Database -> RDS
The page should, look something like this. If this is a new account the DB Instances will be 0/40.
You can Click the “Create Database” button here or click the “DB Instances” link and then the “Create Database” button.
We are creating a new MySQL instance…
Note: This configuration a "one-pager" so the screenshots are only for the settings we need to change.
We are using the Free Tier…
In this example I am creating an instance called “mydb333”
With a Master username of “dbadmin” and a “password”.
Assign the AWS Storage limits…
Use the Default settings for the VPC and DB Subnet but be sure to choose “YES” for the “Public access” because we will be connecting to this instance remotely.
Be sure to choose “password authentication” if not already checked…
Click “Create Database”,… and wait as the top Blue bar will say “Creating Database.
It took about 15 minutes for me.
When completed you should see the DB Instance or DB Identifier (mydb333) under Databases.
How to Connect to AWS RDS MySQL
For this post we are only focusing on connecting to the Cloud instance from a remote machine as opposed to another virtual hosted in the AWS cloud. This could be your workstation or another VPS in another cloud.
Click on the DB Identifier for your new database to view the “EndPoint & Port”. This will display the DB Instances Endpoint, Networking and Security configuration information.
RDS Add Security Group Rules
If you attempt to connect to this Endpoint now you will most likely see a “connection timeout”.
We will need the Endpoint, Port and master username/password to connect to this MySQL instance across the internet. The VPC and the VPC Security Group are (db tales com) automatically created but the default does not allow for connections from the Internet & we need to change this.
Click on the “default (sg-xxxxxx)” VPC security Group.
Note: If the tabs are not displayed below, be sure the checkbox is checked next to the “sg-xxxxxx” security group.
Under the “Inbound” tab click the “Edit inbound rules” button.
We need to change the inbound rule to allow connections from your public IP address to the VPC where our MySQL instance is hosted. (you will need your public IP address for this).
Note: alternatively you could delete and re-create a new rule
Choose the “MySQL/Aurora” type, TCP Protocol and the Port is 3306.
For the IP Address, type in your Public IP address and it should include the IP mask of /32 since it is just one address. From a network standpoint you could also change the mask to open this VPC up to a larger network but that’s outside the scope here.
Click “Save rules” and we can now test connecting to the instance from our public IP address.
Connect Using MySQL WorkBench or DBeaver
We will be using DBeaver to test the connection but you can use MySQL Workbench or any other IDE with the same settings.
We use the Endpoint as the “Server Host”, 3306 for the port but do not enter a Database. We are simply (db tales com) connecting to the instance and there are no User DB’s yet. Enter the Master username/password and “Test” the connection.
When the connection is save you will be able simply to click on it to make the connection to your MySQL cloud instance.
