39

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?

14 Answers 14

27

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.

  • One thing I needed to do on the server side was to make sure the /etc/ssh/sshd_config had was this line: AuthorizedKeysFile /home/root/.ssh/authorized_keys and that the authorized_keys had my PUBLIC key as an entry. – RyanNerd Dec 15 '15 at 6:31
  • Please clarify if step 2 which set AllowTcpForwarding yes is applied to the remote server i.e. the host that has MySQL instance we're trying to connect to; or the local machine with MySQL Workbench installed – Nam G VU Apr 11 '17 at 3:45
  • @NamGVU step 2 applies to the remote server where MySQL is installed. Particularly to the OpenSSH server that provides tunneling to MySQL through SSH. – Eye Apr 11 '17 at 4:54
  • I tried but still fail to tunnel. MySQL Workbench tells me to read more error detail in the log file. May you know where to read? – Nam G VU Apr 11 '17 at 4:58
  • 1
    I got it work today - need a reboot after configuring the AllowTcpForwarding entry – Nam G VU Apr 12 '17 at 10:40
8

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

7

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 !!!

  • 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
3

You may be using an older version of MySQL Workbench and need to update. This is a bug in version 6.0.8, which is currently the version in Ubuntu repositories. Updating to version 6.3.6 fixed this for me.

Downloads here: http://dev.mysql.com/downloads/workbench/#downloads

2

One thing that isn't mentioned in any other answer is the importance of the OpenSSH format for the key as stated on SO (https://stackoverflow.com/questions/34504232/mysql-workbench-failing-to-connect-via-ssh-due-to-key/38108623#38108623).

Despite the answer there, I was able to use a password protected key with MySQL Workbench 6.3.7 (64 bit, Windows 10).

2

My problem was due to the fact that I was trying to use an ed25519 SSH key. I noticed this error on the SSH server in auth.log:

sshd[25251]: Connection closed by 192.168.x.x [preauth]

Once I switched to using an RSA key, everything worked as expected.

1

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.

1

I faced the same problem. I checked and tried to set AllowTcpForwarding Yes but it was missing in my sshd_config so no help. make sure the ssh hostname is NOT the same with the mysql hostname(use localhost).

In workbench, choose + to add new connection and set the following:

  • connection method: standard TCP/IP over SSH
  • SSH Hostname: 192.168.0.50:22 (place remote SSH server IP and port(optional))
  • SSH Username: sshuser
  • You can set password or add at the prompt
  • MYSQL Hostname: localhost or 127.0.0.1
  • MYSQL Server port:3306
  • You can set password or add at the prompt

Test connection. It should be successful then hit OK.Viola!

1

Sometimes keys created by PuTTY won't work. Use ssh-keygen on the Linux box to create a key pair. Copy the contents of the new id_rsa to a text file on Windows. Make sure to add the contents of id_rsa.pub to authorized_keys on the Linux box. All other defaults in Workbench are fine including 127.0.0.1 for MySQL Hostname. Of course it has to be Standard TCP/IP over SSH.

1

I came up with the same error. The problem is "somewhat" the timeout. I cranked even the value up to 120 seconds which did not help.

In my case I could solve it doing a nslookup myserver.com and using the IP address instead of the hostname. My assumption is a problem trying to connect from IPv4 to IPv6.

0

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.

0

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.

  • Private keys should never be used as public keys that's why they are private. – James Anderson Jun 3 '14 at 17:48
  • @JamesAnderson isn't that what the bug is about? The text is asking for private, it should read public...at least according to the bug link. Or not? – Thufir Mar 8 '15 at 1:44
-1

Only what I found... often I'm creating users on SSH server without shell (such as /sbin/nologin) to prevent them be able to login to a server and create files and etc there... (for production systems we're doing it on firewalls).

In a regular Linux environment after that, you still can forward ports after that such as:

ssh -Nf -L 3306:%mysql_ip%:%mysql_port% %ssh_host%

and after that connect to it from local workstation as:

mysql -h localhost:3306 -u %mysql_user% -p

But workbench giving an error that it can't connect to MySQL... If you will change the shell for that user to, let say, /bin/bash - everything works fine after that.

No idea why Workbench is requiring local shell on the remote SSH server.

-1

Just create a new RSA key with the format correct to mysql workbench.

For example:

ssh-keygen -t rsa -b 4096 -C "your_email@example.com"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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