Securing the connection between MySQL and MySQL Administrator using an SSH tunnel

Want to support HowtoForge? Become a subscriber!
 
Submitted by Craig (Contact Author) (Forums) on Sat, 2006-12-30 03:29. :: MySQL | MySQL | Security

Securing the connection between MySQL and MySQL Administrator using an SSH tunnel

Version 1.0
Author: Craig <craig [at] cass-hacks [dot] com>
Last edited 12/30/2006

This is a description of how to set up a secure tunnel between your MySQL Server and a locally running MySQL Administrator using Putty. By creating a secure tunnel to your MySQL server using Putty, you can grant localhost access to powerful applications like MySQL Administrator while at the same time, make your server appear as if it isn't even there. In effect, make your MySQL server disappear from the outside world.

You will need the following software:

When completed, you will not only have a secure connection between your remote MySQL Server and a local instance of MySQL Administrator but more so, you will open up all the functions and features of MySQL Administrator that are only available to instances running locally on the server itself.

Step 1, Selecting a server connection profile in Putty

Click on whichever connection name you wish to use for the secure tunnel.

Step 1 - Select the desired connection profile.

Step 2, Click "Load"

Step 2 -  Load the connection profile

Step 3, Go to Connection > SSH > Tunnels

Step 3 - Go to Connection > SSH > Tunnels

Step 4, Enter MySQL connection information

Step 4 - Enter MySQL connection information

The "Source port" is the local port your MySQL Administrator will connect to instead of the remote (server) port it normally would. This can be any port currently unused on your system and should be different for every tunnel you wish to create.

The "Desitination" is the address and port of your actual MySQL Server. But since your SSH connection will be considered "local" to your MySQL Server, you should use either "127.0.0.1" or "localhost" depending on what works best.

Step 5, Adding the new tunnel information

Step 5 - Adding the new tunnel information

Make sure the information in "Source port" and "Destination" are correct, that the "Local" radio button is selected and click "Add".

You should now see the new tunnel information displayed as shown.

Step 6 - SAVE YOUR SETTINGS!!!

Step 6 - SAVE THE NEW CONNECTION SETTINGS!!!!

BEFORE YOU DO ANYTHING ELSE go back to the main Putty screen and save the connection profile updates.

This can not be stressed strongly enough! If you do not save your new tunnel information, not only will you lose it on closing putty but you'll also lose it if you simply make a connection and you may even lose it while trying to make the connection in the first place!

Step 7 - Connect to the server

Connect to the server from Putty as you normally would.

Step 8 - Configure MySQL Administrator's connection settings.

Step 8 - Configure MySQL Administrator connection settings.

Since your connection will now be through the secure tunnel created by Putty, your "Server Host" will now be local so use "127.0.0.1".

The "Port" should be set to the same port you set in Step 4.

Once the connection settings are correct, click "OK" and you should then be able to succesfully connect to your MySQL server except now, through a secure tunnel!


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Anonymous (not registered) on Sat, 2013-12-14 04:05.
Thanks for creating this page
Submitted by iTiki (not registered) on Sun, 2009-05-24 01:27.

Just sending some mad gratitude for the precise instructions. Worked immediately with no alterations needed.

Submitted by Anders (not registered) on Sun, 2009-01-11 13:11.
It´s working perfect. Thank you so much!
Submitted by conwayse (registered user) on Tue, 2007-01-09 16:11.

I managed to connect to the server using MySQL Administrator. If you use an account other than root it needs to be able to login locally.

I got the following error message on connection: Either the server service or the configuration file could not be found. Startup variables and service section are therefor disabled. I suspect the author of this procedure has MySQL Administrator running locally and used this procedure to get a copy running on another machine.

Startup variables and service section are only available locally. This procedure when complete would be a great work around for those systems that don't run MySQL administrator locally. I suspect some local config files need to be installed on the server to support the software. If I get it to work I will leave the bread crumbs.

Submitted by kuriharu (registered user) on Wed, 2007-01-03 01:46.

I tried this but no dice. As requested, I opened a case in the forums.

This is a cool idea, tho'. For those who get it to work it's probably pretty cool!

 

Submitted by aut0maticdan (registered user) on Tue, 2007-01-02 16:42.

Make sure you use 127.0.0.1 or another alias (such as something hosts file) to make the mysql connection.  If you use "localhost" mysql, you will not be able to authenticate properly since mysql will think you are trying to use the local socket.

 This article is not incorrect,  but I didn't want any to go crazy with authentication problems trying to use localhost.

Submitted by Matthew M (not registered) on Mon, 2009-03-16 15:44.

One problem --

 This works if you're sitting at a PC and want secure access to your MySQL server. But what if you need to run a process once a day on your home server (or PC) and need a STABLE connection to your remote MySQL server?

 In other words, how do you connect to the server again when the connection gets dropped for whatever reason? How do you "re-open the tunnel" when it caves in, so to speak?

 Thanks,

Matthew

 

Submitted by Anonymous (not registered) on Tue, 2010-05-11 16:19.

You use screen command line application in linux  to use persistent connections.

Greetings from Chile