CODEWITHSUNDEEP
mysqlamazon-web-servicesremote-access
Jeffrey Hunter
Jeffrey Hunter

Reputation: 1163

Connect to MySQL on AWS from local machine

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?

Upvotes: 47

Views: 89897

Answers (7)

Luis Felix
Luis Felix

Reputation: 1

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.

Upvotes: 0

Stephan B
Stephan B

Reputation: 3701

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

Upvotes: 15

vaquar khan
vaquar khan

Reputation: 11449

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

Upvotes: 2

user3078359
user3078359

Reputation: 717

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

Upvotes: 4

mikemay
mikemay

Reputation: 4647

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

Upvotes: 55

chrisco
chrisco

Reputation: 832

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

Upvotes: 2

Cbox
Cbox

Reputation: 1137

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.

Upvotes: 32

Related Questions