MySQL Remote Access: localhost Is the Bug, Not Port 3306

Where does jdbc:mysql://localhost:3306/test actually go?

Nowhere off the machine. localhost resolves to the loopback interface (127.0.0.1), so the client stack hands the TCP segment straight back to the same host and never touches an Ethernet port. Run that URL from a laptop on a hotel Wi-Fi and the driver is looking for a MySQL server on the laptop itself. The connection fails at layer three before any MySQL authentication packet exists.

The host field in a JDBC URL or in MySQL Workbench must be an address that is routable from the client. Inside the office LAN that is the server's private address. From outside, it is the site's public IP plus whatever port the edge router has been told to translate. Those are two different connection profiles and they need two different saved entries.

Check: from the database server console, mysql -h 127.0.0.1 -u user -p succeeds. That proves the daemon is running and the credentials are valid, and it isolates every later failure to the network path.

Is the server listening on anything but loopback?

A default-hardened MySQL install binds only to 127.0.0.1. The daemon is up, the port is open, and every off-box connection is refused at the TCP layer. Read the effective value rather than trusting the config file, since multiple include directories can override it:

SHOW VARIABLES LIKE 'bind_address';

If it returns 127.0.0.1, edit the server option file, set the bind address to the server's LAN interface address (or 0.0.0.0 to listen on all interfaces), and restart the service. Binding to the specific LAN address is the tighter choice on a machine with more than one NIC — common on plant servers that straddle a control VLAN and an office VLAN.

Grants are a separate gate. MySQL accounts are keyed on user and host, so 'tamer'@'localhost' is a different account from 'tamer'@'%'. A remote login against a loopback-only account fails with an access-denied error even after the network path is perfect. Create or extend the account for the source range you actually connect from, and keep it narrow:

SELECT user, host FROM mysql.user;
CREATE USER 'tamer'@'203.0.113.%' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'tamer'@'203.0.113.%';
FLUSH PRIVILEGES;

Check: from a second machine on the office LAN, mysql -h <server_private_ip> -u tamer -p test connects. Layer one and layer two are proven, the daemon is reachable off-box, and the account works. Nothing beyond this point is a MySQL problem.

Which hop drops the packet on the way out?

Between the remote client and the server sit, at minimum: the client's own firewall, the remote site's outbound policy, the internet, the office edge router's NAT table, any internal firewall or VLAN ACL, and the server's host firewall. Test them in order, physical and transport layer before protocol.

1. From the LAN test machine, confirm the host firewall on the server permits inbound TCP 3306 from the LAN subnet — a Windows Defender Firewall inbound rule or a firewalld/iptables entry, not a blanket disable.
2. Confirm the site's public IP. Static is required for a fixed connection profile; if the ISP hands out a dynamic address, register a dynamic DNS name and point the client at that name instead.
3. Check that the outbound network you are sitting on allows the destination port at all. Many guest and hotel networks pass 443 and 80 and drop everything else, which is one argument for choosing a high external port.
4. Probe the port from outside with a raw TCP test before involving any MySQL client: nc -vz <public_ip> 4000 or PowerShell Test-NetConnection <public_ip> -Port 4000. A refused connection means something answered; a timeout means a filter swallowed it silently.

Do not diagnose with a MySQL client during this stage. Its error text conflates a TCP timeout, a TLS negotiation failure, and an authentication rejection into similar-looking messages, and you lose the layer separation you need.

How do you build the NAT forward correctly?

The edge router translates one WAN-side socket to one LAN-side socket. Publish a non-standard external port rather than 3306 — the scanners that hammer the default port are automated and constant, and moving off it removes most of the noise from the auth log.

Two failures dominate here. First, the internal IP moves: the server picks up a new DHCP lease after a reboot and the forward now points at a printer. Pin the address with a reservation on the DHCP server. Second, the router refuses hairpin NAT, so a test from inside the office to the public IP fails while an actual remote client works fine. Always test the forward from a genuinely external connection — a phone hotspot is enough.

Check: the external TCP probe to <public_ip>:4000 returns open, and the MySQL error log or SHOW PROCESSLIST shows a connection attempt arriving from the remote public address.

Why tunnel instead of publishing 3306?

A forwarded MySQL port is a database credential prompt exposed to the entire internet. The protocol will happily negotiate an unencrypted session unless the server requires TLS, which puts query text and result sets on the wire in clear. On a plant network where that server also holds production or recipe data, the exposure is not worth the convenience.

Two better paths, in order of preference:

1. Site VPN. The remote client joins the office subnet, and the connection profile reverts to the plain private IP on 3306. No NAT rule, no published port, and the authentication and encryption are handled by the VPN concentrator. This is the option to ask the IT owner for first.
2. SSH tunnel. Publish only SSH on the edge and let it carry the MySQL session. MySQL Workbench has this built in as a "Standard TCP/IP over SSH" connection type. From a shell the equivalent is a local forward, after which the client points at 127.0.0.1 on the forwarded local port:

   ssh -L 3307:127.0.0.1:3306 user@<public_ip>
   mysql -h 127.0.0.1 -P 3307 -u tamer -p test
   

   Note what this does to the JDBC string: the original jdbc:mysql://localhost:3306/test becomes correct again, because the tunnel really has put a MySQL endpoint on the client's loopback interface.

If the direct forward is the only option available, require TLS on the account (ALTER USER ... REQUIRE SSL), restrict the grant to the smallest set of schemas and statements the remote work needs, and hold the source-IP restriction on the router rule.

End-to-end verification

Run the full chain from the remote location, in this order, and stop at the first step that fails — the step that fails names the hop.

1. TCP probe: Test-NetConnection <public_ip> -Port 4000 (or the SSH port if tunnelling) returns success.
2. Client connect: MySQL Workbench with host <public_ip>, port 4000, or the SSH profile with host 127.0.0.1 and the forwarded local port.
3. Session identity: — USER() must show the remote public address and @@hostname must be the office server, not a local copy.
4. Transport check: STATUS; and confirm the SSL cipher line is populated if TLS is required.
5. Write proof: run an UPDATE inside a transaction against a scratch row, COMMIT, reconnect, and re-select that row to confirm the change persisted on the office server rather than in a cached editor buffer.

FAQ