In a previous article I wrote how to create a SSH tunnel with Putty and use it as a proxy to browse the web. That article can be found here and this article will continue on that track and show you how to connect to a remote MySQL Server using mySQL Workbench (follow this link to download) from your local computer. This is actually nothing new but I noticed that I hadn’t written about it here so here goes.

 

Many mySQL Servers on the Internet are installed and configured to only accept connections from localhost. This is usually enough when all applications/services are installed on the same server and you don’t need to access it from a remote location.

One way to add remote access is to create a mysql user, often locked down to an IP-address or domain name. The commands for adding a user is:

The first row adds the user monty from any location with the password ‘some_pass’ and the second row grants the user monty ALL provileges to ALL databases on the server.

That is not really secure, anyone that somehow gets your username and password will have full access to all your valuable data in any database that you have on your server.

What can we do to add some security to this? First of, only create users that can connect from a specific location, most often localhost. If we want to create the user monty with the same password as above but only allow it from localhost, the commands would look like this instead

Monty would still have access to all database to do as he pleases but he can only do it from localhost. That raises another question, how can we connect from home as monty if we tell mySQL Server to only accept connections from localhost?

This is where we use Putty and its ability to tunnel traffic. Let me show you how with a couple of screenshots.

Start Putty and add your IP or domain name in the highlighted area.

 

Now exand the SSH option (1) under Connection on the left side of Putty. Click Tunnel (2) as it now will be visible.

 

Fill in the local port (3) that you will be connecting to on your own computer. The destination (4) should be set to 127.0.0.1:3306 unless you have changed the listening port of mySQL.

Click the Add button and the newly entered values will be stored in the text box above.

Now we are ready to connect to the server, click the Open button and you will be able to login to your server.

 

Leave Putty open, you can minimize it but whatever you do, don’t close the connection.

Open mySQL Workbenchand in the top left corner you will see this

Click that small plus sign to add a new connection

In the dialog box we need to set a name for this new connection and a hostname and port that we will connect to. If you followed the instructions earlier and set the same values for the tunnel settings in putty as the screenshot the hostname and port should be set to 127.0.0.1 and 3306.

You should also enter a username that you will use to connect to mySQL, this can be root but it is not neccesary.

When everything is set, click the OK button and save the newly created connection.

Connect to your mySQL server by clicking on the newly created connection

and then enter the password for the user that you connect as.

Remember that if you close Putty, the connection will also close.

That is all, enjoy the possibility to work from home. Explore mySQL workbench, there is a multitude of features in that program.