Connecting to MySQL Using an SSH Tunnel: A Step-by-Step Guide

tutorial mysql bash security linux

Introduction

MySQL is a relational database management system (RDBMS) that contains linked data in the form of tables. Storing, retrieving, and remotely accessing MySQL databases is of paramount importance.

One of the most secure methods of establishing a connection is SSH tunneling, also known as SSH port forwarding.

SSH tunneling establishes an encrypted connection between two machines, a local machine and a remote server, over an unsecured network, providing a secure path for MySQL connections. The connection acts as a secure channel, ensuring that the data being transmitted remains protected from eavesdropping, connection hijacking, and other attacks.

In this detailed guide, we will cover the intricacies of connecting to MySQL via SSH tunnel. By following this step-by-step approach, you will learn how to securely work with data on a remote MySQL server. The guide is also applicable to MariaDB.

Preparing for connection

Before creating an SSH tunnel to connect to MySQL, some prerequisites must be met.

  • MySQL server is installed and running, and SSH access is enabled on the remote machine.
  • MySQL and SSH clients are installed on the local machine. Users can use MySQL client tools such as mysql via the command line or graphical user interfaces such as MySQL Workbench.

Set up an SSH tunnel

Your operating system determines the process of creating an SSH connection.

  • Windows - You can use tools such as PuTTY to set up an SSH tunnel.
  • Unix-based systems such as macOS or Linux use the OpenSSH package and terminal commands ssh from with appropriate arguments to set up the tunnel.

Create an SSH Tunnel on Linux and macOS

  1. Open a terminal and use the following syntax to create an SSH tunnel:
ssh -p [SSH_PORT] -i ~/.ssh/[SSH_PRIVATE_KEY].pem -N -L [LOCAL_PORT]:127.0.0.1:[DB_PORT] [SSH_USER]@[SSH_SERVER]
  • LOCAL_PORT - the port number on your local machine (e.g. 3306)
  • SSH_SERVER - hostname or IP address of MySQL server
  • DB_PORT - MySQL server port (default 3306)
  • SSH_USER - user name on the remote server
  • SSH_PORT - SSH server port, by default it is port 22. If the port has not been changed, this argument can be omitted.
  • SSH_PRIVATE_KEY - path to private key from ssh server. It is usually stored in the ~/.ssh directory where your key pair is stored. In case your public key has already been added to the server - this argument can be omitted from the general command.
  • -N - Tells SSH not to execute the remote command.
  • -L - Creates local port forwarding, the remote server port will be forwarded to the local machine

Example:

ssh -i ~/.ssh/example.pem -N -L 3307:127.0.0.1:3306 ubuntu@example.com

MySQL client - remote connection

  1. Once the remote host port is listened to by your local machine you can configure the MySQL client on your computer to access the remote database using the local address 127.0.0.1:[LOCAL_PORT].

You must specify MySQL user credentials to access the server. For example, to connect via the mysql command line, use this command:

mysql -u MYSQL_USER -PLOCAL_PORT -p -h 127.0.0.1
mysql -u example_user -P3307 -p -h 127.0.0.1

Where MYSQL_USER is the name of the remote MySQL user with appropriate database access rights, LOCAL_PORT is the port your operating system is listening on. When prompted, enter the password of this database user.

Creating an SSH tunnel in Windows. Using PuTTY

    1. Download and run PuTTY: a popular SSH and telnet client for Windows.
    1. Configure an SSH tunnel:. Enter the host name or IP address of the remote server in the "Host name (or IP address)" field, specified username before the @, e.g. ubuntu@1.1.1.1 .

Configure PuTTY session host and username

    1. Navigate to "Connection > SSH > Tunnels" in the left menu. Enter the local port number (for example, 3307) in the "Source port" field. Enter the MySQL server hostname and port (default 3306) in the "Destination" field (format: 127.0.0.1:DB_PORT). Then click the "Add" button to add the forwarded port.

Configure an SSH tunnel

    1. Authentication: Go to SSH/Auth/Credentials and select the path to the private SSH key.

Configure SSH Authentication

    1. Save and establish the connection: Go back to the Session configuration and Enter the session name in the Saved Session field and click on the Save button, and then click "Open" to start the SSH connection.

Save and establish the SSH connection

Finally

In conclusion, setting up a secure connection to MySQL via SSH-tunnel is an important step for data protection when communicating with localhost. The article detailed the steps to create an SSH tunnel for different operating systems. The emphasis was on using a pair of ssh keys when forwarding ports to the local machine, which is a good practice, as opposed to using passwords.

This approach not only increases data protection, but also provides the flexibility to access MySQL databases remotely.

Feel free to leave comments if you have any questions.

Related links

Similar Articles