How to connect to a MySQL database using an SSH Tunnel
This article was posted in: Web Hosting
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 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 Windows, then you will need to install PuTTY - read the article How to install PuTTY for SSH shell access
If you are using a Mac, don't worry - you already have everything you need (you can smile smugly).
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
The easiest way is by example. Open the Terminal application (using Spotlight, or go find it in Applications/Utilities). Then run the following command.
ssh -p 722 -N -L 3306:localhost:3306 user@servername
- servername = substitute your own domain name, or Home Server name
- user = your cPanel username
For example, if your Home Server was hestia.krystal.co.uk and your cPanel username was krystald then the command would be
ssh -p 722 -N -L 3306:localhost:3306 email@example.com
When you hit return, you will be asked for your password (unless you have setup SSH keys on your Mac) - 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:
ssh -p 722 -f -N -L 3306:localhost:3306 user@servername
Note : It is important that you do not have an existing instance of mysql istening on port 3306. Either stop it before running the tunnel above, or, change your connection command to something like:
ssh -p 722 -N -L 3333:localhost:3306 user@servername
This will present the local end of the tunnel on your computer on port 3333 instead.
Windows Users - fire up PuTTY!
- 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 config category
- Tick the Don't start a shell or command at all check box (you can skip this step if you also want to play around with 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 the Category pane and click on Session again.
- Enter a name for your new connection in Saved Sessions.
- 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. 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 (just close the window in the usual way using -F4)
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 server).