Saturday 1 March 2008

SSH Tunneling for mySQL GUI tools from Windows to a production Linux server

I thought I might add another simple tip.

When I am messing around with a wesite... I spend a lot of my time creating, editing and manipulating the databases.

Any commercial Hosting Service, worth their salt, will block port 3306 to mysql.
It is a simple and basic security measure, that must be left in place.

So I often have to use the command line tools to do all my work. This is fine for most things. But when I am editing data, it can be a real pig and slows me down.

I want to be using a GUI tool.

So how... do I use the availble GUI tools, when their communications are blocked?

Simple, I use PuTTY to create a local tunnel through the firewall to the server.

I am going to assume you already have PuTTY and are used to using it.
Otherwise my first suggestion is learn PuTTY.



How To do it...



1: Create a connection in PuTTY to your server as normal.

  • HostName, A on fig.
  • Port(22/SSH), B on fig.
  • Give it a name, C on fig.
  • Click save, D on fig.

2. Goto the Connection->SSH->Tunnels option at the bottom of options of the left. E on fig.



3. Make sure the local and Auto radio buttons are selected. F on Fig.

4. Enter the Source port. G on fig. This is the listener port you will connect to on your local machine. If you have mySQL running locally on 3306, then I suggest 3307. Continue incrementing the port number for each server you add.

5. Enter a destination HOSTNAME:PORT. H on Fig.This is the destination FROM the remote server, so if you are SSHing directly into the mySQL server, the hostname needs to be localhost:3306 or 127.0.0.1:3306.

6. Click Add. I on Fig.



7. Check that your tunnel is now shown in the list box. J on fig. The info displayed should show "L3307" which is localhost port 3307, and localhost:3306, which is the destination host & port.

7. Remember to save your setting before you open your connection. D on Fig.

8. Start you PuTTY connection, (Open, K on Fig) and log in as normal...

9. Now Start the mySQL GUI, I am using the MySQLQueryBrowser.





10. Enter the Server Host. L on fig. This should be localhost , as we are connecting to putty.

11. Enter the Port. M on fig. This is our local putty listening port, in this case 3307 .

12. Enter your MySQL Username and Password. N & O on fig.

13. Click OK. P on fig.

That's it...

You should now be connected to MySQL.

Finally

Set up a batch file to create the PuTTY connection.

Put this in a batch file. Obviously change the location to point to your install of Putty.

c:\some\dir\with\putty\in\it\putty.exe -load mysqltunnel

You can then of course, create a keyboard shortcut to the batch file.



Errors

If you get a user not allowed error, then you need to check how you log in to mysql using the command line on the server. This will affect your tunneling setting.



If you get a Can't Connect error, you have forgotten to start the putty session first...

2 comments:

  1. Is there a way to avoid logging in SSH as root to administrate MySQL - using the MySQL Administrator GUI tool to change the server parameters? (Do something like sudo ?)

    ReplyDelete
  2. The windows based hosting arrangements could be imparted to an assortment of sites too since they are regularly planned along these lines. Windows Hosting

    ReplyDelete