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.
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 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 rootMySQL 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.1The “Standard TCP/IP over SSH” method does not work: it connects over TCP, which unix socket authentication rejects.
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