How can I connect to MySQL remotely and still keep it safe?

Solution 1:

Yes, with SSH port forwarding.

On your local machine, issue the command

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

and then you can connect your local MySQL admin tool to port 3306 on your local machine. You have to consider though that for the MySQL the connection appears to be coming from 127.0.0.1 as well, so the access rights for your MySQL user most be set accordingly.

If you happen to use a MySQL server on the local machine, port 3306 will be already taken, but you can just use a different port for the forwarding (13306:127.0.0.1:3306) and then point your admin tool to that different port.

(More info with man ssh).

Edit Some more explanations as an answer to your comments:

1. You will use this command on your local machine where you usually work, just before you want to do some administrative work on your database. What is does is to create a tunnel via SSH that forwards all traffic going to a port on your local machine to another port on another machine.
2. In this case, the tunnel ends on the MySQL server, which you contact directly via SSH. The part [email protected] is just a placeholder, replace it with the username of your shell account and the IP address or host name of MysQL server. From your local machine, that will definitively not be localhost though. This part is not at all about your MySQL username, because SSH doesn't know about MySQL.
3. If you work as root on your database, you will connect your MySQL admin tool to port 3306 on the host 127.0.0.1 and use the username root. Although you work from a remote host, for the MysQL server the connection appears to coming from localhost, so the access permissions must be set accordingly.
4. SSH port forwarding / tunneling is an extremely powerful tool. You should definitely read more about it.