Here’s a clear, end-to-end tutorial you can keep handy. It explains what “socket-based” vs “TCP-based” really means, how to tell which one you’re using, and how to configure apps for each—using our Keycloak ↔ MySQL (XAMPP/LAMPP) case as the running example.

---

Concept: Socket-based vs TCP-based

Two different “pipes” your app can use to talk to a service (like MySQL)

• UNIX domain socket (UDS)
  • A special file on disk (e.g. /opt/lampp/var/mysql/mysql.sock).
  • Only works on the same machine.
  • Low overhead (skips the TCP/IP stack) → slightly faster and very common on local servers.
  • Access control via filesystem permissions + DB auth.
• TCP/IP socket
  • A network endpoint like 127.0.0.1:3306 or 10.0.0.5:3306.
  • Works locally and remotely.
  • A tad more overhead but universally supported by drivers and load balancers.
  • Access control via firewall/bind address + DB auth.

Mental model

[ App ] --(UDS file)--> [/var/run/.../mysql.sock]  (local only)
[ App ] --(TCP port)--> [127.0.0.1:3306]          (local/remote)
Code language: JavaScript (javascript)

Many servers (MySQL/MariaDB, Nginx, Redis, etc.) can expose both at the same time.

---

How clients decide which one they’re using

• MySQL CLI
  • mysql -h 127.0.0.1 ... → TCP
  • mysql -S /path/to/mysql.sock ... → UNIX socket
  • mysql -h localhost ... → varies by distro/driver (often socket on Linux)
• JDBC (Java)
  • TCP: jdbc:mysql://127.0.0.1:3306/db (MySQL driver)
  • Socket (Java): use a driver that supports it (e.g., MariaDB Connector/J):
    jdbc:mariadb://localhost:3306/db?localSocket=/path/to/mysql.sock
  • If you pass a UDS parameter the driver doesn’t support, it falls back to TCP.
• Other client libs (quick cheatsheet below) often accept socketPath / unix_socket / unixSocket parameters.

---

How to check which one is actually in use

On the server

1. Is the service listening on TCP 3306?

ss -lntp | grep 3306   # or: netstat -plnt | grep 3306
Code language: PHP (php)

• If you see LISTEN ... 127.0.0.1:3306 (or 0.0.0.0:3306) → TCP is enabled.
• If nothing prints → likely socket-only.

2. Does the socket file exist?

ls -l /opt/lampp/var/mysql/mysql.sock
Code language: JavaScript (javascript)

• If present and MySQL is running → socket is enabled.

3. Ask MySQL itself:

SHOW VARIABLES LIKE 'socket';
SHOW VARIABLES LIKE 'port';
SHOW VARIABLES LIKE 'bind_address';
Code language: JavaScript (javascript)

From the client/app

1. Try both ways explicitly:

# TCP
/opt/lampp/bin/mysql -h 127.0.0.1 -P 3306 -u USER -p DB -e "SELECT 1;"

# Socket
/opt/lampp/bin/mysql -S /opt/lampp/var/mysql/mysql.sock -u USER -p DB -e "SELECT 1;"
Code language: PHP (php)

2. Logs & driver names
   • Java stack traces that mention **com.mysql.cj**... = MySQL Connector/J.
   • Java stack traces that mention **org.mariadb.jdbc**... = MariaDB Connector/J.
   • “Driver does not support provided URL” usually means the driver family doesn’t match your JDBC URL scheme.
3. See open files/sockets of a running process:

pid=$(pgrep -f 'org.keycloak.quarkus' || true)
[ -n "$pid" ] && sudo lsof -p "$pid" | egrep 'mysql-connector-j|mariadb-java-client|mysql.sock|TCP'
Code language: JavaScript (javascript)

---

Configure the server for each mode (MySQL/MariaDB)

File: /opt/lampp/etc/my.cnf (XAMPP/LAMPP), under [mysqld]:

Enable / tune UNIX socket

socket=/opt/lampp/var/mysql/mysql.sock
# (this is default in XAMPP; usually already set)
Code language: PHP (php)

Restart:

sudo /opt/lampp/lampp restartmysql

Enable TCP listening

bind-address=127.0.0.1   # or 0.0.0.0 if remote access is needed
# ensure there is NO 'skip-networking'
port=3306
Code language: PHP (php)

Restart & verify:

sudo /opt/lampp/lampp restartmysql
ss -lntp | grep 3306

---

Configure applications for each mode

Below are proven patterns (including the exact ones you used today).

Keycloak (Quarkus 17+)

A) TCP (production-friendly)

# conf/keycloak.conf
db=mysql
db-url=jdbc:mysql://127.0.0.1:3306/keycloak_db?useSSL=false&allowPublicKeyRetrieval=true
db-username=keycloak
# put the password in env to avoid parsing issues with '#'
Code language: PHP (php)

export KC_DB_PASSWORD='Strong#Passw0rd!'
bin/kc.sh build
bin/kc.sh start --optimized
Code language: JavaScript (javascript)

B) UNIX socket (local same-host; requires driver that supports it)

Use MariaDB Connector/J (already present in your install):

# conf/keycloak.conf
db=mariadb
db-url=jdbc:mariadb://localhost:3306/keycloak_db?localSocket=/opt/lampp/var/mysql/mysql.sock
db-username=root
Code language: PHP (php)

