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.
how can i connect any application to remote mysql through registry using c++?
I do not have mysql on my local machine.
Great article. This is exactly what I was looking for.
You don’t need a local copy of MySQL for this to work.
thank you very much
this is exact solution what I was looking.
this was perfect – just what I was looking for
Very very nice article.
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!
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.
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?
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.
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!
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.
thanks! this informative article save my live
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
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
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
“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).
mutual grange company insurance national insurance national company mutual grange
thank you!
amazing explanation..helped a lot..thank you!
Very helpful , worked for me . My ISP was not allowing me to connect from their mysql server from my local application.
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.”
Hi! I was surfing and found your blog post… nice! I love your blog.
Cheers! Sandra. R.
Interesting, but somehow I don’t think this is the whole story. I wrote about this in more detail a while back.
Interesting, but somehow I don’t think this is the whole story. I wrote about this in more detail a while back.
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
Brilliant! worked like a charm! thanks
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.
Wow, I might consider subscribe to your blog with my rss reader right now!
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.
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?
Thanks, exactly what I was looking for!
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.
You covered all in detail, bro.
Free Nokia E71
Thanks for posting this. Bookmarking your blog.
You covered all in detail, bro.
Thanks for posting this. Bookmarking your blog.
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
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?
To manage MySQL servers from your browser, you might want to try a program made by NovaQauntum.
I have really enjoyed reading this article!
This is a really good read for me. Must agree that you are one of the best bloggers I ever saw.
So great Post. It’s really a very good article and useful for me, and I have noticed all your important points. Thanks
Get a logo design online at 48hourslogo
I have read your posts before. The more I learn, the more I keep coming back! ;-P
Because of reading your blog, I decided to create my own. I had never been interested in keeping a blog until I saw how interesting yours was, then I was inspired!
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?
Exactly what I was looking for!
Hi, really Amazing article!
Hey There! just wanted to say I enjoyed the article. Bloody brilliant job! Keep them coming!
estetik di? hekimli?i hskk?nda bilgiler i?ermektedir ortodonti
It’s so refreshing to find articles like the ones you post on your site. Very informative reading. I will keep you bookmarked. Thanks!. oven parts
I admire what you’ve got accomplished here. I such as the portion in which you say you might be carrying out this to give back but I would assume by all of the comments that this can be working for you personally at the same time.
It is amazing at how far they will go! I admire you and thank you for trying to wake people up with all the great information you are putting out there.
I admire the work being done here, top quality article. Resolved my problem for me, thanks.
Hello. I appreciate the points you have made. I dont think I’ve actually thought about it in that way. Thanks for taking the time to write this all out.
Hello, ever heared about F-Games.be? It’s a interesting site with many online flash games!
What a resource Thanks a lot …
Aloha, I am loving your blog very much and I have been reader of your blog since long time.
Thanks
Hello, Your article is detailed and I am loving your blog very much.
thanks good post, I like this blog, usefull
Thanks for this chunk of info….well i got some questions….would love if u answer them
Even though this might not go with this topic, I just think it’s important news to know that right now they are desperately trying to get people interested in Real Estate, even going as far as selling houses for under 20 grand with just 10% down. I sincerely apologize for even adding this comment, but by me being into making money and coming across this news on another way to make some cash, I felt it necessary to share the news with others into business who may also want to expand on their money making ventures
Me parece bien
Me parece bueno el articulo creo que enlazare esta web en mi pagina
Love tthe post and useful info you have provided thankyou
hi, thanks for this article. I gona bookmark it and digg to my buddy
Download Konstenlos Musik here!
informative article
Such a nice post. Keep it up
Hey Very cool post. I appreciate it when people like yourself take this much time to deliver OUTSTANDING content!
(I am so sick of automated garbage!)
Best,
Bob R.
Thank you! It was a great studying, I discovered superb worth inside your weblog, might be this isn’t suitable, but I understand everybody every now and then may well will need some little determination and I found this wonderful other weblog about transforming your existence and creating hundreds of thousands online, it’s in spanish though, it is called como ser millonario, you’ll be able to constantly use Google translator! Have a awesome day!
Increase thousands visit to your website with Automatik Traffic System..
wow! Voted 10/10! Build Massive Traffic With SEO Secrets! Voted Best SEO Book! 35+ Fanatic Reviews
spy and control any webcam from anywhere in the world!
How To Grow Your Own Feminine Breasts Using Proven Male Breast Enlargement Techniques
Greetings, I can certainly appreciate your post here, however I have a slightly different idea. At any rate have a good holiday.
Thanks!
Bob
Did heundergone a great custom web site design
The article “certainly the concept of associative thinking” is the opposite of this article.
http://www.e-desene.eu Desene animate online! Poti viziona desene animate cu personaje precum Mickey Mouse, Tom si Jerry, Spiderman. Desene animate si jocuri.
I just came across this blog I really like it because I found justin bieber lyrics