How to connect to a MySQL database using an SSH Tunnel
Updated 2 years ago
by
Ben Oates
Connections to MySQL servers over port 3306 are not encrypted, and so this presents a real security risk. Indeed, some credit card processors will not provide user accounts to websites that are hosted on servers that allow unencrypted connections on port 3306.
This article will describe the steps required in order to connect securely to your web hosting account's MySQL server using an SSH Tunnel. This is an advanced topic and assumes that you have some experience of connecting remotely to MySQL servers, TCP/IP, and SSH.
If you are using a Mac, don't worry - you already have everything you need.
What we are trying to achieve
SSH (Secure SHell) is a secure method of connecting to another computer. You may know that SSH allows secure terminal sessions and secure FTP (SFTP) connections, but it can also be used in other ways. An SSH connection can also serve as a secure Tunnel, through which other data can be securely exchanged.
Like any tunnel, it has two ends. In the case of an SSH Tunnel we specify a Port at each end. In the case of MySQL the port at the server end would be 3306 (the standard port for MySQL). At the client end, it could be anything you like, but in this article we will stick with 3306 also (if you are running another MySQL server locally, you might want to change the port to another number).
Mac Users - Creating the SSH Tunnel
Open the Terminal application (using Spotlight, or go find it in Applications/Utilities). Then run the following command.
When you hit return, you will be asked for your password (unless you have setup SSH keys on your Mac - link opens in a new window) - just enter your password and hit return - the terminal won't do anything else, so just minimise/hide the window.
That's it! You can now connect to MySQL from your local computer using 127.0.0.1 (some GUIs will connect using localhost or 127.0.0.1) as the server name on port 3306 and you will be connected securely (via the tunnel, to the server). If you wanted the connection to be permanent until you next log out, then add the -f switch:
Note : It is important that you do not have an existing instance of MySQL listening on port 3306. Either stop it before running the tunnel above, or, change your connection command to something like:
This will present the local end of the tunnel on your computer on port 3333 instead.
Windows Users
You'll need to use PuTTY to create the SSH tunnel and we have separate guide that details how to Install and setup PuTTY.
Once you've got PuTTY installed come back here to continue the configuration for remote MySQL access.
Open PuTTY and enter your domain name, Home Server name, or server's IP address in the Host Name (or IP address) field. Set the Port to 722.
Click on the SSH configuration category. Tick the Don't start a shell or command at all check box (you can skip this step if you also want access to the server's command line).
Click the Tunnels category. Enter 3306 in the Source port. Enter localhost:3306 in the Destination. Click the Add button.It should end up looking like this:
Now scroll back up to the Session options again. Enter a name for your new connection in the Saved Sessions field. Click the Save button. Click the Open button to start the Tunnel!You will now see the Terminal window open and you will be asked to enter your password (unless you have setup SSH keys on Windows - covered in our Installing PuTTY guide). Just enter your account password and then the terminal will just sit there. You can minimise the window until you want to close the connection. That's it! You can now connect to MySQL from your local computer using localhost as the server name on port 3306 and you will be connected securely (via the tunnel, to the remote server).
