Here is how I got MySQL Workbench to connect to a Windows 2000 server using SSH.

Environment:
Server: Windows 2000 Server running MySQL Community 5.1.45-community
Workstation: Windows 7 Professional running MySQL Workbench (5.2.30)

What you will need:
SSH Program: I use FreeSSHd (1.2.4), http://www.freesshd.com

This program needs to be installed on the server. At first I installed this as a service, but for whatever reason I had trouble accessing the system tray icon when run as a service. Moreover, the system tray icon was the only place I could find that allowed you to edit the config file in GUI format.

Installing FreeSSHd:
1. Run the install program
2. Keep default path for install or change to suit your needs.
3. Choose Full installation
4. When it prompts you to run as a system service, choose no. (I got it to work initially as a service but this method is a little more straight-forward)

Configure FreeSSHd:

1. Click the FreeSSHd icon on desktop. This should start the program and put an icon in your system tray.

2. Right click the icon in system tray and choose settings.

3. Go to SSH tab. For listen address choose 0.0.0.0 (all interfaces) or any appropriate IP address. (This will be the IP that your MySQL Workbench client will connect into.

4. For port, choose 22. You could probably choose any available port. This will be the port your MySQL workbench connects into.

5. In the authentication tab, disable Public Key authentication, and make Password authentication required. (There are different ways to go about this step but I choose password)

6. In the tunneling tab choose Allow local port forwarding and also the option beneath it â??but forward only to localhostâ??.

7. In the user tab create a user with a password and grant them access to Shell, SFTP and tunneling.

8. In the logging tab choose log events. This will help with trouble shooting. For me authentication was challenging and it was nice to see what went wrong.

9. In the SFTP tab, choose the root drive where your MySQL installation is located. We need to set this correctly, so we can access the My.ini file which workbench needs access to.

FreeSSHd should be all set to go. Hit apply to save all changes. On the server status tab Stop and Start the server to make sure all the new changes are loaded in. Make sure this shows a green checkmark and says SSH server is running. For whatever reason, I needed to choose â??unloadâ?? from the system tray icon and restart FreeSSHd to get the config changes to be loaded in.

MySQL Workbench Config:

1. Start MySQL Workbench

2. Create New Server Instance â?? This will launch a wizard type tool

3. Enter in Remote Host (IP or name)

4. Choose standard TCP/IP. Enter Host, Port (3306) and username. We will configure the SSH portion in a moment.

5. For remote management choose SSH login based management. Then choose your MySQL installation type.

6. In the next screen set the remote SSH configuration parameters. Hostname is your server or IP. Port is 22 or whatever you set FreeSShd to, and username is whatever you setup.

7. Hit next, it should do a test. Mine kept failing on the third part, Check MySQL configuration file Operation failed: File %ProgramFiles%\MySQL\MySQL Server 5.1\my.ini doesn't exist. Hopefully we can fix that later.

8. In the review MySQL Management parameters section if you have an error finding youâ??re my.ini file click the Change parameters checkbox and hit next.

9. In the path to configuration section hit the button to browse files. If SFTP is running correctly, you should be able to browse your serverâ??s local drive and select the correct path for the my.ini file. Then check path and hopefully it lets you know if it is valid.

10. In the section of Server Instance, put in â??mysqldâ?? (without quotes). I only have one server instance running and this seemed to work.

11. For the server instance name choose a name that is appropriate. I am pretty sure this name is what will show up in Workbench to identify this connection entry we created.

After that, run MySQL Workbench, click on your server instance and you should be good to go!

Addition: To get the stop/start server command to work correctly, go to manage server instance, and select your correct profile. Choose the system profile tab and for the Start MySQL section change "sc start mysql" to "net start mysql". Do the same for the stop command. Also the 'Acquire administrator rights' was not needed for my scenario.

Last updated 16 Aug 2024