I'm trying to connect to my database via SSH tunneling from one of our web app servers with MySQL Workbench. Here's the basic config; note that I changed some values in the screenshot for security reasons.
The problem is every time I try to connect over an SSH tunnel from one of our app servers, I get the following error:
Failed to connect to us-east-1.amazonaws.com through SSH tunnel at computer.amazonaws.com with user social_shop_prod. Can't connect to MySQL server on 127.0.0.1.
However, if I use the same credentials over SSH via the following command line:
mysql -u social_shop_prod -h us-east-1.amazonaws.com -p
I can connect successfully and get the MySQL interactive command prompt.
Been talking with the rest of my development team here and none of us can figure out why I can't tunnel over SSH from our app servers with Workbench; but when I SSH to one of our app servers and connect to MySQL via the command line; I can connect successfully.
And why is it trying to connect over 127.0.0.1? I didn't specify that in the configuration; nor is my hosts file redirecting the domains shown below to that IP.
Any constructive input is greatly appreciated.
Best Answer
Since you are connecting through a SSH tunnel, this means that the MySQL port 3306 from us-east-1.amazonaws.com is being opened locally on your computer. The ip address of your computer is 127.0.0.1 or localhost. When you connect to the mysql server on us-east-1.amazonaws.com, you're actually accessing it via 127.0.0.1, i.e. your computer. If you had another tunnel open, or MySQL running locally on your computer, then it may be that other MySQL server that's rejecting your authentication attempts
There are some tests you can try :
1. What ports is you Windows computer listening on
From a command prompt : netstat -a (lists all the ports that are open)
In linux it would be : netstat -tlpn
2. Basic connectivity test
From a DOS command prompt or linux console : telnet 127.0.0.1 3306
If you get a time out, or some other program responds then your tunnel isn't setup correctly.
3. Change the port number that MySQL Workbench is opening locally
We're assuming that MySQL workbench is creating the tunnel on your computer. If so, in MySQL workbench, try tunneling through another port number like 9000.
Make sure that 9000 wasn't listed as an open port from : netstat -a
4. Try connecting to MySQL from us-east-1.amazonaws.com
mysql -u myuser -h 127.0.0.1 -p
And as Rolando said, you'll want to verify that you are connecting with the right credentials. For example, if you are connecting as myuser@127.0.0.1 and you have a user myuser without a host, you probably won't be able to connect using user@127.0.0.1.