Connecting a MySQL workbench to Amazon Web Services’ Relational Database Service
I followed the official AWS guide to connect mysql Workbench to AWS RDS and got my setup running within minutes, the end… Just kidding.
I got a pesky “Can’t connect to MySQL server on ‘instanceName.us-west-1.rds.amazonaws.com’ (60)” error when trying to connect to the database. So, here’s my full guide to instantiating a MySqL database on AWS and connecting to it. Lastly, I’ll briefly comment on how you can easily change this for another relational database management system, such as PostGreSQL.
Requirements
- AWS account
- MySQL Database admin tool
I installed the community MySQL workbench and that’s the one featured in this guide. You can also use MySql shell and I will also show how to connect to the db using the terminal. To install MySQL shell, I had update to Xcode, then run.
$brew update
$brew install mysql
STEP 1: Instantiating a AWS RDS
From your AWS console, search RDS, which stands for Relational Database Service. (Fun fact, the relational is not derived from how the tables “relate” to each other, but from the term relation, which is part of set theory).
Click “Get Started Now” or “Launch a DB instance” (depending if you have instantiated a database already or not). We’ll chose MySQL and click next.
Now under the DB details, we’ll leave everything as is. Why not the latest and great version of MySQL you ask? There are some known issues with MySql 5.7 and AWS, so we’ll just stick with mysql 5.6.x
Next up, nothing to change here if you want to stay in the free-tier. Otherwise, pick a db instance as hefty as you want.
Now we want to specify the db instance identifier, username, and password. The username and password is what we need to login later so keep this handy.
My password was “masterfightingduckhorse” as inspired by xkcd’s naming guide.
We’re keeping the defaults here as well, but we’ll have to make some changes to the VPC later. Note that our DB is publicly accessibility.
Finally, we name our database and instantiate.
In your Amazon RDS console for your instance, it should show DB instance status = creating. When status = available an endpoint under “Connect” should be generated and you should be able to connect using only the endpoint, master username, and password.
STEP 2: Connect Database Admin tool to AWS RDS
Now, with MySQL workbench click the (+) button to create a new connection. Copy your generated endpoint to hostname and enter your username and password to get connected.
Alternatively, you could enter
$mysql -h putEndPointHere.com -P 3306 -u root -p
Now, most guides state that you should now be connected. Unfortunately, this was not the case for me. My database was online, so I suspect it was the VPC (Virtual Private Cloud) configuration.
From above, we can see that clients do not connect directly to the DB. Instead, we connect to a VPC, which can set various rules for who can connect. For different types of DB access through VPC, check out Amazon’s Detailed Guide.
In our db instance, under “Security and Network”, I see that our DB is publicly accessible, which means devices outside of our VPC are allowed to connect to it. That’s correct. Now let’s click our specific security group. In my case “rds-launch-wizard-1…”
A EC2 Management console should pop up. (Our RDS is protected by a VPC OR EC2 security group).
On the bottom lets click Inbound, then edit, to change these rules. Let’s go ahead and allow any type of traffic, with any protocol, from anywhere to access our database on any port, (definitely not secure).
And viola! That should work. You should be able to query the DB now.
Unfortunately, our VPC security nightmare. Let’s make things a little more secure by restricting access to just our IP address, with the TCP protocol, and only on port 3306 (where our db is open).
Now, is this a long term solution? Probably not, if your current IP address was a public wifi network from Starbucks, anyone there could attempt to access your db. You’d probably don’t want to keep manually updating your IP address either. A longterm solution would be using EC2 Key pairs, but that’s a topic for another time.
Finally, you instantiating a different DB, like PostGreSQL is not so different. Besides the engine option, you’d also need a different admin tool, such as PGAdmin. Otherwise, the inputs for hostname, master user name, and password are the same.
