Why localhost Fails While 127.0.0.1 Works in MySQLi
When you instantiate a new MySQLi connection using new MySQLi('localhost', ...) on Linux-based systems, you often encounter the specific error: (HY000/2002): Can't connect to local MySQL server through socket 'MySQL' (2). This isn't a network connectivity issue or a permission problem; it is a fundamental misunderstanding of how PHP resolves hostnames versus IP addresses in the context of database drivers.
The root cause lies in the behavior of the underlying C library used by PHP to resolve DNS names. When you pass the string 'localhost' to MySQLi, the driver attempts to connect via a Unix socket first, assuming this is the standard method for local connections. However, on many Linux distributions (particularly Debian and Ubuntu derivatives like Raspberry Pi OS), the default configuration often disables or misconfigures the socket connection when resolving 'localhost'.
Conversely, when you pass '127.0.0.1', the driver explicitly ignores sockets and attempts a TCP/IP connection instead. Since your user kamil has privileges granted for both 109.255.177.28 and localhost, the TCP connection succeeds, even though the hostname resolution logic suggests otherwise.
The Socket vs. TCP Mismatch
The error message explicitly mentions a socket file named 'MySQL'. This filename is typically derived from the operating system's default configuration found in /etc/mysql/mysql.conf.d/mysqld.cnf or similar locations, where socket might be set to /var/run/mysqld/mysql.sock. However, PHP's mysqli driver does not always default to this socket when the host is resolved as a name.
The discrepancy arises because localhost is an alias that can resolve to either the loopback IP address (127.0.0.1) or the Unix domain socket path. On Windows, localhost almost exclusively resolves to a TCP connection on port 3306. On Linux, however, the gethostbyname() function often returns the IP address for 'localhost', but the MySQL client library might still try the socket first depending on the version and build flags.
If your PHP environment was compiled with the default settings or if your system's /etc/hosts file does not explicitly map localhost to 127.0.0.1 in a way that overrides the socket preference, the driver gets confused. The error code (2) indicates "Connection refused", which happens when the client tries to open a TCP connection but finds nothing listening there, or conversely, tries to use a socket path that doesn't exist or isn't configured for the current process.
Resolving the Connection Issue
To fix this immediately without changing your database configuration, simply switch your connection string from 'localhost' to '127.0.0.1'. This forces the MySQLi driver to bypass the ambiguous socket resolution and establish a direct TCP/IP handshake with the MySQL server.
// Preferred approach for cross-platform consistency
$db = new mysqli('127.0.0.1', 'kamil', 'password');
// Alternatively, if you know your specific socket path exists and want to force sockets:
// $db = new mysqli('', 'kamil', 'password', '', '/var/run/mysqld/mysql.sock');
Long-Term Configuration Fixes
While using the IP address is a quick workaround, relying on 'localhost' is generally preferred for readability. To restore the ability to use 'localhost', you must ensure your system's DNS resolution aligns with your MySQL expectations.
- Check
/etc/hosts: Ensure that127.0.0.1is mapped strictly tolocalhost.grep localhost /etc/hosts # Should output: 127.0.0.1 localhost - Verify MySQL Socket Configuration: Check your MySQL configuration file (
my.cnf) for thesocketdirective. If it is commented out or set to a non-standard path, the client might fail to find it. - Use Specific Socket Paths: If you require socket connections (often faster as they avoid network stack overhead), explicitly provide the socket path in the constructor:
$socket = '/var/run/mysqld/mysql.sock'; // Path may vary by distro $db = new mysqli('localhost', 'kamil', 'password', '', $socket);
Security and User Host Restrictions
Your mysql.user table shows that kamil is allowed to connect from localhost and 109.255.177.28. This confirms your database user permissions are correctly configured for both the TCP loopback interface and the specific external IP. The issue is purely client-side resolution logic.
If you are deploying this application on a shared hosting environment or a server where you cannot modify my.cnf, sticking to '127.0.0.1' is the most robust solution. It eliminates ambiguity between socket files and TCP ports, ensuring your application remains stable regardless of the underlying OS configuration quirks.
For projects using the Laravel framework, this same principle applies. When configuring your .env file, always prioritize '127.0.0.1' over 'localhost' in your DB_HOST setting if you encounter socket errors on Linux. This practice ensures consistent behavior across development and staging environments where MySQL socket paths often differ from the standard expectations of the PHP MySQL driver.
