Connecting to Mysql within Amazon VPC

Hi all,

It’s been a long time since my last post. Though days indeed :  lot of work and annoying health issues (cause and consequence, in that order). I’m back, with a lot of things to share about data processing / data warehousing / BI.

Let’s start with something small, but usefull : how to connect to an Amazon EC2 instance running mysql from within a VPC (Virtual Private Cloud) ? There is a small trick to know and to apply in order to open the connectivity.

First, you need a good understanding of the following :

• AWS : http://aws.amazon.com
• EC2 : http://aws.amazon.com/en/documentation/ec2/
• VPC : http://aws.amazon.com/en/documentation/vpc/
• MYSQL (and user mgmt) : http://dev.mysql.com/doc/refman/5.0/en/create-user.html

Back to good old practice : a drawing !

What we want to achieve is connecting and querying a MySQL database hosted on an EC2 instance. We want to connect and query from within the same VPC and subnet.

Have a closer look

This sounds easy, you would say : deploy mysql, create a database, create a user, grant this user for remote access, open security groups for the db port and go on … Well, according to the VPC and the subnet, this is not really that simple.

Let’s have a close look to our instances and have a quick review :

Using that setup – which is ok for any other cloud/on premises implementation – won’t be enough to give you access to your mysql database. Remember we are in a VPC and a subnet. Your connection request will be refused.

The solution

Could be hard to diagnose, but pretty easy to apply : configuring your MySQL user with read_user@% is not sufficient to grant access. You need to configure your user with providing the hostname of the mysql instance. Simple, mysql instance has internal ip = 10.0.0.4, means your hostname – Amazon style – is ip-10-0-0-4  (note : hyphens, not dots).

Then you simply have to configure your user as read_user@ip-10-0-0-4 then your connection will be granted !

Enjoy.