Recipe: MySQL

Root vs app user, init scripts, secrets.

MySQL (and the drop-in MariaDB) follow the same official-image conventions as Postgres and Mongo: environment variables to bootstrap the database and users, _FILE variants to read those from secrets, a named volume for /var/lib/mysql, and an init-script directory. Getting the user model right is the security work — the app must never connect as root.

services:
  db:
    image: mysql:8.4
    environment:
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mysql_root_pw
      MYSQL_DATABASE: payments               # created on first run
      MYSQL_USER: app                        # a non-root app user...
      MYSQL_PASSWORD_FILE: /run/secrets/mysql_app_pw   # ...with its own password
    secrets: [mysql_root_pw, mysql_app_pw]
    volumes: ["mysqldata:/var/lib/mysql"]
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1"]
      interval: 10s
      retries: 5
volumes: { mysqldata: {} }
secrets:
  mysql_root_pw: { file: ./secrets/mysql_root_pw.txt }
  mysql_app_pw:  { file: ./secrets/mysql_app_pw.txt }

Root stays inside; the app gets a scoped user

MYSQL_USER and MYSQL_PASSWORD create an application account with full rights on MYSQL_DATABASE only — that is the account your app uses. The root password exists for administration but should never be in the app’s connection string. For anything finer than “all rights on one database,” add a script to the init directory that GRANTs exactly the privileges the app needs.

-- runs once on first startup; tighten the app user beyond the default grant
REVOKE ALL PRIVILEGES ON payments.* FROM 'app'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON payments.* TO 'app'@'%';
FLUSH PRIVILEGES;

Verify the account model

Confirm the app user exists and is scoped, and that the data survives a recreate. Connecting as the app user and listing its grants shows exactly what a leaked app credential could do — which should be “work with the payments database,” not “administer the server.”

$ docker compose exec db \
    mysql -uapp -p"$(cat secrets/mysql_app_pw.txt)" -e "SHOW GRANTS;"
GRANT SELECT, INSERT, UPDATE, DELETE ON `payments`.* TO `app`@`%`   # scoped, not root