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

MySQLmysql-workbench

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?

Best Answer

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.

Related Question