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

January 8, 2015 19.6k views
mskramst
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

Log In to Comment
5 Answers
kamaln7 MOD January 9, 2015

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?

Reply
  • mskramst January 14, 2015

    Awesome! Worked first time trying. Thanks! Used server root password and mysql root password. Though I love improving my SQL queries long hand, having an actual interface is so much simpler!

  • michaelf January 21, 2016

    Thanks for this. I spent about 3 hours trying to figure this out, I was editing my.cnf ,adding and removing users to no avail. For other having issues with Workbench (using build 6.3.6) using the OpenSSH private key worked and you do NOT have to make changes my.cnf.

    My only question for kamaln7 is using this method (standard TCP/IP over SSH) secure? I'm pretty sure it is but would like confirmation. Also, there are a number of community questions regarding this topic (connecting to MySQL Workbench) with various suggestions/edits/etc. It would be very helpful to see a DO article "How to connect to MySQL Workbench".

  • axelkreative June 11, 2016

    Converted my private key to OpenSSH format then it's worked. Thanks!

  • bagman1673 September 24, 2017

    Awesome post! Wasted a lot of time trying to get MySQL workbench to use SSH keys with no luck whatsoever. This worked first time.

  • pocallaghan881 April 15, 2018

    Using the mysql user "admin" and the password which is provided in the welcome message helped too.

    Couldn't get in using "root", using "admin" worked.

  • michat March 19, 2019

    Oh my GOOOOD! this works and you made it look so easy.

info3802bb8e18f December 20, 2015

I have been searching for hours and kamaln7's suggestion worked! Thank you very much!

Reply
kappiii January 12, 2016
[deleted]
Reply
skmurthy October 21, 2016

I tried the above steps and not working for me. The steps I followed are as follows:

  1. Open puttygen
  2. Load private key
  3. Go to conversions
  4. Click Export OpenSSH key
  5. Once prompted save the key as private_key.ppk
  6. In workbench use the this key and try logging in.

In step 5 Instead of saving as private_key.ppk, I even tried to save without .ppk extension. Both did not work. is there anything I am missing here. Thanks!

Reply
cutups May 20, 2017

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.waitconnection 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:connectssh:288]: IOError, probably caused by file C:\Users\cutups\AppData\Roaming\MySQL\Workbench\ssh\knownhosts not found, the message was: [Errno 2] No such file or directory: u'C:\Users\cutups\AppData\Roaming\MySQL\Workbench\ssh\knownhosts'
17:38:49 [ERR][sshtunnel.py:notifyexceptionerror:233]: Traceback (most recent call last):
File "C:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE\sshtunnel.py", line 298, in _connectssh
lookforkeys=haskey, allowagent=haskey)
File "C:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\client.py", line 301, in connect
t.startclient()
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?

Reply
  • janssencanturias June 30, 2017

    I'm running in the same problem, I've researching about this for more than 2 days. Forgive me for being NOOB. I'm new to Digital Ocean and SSH things :(

Have another answer? Share your knowledge.