Creating an SSH Tunnel for MySQL Remote Access

Maintaining a secure database environment is vital in today's digital age. It helps prevent breaches and ensure the confidentiality of your information. A highly effective process for enhancing MySQL connection security is by implementing an SSH tunnel for remote access. This approach establishes an encrypted tunnel between your device and the server, ensuring data remains secure.

SSH Tunneling

Copy link

SSH tunneling, also referred to as SSH port forwarding, enables the secure transmission of data between networks. By establishing an encrypted SSH tunnel, data can be safely transferred without the risk of exposure to potential threats.

It possesses several benefits:

• Security: Encrypts data, keeping it safe from being seen or intercepted by others.
• Bypassing Restrictions: Allows access to services and resources blocked by firewalls.
• Flexibility: Can handle all network traffic types, fitting many uses.

And if you’re looking for a reliable, high-performance, and budget-friendly solution for your workflows, Hostman has you covered with Linux VPS Hosting options, including Debian VPS, Ubuntu VPS, and VPS CentOS.

Types of SSH Tunneling

Copy link

SSH tunneling is of three types:

1. Local Port Forwarding: It lets you redirect a port from your local machine to a destination machine using a tunnel. This is the method used in our guide. For example:

ssh -L 3307:localhost:3306 your_username@your_server_ip

2. Remote Port Forwarding: It lets you redirect a port from a remote machine to your local machine. This is useful for accessing local services from a remote machine. For example:

ssh -R 9090:localhost:80 your_username@your_server_ip

3. Dynamic Port Forwarding: It lets you create a SOCKS proxy to dynamically forward traffic through an SSH tunnel. This is useful for secure web browsing or bypassing firewalls. For example:

ssh -R 9090:localhost:80 your_username@your_server_ip

Prerequisites

Copy link

Before beginning, ensure you have:

• SSH client (OpenSSH, or PuTTY for Windows)
• MySQL server info
• SSH into the MySQL host machine securely.

Setting Up Remote Access

Copy link

Go through these essential steps to securely set up remote access to your MySQL server through SSH tunnel:

Step 1: Facilitate Connectivity

Copy link

For remote access, tune it to listen on an external IP. This allows SQL access from localhost to all IPs. Here’s how to do it:

Access MySQL Config File

Using a text editor, access the config file. On Ubuntu, it's typically located at:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

If the file isn't in its expected place, search for it with:

sudo find / -name mysqld.cnf

Edit bind-address

Inside the file, find bind-address line, which is set to 127.0.0.1 by default, limiting server to local connections:

Change the address to allow connections from all IP addresses by setting it to 0.0.0.0.

Save changes by pressing Ctrl+X, Y to confirm, and Enter to exit.

Restart MySQL

Restart service to apply the updated settings:

sudo systemctl restart mysql

Step 2: Adjust Firewall

Copy link

By default, 3306 is the standard port in MySQL. To permit remote access, ensure this port is opened in your firewall settings. Tailor these steps to your specific firewall service.

Open Port via UFW

On Ubuntu, UFW is a pre-installed firewall utility. To allow traffic on 3306:

sudo ufw allow from remote_ip to any port 3306

Substitute remote_ip with actual IP.

Open Port via Firewalld

On Red Hat-based and Fedora systems, Firewalld is the primary firewall tool. To open port 3306 for traffic, run these commands:

sudo firewall-cmd --zone=public --add-service=mysql --permanent
sudo firewall-cmd --reload

The first command permanently allows MySQL traffic, and the second reloads the firewall to make the changes.

Step 3: Open Your SSH Client

Copy link

Fire up your go-to SSH client. Opt for PuTTY on Windows, or the terminal if using macOS or Linux.

Using Terminal (Linux or macOS)

Implement this command:

ssh -L 3307:localhost:3306 your_username@your_server_ip

• 3307: It's the local port your computer will listen to.

• localhost: It's a MySQL server address used by the SSH. It's where the service runs on the machine you're connecting to.

• 3306: The remote port where the server listens for incoming connections.

• username@server_ip: Your SSH login details.

When required, verify the server's fingerprint. Confirm it matches by typing "yes" and pressing Enter. 

Once confirmed, enter your SSH password if asked and press Enter for tunneling.

After the tunnel is up, all traffic destined to local port 3307 will be forwarded to the remote machine in a secure fashion.

Using PuTTY (Windows)

Windows users can use the below-given instructions to perform tunneling:

• Launch PuTTY.

• From the left menu, direct to Connection > SSH > Tunnels.

• Input 3307 for Source port and localhost:3306 for the Destination field. Then hit Add.

• Navigate back to Session menu, enter server’s IP address and start the session using the Open button.

If you haven’t configured your user permissions yet, our How to Show Users in MySQL tutorial shows you how to list accounts and verify privileges—so you can confirm the SSH-forwarded connection uses only the minimal credentials you need.

Step 4: Connect to MySQL

Copy link

After setting up the tunnel, seamlessly link to the server through:

sudo mysql -h localhost -P 3307 -u your_mysql_user -p

Step 5: Verify the Connection

Copy link

Log into server and check if you can run queries:

Additional Safeguards for Enhanced Security

Copy link

To further enhance the MySQL remote access security, consider the following:

Implement Robust Passwords and Authentication

Copy link

Ensure using strong, unique passwords for both servers accounts. Implement key-based SSH authentication for added security. Here's how to set up SSH key authentication:

• Generate an SSH key pair via:

ssh-keygen -t rsa -b 4096 -C "your_email@example.com"

• Copy the public key to the server via:

ssh-copy-id your_username@your_server_ip

Regularly Update Your Software

Copy link

Ensure that your server, client, and all associated software are consistently updated with the latest security patches and enhancements. This practice safeguards your system against known vulnerabilities and potential threats.

Supervise and Audit Access

Copy link

Consistently examine access logs on both your MySQL and SSH server. Watch for any unusual activities or unauthorized attempts to gain access. Set up logging for both services:

• Check the SSH logs via:

sudo tail /var/log/auth.log

• Enable and check MySQL logs by adding the below-given lines in the configuration file:

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log

• You can view the general query log via:

sudo cat /var/log/mysql/mysql-general.log

• To continuously monitor the log file in real time, use:

sudo tail -f /var/log/mysql/mysql-general.log

Implement IP Whitelisting

Copy link

Limit access to your MySQL by applying IP whitelisting. It ensures that connections are permitted only from specified IP addresses, thereby enhancing security:

sudo ufw allow from your_trusted_ip to any port 3306

Replace your_trusted_ip with the IP address you trust.

Troubleshooting Issues

Copy link

Here are a few common problems and solutions:

• Unable to Connect: Check SSH configuration and firewall rules. Ensure the SSH tunnel is correctly established and the server is reachable.

• Port Already in Use: Change the local forwarding port from 3307 to another available port.

• Authentication Errors: Verify your server's credentials. Ensure that the correct user permissions are set.

• MySQL Server Not Listening on Correct IP: Double-check the MySQL bind-address configuration and ensure the server is listening on the correct IP.

Conclusion

Copy link

By adhering to this guide, you'll securely connect to your MySQL database via an SSH tunnel. This method not only boosts security but also enhances remote database management efficiency. 

Regularly check your SSH tunnel setup to ensure a stable, secure connection. This practice ensures your data stays protected, providing peace of mind for seamless database operations.

Hostman provides pre-configured and ready-to-use cloud databases, including cloud MySQL.