Remote connection to MySQL

Make sure you haven't binded MySQL to 127.0.0.1 or localhost (just comment out the line for listen)

You'l also have to open the firewall to port 3306 to allow external connections without the SSH tunnel. (The SSH option in Sequel Pro creates an SSH tunnel, which lets you then connect to the server using "localhost" / 127.0.0.1).

In general the steps to allowing external MySQL connections are:

• Bind MySQL to listen on all networks. I generally edit /etc/mysql/my.cnf file and set the bind to 0.0.0.0, which listens on all networks. As @basy points out, commenting it out means it defaults to this value (see that here)

# In /etc/mysql/my.cnf
bind-address = 0.0.0.0

• Open the firewall up to allow incoming connections on the MySQL port 3306

I think forge lets you control this from the control panel so you don't have to bother with iptables / ufw (Forge I believe uses UFW to control iptables firewall settings)

• Create a MySQL user who can connect either from a specific IP or from any remote host

# Allow connection from anywhere, as denoted by `%` hostname
# or use a specific IP address of where you're connection from
# if that's known/static (unchanging)
CREATE USER 'some-username'@'%' IDENTIFIED BY 'some_password';

# Grant all privileges to a db and all tables within. All priv. is dangerous, you may want
# to select only a few for this user
GRANT ALL PRIVILEGES on your_database.* TO 'some-username'@'%';

This is generally "dangerous" in that you're allowing external connections to your database. That should raise a fairly large red flag in terms of security, but use your judgement!

@fideloper How we can allow several IP addresses for the same user? Thanks.

@fideloper @bashy Following the above steps, I ended up having the main user to be able to connect without the need of SSH after I opened the Firewall. What I'm looking to is to have only the newly created user to be able to connect without SSH. Any thoughts on this?

@alexmansour You'll need to allow the IP(s) via the firewall then.

@bashy Thanks for your reply. That's what I did and I was able to connect via this allowed IP using the main user without the need of SSH.

BTW, should I use "GRANT ALL PRIVILEGES on DB_Name.* TO 'user'@'xxxxxx' WITH GRANT OPTION; " or just the Firewall ?

@alexmansour If they need all permissions, sure... I would only allow certain ones for general users. No need to shutdown MySQL server from that user? :P

@bashy That's true. This is not my current point, I meant that both Allowing certain IP via Firewall and Giving the MySQL user access to certain 'user'@'ip' versus 'user'@'%'.

Regarding the above issue, can we restrict connection via TCP/IP for certain users or DBs and kept the others require SSH tunnels?

Here is a post I found that talks about adding remote users by IP address:

https://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

If your using a hosting control panel such as CPanel there is an option in most to allow remote access restricted by IP.

Hope this helps solve your issue.

Regards,

Ray

@alexmansour You would do it for that IP too, not % if you only want remote access, not local.

All you need to do is go in the forge interface for that server and click Network in the left menu, add a new firewall rule allowing port 3306, you can even restrict it by IP.