Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on?

I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL Workbench (PC) offers to make a connection via TCP over ssh. It runs on port 3306 on the remote server where command-line mysql works fine.

I used the following session details:

  • Connection Method: TCP/IP over SSH.
  • SSH Hostname: mysql.myhost.com:3306
  • SSH username: my linux login
  • SSH public key file: my local public key file
  • MySQL hostname: 127.0.0.1 MySQL
  • Server Port: 3306
  • Username: root

I get an error message when I try to connect: "Failed to connect to MySQL at 127.0.0.1:3306 through SSH tunnel at mysql.myhost.com with user root"

"Can't connect to MySQL server on '127.0.0.1' (10061)"

As another test - I set up a SSH tunnel with port 3306 using Putty and I can connect OK using MySQL Workbench through that tunnel which forwards connections to my local 3306 to the remote server as described above. But I can't get "TCP/IP over SSH" working in Workbench.

Secondary question: when Workbench asks for "Path to SSH public key file" doesn't it really need my private key file?

share|improve this question
1  
Good grief. bugs.mysql.com/bug.php?id=61368 shows that IS a PRIVATE key file that is needed in OpenSSH format. I wondered about that but was unsure. –  Dizzley Jun 26 '11 at 14:09
    
It is a simple problem, but make sure your fields don't have any extra spaces in them. –  kabucey Jul 13 '12 at 14:00

6 Answers 6

I think the TCP/IP over SSH approach works by establishing a "normal" SSH connection underlying the MySQL connection (in the same way as you would tunnel using -L with the OpenSSH command-line client).

Therefore, you'd need to specify a connection to an SSH server on server via which you're establishing the tunnel. Here, you seem to be using mysql.myhost.com:3306, which would imply that you're running this SSH server (not MySQL) on port 3306.

It's possible to bind a MySQL server on 127.0.0.1:3306 and an SSH server on your external IP address for mysql.myhost.com on port 3306, but that's very unlikely. I guess your SSH server is listening on port 22 (the default).

You should probably use mysql.myhost.com:22. (Check you can connect to it via a normal SSH client such as Putty too.)

share|improve this answer

You may need to check the users in the mysql.user table.

Run this query:

SELECT user,host FROM mysql.user;

You should see something like this:

mysql> SELECT user,host,password FROM mysql.user;
+------------------+-------------+-------------------------------------------+
| user             | host        | password                                  |
+------------------+-------------+-------------------------------------------+
| root             | localhost   | *7A670E02260CDEEFF062DD08F3A6F6DA079998CB |
| ping             | %           | *124E1DB56CC8D6E2FEE8315BB2544BF04B980DB6 |
| admin            | 10.67.135.% | 1a6858054a41fede                          |
| icorbin          | 10.67.135.% | 366ed93a7396650e                          |
+------------------+-------------+-------------------------------------------+
4 rows in set (0.00 sec)

Please notice that

  • root@localhost can login from localhost only.
  • ping@'%' can login via TCP/IP
  • admin@10.67.135.% can login via TCP/IP from that netblock only
  • icorbin@10.67.135.% can login via TCP/IP from that netblock only

If you want root to connect via TCP/IP you must specify IP address or netblock for a root user.

Something like this:

GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'whateverpassword';

or if the root password is the same for root@localhost then

GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY PASSWORD '*7A670E02260CDEEFF062DD08F3A6F6DA079998CB ';

CAVEAT : root@'%' is normanlly not recommended. Maybe try root@'10.%' or any other netblock for root.

Give it a Try !!!

share|improve this answer
2  
Shouldn't ...@localhost work via the SSH tunnel, since as far as the MySQL server is concerned, the connection comes from the end of the tunnel? –  Bruno Jun 30 '11 at 16:56
    
@Bruno : One sure way to know is to successfully connect and then run SELECT USER(),CURRENT_USER(); and see what it outputs. The function USER() echoes what you attempted to authenticate as, while CURRENT_USER() echoes what MySQL allowed you to authenticate as. If CURRENT_USER() echoes root@localhost, then the answer to your question is yes. –  RolandoMySQLDBA Jun 30 '11 at 17:43

I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration.

  1. I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect.
  2. I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.
  3. Finally I have the following entered in MySQL WorkBench.

    • SSH Hostname: 192.168.0.8:22 (my SSH server listens to port 22)
    • SSH Username: sshuser
    • SSH Key File: *C:\Users\windowsuser\.ssh\id_rsa* (should be private key, even though it says public)
    • MySQL Hostname: 127.0.0.1 (this should not be changed, since MySQL server by default is bound to localhost only which I didn't change)
    • MySQL Server Port: 3306 (also default)
    • Username: root

The only remaining thing for you is to configure correctly your SSH server to work with keys rather than passwords. Hope this will help someone.

share|improve this answer
1  
This is awesome... short simple and to the point! –  TacB0sS Aug 26 at 21:05

Just had this same issue on Ubuntu machine connecting to a server running MySQL version 5.5.29 and MySQL Workbench 5.2.40. The SSH server requires the use of a ssh-key.

I wasn't able to connect to the MySQL server using the root user, instead I had to create a separate non-root user to use for the login. After that I was able to connected just fine.

Hope this helps.

share|improve this answer

You're trying to connect to the server via ssh but using the mysql port. The port you want is whatever your ssh server is listening on, typically 22, then localhost and 3306 for mysql hostname and port.

share|improve this answer

OK, I know this is an old question, but I pulled my hair out over this for hours. I checked everything mentioned by Bruno and Eye and it all seemed good. Then I realized it really was a private/public key thing. So I fired up Pageant and added my private key, so that it create a public key which MySQL Workbench could read and voila, connected! (It was actually kind of anticlimactic when MySQL Workbench actually started working, but in a happy way.)

TLDR: Use Pageant to generate a public key from your private key.

share|improve this answer
    
Private keys should never be used as public keys that's why they are private. –  James Anderson Jun 3 at 17:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.