Using Putty to connect with Database Workbench 4 to MySQL via SSH
This is written by a customer and unchecked by Upscene Productions.
- MySQL on remote server
- Putty SSH Client, it's the standard and free
- SSH Server running on the server that you want to connect to, you will need your a log in if you don't already have SSH Access. Some shared hosting companies don't allow this without some good reason. This is one.
- Database Workbench
- Windows 7
- Database Workbench 4.4.x (Pro and Lite tested)
- VirtualBox running Mint 15 Linux (https://www.virtualbox.org/ It is awesome)
- SSH server running VirtualBox
- MySQL running on VirtualBox
It's free and you should have this if you already don't.
Connecting via SSH
Step 1 - connecting Putty
Start putty and navigate to the 'Connection' category then open up the 'SSH' options. This will reveal a few new categories but select 'Tunnels' (See Putty_Tunnel_1). Once the 'Tunnels' option is selected you will need to set the 'Source port' to the LOCAL PORT you want to connect through. If you are running a local windows version of MySql do not pick 3306 (or other port MySQL is using) as you will connect to that and never leave your local machine. It's safe to pick another port like 3307 just in case. This port is used later in the DBWorkbench connection dialog.
Next you need to set the destination which is what your remote MySql instance will see when you connect up.
Most default MySql seem to allow only localhost on port 3306, so that is a safe bet. The entry for 'Destination' should be something like '127.0.0.1:3306' and leave the other settings as shown in the picture.
Once you have entered the 'Source port' and 'Destination' Hit the 'Add' buton. Your Putty screen should look like this - (See Putty_Tunnel_2). If you make a mistake, just select the entire line and hit the 'Remove' button and re-edit your connection data.
After this you can save your configuration by going back to the 'Session' category at the top of tree view. DON'T FORGET TO ADD IN YOUR HOST NAME before saving your session data. Type in a name such as 'SSH MySQL Tunnel' and save the configuration. (see Putty_Tunnel_3)
Putty does not have the friendliest user interface but play with it and verify that your saved session is really what you expect it to be by looking back a the 'Tunnel' configuration. (see Putty_Tunnel_4). Double click your Saved Session or click the 'Open' button and you should be on your way.
VERIFY YOU CAN CONNECT TO THE REMOTE SERVER NOW. If you can't connect go back and check setting. You may see some messages about a new key when you first run the connection, this is OK, accept and move on. Once connected LEAVE PUTTY RUNNING, this will not work unless you are connected with Putty.
Step 2 - using the tunnel in Database Workbench
Setting up DBWorkbench is easy, you will just register a new MySQL data source and about the only difference will be the new port number that you specified in Putty. In our example case it was 3307. Here is the picture of Database Workbench's (See DBWorkbench_MySQL_Setup). Hostname should be 'localhost' since we are using Putty to tunnel to the remote server and actually DBWorkbench is making a connection to Putty not the remote server directly.
And finally Success! I have connected up DBWB4 (pro and lite) to my VirtualBox Linux installation. No need to run WINE or other poor tools when you can run things native.
I have only done minor testing on MySQL so your mileage and performance may vary, but it's worth a try so you can use a real database tools. (See DBWorkbench_Working)