---
name: bnna-mariadb-setup
version: 1.1.0
description: Set up MariaDB 11.4 on an Alpine LXC via apk. Use when installing mariadb on a bnna Alpine CT, configuring port 3306 with tcpfwd forwarding 13306 (TLS router), creating tenant databases, or managing MariaDB users. Covers apk install, OpenRC services, tcpfwd, connection strings. Version pinned to 11.4 (amd64v3 builds after 11.4 break on amd64v2 dev servers).
---

# Setup MariaDB (BNNA Alpine)

## Ports

- MariaDB listens on its default port **3306**.
- **tcpfwd** forwards port **13306** (= 10000 + 3306) to `<service-ip>:3306` for the TLS router.

## Prerequisites

1. tcpfwd binary must be deployed to `~/bin/tcpfwd` before running
   the setup script. Build and copy:

```sh
cd tmp.d/golib/cmd/tcpfwd
CGO_ENABLED=0 GOOS=linux GOARCH=amd64 go build -o tcpfwd .
scp tcpfwd app@<host>:~/bin/tcpfwd
```

## Quick Start

Run the setup script as the `app` user (bnna template default). The script
uses `sudo` for privileged operations:

```sh
scp scripts/maria-setup-alpine.sh app@<host>:~/maria-setup-alpine.sh
ssh app@<host> "sh ~/maria-setup-alpine.sh"
```

Root uses unix_socket auth only — no password needed. Use `sudo mariadb` to connect.

```sh
# VERIFY: mariadb is listening on 3306
ssh app@<host> "sudo mariadb -e 'SELECT 1 AS ok'"

# VERIFY: tcpfwd forwarding 13306 → <service-ip>:3306
ssh app@<host> "netstat -tlnp | grep 13306"
```

## What the Script Does

1. `apk add` — mariadb, mariadb-client, mariadb-openrc, tzdata, openssl, logrotate
2. `/etc/init.d/mariadb setup` — initializes the database in `/var/lib/mysql`
3. Enables network listening — replaces `skip-networking` with `bind-address = 0.0.0.0`
4. Starts MariaDB and adds to default runlevel
5. Creates `mariadb-fwd` OpenRC service for tcpfwd (13306 → <service-ip>:3306)
6. Secures installation — removes anonymous users, drops test DB, sets root to
   `unix_socket` only (no password, no TCP access)

## Key Differences from PostgreSQL Setup

| Aspect | PostgreSQL | MariaDB |
|--------|-----------|---------|
| Install method | `webi postgres` (user-level) | `sudo apk add mariadb` (system-level) |
| Run as | `app` user | `app` user (sudo for privileged ops) |
| Helper package | `pg-essentials` | `maria-createdb` script |
| Access control | `pg_hba.conf` + roles/groups | `GRANT` statements + `user@host` |
| Config | `postgresql.conf` (in data dir) | `/etc/my.cnf.d/mariadb-server.cnf` |
| Data directory | `~/.local/share/postgres/var` | `/var/lib/mysql` |
| Socket | TCP default | `/run/mysqld/mysqld.sock` |
| Local root auth | superuser password | `unix_socket` plugin (no password needed) |
| Service manager | serviceman | OpenRC init scripts (apk provides) |

## Version Pin

MUST: Alpine 3.23 ships MariaDB **11.4.9** via apk — this is the correct version.
Do NOT use `webi mariadb@11.4` on Alpine — it downloads glibc binaries that fail
on musl. After 11.4, official MariaDB builds switched to amd64v3 which does not
run on our amd64v2 dev servers.

## Creating Databases

Deploy `maria-createdb` to the CT, then run as the `app` user:

```sh
scp scripts/maria-createdb.sh app@<host>:~/maria-createdb
ssh app@<host> "chmod +x ~/maria-createdb && ~/maria-createdb myapp"
```

This creates:
- User `<name>@'172.24.0.%'` with a generated password
- Database `<name>` with full privileges granted to the user

Uses underscores, not hyphens (e.g. `foo_app` not `foo-app`). Save the
password — it's shown once.

## Connection String Format

Direct connection (within the same VLAN):
```
mysql://<dbname>:<password>@<container-ip>:3306/<dbname>
```

Via tcpfwd (TLS router port):
```
mysql://<dbname>:<password>@<container-ip>:13306/<dbname>
```

External via TLS router (mysql ALPN, bnnanet/tlsrouter#22):
```
mysql://<dbname>:<password>@tls-<ip-dashes>.a.bnna.net:443/<dbname>
```

## Security Model

See `docs/2026-04-08_mariadb-security-model.md` for the full security model.

## Related Skills

- `bnna-infra-mariadb-setup` — shared infra MariaDB on vsvc174
- `bnna-tls-router` — TLS router port/ALPN reference
- `bnna-deploy-service` — service deployment patterns