The Illusion of Port Configuration in MySQL Connections
When debugging a PDO connection failure, it is easy to assume that the DSN (Data Source Name) syntax is the culprit if a specific server rejects the connection while others accept it. In your case, the discrepancy between connecting successfully without an explicit port and failing when specifying port=3309âdespite having firewall rules open for that non-standard portâpoints to a subtle interaction between PDO's driver defaults and MySQL's internal architecture.
The core issue lies in how the MySQLi/PDO driver resolves hostnames versus IP addresses when a custom port is involved. When you omit the port parameter, as in mysql:host=hotsname;dbname=dbname, PDO attempts to resolve the hostname hotsname via DNS. Once resolved to an IP address, the underlying socket library initiates a connection attempt. If your firewall rules are configured to redirect or accept traffic on port 3309 but funnel it to the service listening on standard port 3306, you might assume the driver is smart enough to follow that redirection. However, network redirection (such as iptables DNAT) often happens at a lower level than application-layer protocol negotiation.
The specific error message "Cannot connect to mysql server on xxx.xxx.xxx.xxx" suggests a TCP-level refusal or timeout before any handshake completes. When you specify port=3309, PDO explicitly instructs the socket layer to attempt a connection to that port on the resolved IP address. If your firewall is passing traffic through 3309 but the MySQL service itself is strictly bound to and listening only on 3306, the connection will fail because there is no process accepting TCP packets on port 3309. The firewall rule allowing "incoming on 3306" while traffic arrives on 3309 implies a potential misconfiguration in how the redirection is applied or a misunderstanding of whether the service actually accepts connections on the non-standard port.
The update you provided clarifies the situation: the connection works only when the firewall explicitly allows incoming traffic on 3306 to the server, effectively bypassing the need for the custom port logic or ensuring that 3309 is truly forwarding to a listening service. If the MySQL daemon (mysqld) is configured with port=3306 in its configuration file (e.g., /etc/mysql/my.cnf), it will not accept connections on any other port, regardless of what your firewall does with incoming packets destined for 3309 unless you are using a proxy or load balancer that terminates the TCP stream.
A common pitfall when dealing with non-standard ports is assuming that opening the port in the firewall is sufficient. The service must be actively listening on that port. If mysqld is configured to listen on 3306, but your application tries to connect to 3309, you are essentially trying to reach a door that does not exist on that specific building's facade, even if the street sign (firewall) points there.
To resolve this definitively, ensure that the port parameter in your DSN matches exactly what is configured in the MySQL server's configuration file. If you must use port 3309 for external reasons, update the MySQL server configuration to listen on that specific port and restart the service:
sudo systemctl restart mysql
Verify the listening port using netstat or ss:
ss -tlnp | grep mysqld
If you cannot change the server configuration, ensure your firewall is performing a true DNAT (Destination NAT) that changes the destination port from 3309 to 3306 before the packet reaches the local loopback or network stack. Simply allowing 3306 traffic does not help if the connection attempt is explicitly targeting 3309; the server must be listening on the port the client requests.
In production environments, relying on non-standard ports for internal databases can introduce unnecessary complexity in firewall management and debugging. The most robust solution is to align the application's DSN with the service's actual listening port or to normalize the network topology so that the external-facing port matches the internal service port. For developers working with Laravel applications, remember that while custom database configurations are supported, they often require additional attention to connection pooling and environment variable management compared to standard setups.