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

March 27, 2017 5.3k views
MySQL Firewall Debian
87cd3dbc7d416da248a0fd48f0f26f2d4bd300ba
By:
marcosmendes

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?

Log In to Comment
2 Answers
marcosmendes 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.

Reply
  • jtittle1 March 28, 2017

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

  • brandaopj January 3, 2018

    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.

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

Reply
  • marcosmendes March 28, 2017

    Thanks for answer hansen, the message was cannot connect, but i just solve it. I will let the solution for other people who has the same problem.

Have another answer? Share your knowledge.

Related Questions