Mysql – How to connect to MySQL remotely and still keep it safe

freebsdMySQLremote-access

As you know, keeping the MySQL port 3306 open is unsafe. However, I would like to use a good MySQL administration program instead of phpMyAdmin, which requires 3306 port to be open.

Of course I can allow only my IP address to connect to port 3306 using ipfw. The problem is that my IP address changes every 24 hours, so I would have to add a new rule for every IP address.

Isn't there a better way to keep MySQL safe and still use the remote connection?

Best Answer

Yes, with SSH port forwarding.

On your local machine, issue the command 

ssh -L 3306:127.0.0.1:3306 username@mysqlhost.com

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 username@mysqlhost.com 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.
Related Topic