Goal: Connect to MySQL server securely using SSH.

Instead of opening a port to connect directly, a SSH tunnel will be created first that will carry out all the communication securely between my laptop and remote server.

I have this setup:

1. Laptop:
   • Windows 7.
   • SQLyog Community edition (version 8.18, I really like this version)
2. Server:
   • Ubuntu
   • SSH enabled
   • MySQL server listening in localhost traditional 3306 port.

Alternative, unsecure method:

1. Bind mysql to listen to any network card
2. Allow the user to connect from any ip (% wildcard).
3. More details can be found here for example.

A better approach: using the SSH tunnel. What’s missing? Following the instructions from this blog entry, pretty much I set it up without problems. The only difference is that I use Git for windows as my environment to create the tunnel and SQLyog as my tool.

Since I already have a local MySQL service running in my laptop, instead of having the local port listening to the traditional 3306, I will have it listen on 3307, it will also make the example a little bit clearer.

On your gitbash type:
ssh -L 3307:localhost:3306 username@remoteserver.com

Where username is the name of the account at the remote server; and remoteserver.com should be replace either by the IP or name of your remote host. The only caveat is that even if you add -N as somebody pointed out in the comments from the link provided, in Gitbash it seems that the process won’t run in the background, thus, if you close your window, the connection will be lost. In my case, I am fine with another window running the process.

After you have successfully logged in to your remote host, open SQLyog and create a connection, something like:

Connection details

Behind the scenes, this is what’s happening:

1. Your SQLyog connects to localhost on port 3307.
2. The ssh connection (bound to port 3307 locally) forwards that to the remote server (no more plaintext going thru the Internet).
3. The ssh service at the remote server creates a local connection to the mysql service. This is done transparently, there is no need to configure anything for the ssh service nor the mysql service.

That’s it! Your connection is now secure you have some extra benefits:

1. You can bind your mysql service to localhost, no need to open the service to listen on all IP addresses.
2. You don’t have to add any other users to your mysql server, use the ones that are created for localhost, you can even use your root user to do some management tasks.

This video guides you to do it using putty and MySQL Query Browser: http://www.youtube.com/watch?v=kJqTLCXPzg4

Enjoy!