Chapter 6
Chapter 6 — Securing Remote Access
The fastest way to get your MySQL server compromised is to expose port 3306 to the internet with bind-address = 0.0.0.0 and no firewall. Bots scan for it continuously — if they find it, they'll attempt to brute-force every account within minutes. This chapter covers the right way to think about remote access: restricting what MySQL listens on, layering firewall rules, enforcing SSL, and — for home servers — the much better alternative of an SSH tunnel that opens nothing to the internet at all.
What this chapter covers: Checking what MySQL is actually listening on. The three-layer security model (bind-address + UFW + MySQL user@host). The
bind-address options and which to use. UFW rules scoped to specific IPs. SSL/TLS status in MySQL 8.0 (on by default), verifying encryption is in use, and enforcing it per-user. The SSH tunnel — why it's the right choice for home servers wanting GUI tool access, with a complete walkthrough. Three practical scenarios covering local-only, LAN access, and remote access. Troubleshooting.
First Question: Do You Actually Need Remote Access?
Before configuring anything, decide whether remote access to MySQL is genuinely needed. Most web servers run the application and the database on the same machine — PHP connects to MySQL via the Unix socket, and bind-address = 127.0.0.1 is the correct and secure configuration. Remote access is only needed when:
- Your application runs on a separate server — a dedicated app tier connecting to a dedicated database server
- You want to use a GUI tool like MySQL Workbench from your development machine
- You're running replication — a replica server reading from the primary
- A backup script on a different machine needs to dump the database
If none of these apply, leave bind-address = 127.0.0.1 and skip the rest of this chapter — you're already in the most secure configuration possible.
For single-server home setups: if you just want to use MySQL Workbench from your Windows/Mac machine, the SSH tunnel (covered later in this chapter) gives you that without opening any port to the internet. It's the recommended approach.
Checking What MySQL Is Currently Listening On
# See which interfaces and ports MySQL is listening on
$ sudo ss -tlnp | grep mysql
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 70 127.0.0.1:3306 0.0.0.0:* users:(("mysqld",pid=1234))
LISTEN 0 70 127.0.0.1:33060 0.0.0.0:* users:(("mysqld",pid=1234))
# 127.0.0.1 = localhost only — not accessible from outside. Good.
# 33060 = MySQL X protocol port (used by MySQL Shell)
# Compare with what SHOULDN'T be there (bound to all interfaces):
LISTEN 0 70 0.0.0.0:3306 0.0.0.0:* users:(("mysqld",pid=1234))
# 0.0.0.0 = listening on ALL interfaces — reachable from the internet
# Also check via MySQL itself
$ sudo mysql -e "SHOW VARIABLES LIKE 'bind_address';"
+--------------+-----------+
| Variable_name| Value |
+--------------+-----------+
| bind_address | 127.0.0.1 |
+--------------+-----------+
The Three-Layer Security Model
Remote MySQL access is only possible when all three layers permit it. Each layer is independently controlled. Missing any one of them blocks the connection.
1
bind-address — which interfaces MySQL listens on
Set in mysqld.cnf.
127.0.0.1 = local only, 0.0.0.0 = all interfaces (internet-accessible). This is the first gate — if MySQL isn't listening on an interface, no connection from that network is even possible.↓
2
UFW — which source IPs can reach port 3306
Set with ufw allow. Even if MySQL is listening on all interfaces, UFW drops packets from addresses not explicitly permitted. Always scope rules to specific source IPs — never
ufw allow 3306 without a source restriction.↓
3
MySQL user@host — which accounts authenticate from which hosts
Set with CREATE USER / GRANT. A connection that passes the firewall still needs a MySQL account whose
@host matches the source IP. 'user'@'192.168.1.50' will only authenticate from that exact IP — all others get access denied.Remote machine (192.168.1.50) trying to connect to MySQL:
[192.168.1.50] ──► port 3306 ──► Layer 1: bind-address?
│
0.0.0.0 → pass │ 127.0.0.1 → DROP (never reaches UFW)
▼
Layer 2: UFW rule?
│
allow from 192.168.1.50 → pass │ no rule → DROP
▼
Layer 3: MySQL user@host?
│
'user'@'192.168.1.50' → AUTHENTICATED │ no match → ACCESS DENIED
All three must say YES for a connection to succeed.
Configuring bind-address
127.0.0.1
Listen on loopback only. Only connections from the same machine (via Unix socket or TCP loopback) are accepted.
Default — use for single-server setups
0.0.0.0
Listen on all IPv4 interfaces. Reachable from the internet — must be combined with tight UFW rules and MySQL user@host restrictions.
Only if remote access is needed
192.168.1.10
Listen on a specific interface (your LAN IP). Unreachable from the internet even without UFW — only accessible from the local network.
For LAN-only access (most secure)
::
Listen on all IPv4 and IPv6 interfaces. Use with care — IPv6 may bypass firewall rules you wrote thinking only about IPv4.
Only if you need IPv6 remote access
Change it in /etc/mysql/mysql.conf.d/mysqld.cnf:
[mysqld]
# Local only (default, most secure)
bind-address = 127.0.0.1
# LAN access only (bind to your server's LAN IP — find it with: ip addr show)
# bind-address = 192.168.1.100
# All interfaces (needed for public remote access — must use with UFW + user@host)
# bind-address = 0.0.0.0
# After editing the config, validate and restart
$ sudo mysqld --validate-config && sudo systemctl restart mysql
# Confirm the new bind address is active
$ sudo ss -tlnp | grep 3306
Find your server's LAN IP first. Before setting a specific LAN IP as the bind address, confirm it:
ip addr show | grep 'inet '. Use the address on your LAN interface (typically eth0, ens3, or similar — not lo). If that IP changes (DHCP), MySQL won't start next time. Set a static LAN IP on your server or router before using this approach.
UFW Rules for MySQL
Never run
ufw allow 3306 without a source restriction. This opens MySQL to the entire internet. Bots scan for port 3306 continuously. Always scope the rule to specific IPs or subnets.
# Allow MySQL from a specific LAN machine only
$ sudo ufw allow from 192.168.1.50 to any port 3306 comment 'MySQL - dev workstation'
# Allow from an entire LAN subnet
$ sudo ufw allow from 192.168.1.0/24 to any port 3306 comment 'MySQL - LAN only'
# Allow from a specific remote IP (e.g. a known VPS or office IP)
$ sudo ufw allow from 203.0.113.42 to any port 3306 comment 'MySQL - office IP'
# Verify rules were added
$ sudo ufw status numbered | grep 3306
To Action From
-- ------ ----
[ 5] 3306 ALLOW IN 192.168.1.0/24
# Remove a rule if you no longer need it (use the number from ufw status numbered)
$ sudo ufw delete 5
SSL/TLS — Encrypting the Connection
Even on a private LAN, credentials and query results travel in plain text if the connection isn't encrypted. MySQL 8.0 solves this by generating self-signed SSL certificates automatically at first startup and enabling SSL by default — no configuration required. This section shows how to verify encryption is working and how to enforce it.
Checking SSL status
# Check that SSL is available on the server
mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
# Check where the auto-generated certificates live
mysql> SHOW VARIABLES LIKE '%ssl_cert%';
+-----------------+------------------------------+
| Variable_name | Value |
+-----------------+------------------------------+
| ssl_cert | /var/lib/mysql/server-cert.pem|
+-----------------+------------------------------+
# Check if YOUR CURRENT connection is encrypted
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| Ssl_cipher | TLS_AES_256_GCM_SHA384 |
+---------------+-----------------------------+
# A non-empty cipher = encrypted connection ✓
# Empty Ssl_cipher = unencrypted connection
Socket connection (sudo mysql)
Local Unix socket connections skip SSL — there's nothing to encrypt on a socket.
Ssl_cipher will be empty. This is fine — socket connections never leave the machine.
Remote TCP connection without SSL
Ssl_cipher empty on a TCP connection from another machine = credentials and data are in plain text on the wire. Add --ssl-mode=REQUIRED to enforce SSL for remote clients.
Connecting with SSL from a remote client
# Connect from a remote machine with SSL required
$ mysql -u bookshop_app -p -h 192.168.1.100 --ssl-mode=REQUIRED
# --ssl-mode options:
# DISABLED = never use SSL
# PREFERRED = use SSL if available, plain text otherwise (default)
# REQUIRED = always use SSL, fail if unavailable
# VERIFY_CA = SSL + verify the server certificate against a CA
# VERIFY_IDENTITY = SSL + verify CA + verify hostname matches cert
# With self-signed certs (MySQL's auto-generated default):
# REQUIRED works — encrypts the connection
# VERIFY_CA and VERIFY_IDENTITY will FAIL unless you distribute the CA cert
# Confirm SSL is active on the remote connection
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+------------------------+
| Ssl_cipher | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
Enforcing SSL for specific users
# Require SSL for any connection from this user account
# If a client connects without SSL, the connection is rejected
mysql> ALTER USER 'bookshop_app'@'192.168.1.50' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)
# Create a new remote user with SSL required from the start
mysql> CREATE USER 'remote_admin'@'192.168.1.50'
-> IDENTIFIED BY 'RemoteP@ss!5'
-> REQUIRE SSL;
# Check what a user REQUIRE setting is
mysql> SHOW CREATE USER 'bookshop_app'@'192.168.1.50'\G
CREATE USER 'bookshop_app'@'192.168.1.50'
IDENTIFIED WITH caching_sha2_password AS '...'
REQUIRE SSL
PASSWORD EXPIRE DEFAULT ...
# Remove the SSL requirement (back to optional)
mysql> ALTER USER 'bookshop_app'@'192.168.1.50' REQUIRE NONE;
In PHP/PDO, enable SSL for MySQL connections with the
PDO::MYSQL_ATTR_SSL_CA option or by setting ssl_mode=REQUIRED in the DSN. Without this, even if MySQL requires SSL for the account, PHP may send a plain-text connection and get rejected.
The SSH Tunnel — The Right Answer for Home Servers
Opening port 3306 to the internet, even with UFW restrictions and SSL, adds a persistent attack surface. If your MySQL is only accessed remotely by you — from your dev machine, for administration or using MySQL Workbench — an SSH tunnel gives you full access with no port opened, no firewall rules for MySQL, and no credentials exposed. The tunnel forwards a local port on your machine through your SSH connection to MySQL on the server.
Without SSH tunnel (port 3306 open to internet):
[Your machine] ──► internet ──► port 3306 ──► MySQL
↑ exposed to bots/scanners
With SSH tunnel (nothing open except port 22):
[Your machine:3307] ──► SSH (port 22) ──► [Server: localhost:3306]
↑ fully encrypted, protected by SSH keys
MySQL sees the connection as coming from 127.0.0.1 (localhost)
Setup Walkthrough · SSH Tunnel to MySQL
Access MySQL remotely via SSH — no port 3306 opening required.
1
Prerequisites: SSH access to the server is working (port 22 open, your SSH key is installed). MySQL's
bind-address stays at 127.0.0.1 — no changes needed to MySQL config.
2
Open the tunnel from your dev machine (Linux, macOS, or WSL on Windows).
# Basic tunnel: forward local port 3307 → server's localhost:3306 via SSH
$ ssh -L 3307:127.0.0.1:3306 philip@your-server.example.com -N
# Flags:
# -L 3307:127.0.0.1:3306 = local port 3307 → remote 127.0.0.1:3306
# -N = don't run a command (tunnel only, no shell)
# Leave this terminal open while you need the tunnel
# Background version (runs without occupying a terminal)
$ ssh -L 3307:127.0.0.1:3306 philip@your-server.example.com -fN
# -f = fork to background after authentication
3
Connect MySQL client to the tunnel (while the tunnel is running).
# Connect as if MySQL is running locally on port 3307
$ mysql -u bookshop_app -p -h 127.0.0.1 -P 3307
# MySQL sees this connection as coming from 127.0.0.1 (localhost)
# so the user account must be bookshop_app@'localhost' or bookshop_app@'127.0.0.1'
4
Connect MySQL Workbench via SSH tunnel (no terminal needed — Workbench handles it).
# In MySQL Workbench, create a new connection:
# Connection Method: Standard TCP/IP over SSH
#
# SSH Hostname: your-server.example.com:22
# SSH Username: philip
# SSH Key File: C:\Users\philip\.ssh\id_ed25519 (your private key)
#
# MySQL Hostname: 127.0.0.1
# MySQL Port: 3306
# Username: bookshop_app
# Password: (stored in vault or entered at connect time)
#
# Workbench opens the tunnel automatically when you connect
5
Add a shortcut to your SSH config so you don't type the full command each time.
# Add to ~/.ssh/config on your dev machine:
Host mysql-tunnel
HostName your-server.example.com
User philip
IdentityFile ~/.ssh/id_ed25519
LocalForward 3307 127.0.0.1:3306
# Then open the tunnel with just:
$ ssh mysql-tunnel -N
No changes to MySQL config, no UFW rules for port 3306, no new attack surface. The tunnel is authenticated entirely by your SSH key.
Three Practical Scenarios
Scenario A — Recommended for most single-server setups
Local access only — web app and MySQL on the same server
bind-address stays at
UFW: no rule for port 3306 — not needed.
User accounts:
SSL: not needed for socket connections.
This is the most secure configuration. PHP connects via Unix socket (
127.0.0.1 (default).UFW: no rule for port 3306 — not needed.
User accounts:
'app_user'@'localhost' — already correct.SSL: not needed for socket connections.
This is the most secure configuration. PHP connects via Unix socket (
host=localhost in DSN). Nothing listens on a network interface for MySQL. Zero attack surface.
Scenario B — LAN access from a dev workstation
Open MySQL to your home/office network only
bind-address: set to your server's static LAN IP (e.g.
UFW:
User account:
SSL: enforce with
Only reachable from inside your LAN. If your server is behind a home router, it's not directly internet-accessible anyway — but binding to the LAN IP and using UFW provides defence in depth.
192.168.1.100). This only exposes MySQL on the LAN interface — not to the internet even without UFW.UFW:
sudo ufw allow from 192.168.1.0/24 to any port 3306 comment 'MySQL LAN'User account:
CREATE USER 'dev_user'@'192.168.1.%' IDENTIFIED BY '...' REQUIRE SSL;SSL: enforce with
REQUIRE SSL on the account and --ssl-mode=REQUIRED on the client.Only reachable from inside your LAN. If your server is behind a home router, it's not directly internet-accessible anyway — but binding to the LAN IP and using UFW provides defence in depth.
Scenario C — Recommended for remote admin access
Remote access via SSH tunnel — nothing opened to the internet
bind-address: stays at
UFW: no rule for port 3306 — not needed.
User accounts:
SSL: not needed — the SSH channel is already encrypted.
To connect:
Zero attack surface for MySQL. The only thing exposed is SSH (port 22), which is already protected by key-based auth and SSH hardening from the Security course.
127.0.0.1 (no changes).UFW: no rule for port 3306 — not needed.
User accounts:
'admin'@'127.0.0.1' — the tunnel makes remote connections appear local.SSL: not needed — the SSH channel is already encrypted.
To connect:
ssh -L 3307:127.0.0.1:3306 philip@server.example.com -N, then connect MySQL to 127.0.0.1:3307. MySQL Workbench supports this natively via "Standard TCP/IP over SSH".Zero attack surface for MySQL. The only thing exposed is SSH (port 22), which is already protected by key-based auth and SSH hardening from the Security course.
Troubleshooting
Can't connect remotely even though bind-address is 0.0.0.0
Check all three layers in order. Layer 1:
sudo ss -tlnp | grep 3306 — does it show 0.0.0.0:3306? If not, restart MySQL after saving the config change. Layer 2: sudo ufw status — is there a rule allowing the source IP on port 3306? Layer 3: SELECT user, host FROM mysql.user WHERE user='youruser'; — does the host column match the connecting IP (exactly or via wildcard)? All three must pass.
MySQL won't start after changing bind-address
The IP you specified in
bind-address doesn't exist on any network interface on the server. Check available IPs: ip addr show. If you set a static LAN IP that hasn't been assigned yet, or your DHCP IP changed, MySQL can't bind to it. Either fix the network config to assign that IP statically, or change bind-address back to 127.0.0.1. Check the error log: sudo tail -20 /var/log/mysql/error.log.
SSH tunnel opens but MySQL connection through it says "Access denied"
The tunnel makes the connection appear to come from
127.0.0.1 (loopback), not from your actual dev machine IP. The MySQL user account must have @'localhost' or @'127.0.0.1' as its host — not @'192.168.1.50'. Check: SELECT user, host FROM mysql.user WHERE user='youruser';. If necessary: CREATE USER 'youruser'@'127.0.0.1' IDENTIFIED BY 'pass'; and grant the same privileges.
SSL required but getting "SSL connection error: unknown error number"
Most common cause: TLS version mismatch. MySQL 8.0 defaults to TLS 1.2 and 1.3; very old MySQL clients or PHP builds may only support TLS 1.0. Check the MySQL server's TLS configuration:
SHOW VARIABLES LIKE 'tls_version';. Also check the client side: for PHP, ensure OpenSSL is recent. A quick diagnostic: mysql -u user -p -h host --ssl-mode=REQUIRED --tls-version=TLSv1.2 — if this works, it's a TLS 1.3 compatibility issue on the client.
UFW rule added but connection from that IP is still refused
Check that UFW is actually enabled:
sudo ufw status — should say "Status: active". Also verify the rule direction: ufw allow from creates an ALLOW IN rule (for incoming connections), which is what you want. Check the rule is listed: sudo ufw status numbered | grep 3306. If the server is behind a NAT router (e.g. at home), the connecting machine's IP visible to the server is the LAN IP, not the internet IP — make sure your UFW rule matches the LAN IP.
Quick Reference — Chapter 6
| Command / Setting | Purpose |
|---|---|
| sudo ss -tlnp | grep mysql | Check which interfaces and ports MySQL is listening on |
| bind-address = 127.0.0.1 | Listen on localhost only — safest for single-server setups (in mysqld.cnf) |
| bind-address = 0.0.0.0 | Listen on all interfaces — only use if remote access is needed, must combine with UFW |
| sudo ufw allow from 192.168.1.50 to any port 3306 | Open MySQL to a specific IP only — always scope to source IP, never bare "allow 3306" |
| SHOW STATUS LIKE 'Ssl_cipher'; | Check if the current connection is SSL encrypted (empty = not encrypted) |
| SHOW VARIABLES LIKE 'have_ssl'; | Confirm SSL is available on the server (YES = auto-generated certs in /var/lib/mysql) |
| ALTER USER 'u'@'h' REQUIRE SSL; | Force SSL for a specific user — connections without SSL are rejected |
| mysql --ssl-mode=REQUIRED -h host -u user -p | Connect with SSL enforced (client-side flag) |
| ssh -L 3307:127.0.0.1:3306 user@server -N | Open SSH tunnel: connect locally on port 3307 to reach remote MySQL on 3306 |
| mysql -h 127.0.0.1 -P 3307 -u user -p | Connect to MySQL through the SSH tunnel (run after the tunnel is open) |
| SHOW CREATE USER 'u'@'h'\G | View a user's SSL requirements and other account settings |