export KC_DB_PASSWORD='Hs?gb?S345?3#s'
bin/kc.sh build
bin/kc.sh start-dev
Code language: JavaScript (javascript)

Why your earlier fix worked: setting KC_DB=mariadb + jdbc:mariadb://...localSocket=... made Keycloak load the MariaDB driver, which does speak UNIX sockets natively.

---

PHP (PDO)

// TCP
$pdo = new PDO('mysql:host=127.0.0.1;port=3306;dbname=keycloak_db', 'user', 'pass');

// UNIX socket
$pdo = new PDO('mysql:unix_socket=/opt/lampp/var/mysql/mysql.sock;dbname=keycloak_db', 'user', 'pass');
Code language: PHP (php)

Node.js (mysql2)

// TCP
createConnection({ host: '127.0.0.1', port: 3306, user: 'user', password: 'pass', database: 'keycloak_db' });

// UNIX socket
createConnection({ socketPath: '/opt/lampp/var/mysql/mysql.sock', user: 'user', password: 'pass', database: 'keycloak_db' });
Code language: JavaScript (javascript)

Python (PyMySQL / mysqlclient)

# TCP
pymysql.connect(host='127.0.0.1', port=3306, user='user', password='pass', db='keycloak_db')

# UNIX socket
pymysql.connect(unix_socket='/opt/lampp/var/mysql/mysql.sock', user='user', password='pass', db='keycloak_db')
Code language: PHP (php)

Go (go-sql-driver/mysql)

// TCP
dsn := "user:pass@tcp(127.0.0.1:3306)/keycloak_db?parseTime=true"

// UNIX socket
dsn := "user:pass@unix(/opt/lampp/var/mysql/mysql.sock)/keycloak_db?parseTime=true"
db, _ := sql.Open("mysql", dsn)
Code language: JavaScript (javascript)

---

Decision guide: which should I use?

• Use UNIX socket when:
  • The app and DB are on the same host, and you want simplicity + a tiny perf edge.
  • Your driver supports it easily (e.g., MariaDB Connector/J).
• Use TCP when:
  • You might run the DB remotely, in containers, or behind a proxy.
  • You need universal compatibility and predictable behavior across drivers.
  • You want to use firewalls, VPCs, or TLS on the wire.

---

Common errors → what they mean → how to fix

Error/Log text	Meaning	Fix
Connection refused	App tried TCP, but nothing is listening on that IP:port	Enable TCP (bind-address, port), or switch app to socket URL
Communications link failure	Can’t reach server over TCP (firewall/offline/bad host)	Verify ss -lntp, network path, host/port
Driver does not support provided URL	Driver family mismatched with URL (MySQL vs MariaDB)	Align: db=mysql + jdbc:mysql://... or db=mariadb + jdbc:mariadb://...
Access denied for user	Auth/host rules don’t match	Ensure grants match the host: 'user'@'127.0.0.1' for TCP; 'user'@'localhost' often for socket

---

One-shot diagnostic script (copy-paste)

This tells you, in plain English, which paths are available and which work:

#!/usr/bin/env bash
SOCK="/opt/lampp/var/mysql/mysql.sock"
DB="keycloak_db"; U="root"; H="127.0.0.1"; P=3306

echo "=== Server-side checks ==="
if ss -lntp | grep -q ":$P "; then
  echo "TCP: MySQL is listening on $H:$P"
else
  echo "TCP: Nothing listening on port $P"
fi

if [ -S "$SOCK" ]; then
  echo "UDS: Socket exists at $SOCK"
else
  echo "UDS: Socket file not found at $SOCK"
fi

echo "=== Client connectivity tests ==="
if /opt/lampp/bin/mysql -h "$H" -P "$P" -u "$U" -e "SELECT 1;" >/dev/null 2>&1; then
  echo "TCP: Client can connect to $H:$P as $U"
else
  echo "TCP: Client CANNOT connect to $H:$P as $U"
fi

if [ -S "$SOCK" ]; then
  if /opt/lampp/bin/mysql -S "$SOCK" -u "$U" -e "SELECT 1;" >/dev/null 2>&1; then
    echo "UDS: Client can connect via $SOCK as $U"
  else
    echo "UDS: Client CANNOT connect via $SOCK as $U"
  fi
fi
Code language: PHP (php)

---

Case study (what we fixed today)

• Your MySQL (XAMPP) was effectively socket-only → TCP attempts from Keycloak failed with Connection refused.
• Switching Keycloak to MariaDB Connector/J and using
  jdbc:mariadb://localhost:3306/keycloak_db?localSocket=/opt/lampp/var/mysql/mysql.sock
  worked immediately.
• Key insight: Match the driver family to the URL, and be explicit about socket vs TCP.

---

Quick reference (cheat sheet)

• Check TCP: ss -lntp | grep 3306
• Check socket: ls -l /opt/lampp/var/mysql/mysql.sock
• Force TCP (CLI): mysql -h 127.0.0.1 -P 3306 ...
• Force socket (CLI): mysql -S /path/to/mysql.sock ...
• Keycloak TCP: db=mysql + jdbc:mysql://127.0.0.1:3306/...
• Keycloak socket: db=mariadb + jdbc:mariadb://localhost:3306/...localSocket=...
• Env beats file: CLI > ENV > keycloak.conf

---