SSH Tunneling: Forwarding Local Port to Remote Unix Socket for MySQL Connection


2 views

When working with remote MySQL servers configured for Unix socket connections (/var/run/mysqld/mysql.sock), traditional port forwarding (ssh -L) doesn't directly solve the problem. Many system administrators prefer socket connections for security reasons, but this creates connectivity challenges for remote access.

We can achieve this through a two-step approach using socat combined with SSH tunneling:


# On the remote server (VPS):
socat TCP-LISTEN:3306,fork UNIX-CONNECT:/var/run/mysqld/mysql.sock

# On your local machine:
ssh -N -L 3307:localhost:3306 user@vps.example.com

Here's a more robust implementation with error handling:


#!/bin/bash
# Remote setup (run once on VPS)
if [ "$1" == "remote" ]; then
    if ! command -v socat &> /dev/null; then
        echo "Installing socat..."
        sudo apt-get install -y socat
    fi
    
    # Create systemd service for persistent socket forwarding
    cat <

For systems where you can't expose TCP ports at all, consider this creative solution:


# On local machine create a fake socket that forwards through SSH
ssh -nNT -L ./fake_mysql.sock:/var/run/mysqld/mysql.sock user@vps.example.com

# Then connect normally to the local socket
mysql --socket=./fake_mysql.sock -u dbuser -p

Note: This requires OpenSSH 7.3+ with StreamLocalBindUnlink=yes in sshd_config on the remote server.

  • Always use SSH key authentication instead of passwords
  • Consider adding firewall rules to restrict which IPs can connect to the forwarded TCP port
  • For production systems, implement connection rate limiting
  • Regularly audit your SSH configurations and authorized keys

For high-throughput scenarios, adjust these SSH options:


ssh -C -c aes256-gcm@openssh.com \
    -o Compression=yes \
    -L 3307:localhost:3306 \
    user@vps.example.com

When working with MySQL on Linux servers, many administrators prefer Unix domain sockets over TCP ports for local connections due to their better performance and security. However, this creates challenges when you need remote access from another machine. While port forwarding through SSH is straightforward (ssh -L 3307:localhost:3306 user@remote), forwarding to a socket file requires a different approach.

Before diving into solutions, let's address why you might want to avoid TCP:

  • Security: No network stack involvement means reduced attack surface
  • Performance: Unix sockets have lower latency than TCP loopback
  • Configuration: Many MySQL installations default to socket-only for local connections

The most reliable method combines socat (SOcket CAT) with SSH port forwarding:


# On remote server (forward socket to port):
socat TCP-LISTEN:12345,fork,reuseaddr UNIX-CONNECT:/var/run/mysqld/mysql.sock &

# On local machine (forward port through SSH):
ssh -N -L 12345:localhost:12345 user@remote &

# On local machine (connect to forwarded socket):
mysql -h 127.0.0.1 -P 12345 -u user -p

For some use cases, you might consider mounting the remote socket directory:


sshfs user@remote:/var/run/mysqld /mnt/remote-mysql-sock
mysql --socket=/mnt/remote-mysql-sock/mysql.sock -u user -p
  • File permissions: Ensure your SSH user can access the socket file
  • Stability: The socat process needs to remain running
  • Security: Still encrypts traffic via SSH, but adds socat as another component

Here's a bash script to handle the entire process:


#!/bin/bash
REMOTE_USER="user"
REMOTE_HOST="example.com"
SOCKET_PATH="/var/run/mysqld/mysql.sock"
LOCAL_PORT=12345

# Start remote socat if not running
ssh $REMOTE_USER@$REMOTE_HOST "pgrep socat || socat TCP-LISTEN:$LOCAL_PORT,fork,reuseaddr UNIX-CONNECT:$SOCKET_PATH &"

# Set up SSH tunnel
ssh -f -N -L $LOCAL_PORT:localhost:$LOCAL_PORT $REMOTE_USER@$REMOTE_HOST

echo "MySQL socket forwarded. Connect using:"
echo "mysql -h 127.0.0.1 -P $LOCAL_PORT -u your_user -p"