Connect to MySQL via SSH from MySQL Workbench

April 23, 2019 1.3k

MySQL Ubuntu 18.04

By:

hellod5a9a688320fa05acc04a

MySQL Workbench throws: “Failed top Connect to MySQL at 127.0.0.1:3306 through SSH tunnel at root@xxx.xx.xx.xxx with user root”

I can SSH into the server using ssh key and I can login to the MySQL using the credentials when the LAMP droplet was created.

Workbench (8.0.15) settings are:
SSH Hostname: <droplet ip>
SSH User: root
SSH Key: <my ssh key>
MySQL Hostname: 127.0.0.1
MySQL port: 3306
Username: root
Password: <droplet mysql pass>

Log In to Comment

1 Answer

jarland MOD April 23, 2019

Greetings!

You may need to enable port forwarding in the SSH config. Try this:

echo "AllowTcpForwarding yes" >> /etc/ssh/sshd_config
systemctl restart ssh

Then try making the connection again in your application and see if that helps.

Jarland

hellod5a9a688320fa05acc04a April 23, 2019

Thanks, tried that but unfortunately it didn’t work.
- jarland MOD April 23, 2019
  
  Hmm. Any chance Windows firewall or internet security software could be blocking the port? I assume the tunnel exposes the port to the OS and then connects, and that it’s not all sandboxed.
- asciant April 23, 2019
  
  It is possible that MySQL is limiting hostname access on the root account (tunnelling is meant to get around this).
  
  You could try running the following query from the MySQL terminal:
  
  SELECT user,host FROM mysql.user;
  
  You might find from the output that root is only allowed to connect to the hostname localhost.
  
  Which may be quickly fixed by changing MySQL Hostname: 127.0.0.1 to MySQL Hostname: localhost.
  
  Good luck!
  
  hellod5a9a688320fa05acc04a April 24, 2019
  
  Don’t think it’s a firewall issue, tried on Ubuntu and OSX (with Sequel Pro) and over a 4G network.
  Switched between 127.0.0.1 and localhost and still the same issue.
  SSH’d in and tried connecting to mysql using the root mysql created during the droplet creation and can confirm it works.
  
  asciant April 24, 2019
  
  You may have already covered this one off, but one thing that really irked me about MySQL workbench is that you have to supply it the private key for some reason.
  
  Is it possible that you have SSH Key: <my ssh key> as the .pub file (idrsa.pub), and not the (idrsa) file?
  
  There are some screenshots on this article: https://wavemotiondigital.com/tutorials/connecting-to-databases-via-ssh-with-mysql-workbench/
  
  hellod5a9a688320fa05acc04a April 24, 2019
  
  Yeah, I’m using the id_rsa key in both workbench and Sequel Pro.
  
  OK, finally got it working by created a new mysql account.

Have another answer? Share your knowledge.