Question

Trying to connect to MySQL database server through MySQL Workbench using SSH

  • Posted on January 8, 2015
  • mskramstAsked by mskramst

I am having difficulty connecting to MYSQL database using Ubuntu server with Drupal application using SSH. I guess I’m not sure of the settings to put into the config screen.

SSH Hostname: my ip address SSH Username: root SSH Password: my server root pw SSH Password: Linked to putty key MySQL Hostname: 127.0.0.1 MySQL Server Port: 3306 Username: root password:MySQL pw

This does not let me in. Any suggestions? Ideas? I would like an easier way to access the databases without using terminal.

Also had trouble installing phpmyadmin on this pre-configured server.

Thanks, Mike

Show comments

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

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.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

Try converting your PuTTy key to the OpenSSH format:

  1. Open PuttyGen
  2. Click Load
  3. Load your private key
  4. Go to Conversions->Export OpenSSH and export your private key
  5. Leave the SSH Password field blank and select the converted private key

Does that work? Also, are you using MySQL’s root password or your Drupal MySQL user’s password?

I had done all of the above and still was not able to connect workbench to the mysql db in my lemp droplet.

For the default lemp droplet on the marketplace, the root mysql user is configured to authenticate via auth_socket instead of password. This allows you to connect using the mysql prompt when connected to the droplet via ssh, but not from outside (even though the ssh tunnel connection part of your workbench connection profile is working). If you are getting an error from mysql workbench when trying to connect of: Access denied for user 'root'@'localhost', this is likely the cause.

To fix it, follow the instructions at Configuring Password Access for the MySQL Root Account here: https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-on-ubuntu-20-04#step-2-—-adjusting-user-authentication-and-privileges to change from auth-socket to caching_sha2_password or the older mysql_native_password and you should be able to connect. You can change it back to auth_socket later when you are done if you are concerned about security.

This looked promising to me, but i’m unable to connect using this method.

When i test the connection it tells me:

Could not connect the SSH Tunnel Authentication error, unhandled exception caught in tunnel manager, please refer to logs in details

In the mysql workbench logs i see:

17:37:43 [INF][ SSH tunnel]: TunnelManager.wait_connection authentication error: Authentication error, unhandled exception caught in tunnel manager, please refer to logs for details 17:37:43 [ERR][ SSH tunnel]: Authentication error opening SSH tunnel: Authentication error, unhandled exception caught in tunnel manager, please refer to logs for details 17:38:49 [INF][ SSH tunnel]: Existing SSH tunnel not found, opening new one 17:38:49 [INF][ SSH tunnel]: Opening SSH tunnel to xxx.xxx.xxx.xxx:22 17:38:49 [WRN][sshtunnel.py:_connect_ssh:288]: IOError, probably caused by file C:\Users\cutups\AppData\Roaming\MySQL\Workbench\ssh\known_hosts not found, the message was: [Errno 2] No such file or directory: u’C:\Users\cutups\AppData\Roaming\MySQL\Workbench\ssh\known_hosts’ 17:38:49 [ERR][sshtunnel.py:notify_exception_error:233]: Traceback (most recent call last): File “C:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE\sshtunnel.py”, line 298, in _connect_ssh look_for_keys=has_key, allow_agent=has_key) File “C:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\client.py”, line 301, in connect t.start_client() File “C:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\transport.py”, line 461, in start_client raise e SSHException: Incompatible ssh peer (no acceptable kex algorithm)

17:38:50 [INF][ SSH tunnel]: TunnelManager.wait_connection authentication error: Authentication error, unhandled exception caught in tunnel manager, please refer to logs for details 17:38:50 [ERR][ SSH tunnel]: Authentication error opening SSH tunnel: Authentication error, unhandled exception caught in tunnel manager, please refer to logs for details

Any ideas on what this might be?