Introduction
In many scenarios, database servers are not exposed directly to the internet for security reasons. However, developers and applications still need to connect to them securely. One of the best solutions is to create an SSH tunnel that forwards local connections to the remote MySQL server.
In this guide, we will:
- Create a restricted SSH user who can only tunnel and not log in interactively.
- Use /sbin/nologin to prevent shell access.
- Set up port forwarding for secure MySQL access.
- Test and verify the tunnel.
Step 1: Create the SSH User
On the SSH gateway server, create a dedicated user:
|
1 |
sudo adduser mysqltunnel |
Set a strong password when prompted.
Step 2: Restrict the User to SSH Tunneling Only
Set the User’s Shell to /sbin/nologin
To prevent interactive logins, change the user’s shell:
|
1 |
sudo usermod --shell /sbin/nologin mysqltunnel |
Restrict the User in SSH Configuration
Edit the SSH config file:
|
1 |
sudo nano /etc/ssh/sshd_config |
Add the following at the bottom:
|
1 2 3 4 5 6 |
Match User mysqltunnel AllowTcpForwarding yes PermitTTY no ForceCommand echo 'This account is restricted to tunneling only.' X11Forwarding no AllowAgentForwarding no |
Restart SSH:
|
1 |
sudo systemctl restart ssh |
Step 3: Set Up the SSH Tunnel
On your local machine, use the following SSH command to set up a tunnel:
|
1 |
ssh -N -L 3307:remote-mysql-server:3306 mysqltunnel@your-ssh-server.com |
Step 4: Verify the Tunnel
Once the SSH command is running, connect to MySQL via the tunnel:
|
1 |
mysql -h 127.0.0.1 -P 3307 -u your_db_user -p |
Optional: Use SSH Key Authentication Instead of Passwords
Step 1: Generate SSH Key on Your Local Machine
|
1 |
ssh-keygen -t rsa -b 4096 -f ~/.ssh/mysqltunnel_key |
Step 2: Copy the Key to the Server
|
1 |
ssh-copy-id -i ~/.ssh/mysqltunnel_key.pub mysqltunnel@your-ssh-server.com |
Optional: Automatically Start the Tunnel on Boot
To make the SSH tunnel start automatically on reboot, create a systemd service:
|
1 |
sudo nano /etc/systemd/system/mysql-tunnel.service |
Add:
|
1 2 3 4 5 6 7 8 9 10 11 |
[Unit] Description=SSH Tunnel for MySQL After=network.target [Service] ExecStart=/usr/bin/ssh -N -L 3307:remote-mysql-server:3306 mysqltunnel@your-ssh-server.com Restart=always User=your-username [Install] WantedBy=multi-user.target |
Enable it:
|
1 2 |
sudo systemctl enable mysql-tunnel sudo systemctl start mysql-tunnel |
Conclusion
By following this guide, you have successfully created a secure, restricted SSH tunnel to access a remote MySQL database without exposing it to the public internet. Here’s what we achieved:
✅ Created a restricted SSH user (mysqltunnel)
✅ Disabled interactive shell access (/sbin/nologin)
✅ Configured SSH to allow only tunneling
✅ Set up an SSH tunnel to MySQL securely
✅ Verified the connection with MySQL Workbench
✅ Optionally enabled SSH key authentication
✅ Configured automatic tunnel startup on boot
Would you like to add firewall rules or further harden the security of this setup? Let us know in the comments!
