mskramst
By:
mskramst

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

January 8, 2015 12.2k views

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

5 Answers

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?

  • 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!

  • 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".

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

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

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

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!

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\known
hosts 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 _connect
ssh
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.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?

  • 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.