SQL Zone is brought to you in partnership with:

kevin has posted 2 posts at DZone. View Full User Profile

Connecting MySQL GUI Client to a Remote MySQL Server

12.05.2008
| 30971 views |
  • submit to reddit

Many of the webhosting companies has blocked port 3306 to prevent access from outside, todefend against from security threats. As a consequence, users are required touse web-based client to access their MySQL Server provided by the hostingcompanies. These web-based clients are usually not visually appealing withannoying page refreshes.

However, with the increasing demand from users to connect from remote MySQLclients, some web hosting companies provides SSH Connection which allows userto login remotely across the Internet and support connection through thesoftware based clients.

In this article I will demonstrate how to connect the MySQL GUI tool – Navicat, http://mysql.navicat.com to a remote server using SSH port forwarding. Thisarticle will be written with the assumption that the reader is using MicrosoftWindows, but the principles presented will be applicable to Linux users.

What Is SSH?

SSH stands forSecure Shell and is a communication protocol for connecting to remote computersover TCP/IP. Encryption provides confidentiality and data integrity, and SSHuses public-key cryptography to authenticate the remote computer and to allowthe remote computer to authenticate the user if necessary.

There are several benefits to using SSH:
  • Connect to a MySQL server from behind a firewall when the MySQL server port is blocked.
  • Automate the authentication of users, no passwords sent in plain text to prevent the stealing of passwords.
  • Offers Multiple strong authentication methods that prevent such security threats as spoofing identity.
  • Offers Encryption and compression of data for security and speed.
  • Secure the file transfer.

What is SSH Port Forwarding

When a mysql clientcommunicates with the MySQL server, all communication (with the exception ofthe user password) is done in plain text. What this means is that if anunscrupulous individual gets between your client and the server, they can havefull access to all information transmitted. In order to protect yourinformation you need to encrypt communications between the MySQL server and theGUI client. SSH can be used toencrypt communications between the client and server. This is known as SSH portforwarding or SSH tunneling. One benefit of SSH port forwarding is that we canconnect to a MySQL server from behind a firewall when the MySQL server port isblocked. SSH will listen ona specified port on the client machine, encrypt the data it receives, andforward it to the remote SSH host on port 22 (the SSH protocol port). Theremote 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 separateSSH and MySQL servers are supported.
Setting up a SSH Connection to your MySQL Server with Navicat

To successfully establish a SSH connection, set the SSH connection properties in the corresponding boxes: Host name/IP address, Port number, User name, Authentication Method and Password.

1. Click or choose File ->New Connection to set up the Connection Properties.
2. Select the SSH tab and enable Use SSH Tunnel. 
3. Fill in the required information in the SSH Tab:

Host name/IP address
A host where SSH server is activated.

Port
A port where SSH server is activated, by default it is 22.

User name
A user on Linux machine. (It is a Linux user. It is not the user of MySQL Server.)

Authentication Method
Choose Password Authentication

Password
The password of your user account in linux 

4. In the General Settings Tab, the settings should be set relatively to the SSH server. For example, host_of_mysqldatabase shown below is the host address, which is provided by your hosting company of your remote MySQL database. Connection Name
A friendly name to best describe your connection.

Host name/IP
address The host where MySQL Server is located in point of view SSH server. If SSH and MySQL Server are on the same machine, it is equal to SSH Host, or may be 'localhost'.

Port
The port of MySQL Server on Remote Host, by default it is 3306.

User name
The username of your MySQL Server.

Password
The password of your MySQL user. By clicking OK, the SSH connection is made.

Hosting Companies providing SSH Connections
Listed below are some hosting companies which provide SSH connections and remote MySQL Connection for Major MySQL GUI Tools including Navicat.

1&1

1and1 Hosting

1Host Web Hosting
3FN.net
A2 Hosting
Advanced Network Hosts
AN Host
AN Hosting
Aplus.net
Apollo Hosting
Argon Hosting
Blue Host
Bounceweb
Cirtexhosting
Crucial Paradigm
Dotservant.com
Dreamhost
eChristian Web Hosting
FastDomain
Flux Services

Hagen Hosting

HostGator.com

HostHead

Hostican

Hostland
HostMonster.com
Hostpapa
HostRocket.com
Imhosted
InMotion Hosting
Intermedia
iTeraWeb Solutions
IX Web Hosting
Joyent
Liquid Web
Lunarpages.com
ME Webhost
Media Temple
Naked Hosting
Netfirms
Net-Trend
Omnis Hosting
PowWeb
Practical Webhost
Server Pronto
ServerPro Web Hosting
SimpleHelix
Start Logic
Superbhosting.net
Tbhost.com
techhosting.com
The Host Group
ThinkHost
UK2NET
Velcom.com
Verio
Vision Web Hosting
Web Hosting Buzz
Web Hosting Pad
WebWizards.net
WestHost
WireNine
YourServing
Your-Site.com

In the following section, we have selected one of a popular hosting companies and show how a SSH Connection to the MySQL Database can be established with Navicat.

Published at DZone with permission of its author, kevin cheung.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Tim Thomas replied on Fri, 2008/12/05 - 10:46am

Or just use openssh:

 

ssh -L 3306:localhost:3306 myusername@remotehost

 

Then just use whatever mysql client you want and connect to localhost:3006.

cccccc mmmmmmm replied on Fri, 2008/12/05 - 9:08pm

i use navicat on mac.. and theres two many popups to manage.  and the mac.. you have to go back into expose everytime you want to find the windows.. to say the least, not to productive.  I prefer mysql query browser.   

Wong Qil replied on Mon, 2008/12/08 - 12:10am

I use SQLyog on windows, I have to say it's really wonderful for mysql client, even Community Edition.

alan axford replied on Mon, 2008/12/08 - 10:54am

I've been using Navicat for 3 years and like it so much. It contains all essential features for my daily MySQL management. And just realized it released Oracle version few weeks ago. I'm using Toad for Oracle and thinking to replace it if Navicat for Oracle version performs as good as its MySQL version. I hope Navicat would support MS SQL, as I also use MS SQL heavily in my office.

 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.