Connecting To a Remote MySQL Server Securely Using SSH Port Forwarding

Connecting Securely

Often times utilizing remote MySQL databases is as simply as opening a SSH session to the remote machine and typing 'mysql -u username -p' and using the MySQL command line client. Sometimes, however, this can be cumbersome and you might wish to use a GUI based management tool. This becomes a problem however if the remote database is behind a firewall that won't allow remote MySQL port connections (MySQL accepts connections on port 3306 by default) or if you're worried about your password being transmitted in clear text. This short article describes how to connect to a remote MySQL server securely. By default, MySQL clients issue passwords in clear text to the server (meaning if they are intercepted they aren't secured at all). In order to connect to a remote server you can use port forwarding. Using this method you can also bypass firewalls that allow remote SSH connections (i.e. open port 22), but block remote MySQL connection (i.e. closed or blocked port 3306).

What is Port Forwarding?

Port forwarding is a means by which to redirect connections from one port on one machine to any other port on any other machine. This is also referred to as 'tunneling.' What we're going to do in this instance is set up an SSH tunnel to facilitate our port forwarding. Using this method we'll first establish an SSH connection to the remote machine. Then, using a configuration that allows connection through a local port to be forwarded to a remote machine through the SSH tunnel we'll be able to connect to a remote port that securely. This will also allow you to connect to remote ports that might not normally be available because they are protected by a firewall.

Configuring the Connection

To begin you must first install PuTTY. You can download PuTTY from http://www.openssh.com/windows.html. Installation is trivial. Once you start PuTTY you first have to set up your port forwarding. To do this start PuTTY which will open the configuration window. Go to the Tunnels option (under Connection ' SSH) and set up a new tunnel. Fill out the form under 'Add new forwarded port'. Put '3306' in the source port, and 'localhost:3306' in the destination. Make sure the 'local' and 'auto' radio buttons are checked, then click the 'Add' button.

Next check on the 'Session' configuration (it should be at the top level at the top of the list). Type in a host name and port, make sure the 'SSH' radio button is checked, type a name in the 'Saved Sessions' dialogue and click the 'Save' button. Next highlight your newly saved session and click the 'Open' button. This should open a new session to the remote machine. You will be prompted for a username and password.

Connecting to the Remote Server (Through the Tunnel)

Now you're ready to connect with any MySQL client. Make sure any local MySQL servers are shut down to avoid any confusion and nothing else is using port 3306. One connection program you might want to use is the MySQL Query Browser available from MySQL AB's website at http://dev.mysql.com/downloads/. When you start MySQL Query Browser simply type in the connection username and password for the remote machine, but specify localhost as the host and 3306 as the port. This connection will tunnel using PuTTY's already established SSH connection from the local machine to the remote machine allowing you to use GUI managers locally.