Question

Connect to MySQL via SSH from MySQL Workbench

Posted April 23, 2019 3.7k

MySQL Ubuntu 18.04

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>

By:

hellod5a9a688320fa05acc04a

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

1 answer

jarland 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

Reply Report

hellod5a9a688320fa05acc04a April 23, 2019

Thanks, tried that but unfortunately it didn’t work.
Reply Report
- jarland 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.
  
  Reply Report
- 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!
  
  Reply Report
  
  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.
  
  Reply Report
  
  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/
  
  Reply Report
  
  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.
  
  Reply Report

Submit an Answer

Looking for something else?

Ask a new question Search for more help