Question

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

Posted on March 27, 2017
MySQL Debian Firewall
Asked by Marcos Mendes

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 0.0.0.0 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?

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

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.

Marcos Mendes • March 28, 2017

Accepted Answer

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 127.0.0.1 (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

FLUSH PRIVILEGES;

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.

Flamber Hansen • March 28, 2017

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