Is it possible remote access mysql via Standard TCP on Workbench (without ssh)?

Posted March 27, 2017 10.7k views

My droplet is connected via ssh, i also have a firewall ufw installed.

What i tried:

Change bind address from mysql configuration file to my droplet IP, also tried change to for accepting request from any IP.

nano /etc/mysql/my.cnf ( Configuration file )

In ufw i allowed 3306/tcp for ipv4 and ipv6, if i run sudo ufw status it is looking like

3306/tcp | ALLOW | Anywhere
3306/tcp | ALLOW | Anywhere (v6)

Still can’t access mysql via Workbench with standard tcp. Am i forgetting something?
Should i do expose mysql port with nginx or someting like that?

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.

Submit an Answer
2 answers

I just realized it was a set of config together. So, let’s get started.

First of all, you have to edit mysql configuration file

nano /etc/mysql/my.cnf

So, you have to change bind-address key to droplet ip instead of (Local Only)

bind-address = YOUR_DROPLET_IP

Restart Mysql Service to apply changes

sudo service mysql restart

Now, you have to allow mysql port on ufw firewall

sudo ufw allow 3306/tcp

This will enable rules for IPV4 and IPV6 both

Now let’s create an user on Mysql, the host will be your droplet ip instead of @localhost

CREATE USER 'newuser'@'your_droplet_ip' IDENTIFIED BY 'password';

Grant permission you want, in my case, i want grant permission for all databases for this user

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'your_droplet_ip';

Don’t forget to reload mysql privileges running


Restart mysql to ensure changes will be applied

sudo service mysql restart

It’s done, now you have a database with remote access via standard tcp, you will be able to connect with your mysql just providing host, user and password.

Be aware this approach is not safe for production applications, it must be used in special cases. For production applications restrict database access only for local requests.

  • @marcosmendes

    Despite the note at the bottom, I wouldn’t recommend giving one user access to all databases, whether dev or production. That’s essentially creating a slightly less privileged root user with the power to destroy a lot of data with one single careless command.

    You can also reduce the above by one step as there’s no need to create the user first and then grant them privileges :-). You can do it all at once.

    grant all on dbname.* to 'user'@'host' identified by 'password';

    That’ll create the user and grant permissions without having to create the user first.

  • i am using heidi sql and followed all the steps.

    i am having this error when connecting:

    [DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentationGeneral network error.

You’re on the right track, but maybe the user you’re using to login to MySQL isn’t allowed to be used for outside networks?
Don’t you get any error messages anywhere when you’re trying to connect? There’s a big difference between “cannot connect” and “user/pass is not correct”.