Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 9 down vote accepted

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.
share|improve this answer
    
So, when I will use the command you gave, I'll be able to connect remotely to my mysql server, but then, local connection like website and other services wont be available until I forward the port? Sounds like a tunnel for me. –  Cyclone Feb 25 '12 at 15:19
    
And what exactly does this mean: username@mysqlhost.com it should be root@localhost for a mysql root user? –  Cyclone Feb 25 '12 at 15:40
    
@Cyclone: See my edit. –  SvW Feb 25 '12 at 17:08
    
Look what I've added: pastebin.com/VHkyhTir And I still can not connect remotely. –  Cyclone Feb 25 '12 at 17:43
    
This unfortunately not work... –  Cyclone Feb 25 '12 at 22:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.