I want to check something in the production database. The fastest path — opening MySQL's port 3306 to the internet — is also the worst path. That's a lot of bots that will find it and hammer it within hours.
The right way to do remote database access is through an SSH tunnel: your GUI client connects to a local port, SSH transparently forwards that traffic to the database server over an encrypted connection. From MySQL's perspective, you're connecting from localhost. The database port never touches the public internet.
This guide covers three approaches in order of security: SSH tunnel (what I use for daily work), VPN-based access, and direct TCP with strict IP restriction.
I run MySQL on Tencent Cloud Lighthouse. For secure remote database access, Lighthouse's console-level firewall gives you an important additional layer: you can block MySQL's default port (27017/3306) at the infrastructure level, independent of the OS firewall, so the port is never exposed even if UFW is misconfigured. The SSH tunnel approach in this guide works particularly well with Lighthouse because OrcaTerm (the browser-based terminal) and the console firewall rules are managed in the same control panel — easy to verify and adjust.
- Key Takeaways
MySQL on port 3306 exposed to the internet receives:
The correct approach: MySQL listens only on localhost (127.0.0.1). Access from outside goes through SSH (encrypted, key-authenticated) or a VPN.
An SSH tunnel creates an encrypted connection that forwards a local port to MySQL on the remote server.
# On your local machine:
# Syntax: ssh -L LOCAL_PORT:REMOTE_HOST:REMOTE_PORT user@server
ssh -L 3307:127.0.0.1:3306 ubuntu@YOUR_SERVER_IP -N
# -L: local port forwarding
# 3307: port on your local machine (can be any unused port)
# 127.0.0.1:3306: MySQL on the server (relative to the server)
# -N: don't execute a command, just forward
# Now in a different terminal, connect to MySQL via the tunnel:
mysql -h 127.0.0.1 -P 3307 -u myapp_user -p myapp_db
Why port 3307 instead of 3306 locally? Avoids conflict if you have a local MySQL running on 3306.
Keep the SSH tunnel open as long as you need the connection. Close it with Ctrl+C.
For a more permanent tunnel that reconnects automatically, use autossh:
# Install autossh
sudo apt install -y autossh # On your local machine (Mac: brew install autossh)
# Start persistent tunnel
autossh -M 0 -f -N \
-o "ServerAliveInterval 30" \
-o "ServerAliveCountMax 3" \
-L 3307:127.0.0.1:3306 \
ubuntu@YOUR_SERVER_IP
# The -f flag runs it in the background
# autossh automatically reconnects if the tunnel drops
# /etc/systemd/system/mysql-tunnel.service
[Unit]
Description=SSH Tunnel to MySQL on production server
After=network.target
[Service]
User=YOUR_LOCAL_USER
ExecStart=/usr/bin/autossh -M 0 -N \
-o "ServerAliveInterval 30" \
-o "ServerAliveCountMax 3" \
-o "ExitOnForwardFailure yes" \
-i /home/YOUR_LOCAL_USER/.ssh/id_ed25519 \
-L 3307:127.0.0.1:3306 \
ubuntu@YOUR_SERVER_IP
Restart=always
RestartSec=5s
[Install]
WantedBy=multi-user.target
Add to ~/.ssh/config on your local machine for easy connection:
Host prod-mysql
HostName YOUR_SERVER_IP
User ubuntu
LocalForward 3307 127.0.0.1:3306
IdentityFile ~/.ssh/id_ed25519
Then:
ssh prod-mysql -N # Open the tunnel
mysql -h 127.0.0.1 -P 3307 -u myapp_user -p
If SSH tunnel is not practical (e.g., connecting from a CI/CD system), allow direct MySQL access from a specific IP.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Change:
bind-address = 127.0.0.1
To:
bind-address = 0.0.0.0
Or bind to a specific interface:
bind-address = YOUR_SERVER_PUBLIC_IP
sudo systemctl restart mysql
sudo mysql
-- Create user that can connect only from your specific IP
CREATE USER 'remote_user'@'YOUR_REMOTE_IP' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'remote_user'@'YOUR_REMOTE_IP';
FLUSH PRIVILEGES;
-- Verify
SELECT user, host FROM mysql.user WHERE user = 'remote_user';
# UFW: allow MySQL only from your specific IP
sudo ufw allow from YOUR_REMOTE_IP to any port 3306
# Also open in Lighthouse console firewall:
# Add rule: TCP 3306, source: YOUR_REMOTE_IP
mysql -h YOUR_SERVER_IP -P 3306 -u remote_user -p myapp_db
Most MySQL GUI tools support SSH tunnel natively — no manual terminal commands needed.
Similar to DBeaver — look for the SSH/SSL tab in the data source configuration.
For applications connecting from another server (e.g., a separate app server connecting to a dedicated database server):
Consider using the private network IP instead of the public IP. Tencent Cloud Lighthouse instances in the same region can communicate via private network, which is faster and doesn't count against bandwidth.
DB_HOST=10.x.x.x # Private IP, not public IP
# Node.js (mysql2)
mysql://myapp_user:password@DB_HOST:3306/myapp_db
# Python (SQLAlchemy)
mysql+pymysql://myapp_user:password@DB_HOST:3306/myapp_db
# PHP (PDO)
$dsn = "mysql:host=DB_HOST;port=3306;dbname=myapp_db";
After configuring a remote user and opening the firewall, connections still fail? The issue is usually bind-address.
MySQL only accepts connections on the interface it's bound to. If bind-address = 127.0.0.1, MySQL never receives connections on the public IP — even if the firewall allows them.
Debug:
# Check what address MySQL is listening on
sudo ss -tlnp | grep 3306
# tcp LISTEN 0 151 127.0.0.1:3306 ... (localhost only)
# tcp LISTEN 0 151 0.0.0.0:3306 ... (all interfaces)
# Check the MySQL config
grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf
After changing bind-address, always restart MySQL: sudo systemctl restart mysql.
| Error | Likely cause | Fix |
|---|---|---|
Connection refused |
MySQL not listening on that IP/port | Check bind-address in mysqld.cnf |
Host 'x.x.x.x' is not allowed |
No MySQL user for that host | Create user with @'YOUR_IP' |
Access denied for user |
Wrong password or user doesn't exist | Check user with SELECT user, host FROM mysql.user |
| Firewall timeout | Port blocked | Check UFW + Lighthouse console firewall |
| SSL error | MySQL requires SSL but client doesn't use it | Add --ssl-mode=DISABLED or configure SSL |
# Test MySQL port is reachable (from remote machine)
nc -zv YOUR_SERVER_IP 3306
# or
telnet YOUR_SERVER_IP 3306
Do I need a separate server for remote MySQL?
For most individual projects and small teams, running remote MySQL on the same server as your application is practical. The zero-latency between app and database (localhost communication) is a genuine advantage. Separate database servers are warranted at significant scale.
How do I back up a remote MySQL database?
Use the database's built-in dump tools for logical backups (recoverable to any host). Combine with Lighthouse snapshots for full-server point-in-time recovery. Set up automated daily cron jobs for consistent backups.
How do I connect to remote MySQL remotely for development?
Use SSH tunneling: ssh -L LOCAL_PORT:localhost:DB_PORT user@server. This routes your local database client through SSH without exposing the database port to the internet.
What user permissions should database application users have?
Application users should have only the permissions they need — typically SELECT, INSERT, UPDATE, DELETE on their specific database. Never use the root/admin database account in application connection strings.
htop or your database's status commands and upgrade if you see consistent memory pressure.Set up your secure database connection:
👉 Tencent Cloud Lighthouse — Cloud server with console-level firewall
👉 View current pricing and promotions
👉 Explore all active deals and offers