How to Connect to MySQL through SSH Tunnel

Ashutosh Roy Choudhury -

Introduction

Before we begin talking about how to connect to MySQL through SSH Tunnel, let's briefly understand – What is an SSH Tunnel ?

SSH tunneling, or Secure Shell tunneling, is a method that enables secure communication between two computers. By creating an encrypted tunnel, SSH allows users to securely access remote systems and transfer data.

The MySQL server by default only listens on localhost, which means it can only be accessed by programs running on the same machine.

In other cases, though, you may need to connect to the server from a remote location. Configuring the MySQL server to allow remote connections is one approach, but this needs administration access and may pose security problems.

Creating an SSH tunnel from the local machine to the server is a more secure option. SSH tunneling is a means of relaying services ports through an encrypted SSH connection between a client and a server system.

In this tutorial, you will connect to MySQL through SSH Tunnel. We will also address a few FAQs on how to connect to MySQL through SSH Tunnel.

Advantages of SSH Tunnel

  1. Secure Communication: SSH tunnels provide encrypted connections, protecting sensitive data from interception or malicious attacks.
  2. Remote Access: It allows secure remote access to servers and systems, enabling management and troubleshooting from any location.
  3. Bypassing Restrictions: SSH tunnels can bypass firewalls and access blocked content, ensuring unrestricted internet access.
  4. Secure File Transfers: With SSH tunnels, files can be securely transferred between computers, preventing unauthorized access or tampering.
  5. Privacy and Confidentiality: SSH tunnels ensure privacy by securely transmitting data, safeguarding personal and confidential information from unauthorized access.

Prerequisites

  • Client for SSH.
  • SSH access to the system on which MySQL server runs.

Create an SSH Tunnel on Linux and macOS

The ssh client is preinstalled on most Linux and Unix-based systems.

You may construct an SSH tunnel with the following command if you're using Linux or macOS as your operating system:

ssh -N -L 3336:127.0.0.1:3306 [USER]@[SERVER_IP]

The following are the options that are used:

  • -N - Instructs SSH not to execute a remote command.
  • -L 3336:127.0.0.1:3306 - It creates a local port forwarding. The local port (3306), the target IP (127.0.0.1) and the remote port (3306) are separated with a colon (:).
  • [USER]@[SERVER IP] - The remote SSH user and server IP address.
  • Use the -f option, to run the command in the background.
  • If the SSH server is listening on a port other than 22 (the default), use the -p [PORT NUMBER] option to specify the port.

You'll be requested to enter your SSH user password after running the command. You will be logged in to the server and the SSH tunnel will be established after entering it. Setting up SSH key-based authentication and connecting to the server without typing a password is a fantastic idea.

Now you can point your local computer MySQL client to 127.0.0.1:3336 enter the remote database login credentials and visit the MySQL server.

For example, to connect to the MySQL server using the command line mysql client you would issue:

mysql -u MYSQL_USER -p -h 127.0.0.1

Where MYSQL_USER is the remote MySQL user with database access capabilities.

Enter the MySQL user password when prompted.

In the console where the ssh client is running, press CTRL+C to end the SSH tunnel.

Create an SSH Tunnel on Windows

Windows users will first need to download and install an SSH client application. PuTTY is the most common SSH client for Windows. You can download PuTTY here.

To construct an SSH tunnel to the MySQL server with PuTTY, perform these steps:

  1. Start Putty and type in the server's IP address in the Host name (or IP address) field:

2. Expand SSH and select Tunnels from the Connection menu. In the Source Port field, type 3306 and in the Destination field, type 127.0.0.1:3306:

3. Return to the Session page to save your settings so you don't have to type them again.

In the Saved Session area, type the name of the session and hit the Save button.

4. By clicking the Open button, you can select a previously stored session and connect it to the remote server.

A new window asking for your login and password will pop up. You will be logged in to the server and the SSH tunnel will be formed once you enter the username and password.

You can connect to the server without entering a password if you enable public-key authentication.

You can now use your local MySQL client to connect to the distant database.

Enter 127.0.0.1 in the Hostname / IP field and the MySQL user and password in the User and Password fields if you're using HeidiSQL:

FAQs to Connect to MySQL through SSH Tunnel

What are the benefits of connecting to MySQL through an SSH tunnel? 

Connecting via an SSH tunnel ensures encrypted data transfer, secure authentication, and enables remote access to MySQL servers hosted on private networks or behind firewalls.

Can I connect to MySQL through an SSH tunnel on any operating system?

 Yes, SSH tunnels work on various operating systems like Windows, macOS, and Linux. SSH clients are available for all these platforms.

Do I need root access to use an SSH tunnel for MySQL? 

Root access is not always required. You only need SSH access and the necessary permissions to establish the tunnel and connect to MySQL.

Can I use an SSH tunnel with any MySQL client? 

Yes, you can use an SSH tunnel with any MySQL client that supports connecting through an SSH connection, such as MySQL Workbench or the mysql command-line client.

Are there any performance implications when using an SSH tunnel for MySQL? 

While SSH tunneling introduces some overhead, modern systems generally handle it well, and the impact on performance is minimal in most cases.

Can I connect to multiple MySQL servers through a single SSH tunnel? 

Yes, it is possible to connect to multiple MySQL servers through a single SSH tunnel by specifying different port forwarding configurations for each server.

Is it safe to expose the MySQL port directly to the internet without an SSH tunnel? 

Exposing the MySQL port directly to the internet can pose security risks. Using an SSH tunnel adds an extra layer of protection, making it a safer option for remote connections.

Conclusion

MySQL, the most widely used open-source database server, only accepts connections from localhost. You can safely connect to the distant MySQL server from your local client by creating an SSH tunnel.

If you have any queries, please leave a comment below and we’ll be happy to respond to them.