<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

MySQL Workbench Remote Administration using SSH to Windows Server

Published on
22,501 Points
15,901 Views
1 Endorsement
Last Modified:
Approved
I use MySQL for many of my development projects in a Windows environment.

To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench.

So I decided to make the switch to MySQL Workbench, which after getting used to, works fine except managing the database remotely becomes a little more challenging in a windows environment.

In my development environment I had MySQL administrator on my workstation and the MySQL Database on my server.  I could run all management tasks from MySQL administrator through a connection to port 3306 (main MySQL port).

With MySQL Workbench any administrator tasks (restarting server, GUI edit of config file, viewing logs) needs to be done locally on the server or if done remotely, through SSH.
So I figured that I could just log into my server and install Workbench and then for the admin tasks just remote into the server.

However my server is a Windows 2000 box and MySQL Workbench (5.2.30) does not run on that OS.

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

First my 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 the 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.
1
Author:rcombis
1 Comment

Expert Comment

by:lloydcodrington
Thanks for posting this article. It proved so helpful even though I have a different setup to yourself.

In case anyone has a setup like mine and thinks this article only applies to Win Server please read on.

I have a testing server which is a CentOS Linux box on my LAN.  I have given is a hostname and an IP address set via my router so it never changes.  My development platform is a Window 7 based machine and I changed the \etc\host file to include the hostname for the testing server so I can use the hostname from my browser instead of the IP address.

Now although I had all access in every single way, FTP, SSH, HTTP etc I could not get MySQL workbench to connect to the server.  UNTIL I read this article and got to the last part in relation to the SSH access setup within MySQL workbench.  The first part I could ignore as I already had SSH set up and working fine.  But that last part saved me some hair.

Thanks rcombis. Best of luck.
1

Featured Post

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month