Connect to MySQL on AWS from local machine

Question

I am trying to set up a dev environment on my local machine that accesses a MySQL DB on AWS, but I keep getting a "Can't connect" message.

mysql_connect('xxx.xxx.xxx.xxx:3306', 'USERNAME', 'PASSWORD');

I also commented out the bind-address in the my.cnf file, and granted permissions to the IP address that is connecting.

Anyone ever successfully get this working?

Answer 1

My experience in Aug-2013 was as follows for an RDS instance created through Elastic Beanstalk.

0) Assuming the RDS instance has already been created
1) Log in to the management console: https://console.aws.amazon.com/console/home
2) Select Services->VPC
3) Select Security Groups (on the left hand side)
4) Select the group whose description says "Security Group for RDS DB..."
5) In the Security Group Selected panel at the bottom of the page, choose "Inbound"
6) Select MySQL as the rule.
7) Type the ip address of my local machine e.g. 145.23.32.15/32
8) Click Add Rule and Apply Rule Changes

After doing this I could connect to the database using mysql from my local machine.

a) From management console select Services->RDS
b) Click on DB Instances (I have only one) and select "Go to Details Page" for the required instance
c) Obtain Host and Port from the endpoint
d) From a terminal session do soemthing like: mysql --host blah.blah.blah.us-west-2.rds.amazonaws.com --port 3306 -u my-user-name -p

Answer 2

If you are using MySql on AWS via an RDS instance you must add the IP address you want to connect from to the "DB Security Groups". To do this go to your AWS Managment Console and select RDS.
1. Select "DB Security Groups" on the left panel
2. Select "default"
3. Select "CIDR/IP" from the select box and enter your workstations public IP address. Example:
23.234.192.123/32 (dont forget the /32 for a single ip)
4. Click "Add"
5. Wait a few minutes for it to go into effect and then connect your MySql client.

This only applies for RDS instances, if you are using MySql installed on an EC2 instance then the instructions are the same as accessing MySql from any remote machine.

Answer 3

I suppose this is firewalled by Amazon, try using a SSH tunnel:

http://blogs.oracle.com/divyen/entry/connecting_mysql_server_on_amazon

Note: Do not open MySQL to the public internet, not even when using IP filtering. SSH tunnels are way more secure. Best part of it: The tunnel could be accessible with localhost:3306 on your machine, no need to change the config : )

Answer 4

I am on a Windows 7 machine, and had to make the following 3 changes to be able to connect to AWS RDB.

1. VPC Security Group update in AWS Console (similar to what mikemay has above)

   • From https://console.aws.amazon.com, click on Services (top left) and choose VPC.
   • Next select Security Groups
   • Click on the Security Group which has the description "Security Group for RDS DB..."
   • On the "Inbound" tab, choose "MYSQL" in the Create a New Rule dropdown.
   • Add your IP address in CIDR format and click on Add Rule.
   • Click on Apply Rule Changes.

2. my.cnf update in local MySQL configuration

   • Change "bind-address = 127.0.0.1" to "bind-address = 0.0.0.0"
   • Comment out "skip-networking"

3. Turn OFF Windows Firewall

   • Go to Control Panel/System and Security/Windows Firewall and turn OFF Windows Firewall.

After these changes, I am able to connect through both

• MySQL WorkBench using Database->Connect to Database

• Command Prompt with

  mysql.exe -h <AWS DB Endpoint> -U <UserName> -P <Port Number, likely 3306> -p
  

Answer 5

I have been using MySQL Workbench http://www.mysql.com/products/workbench/ with RDS and it works great. Very easy to create and save a new database service instance. Click "New Server Instance" under "Server Administration" and follow the prompts. You will need to enter the information provided in the AWS RDS webpage for that instance (for example, it's endpoint).

NOTE: In order for you to actually connect, you MUST add your IP address in the "DB Security Groups." The link is in the left-hand column, which is titled "Navigation." I use the "CIDR/IP" option (the other is EC2 Security Group). Make sure to include a "/##" after the IP, such as the "/32" they use in the example (you will see it on the page). In a few seconds, the IP address should be authorized.

After that, go back to MySQL Workbench and complete the New Server Instance creation process.

To use the connection, your code might look something like this (that excerpts of my Java code):

String url = "jdbc:mysql://yourdatabasename.foo.us-east-1.rds.amazonaws.com:3306/";
String userName = "your_user_name";
String password = "your_password";
String dbName = "your_db_name";
String driver = "com.mysql.jdbc.Driver";
Connection connection = DriverManager.getConnection(url + dbName, userName, password);