When working with MySQL on Mac OS X (specifically Lion in this case), you might encounter a peculiar scenario where connections work with 127.0.0.1 but fail with localhost. This isn't just an academic distinction - it points to fundamental networking behavior in Unix-like systems.

The key difference lies in how the system resolves these addresses:

localhost → Unix socket connection (typically /tmp/mysql.sock)
127.0.0.1 → TCP/IP loopback connection

First, let's verify if MySQL is even listening on the socket:

mysqladmin variables | grep socket
// Output should show something like:
// socket                      /tmp/mysql.sock

If no socket path appears, MySQL isn't configured for socket connections.

Here's how the behavior manifests in PHP code:

// Works (TCP/IP):
$link = mysqli_connect('127.0.0.1', 'user', 'password');

// Fails (socket):
$link = mysqli_connect('localhost', 'user', 'password');

Option 1: Force TCP connection for localhost

// In your PHP config:
ini_set('mysqli.default_host', '127.0.0.1');

Option 2: Specify the socket path explicitly

// In my.cnf:
[client]
socket = /tmp/mysql.sock

// In PHP:
$link = mysqli_connect('localhost', 'user', 'password', 'db', null, '/tmp/mysql.sock');

Check your /etc/hosts file contains:

127.0.0.1       localhost
::1             localhost

Remember that Unix sockets are generally faster than TCP loopback. If you're making frequent local connections, resolving the socket issue can provide performance benefits.

Many developers encounter this puzzling scenario where MySQL connections work with "127.0.0.1" but fail with "localhost". This isn't just a naming convention difference - it actually involves fundamental networking and MySQL configuration aspects.

When you use "localhost", MySQL attempts to connect via a Unix socket file rather than TCP/IP. Conversely, "127.0.0.1" forces a TCP/IP connection. The issue typically occurs when:

• The socket file path is misconfigured
• MySQL is configured to skip name resolution
• The hosts file contains incorrect entries
• skip-networking is enabled in my.cnf

Check your current connection type with this MySQL command:

SHOW VARIABLES LIKE 'skip_networking';
SHOW VARIABLES LIKE 'socket';

Option 1: Fix the socket connection
Locate your MySQL socket file (typically /tmp/mysql.sock) and specify it in your connection:

$mysqli = new mysqli('localhost', 'user', 'password', 'database', null, '/tmp/mysql.sock');

Option 2: Force TCP/IP connection
Modify your PHP connection to explicitly use TCP/IP:

$db = new PDO('mysql:host=127.0.0.1;dbname=test', 'user', 'pass');

Check your hosts file (/etc/hosts):

127.0.0.1   localhost
::1         localhost

MySQL Configuration (my.cnf):

[mysqld]
skip-name-resolve = OFF
skip-networking = OFF
socket = /tmp/mysql.sock

For PHP applications, you can test both methods:

// Test localhost connection
try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
    echo "localhost connection successful";
} catch (PDOException $e) {
    echo "localhost failed: " . $e->getMessage();
}

// Test 127.0.0.1 connection
try {
    $dbh = new PDO('mysql:host=127.0.0.1;dbname=test', $user, $pass);
    echo "127.0.0.1 connection successful";
} catch (PDOException $e) {
    echo "127.0.0.1 failed: " . $e->getMessage();
}