How to Connect to a MySQL Server Remotely with MySQL Workbench

For anyone who has working SSH connection but still get connection errors after following the above. These two things helped me fix:

• Generate a private SSH key without passphrase
• When using Puttygen, import your existing private key, then export it as OpenSSH. I previously used simply save private key and it didn’t work with Workbench (more info here https://www.digitalocean.com/community/questions/trying-to-connect-to-mysql-database-server-through-mysql-workbench-using-ssh)

When your key file was generated by Putty it won’t work with Workbench. You have to import it to Puttygen once again and then export it to the OpenSSH format. Then you can use this file at Workbench (you do not have to remove the passphrase in order for it to work).

Worked for me when I did it, hope it works for you!

In the past I’ve always used the LAMP Stack and phpMyAdmin for all of my projects. However, I just started using LEMP instead and encountered problems getting phpMyAdmin to work with the new tech stack. So I decided to give MySQL Workbench a try, but ended up running into a whole bunch of problems getting it set up.

However, now that I have everything all figured out, I figured I should leave a comment with some information that is missing from this article that was critical for me getting this all setup:

If you are using the LEMP stack on DigitalOcean, you will likely need to create a username for a database administrator to access MySQL. So you’ll still use ‘root’ for your ssh username, but you will need to create a username to provide as the MySQL database administrator. When using MySQL Workbench, you’ll be asked for BOTH names.

Setting up a username (and password) for a database administrator is easy. Log into your server via the terminal using ssh, and then simply enter in a few commands:

$ mysql -uroot -p
mysql> CREATE USER 'developer'@'localhost' IDENTIFIED BY 'dev_password'; 
mysql> CREATE USER 'developer'@'%' IDENTIFIED BY 'dev_password';
mysql> GRANT ALL ON *.* TO 'developer'@'localhost';
mysql> GRANT ALL ON *.* TO 'developer'@'%';
$ sudo service mysql restart

REFERENCE: https://stackoverflow.com/questions/5755819/lost-connection-to-mysql-server-at-reading-initial-communication-packet-syste/38070979#38070979

thank you for this tutorial Did you now how to connect into database in java program?

Make sure you change the default bind-address in MySQL settings in your droplet.

A few other sources I found online said to change this in the my.cnf file located in /etc/mysql/ however when I looked it was not located there.

Navigate to the /etc/mysql/mysql.conf.d/ directory and edit the mysqld.cnf file by entering the following command nano mysqld.cnf (May need to use sudo command, can’t remember)

Change the line that says bind-address = 127.0.0.1 to bind-address = 0.0.0.0

This will allow your server to accept connections other IP addresses.

Hope this helps others

Tried this out, but when I try to test the connection I get:

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

Looking in my logs: (obfuscated my IP)

17:23:40 [INF][     SSH tunnel]: Opening SSH tunnel to 45.55.111.111
17:23:40 [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:23:40 [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:23:41 [INF][     SSH tunnel]: TunnelManager.wait_connection authentication error: Authentication error, unhandled exception caught in tunnel manager, please refer to logs for details
17:23:41 [ERR][     SSH tunnel]: Authentication error opening SSH tunnel: Authentication error, unhandled exception caught in tunnel manager, please refer to logs for details

Anybody else find a solution in this case?

I have same problem. Not connecting to remote server. Do I have to enable remote connection On on mysql database?

Hello, it did not work!!! I have run many tutorials, but the same error: lost connection to mysql server at ‘reading initial communication packet’ system error 0 I do not know what to do to fix it. I need help!