How I Created an SSH Tunnel for MariaDB UNIX Socket Access

[ccb056]

I needed to connect to a remote MariaDB instance that was configured with --skip-networking and only listened on a UNIX socket. Direct TCP connections were not possible, so I set up an SSH tunnel to forward the remote socket to my local system.

1. SSH Key Authentication
The remote server uses authorized_keys for key-based login. Here’s how I set it up:

On Windows (PuTTYgen):
- Load or generate your key in PuTTYgen.
- Export it in OpenSSH format:

Code: Select all

PuTTYgen → Load existing key (.ppk) → Conversions → Export OpenSSH key

On the remote server:
- Create the .ssh directory if it doesn’t exist:

Code: Select all

mkdir -p ~/.ssh && chmod 700 ~/.ssh

- Append your public key to authorized_keys:

Code: Select all

echo "your-public-key" >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys

On the local FreeBSD system:
- Copy the exported private key:

Code: Select all

scp id_ed25519 root@local-server:/root/.ssh/id_ed25519
chmod 600 /root/.ssh/id_ed25519

- Configure SSH in ~/.ssh/config:

Code: Select all

Host remote-db
    HostName example.com
    User myuser
    IdentityFile /root/.ssh/id_ed25519

2. Install autossh
On the local system (FreeBSD):

Code: Select all

pkg install autossh

3. Create a TCP → Remote UNIX Socket Tunnel
Since MariaDB replication and most tools expect a TCP endpoint, I forwarded a local TCP port (3307) to the remote UNIX socket:

Code: Select all

autossh -f -M 0 -N \
  -o ExitOnForwardFailure=yes \
  -o ServerAliveInterval=60 -o ServerAliveCountMax=3 \
  -L 3307:/path/to/remote/mysqld.sock remote-db

4. Test the Connection

Code: Select all

mysql -h 127.0.0.1 -P 3307 -u myuser

Result:

Welcome to the MariaDB monitor...
Server version: 10.x.x-MariaDB

5. Make It Persistent
Created an rc.d script on FreeBSD to keep the tunnel alive at boot:

Code: Select all

#!/bin/sh
# PROVIDE: mysql_tunnel
# REQUIRE: NETWORKING
# KEYWORD: shutdown

. /etc/rc.subr

name="mysql_tunnel"
rcvar=mysql_tunnel_enable
command="/usr/local/bin/autossh"
ssh_host="remote-db"
ssh_opts="-M 0 -N -f -o ExitOnForwardFailure=yes -o ServerAliveInterval=60 -o ServerAliveCountMax=3"
tunnel_opts="-L 3307:/path/to/remote/mysqld.sock"
command_args="${ssh_opts} ${tunnel_opts} ${ssh_host}"

load_rc_config $name
: ${mysql_tunnel_enable:=no}

run_rc_command "$1"

Enable and start:

Code: Select all

chmod +x /usr/local/etc/rc.d/mysql_tunnel
sysrc mysql_tunnel_enable=YES
service mysql_tunnel start

Done! Now I can connect to the remote MariaDB securely via SSH using a local TCP port, even though the remote server has --skip-networking enabled.

Next step: configure replication using this tunnel!