Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect

Question

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?

Answer 1

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.

Answer 2

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
• [email protected].% can login via TCP/IP from that netblock only
• [email protected].% 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 !!!

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

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!

Answer 9

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.

Answer 10

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.

Answer 11

I came up with the same error. My case:

• Ubuntu 18.04
• mysql workbench 8.0.18
• ssh access without password (only via public/private ssh keys)

I solved this issue after:

1. remove mysql-workbench-community (previously installed via mysql website):

   sudo apt remove mysql-workbench-community -y

2. install mysql-workbench

   sudo apt install mysql-workbench -y

3. add new connection

4. click "Store in Keychain" for mysql (not for ssh user) and set password
5. click "Test connetion"
6. after prompt password, leave input empty and check "save password"
7. click "OK"

Answer 12

Some people are finding it difficult to connect to the database via mysql workbench. In my case, this happened because I am using version 8.0, and this version does not seem to accept the .ppk format as an SSH key files.

I had to convert the key to OpenSSH format so that the workbench could accept the connection.

The step-by-step for those who don't know the use of Putty to perform the conversion:

Open the PuTTYgen program > Conversions > Import Key > 'Select your key with the .ppk format' > Export OpenSSH> Select the directory and save the new format.

Use this file into mysql workbench and it will work.

Answer 13

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.

Answer 14

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.

Answer 15

Run

SELECT user,host FROM mysql.user;

And make sure that the user that you are trying to log in as through Workbench says localhost in the host column and not %.

If it says %, fix it with:

UPDATE mysql.user SET host="localhost" WHERE user="yourusername";

(Assuming that you created this user to work with Workbench over SSH, or course, as the above will stop that user from loging in via remote TCP)

Answer 16

I stumble upon the error while connecting hosted mysql on amazon ec2 via ssh. I came to realize you have to edit the following file.

/etc/ssh/sshd_config Change the following: AllowTcpForwading from no to yes

Also check if the path to your ssh is right on this file in /ect/ssh/sshd_config. AuthorizedKeysFile /home/user/.sshkey

Then reboot your system and try.

Answer 17

For fingerprints mismatch error:

~/.ssh/known_hosts

Open with Geany or an editor that shows you the line numbers, and delete the offending line.

Answer 18

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.

Answer 19

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

For example:

ssh-keygen -t rsa -b 4096 -C "[email protected]"

Answer 20

First install puttygen

brew install putty

Once you have installed putty gen, generate the pem file from id_rsa and leave that as privatekey file

puttygen id_rsa -O private-openssh -o id_rsa.pem

Now use the .pem in Workbench