Connecting the MySQL GUI Tools to a Remote Server through a Firewall
Introduction
A large percentage of MySQL users are using MySQL on a web server hosted by an ISP. Most hosting providers block port 3306 (the MySQL server port) at the firewall, preventing outside access to MySQL. This is an important security practice and you should be very concerned if your ISP does not block port 3306. In this article I will demonstrate how to connect the MySQL GUI tools, including MySQL Administrator and MySQL Query Browser, to a remote server using SSH port forwarding. This article will be written with the assumption that the reader is using Microsoft Windows, but the principles presented will be applicable to Linux users.
What Is SSH?
SSH stands for Secure SHell and is typically used as an encrypted version of telnet. SSH allows you to access a remote server’s shell without compromising security. In a telnet session all communications, including username and password, are transmitted in plain-text, allowing anyone with adequate resources to listen-in on your session and steal passwords and other information. Such sessions are also susceptible to session hijacking, where a malicious user takes over your session once you have authenticated. SSH serves to prevent such vulnerabilities.
OpenSSH, the tool included with most Linux variants, is described as follows in the OpenSSH FAQ at http://www.openssh.org/faq.html#1.1 :
“OpenSSH is a FREE version of the SSH suite of network connectivity tools that increasing numbers of people on the Internet are coming to rely on. Many users of telnet, rlogin, ftp, and other such programs
might not realize that their password is transmitted across the Internet unencrypted, but it is. OpenSSH encrypts all traffic (including passwords) to effectively eliminate eavesdropping, connection hijacking, and other network-level attacks.”
What is SSH Port Forwarding
When a mysql client communicates with the MySQL server, all communication (with the exception of the user password) is done in plain text. What this means is that if an unscrupulous individual gets between your client and the server, they can have full access to all information transmitted. In order to protect your information you need to encrypt communications between the MySQL server and the GUI client.
SSH can be used to encrypt communications between the client and server. This is known as SSH port forwarding or SSH tunneling. One benefit of SSH port forwarding is that we can connect to a MySQL server from behind a firewall when the MySQL server port is blocked.
SSH will listen on a specified port on the client machine, encrypt the data it receives, and forward it to the remote SSH host on port 22 (the SSH protocol port). The remote SSH host will then decrypt the data and forward it to the MySQL server. The SSH host and the MySQL server do not have to be on separate machines, but separate SSH and MySQL servers are supported.
Requirements for SSH and MySQL
To perform port forwarding between a MySQL client application and the MySQL server, you will need a SSH login account for port forwarding. This account needs to either be located on the server running MySQL, or on a machine that can be accessed remotely via SSH and which in turn has network access to the MySQL server.
For this article we will be using Putty, an Open Source SSH client application written by Simon Tatham and available at http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html . Linux users should have a command-line SSH client already installed.
Creating the SSH Tunnel
We will first need to configure a Putty session for port-forwarding. Our first step is to configure Putty to connect via the SSH protocol and specify the server address:
Once we have configured our host and protocol, we can move on to configuring the SSH tunnel. To create an SSH tunnel, we specify a destination host and port:
In this example, we are specifying that port 3306 on our client machine should be forwarded to port 3306 on the remote server. You can also forward data from a local port number that is different than the remote port number. For example, on my development machine I keep a local copy of MySQL running on port 3306. On my production server I also run MySQL on port 3306. I can configure port forwarding with port 3306 as the local port, but all traffic on port 3306 will be intercepted and forwarded, making the local copy of MySQL unreachable. If I change the source port setting to 3307, I can access the remote server through port 3307 and the local server through port 3306.
When the remote SSH host is on a different machine than the MySQL server, replace 127.0.0.1 with the IP address of the MySQL server (relative to the SSH host).
Once we have added our port forwarding directives, we can then save the session to make it available for repeated use. In the session menu specify a saved session name and click save to add this to the list of saved sessions:
Once you have created and saved your session, you can add a shortcut to your desktop to quickly access port forwarding. Right-click on your desktop and choose New> Shortcut. Configure the shortcut and assign the target as /path/to/putty/putty.exe -load sessionname. In the example above, with putty at C:\putty.exe and the profile saved under the name MySQLTunnel, you would assign the shortcut target to be:
C:\putty.exe -load mysqltunnel
To open the session, double-click the icon and provide a username and password when prompted. A Putty window will open and SSH port forwarding will be established after you successfully log in. When you are finished using the tunnel you can close the Putty window to end SSH port forwarding.
Using the SSH Tunnel
Once SSH port forwarding is established, open your client application (I will use the MySQL Query Browser in this example).
Set the server host to 127.0.0.1, using port 3306 (unless you configured a different port for your tunnel to prevent conflicts with a local copy of MySQL).
When you connect, Putty will act as a proxy and the client will connect to the remote copy of MySQL through the ISP’s firewall. As an added benefit, these communications will be protected by the encryption capabilities of SSH, preventing third parties from eavesdropping on your MySQL session.
Conclusion
SSH port forwarding is a valuable tool for communicating with remote MySQL servers securely, especially when the remote server is protected by a firewall. While an SSH account on the remote server is required, many ISPs are willing to provide one. SSH port forwarding can be used to protect all MySQL client sessions, including MySQL Administrator, MySQL Query Browser, mysqldump, etc.
April 9th, 2007 at 11:35 am
how can i connect any application to remote mysql through registry using c++?
I do not have mysql on my local machine.
October 26th, 2007 at 8:17 pm
Great article. This is exactly what I was looking for.
You don’t need a local copy of MySQL for this to work.
November 8th, 2007 at 4:44 pm
thank you very much
this is exact solution what I was looking.
November 9th, 2007 at 2:09 am
this was perfect – just what I was looking for
January 23rd, 2008 at 5:22 pm
Very very nice article.
January 25th, 2008 at 5:44 am
Very beautiful, informative and precise article. I am now able to access a personnaly designed MySQL database that is running off an rpath LAMP appliance from my Windows XP machine and do all the nice things with MySQL Data Migration ToolKit, Query Browser and Administrator Utilities. What is strange is that I did know how putty was such a great tool!
February 1st, 2008 at 9:02 am
Excellent article and very useful, by the help of this article I am now using MySQL server on Linux from WinXP machine in GUI mode. Thanks a lot for presenting great things in a simple way.
March 4th, 2008 at 9:38 pm
This does not work for me. I am attempting to connect to the MySql Admin, but it gives me an error saying “Either the server service or the configuration file could not be found. Startup variables and service section are therefore disabled.”
Is there a way around this?
March 18th, 2008 at 3:26 pm
Great article! Exactly what I was looking for! My version of PuTTY is slightly different from the one shown, but I was still able to connect to a database that’s been eluding me for over a week. Finally I’m in! My boss will be very pleased.
April 25th, 2008 at 8:08 pm
I still can’t get this to work, and I am wondering if it because I don’t log into Putty as root. Or perhaps because there doesn’t seem to be anywhere that I specify the name of the database server? We have more than one.
As soon as I click “OK” I get an error message saying
MySQL Error Number 2013
Lost connection to MySQL server during query
I can ping my local port and it works.
Please help!
August 18th, 2008 at 10:48 am
There is a slight configuration change that has to be made on the server side as well.
make sure that you have the following setting in /etc/ssh/sshd_config
AllowTcpForwarding yes
This will fix the MySQL Error Number 2013 problem.
September 9th, 2008 at 9:32 am
thanks! this informative article save my live
October 2nd, 2008 at 8:22 pm
This is great stuff. Thanks very very much for putting it up. Exactly what I needed.
Editor-type suggestion to make it a tiny bit clearer: Make the change to 3007 in your instructions instead of offering it as an option, as pretty much everyone will have mysql server running locally on 3006. Reduces the length of the instructions and makes it clearer. It can’t hurt if people always use 3007 on the local side, right?
Might also want to add a last instruction telling numbskulls like me to click the Open button on the Session menu as the last step. Or double-click the saved session MySQL tunnel.
Them’s my free editor-type comments
Thanks again
October 28th, 2008 at 3:42 pm
Thanks for the Article, I must admit I got a lil bit lost to start with… then I stopped being stupid and read through it properly =D
Awesome works, Thanks
November 19th, 2008 at 1:32 pm
Super Article got things working …. was searching on the web but was not able to find such type of connectivity. Hats off to dear.
Neil
November 26th, 2008 at 3:08 pm
“MySQL Error Number 2013 – Lost connection to MySQL server during query” can also be caused by the directive skip-networking in my.cnf! You have to comment out this directive (but don’t forget to secure your mysql server).
January 8th, 2009 at 3:43 pm
mutual grange company insurance national insurance national company mutual grange
January 15th, 2009 at 7:55 pm
thank you!
January 20th, 2009 at 10:50 pm
amazing explanation..helped a lot..thank you!
March 16th, 2009 at 1:22 pm
Very helpful , worked for me . My ISP was not allowing me to connect from their mysql server from my local application.
April 13th, 2009 at 6:53 pm
I think this requires a local copy of MySQL running, at least to access Administrator. Otherwise, it cannot find the my.ini file, and gives the error:
“Either the server service or the configuration file could not be found. Startup variables and service section are therefore disabled.”
September 10th, 2009 at 9:04 pm
Hi! I was surfing and found your blog post… nice! I love your blog.
Cheers! Sandra. R.
October 2nd, 2009 at 4:22 pm
Interesting, but somehow I don’t think this is the whole story. I wrote about this in more detail a while back.
October 2nd, 2009 at 4:23 pm
Interesting, but somehow I don’t think this is the whole story. I wrote about this in more detail a while back.
October 29th, 2009 at 8:31 pm
I recently had the cracked screen repaired on my iPhone 3G. Magic Phone Repair in TX repaired it and mailed it back to me in 24 hours. It only cost me 69.99 including shipping and insurance. My phone looks new http://www.magicphonerepair.com
November 13th, 2009 at 2:42 am
Brilliant! worked like a charm! thanks
November 20th, 2009 at 1:53 pm
Credit card debt consolidation is the concept in which the person concerned a certain steps to combine and consolidate all its credit card debt at a manageable debt. The main objective of credit card debt consolidation is to get rid of debt, credit card and agree to produce more. For credit card faster debt relief, you can credit card debt consolidation loans. This form of credit available to both secured and unsecured forms She worked with sufficient resources with which your entire credit card to pay outstanding bills and fees.
November 21st, 2009 at 8:22 am
Wow, I might consider subscribe to your blog with my rss reader right now!
November 29th, 2009 at 3:58 pm
Wow, you have really great info there, Thanks because I find this info useful. For me as an blogger, I work with Outlook as my email client and with the help of Email Sorter Wizard, an Outlook add-in, I manage all my email. I am sure people will benefit from your blog.
December 8th, 2009 at 10:52 am
I have an application is updating the data to the MySql server which is located at remote Linux server. So i am first connecting/creating a secure ssh tunnel through the Putty and running my application.So all i am doing it manually. Is there any way to do this at run time?
I want some kind of stuff which would create secure tunnel to the Linux server, give password there so that i can connect to the MySql db through the code. Can anyone help me on this?
December 16th, 2009 at 9:09 pm
Thanks, exactly what I was looking for!
January 22nd, 2010 at 7:23 pm
This worked like a charm, I used putty to tunnel for mysql admin. I am trying to change the mysql startup variables, now that I can login remotely with mysql admin it still doesn’t allow changes to startup variables and processes.
Is the only way to do this install mysql admin on the server itself. Or is there a trick.
January 26th, 2010 at 4:50 am
You covered all in detail, bro.
Free Nokia E71
Thanks for posting this. Bookmarking your blog.
January 26th, 2010 at 5:46 am
You covered all in detail, bro.
Thanks for posting this. Bookmarking your blog.
January 27th, 2010 at 11:36 am
Hi,
Good article. But i have a problem regarding the ssh port forwarding since long. I am able to connect to linux server and Mysql db there using Putty from my local machine, when i tried from my client machine i got error as “Could not connect to Mysql db”. Here my machine is window xp where as my client machine is window server 2003. I do not know what to do. If any one please suggest in the case?
Thanks
January 30th, 2010 at 12:06 am
[...] following guide helped [...]
February 15th, 2010 at 2:26 pm
I want to access MySQL running on a remote Windows XP Pro machine. Do I have to install a SSH program on that machine and, if so, what?