When working with MySQL on Unix-like systems, you might notice the server defaults to using Unix domain sockets for local connections. This behavior occurs when clients connect to 'localhost' rather than '127.0.0.1'. Here's how the two approaches differ:

# Unix socket connection (default for 'localhost')
$ mysql -h localhost -u user -p

# TCP/IP connection (forced by using 127.0.0.1)
$ mysql -h 127.0.0.1 -u user -p

To completely disable Unix socket connections and force TCP, you'll need to modify several configuration files:

# /etc/mysql/my.cnf
[mysqld]
skip-networking = false
bind-address = 127.0.0.1
# socket = /var/run/mysqld/mysqld.sock  # Comment out or remove this line

[client]
protocol = tcp
# socket = /var/run/mysqld/mysqld.sock  # Comment out or remove this line

After making these changes, restart MySQL:

$ sudo service mysql restart

For web applications like Drupal, the connection method is determined by the connection string. Here are examples for common languages:

PHP (mysqli):

// Forces TCP connection
$mysqli = new mysqli("127.0.0.1", "user", "password", "database");

// Uses Unix socket (default behavior)
$mysqli = new mysqli("localhost", "user", "password", "database");

Python:

import MySQLdb

# Forces TCP
db = MySQLdb.connect(host="127.0.0.1", user="user", passwd="password", db="database")

# Might use Unix socket
db = MySQLdb.connect(host="localhost", user="user", passwd="password", db="database")

To confirm connections are using TCP, check the process list or network connections:

$ sudo netstat -tulnp | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1234/mysqld

$ mysqladmin -h 127.0.0.1 -u root -p processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 42 | root | localhost |    | Query   | 0    | init  | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

If you're still seeing socket connections after making these changes:

1. Verify all configuration files that might contain socket settings:

   $ sudo grep -r "socket" /etc/mysql/
   

2. Check if any application configuration files (like Drupal's settings.php) contain 'localhost'
3. Ensure your MySQL user has proper TCP connection privileges:

   GRANT ALL PRIVILEGES ON *.* TO 'user'@'127.0.0.1' IDENTIFIED BY 'password';
   FLUSH PRIVILEGES;
   

While TCP connections are essential for traffic analysis, be aware that:

• Unix sockets are generally faster for local connections (about 30-50% faster in benchmarks)
• TCP adds some overhead due to protocol handling
• For high-performance local applications, consider reverting to sockets after analysis

---

When working with MySQL (especially version 5.1 on Ubuntu systems), you'll often find client connections defaulting to the Unix domain socket rather than TCP/IP, even when both options are available. This behavior occurs because localhost connections automatically use the more efficient Unix socket unless specifically configured otherwise.

# Current socket connection example
unix  2      [ ACC ]     STREAM     LISTENING     3734388  15304/mysqld        /var/run/mysqld/mysqld.sock

For network traffic analysis, debugging, or security auditing purposes, you might need to force TCP connections. Common scenarios include:

• Packet sniffing with tools like tcpdump or Wireshark
• Network-level performance monitoring
• Testing firewall rules
• Preparing for remote database access

First, ensure your MySQL server is properly configured to accept TCP connections:

# In /etc/mysql/my.cnf
[mysqld]
bind-address = 127.0.0.1
# socket = /var/run/mysqld/mysqld.sock  # Comment out or remove

After modifying the configuration, restart MySQL:

sudo service mysql restart

The key to forcing TCP connections lies in how clients connect. Here are examples for different scenarios:

1. MySQL Command Line Client

mysql -h 127.0.0.1 -u username -p
# Instead of:
# mysql -u username -p  # This uses socket

2. PHP Applications (Drupal Example)

The database connection string needs explicit TCP specification:

// In settings.php or equivalent
$databases['default']['default'] = array(
  'driver' => 'mysql',
  'host' => '127.0.0.1',  // Not 'localhost'
  'port' => '3306',
  // ... other parameters
);

3. Connection Strings in Various Languages

# Python
import mysql.connector
db = mysql.connector.connect(
  host="127.0.0.1",  # Not 'localhost'
  user="user",
  passwd="password"
)

// Node.js
const mysql = require('mysql');
const connection = mysql.createConnection({
  host: '127.0.0.1',  // Not 'localhost'
  user: 'me',
  password: 'secret'
});

Check active connections to confirm TCP usage:

netstat -tulpn | grep mysql
# Should show:
# tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      15601/mysqld

• Application Still Using Sockets: Many frameworks treat 'localhost' specially. Always use 127.0.0.1 for TCP.
• Performance Impact: TCP has slightly higher overhead than Unix sockets for local connections.
• Permission Issues: Ensure users have proper host-based privileges ('user'@'127.0.0.1' vs 'user'@'localhost').

For complete socket removal (not recommended for production):

# In my.cnf under [mysqld]
skip-networking = FALSE
socket = /dev/null  # Effectively disables socket

Remember that some MySQL utilities (like mysqladmin) may rely on socket connections.