Solved

How do I connect local MySQL Developer Workbench to remote AWS hosted MySQL database?

Posted on 2014-01-14
11
1,900 Views
Last Modified: 2014-11-12
Hi:

I'm trying to connect my local (windows based) desktop copy of MySQL Developer workbench to a MySQL DB hosted on AWS (EC2 instance).

AWS/MySQL is set up such that I can connect to the database via phpMyAdmin.
So, I'm assuming that the AWS version of MySQL is set up for remote access.
I'm not fond of using phpMyAdmin's web-based interface.

What are the steps I need to do connect to the AWS MySQL DB through my desktop version of Developer Workbench. I know that I've been able to get this working in the past.

Thanks in advance for your time.

JohnB
0
Comment
Question by:jxbma
  • 6
  • 4
11 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39780871
See if phpMyAdmin shows you an IP address for the MySQL server.  Try using that along with your username and password.  Note that if your only login is for 'localhost', you will not be able to make a remote connection.
0
 
LVL 1

Author Comment

by:jxbma
ID: 39781009
This is how I access the AWS based MySQL DB through phpmyadmin:

https://generatedname.mx1.ch/phpmyadmin/

I don't explicitly see any reference to an IP address.

On a previous project, I believe I was able to copy/paste the URL into the Hostname field of the Manage Server Connections dialog.

I looked at the MySQl--> my.cnf file on the server.
It doesn't look like it's got external access turned off.
The port number is the same.

How do I proceed?

Thanks,
JohnB

JB
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39781098
On the main page in phpMyAdmin, there is a line showing the 'server' and another showing the 'user'.  MySQL users are defined by username, password, and access method.  Many places only allow 'localhost' access as a means of preventing unwanted external access.

In the link below, 'monty'@'localhost' and 'monty'@'%' are two different users although we often set them up with the same password.  'monty'@'%' is allowed to connect from anywhere but 'localhost'.  And 'monty'@'localhost' is Only allowed to connect from 'localhost' and Not remotely.  'monty'@'%' can also be 'monty'@'12.34.56.78' which is some specific IP address.

http://dev.mysql.com/doc/refman/5.6/en/adding-users.html
0
 
LVL 1

Author Comment

by:jxbma
ID: 39781388
OK. So I added a new user called root to the MySQL DB through phpmyadmin:

I used the following commands:

CREATE USER 'root'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Open in new window


I still get the following error message:
"Failed to Connect to MySQL at https://generatedname.mx1.ch:3306 with user root"
Unknown MySQL server host 'https://generatedname.mx1.ch' 

Open in new window


If I look at the user table the privilege columns look pretty much the same as what I see for  "user@localhost".

I think the my.cnf file looks OK.
I've attached a copy of it to this comment.

Since I have admin privileges on the VM and in MySQL, I should be able to set this up correctly?

OK, so within the my.cnf file, I see the following line:
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

Open in new window


Uhg.
What am I doing wrong here?
I'm assuming I somehow need to change/disable this  to something and restart the MySQL Server?

Thanks,
JohnB
my.cnf.txt
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39781436
Yes, change it to '0.0.0.0' to allow any IP to access the server.  It is Strongly recommended that you do not allow 'root'@'%'.  'root' has all privileges on all setting on all databases on the server and it is considered very insecure to allow remote access for 'root'.

Info on the bind-address here: http://dev.mysql.com/doc/refman/5.6/en/server-options.html
0
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 1

Author Comment

by:jxbma
ID: 39781453
Hey David:

This is a dev instance, so I'm not quite as worried about security right now.

Questions:
--------------

1) I stopped/started the MySQL service, changed the bind address to 0.0.0.0,
    I still can't connect. It's not recognizing the server host.

2) Should I be creating my MySQL Workbench connection using Standard (TCP/IP) or Standard TCP/IP over SSH? (I do have a ppk key file that I use to connect to basic AWS instance with WinSCP and PUTTY)

3) Regarding the "root" user. Should I create a different user with @'%'?

At this point, I really, really want to get this up and functional for development purposes.

Thanks,
JohnB
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39781479
I would use Standard without SSH.  SSH requires you to set up tunneling and that just complicates things at the moment.  And yes, a different user with @'%'.

While it won't do anything but test the connection, you can try 'telnet' to that server on port 3306 just to see what response you get.  The firewall on the server or network could still be blocking access.
0
 
LVL 1

Author Comment

by:jxbma
ID: 39781508
OK. Cannot connect to that server on port 3306 via telnet.
How do I tell which end is blocking?
I looked @ event logs on windows 7 machine and don't see anything there.

JB
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39781516
It is almost certainly the remote server because (at least for Windows firewall), outgoing connections are almost always permitted.
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 39781522
0
 
LVL 33

Expert Comment

by:shalomc
ID: 39782994
You must grant ingress access to your IP address (or to the world) in the EC2 security group associated with your server.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now