Access MySQL through TCP/IP over SSH

[xaman]

Hi, I managed to get remote access to MySQL database using MySQL Workbench and configuring the /etc/mysql/mariadb.conf.d/50-server.cnf to 0.0.0.0. But I’ve read that this is not secure and a better, more secure way is to use TCP/IP over SSH.

The guides that I found on the internet uses cPanel for the SSH username and password. How can I do this on HestiaCP?

[pluto]

One way to do this would be to set up an SSH tunnel. Lets say you normally connect to your Hestia instance with:

ssh -p 22 [email protected] 

You could add a tunnel to that with this

ssh -p 22 -L 3306:127.0.0.1:3306 [email protected] 

So what this does it it sets up a forward on your local machine (127.0.0.1), forwarding port 3306 on your localhost to port 3306 on the remote server.

So then you just tell Msql workbench to connect to your local machine with -h 127.0.0.1 and that will get forwarded. You’ll need to keep the ssh session open to maintain the connection.

[pluto]

Regarding firewall configuration, yes, opening mysql to the world is not recommended. However you can limit access by specifying your IP address in the firewall rule, rather than 0.0.0.0 (you may need to change this occasionally if you have a dynamic IP.
You can also specify hosts in the mysq.user table. If you do

SELECT User,Host from mysql.user;

You’ll see that each user has an entry set up for localhost and %
eg
webdev_user | % → This is open to any IP address.
webdev_user | localhost → This is open to client/websites running on localhost

I quite often remove the entries with the % as I’m never going to use them. However you could alter this for your user so it just contained an IP address or subnet, which would limit access to those IPs.

eg change % to 198.12.34.45