47

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?

Improve this question
4
  • just my 2 cents: did you allow the port 3306 to be open on your AWS host security group on your public ip ? Commented Dec 1, 2011 at 15:46
  • No...I mean my machine at my home...it would be a remote connection. Commented Dec 1, 2011 at 15:47
  • Check the answers: you need to open your MySQL port in associated security group. Commented Dec 1, 2011 at 15:48
  • Possibly TCP connections to MySQL are disabled for security? Check skip-networking in /etc/mysql/my.cnf Commented Dec 1, 2011 at 16:01

7 Answers 7

Reset to default
55

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I did that. And then in terminal I typed: ssh database.blablabla.us-west-2.rds.amazonaws.com:3306, but it says "Operation to the host blablabla Operation timed out"
try mysql --host blah-blah-blah.us-west-2.rds.amazonaws.com --port 3306 -u <my user name> -p
One more thing to check: when you create the RDS instance from the AWS console, you have to set the "Publicly Available" setting to yes. This must be done in addition to opening the inbound port in the security group.
5 yrs later but this is still the simple and best answer for this one. And yes the db has to be set as Yes for publicly available.
Worked like a charm fro me.
32

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.

Improve this answer

3 Comments

rather than going through the rigmarole of adding the IP address of your client to the RDS instance, I just connect to a running EC2 instance that can connect to the RDS. So your connection method in the "Manage DB Connections" dialog box in MySQL WB is "Standard TCP/IP over SSH", then specify the SSH params, and most importantly, the MySQL Hostname, which is the RDS hostname.
@mblackwell8 did anyone say anything about MySQL WB? We are trying to connect from a php application locally.
This is now out of date as there is no "DB Security Groups" section in the left panel.
15

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 : )

Improve this answer

4 Comments

How about using PHP? mysql_connect() function?
The blog entry above uses 3307 as local port so it does not interfere with a local mysql. You can use mysql_connect('localhost:3307', $user, $pw) to AWS via the tunnel then.
Thanks got it working with MySQL Workbench...still having trouble with mysql_connect but I will play with it.
link does not work anymore, please always put the relevant part in the answer and don't only provide a link.
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
Improve this answer

Comments

2

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);
Improve this answer

Comments

2

When we create RDS need to configured the firewall to accept mySQL connections from local or other instance, as such the packet is being dropped at the firewall level, to resolve this you need to:

  • Login into your AWS console Go to RDS

enter image description here

Note down the security group of your mySQL server (in my case awseb-e)

  • click security groups
  • click your group in the center menu awseb
  • click inbound tab

Select mySQL from the list, add the details of your client server and save the rule

enter image description here NOTE : once you choose my IP ,AWS select your IP , if you need other ip use

https://www.whatismyip.com/my-ip-information/

and add your IP4 IP

enter image description here

Improve this answer

Comments

0

Internet Gateway

Found out that the "Internet Gateway" for my instance was not configured correctly during the creation process.

After ensuring that the instance was set to be publicly accessible and setting up the the correct "Inbound Rules", I also had to configure the Internet Gateway, for my VPC, following the steps in this trouble shooting guide:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Troubleshooting.html#CHAP_Troubleshooting.Connecting

Make sure to write down your VPC, Subnets, Internet Gateway, and Routing Table ID's as you progress, as you'll need to reference back to them.

Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.