Connecting MySQL GUI Client to a Remote MySQL Server
Connecting MySQL GUI Client to a Remote MySQL Server
Join the DZone community and get the full member experience.Join For Free
Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.
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.
Host name/IP address
A friendly name to best describe your connection.
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'.
The port of MySQL Server on Remote Host, by default it is 3306.
The username of your MySQL Server.
The password of your MySQL user. By clicking OK, the SSH connection is made.
Hosting Companies providing SSH Connections
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.
Tutorial on how to establish SSH Connection to a MySQL database hosted in DreamhostDreamHost provides shell access to all of its customers, but it needs to be specifically enabled for each user added to a customer's account. By default, the Type of the user is set to be “FTP”, you will need to enable the shell access before you can connect through SSH.
Enabling the Shell access for an existing user:
1) In your Control Panel, Visit User > Manage Users.
3) Check the 'Enable ssh/telnet?' box for that user.
4) Leave the 'Type' as it is unless you need it to be something other than bash.
5) Click Save
Keep in mind that it takes about 20 minutes for this change to take effect.
After changing the user account to Shell Type, you’ll also need to grant rights for your local machine to connect to the database server, before you can connect remotely by Navicat. This must be done for all MySQL user accounts you plan on logging in from your local machine.
Granting rights for your local machine:
1) Login to your Dreamhost Control Panel
You have completed the settings required in your Dreamhost account, now you will go to Navicat to create the Connection.
To test if the Connection settings are correct, you may click the “Test Connection” button to check.
Connecting to your MySQL Database from remote client Navicat offers you a greater flexibility on working with your data in MySQL. With Navicat, you can easily import your data in to your MySQL database from various file formats, build queries visually, set a schedule to perform backup, generate report from your raw data and more.
To try connecting to your remote MySQL, download a free Navicat MySQL GUI Tool at: http://mysql.navicat.com/download.html.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.
Opinions expressed by DZone contributors are their own.