MariaDB

Overview

The server runs a central MariaDB instance that every WireGuard peer reaches at 10.100.0.1:3306. Workstations run the same module as a local development server for MySQL Workbench.

Module: modules/features/mariadb.nix

Property Value
Endpoint 10.100.0.1:3306, WireGuard only
Data /var/lib/mysql, persisted under /persist
Admin accounts root and user, unix socket authentication only
Backup /persist/var/lib/mysql in the restic B2 repository
flowchart LR
    peers["WireGuard peers"] -->|"TCP 10.100.0.1:3306"| db[(MariaDB)]
    ssh["SSH session"] -->|"unix socket"| db
    db --> data["/persist/var/lib/mysql"]
    data -->|"btrfs snapshot"| restic[Restic → B2]

mysqld listens on every address; the nftables firewall accepts 3306 on wg0 alone, so neither the LAN nor the public address reaches it.

Monitoring

performance_schema is on, which MySQL Workbench’s Performance Dashboard and Performance Reports read. mysqld_exporter scrapes the server through the socket as its own account (mysqld-exporter, PROCESS and REPLICATION CLIENT, SELECT on performance_schema), and its metrics carry namespace="mariadb-host". A service project that uses this instance lists mariadb-host among its namespaces in values/observability/projects.json and gets a Database dashboard in Perses scoped to these metrics.

Workbench’s dashboard also needs the viewing account to hold PROCESS and SELECT on performance_schema and sys:

GRANT PROCESS ON *.* TO '<account>'@'<host>';
GRANT SELECT ON performance_schema.* TO '<account>'@'<host>';
GRANT SELECT ON sys.* TO '<account>'@'<host>';

Admin Access

Admin accounts accept no password and no TCP login: unix socket authentication matches the OS user of the connecting process. user holds all privileges except GRANT; root holds everything.

Open a shell on the server as either account:

ssh -t -p 30351 10.100.0.1 mariadb       # as user
ssh -t -p 30351 10.100.0.1 sudo mariadb  # as root

MySQL Workbench — forward the server’s socket to a local path, then add a connection with method “Local Socket/Pipe”, socket path /tmp/mariadb-server.sock and username user:

ssh -N -p 30351 -L /tmp/mariadb-server.sock:/run/mysqld/mysqld.sock 10.100.0.1

The “Standard TCP/IP over SSH” method does not work: it connects over TCP, which unix socket authentication rejects.

Adding an Application Account

Clients on other hosts connect over TCP with a password account. Create the database and an account scoped to the WireGuard network from an admin session:

CREATE DATABASE app;
CREATE USER 'app'@'10.100.0.%' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON app.* TO 'app'@'10.100.0.%';

Connect from any peer:

mariadb -h 10.100.0.1 -u app -p app