Symetrik Domino Consulting

I’ve been working with Amazon Web Services (AWS) Elastic Cloud Computing (EC2) quite a bit recently.

I’ve also started working with Ubuntu at the same time. The result is the ability to quickly spin up new instances of Ubuntu server to use for various reasons without all the hassle of new hardware, or even alot of configuration headache. Using Amazon Machine Images (AMIs), an administrator can spin up an instance that is already configured, patched, and ready to run.

Using this technique and technology that is new to me has created a bit of a learning curve, and one thing that caught me off guard was the need to use keypairs instead of username/password to access the servers, which is more secure, and is the default method for accessing AWS EC2 servers.

I was able to easily figure out how to access an AWS EC2 using a keypair with SSH command line, and Filezilla, but MySQL Workbench seemed a little more complex.

Above you can see the diagram, which shows my new connection dialogue screen.

Here are the fields, the content, and an explanation of each:

Connection Name: My Dev Site
Connection Method:Connection Method – You must use Standard TCP/IP over SSH. This creates an SSH tunnel first, then establishes the MySQL connection over the SSH tunnel.
SSH Hostname: ec2-46-51-137-37.eu-west-1.computer.amazonaws.com – this is the public DNS name of your Amazon Instance.
SSH Username:Depending on the operating system you are using, this will be either root, ubuntu, or another SSH user you have created.
SSH Key File:/.ec2/mydevsite_com.pem – This is the directory and filename of the keypair file that Amazon supplies you when creating your account and instances. In my case, I saved the keypair file as mydevsite_com.pem in the .ec2 directory because the .ec2 files are also where you install the EC2 tools on Mac OS X.
MySQL Hostname:127.0.0.1 – I could not get this to work with the public DNS name, it only worked for me with 127.0.0.1
MySQL server port:3306 – which is the default
username:root – this is the MySQL username. You may want to use another user besides root on your system.

When you connect to your server, it will ask you to supply the password of your MySQL username, which you may or may not to store in your MySQL Workbench password keychain.

Also, if you want to access MySQL directly via command line, you can do so through a standard ssh tunnel:

cd /.ec2
ssh -i ~/mydevsite_com.pem -L 3306:127.0.0.1:3306 root@your-ec2-ip-or-hostname

In a new terminal window, you can then run
telnet 127.0.0.1:3306 and verify you can access mysql directly.

16 Responses to “How to create a MySQL Workbench connection to Amazon EC2 server”