Creating an SSH Tunnel to Connect Remotely to MySQL

Securely connecting to remote MySQL databases via SSH tunnels can be a bit daunting at first, but once you understand the process, it’s straightforward. This guide outlines the steps to achieve it with clarity.

Definitions

• Remote-Server: The server hosting the MySQL database.

• Working-Server: The local machine or server from which you want to access the MySQL database.

Requirements

1. SSH Key Pair:

• A public key (id_rsa.pub) should already be added to the ~/.ssh/authorized_keys file on the Remote-Server.

• The corresponding private key (id_rsa) should exist on the Working-Server.

2. MySQL Access: Ensure that the MySQL service is running on the Remote-Server, typically on port 3306.

Steps to Set Up the SSH Tunnel

1. Create the SSH Configuration File

• Navigate to the .ssh directory on the Working-Server:

cd ~/.ssh

• Create or edit the config file using your preferred editor (e.g., vim):

vim config

• Add the following configuration:

Host Working-Server
Hostname remote-server.com
User root
IdentitiesOnly yes
IdentityFile /root/.ssh/id_rsa
LocalForward 3307 127.0.0.1:3306

Explanation:

• Host: An alias for the connection. Use any name you like (e.g., “Working-Server”).

• Hostname: The actual domain or IP of the Remote-Server.

• User: The SSH user (e.g., root, or another user with SSH access).

• IdentitiesOnly: Ensures only the specified identity file is used.

• IdentityFile: Path to your private key.

• LocalForward: Maps local port 3307 on the Working-Server to the remote MySQL port 3306.

• Save and exit (:wq in vim).

2. Establish the SSH Tunnel

Run the following command to start the SSH tunnel:

ssh -Nv Working-Server

Flags:

• -N: Prevents executing remote commands (used for tunneling only).

• -v: Enables verbose mode for debugging.

If everything is configured correctly, the tunnel will be established.

3. Connect to the Remote MySQL Database

Now, connect to the MySQL database on the Remote-Server via the local tunnel:

mysql -h 127.0.0.1 -P 3307 -u your_mysql_user -p

Explanation:

• -h 127.0.0.1: Connects to the local host (the SSH tunnel forwards the traffic to the Remote-Server).

• -P 3307: Specifies the local port forwarded in the SSH tunnel.

• -u your_mysql_user: The MySQL username.

• -p: Prompts for the MySQL password.

Testing the Setup

• Ensure the tunnel is running in one terminal.

• Open another terminal and execute the MySQL command. If the connection succeeds, you’re good to go!

Troubleshooting

• SSH Connection Refused: Verify that the public key is properly added to the Remote-Server (~/.ssh/authorized_keys).

• Port Already in Use: If 3307 is occupied on your local machine, choose a different port in the LocalForward configuration (e.g., 3308).

• Firewall Issues: Ensure SSH (port 22) and MySQL (port 3306) are accessible on the Remote-Server.

Final Notes

This setup allows secure access to remote MySQL instances without exposing the MySQL server to the internet. You can reuse this configuration for other applications by simply modifying the LocalForward directive for different ports.

Got questions or need help troubleshooting? Let me know!