Workbench Remote Connection to nging mysql

September 1, 2017 326 views
MySQL Nginx LAMP Stack

Hi folks, I am pretty new to this, whole server management I always user serverpilot. Now I wanted to set up It on my own. I followed this guide, https://www.digitalocean.com/community/tutorials/how-to-deploy-a-laravel-application-with-nginx-on-ubuntu-16-04 and got everything up and running with SSL.
I disabled the ssh access with password and use a ssh_key

Now I just wanted a GUI to manage the mysql database.
I tried that tut https://www.digitalocean.com/community/tutorials/how-to-connect-to-a-mysql-server-remotely-with-mysql-workbench
But I always get errors either

Can't connect to MySQL server on '127.0.0.1' (61)

or

Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I tried following things:

*
In /etc/mysql/mysql.conf.d/mysqld.cnf

  • Comment line bind-address changed address to server ip address

*

sudo ufw allow 3306/tcp
sudo service ufw restart
mysql> GRANT ALL ON db_name.* TO patrick@xxx.xxx.x.xxx IDENTIFIED BY ‘my_password/';

xx.xx.xx.xx my local IP address of laptop/desktop

Or

mysql> GRANT ALL ON *.* TO 'patrick'@'%’;
FLUSH PRIVILEGES; 

Always restart mysql-server after changing

service mysql restart

The mysql workbench parameters are:
SSH Hostname: SERVERIPADRESS:22
SSH Username: ssh username I use to connect via ssh username@serveripaddress
SSH Key File: path to .ssh/id_rsa
MySQL Hostname: Localhost or 127.0.0.1 or Empty
MySQL Server Port: 3306
Username Patrick

If I click “Test Connection” it prompts me for a password, enter the password the described errors appear.

Stuck here since 3 days.

G
patrick

Be the first one to answer this question.