Recipe: WordPress and MySQL with secrets

Two services, secrets WordPress can read, health checks, and a tested backup.

You'll build the classic two-service WordPress stack here: WordPress on PHP 8.5 and Apache, with MySQL 9.7. It gets file secrets, health checks, and a backup that has been restored at least once. On the way you'll reproduce the failure this stack most often shows on a Linux host. MySQL is healthy, the password is right, and the secret file is mounted where the image expects it. Yet every page answers "Error establishing a database connection", because of one chmod 600 that most guides recommend. Everything runs on the main lab VM, secopslog-docker, in ~/lab/r-wordpress with the lesson files.

The Compose file

name: lab-wp
 
services:
  db:
    image: mysql:9.7@sha256:8fb11c38b3551e22d50adbb688f027f8ff4ea2d2445aa277c1cc97b4af85ec54
    environment:
      MYSQL_DATABASE: wordpress
      MYSQL_USER: wp
      MYSQL_PASSWORD_FILE: /run/secrets/db_password
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/db_root_password
      MYSQL_ROOT_HOST: localhost
    secrets: [db_password, db_root_password]
    volumes:
      - db_data:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
      interval: 5s
      timeout: 3s
      retries: 10
      start_period: 60s
    # time for InnoDB to flush and shut down cleanly before Docker sends SIGKILL (default 10s)
    stop_grace_period: 1m
    restart: unless-stopped
 
  wordpress:
    image: wordpress:php8.5-apache@sha256:ce31904fa1e51b73543fa2e35cc8f1747589b2abf48dc747b422749b88b77e9e
    ports:
      - "127.0.0.1:8080:80"
    environment:
      WORDPRESS_DB_HOST: db
      WORDPRESS_DB_NAME: wordpress
      WORDPRESS_DB_USER: wp
      WORDPRESS_DB_PASSWORD_FILE: /run/secrets/db_password
    secrets: [db_password]
    volumes:
      - wp_html:/var/www/html
    depends_on:
      db:
        condition: service_healthy
    healthcheck:
      test: ["CMD", "curl", "-fsS", "-o", "/dev/null", "http://127.0.0.1/"]
      interval: 10s
      timeout: 5s
      retries: 3
      start_period: 20s
    restart: unless-stopped
 
volumes:
  db_data:
  wp_html:
 
secrets:
  db_password:
    file: ./secrets/db_password.txt
  db_root_password:
    file: ./secrets/db_root_password.txt

The database settings follow "Recipe: PostgreSQL, MySQL and MongoDB", which explains them in depth. There's a named volume for /var/lib/mysql, and passwords are read from files through the _FILE variables. MYSQL_ROOT_HOST: localhost stops the image from creating a root@'%' account. A mysqladmin ping health check runs against 127.0.0.1, which can't pass while the image is still running its first-start initialisation. The database publishes no port. stop_grace_period: 1m gives MySQL a minute to flush InnoDB and shut down cleanly. With Docker's default of ten seconds, a busy server can be killed mid-shutdown and run crash recovery on the next start. depends_on with condition: service_healthy holds WordPress back until MySQL accepts connections ("Multi-container apps with Compose" in Docker for beginners covers the conditions). WordPress finds the database as db through Docker's embedded DNS server on the project network.

WordPress gets its own health check. curl -fsS http://127.0.0.1/ fails on any HTTP status of 400 or above, and WordPress returns 500 when it can't reach its database. So this check fails exactly when the site is broken. Before installation the same URL returns a 302 redirect to the installer, which counts as healthy. The image ships curl, which isn't true of every image, so check before you write a probe. The site is published on the VM's loopback address only. In production, put it behind a TLS proxy as in "Recipe: NGINX as a reverse proxy".

Secrets the usual way, and the 500

Generate two random passwords and lock the files down, as most guides tell you to:

MySQL is healthy, so it read its secrets. WordPress started, and its health check turned it unhealthy because the site returns 500. The difference is who reads the file, and when:

Compose without Swarm gives each secret to the container as a read-only bind mount of the host file. It keeps the host's owner (UID 1000, ubuntu on the VM) and mode. The MySQL entrypoint reads its _FILE variables as root, before it drops to the mysql user, and root reads anything. WordPress doesn't read the password at startup at all. Its wp-config.php reads WORDPRESS_DB_PASSWORD_FILE on every request, inside PHP, as www-data (UID 33). That user gets "Permission denied", so WordPress has no password to log in with.

Compose has a long syntax for secrets with uid, gid and mode, and it looks like the fix. The lesson files include an override that sets them:

# An override that tries to fix ownership with the long secret syntax.
# Outside Swarm mode, Compose ignores uid, gid and mode (and says so).
services:
  wordpress:
    secrets:
      - source: db_password
        uid: "33"
        gid: "33"
        mode: 0400

Compose 5.6.1 says it plainly: those fields are Swarm features and are ignored here. What works for you outside Swarm is making the file readable to the container's user and protecting it on the host with the directory:

No other account on the host can enter a 0700 directory. So 0644 files inside it are readable only by ubuntu and root on the host, and by any user inside the containers that mount them. --force-recreate wordpress starts the container with a fresh health status. WordPress would've recovered on the next request anyway, since it reads the file each time. The ps output shows the database's 3306/tcp, 33060/tcp as exposed ports without a host binding: reachable from the project network only.

The file secret keeps the passwords out of the Compose file and out of docker inspect, which lists only the _FILE variables. It doesn't keep them out of every process. The MySQL entrypoint exports the values it read, and they stay in the server process's environment, readable through /proc/1/environ inside the container. "Recipe: PostgreSQL, MySQL and MongoDB" shows it, and "Runtime secrets, done right" (Advanced container security) discusses what that means for your threat model. The same lesson explains why changing a secret file later doesn't change the database password: the image applies MYSQL_PASSWORD only when it initialises an empty volume.

A site with content

A fresh site redirects to its installer. The installer is a form, so you can complete it with curl; the admin password is generated and never printed. Then add a file to the uploads directory, the way you would with a media upload:

The 200 is the installer's success page, and the title is the one you just set. The site now has state in two places. Tables live in the db_data volume. Files live in the wp_html volume, which holds WordPress core, themes, plugins, uploads and the generated wp-config.php. A backup needs both, taken at the same moment, or the database can reference uploads the archive doesn't contain.

Backup and restore

#!/bin/sh
# Usage: ./backup.sh DIR    (run in the directory that holds compose.yaml)
# Stops WordPress so the files and the database are copied at the same moment, and starts it
# again on every exit, also when a step fails. Writes .tmp files and renames them only on success.
set -eu
umask 077
mkdir -p "$1"
dir=$(realpath "$1")
trap 'docker compose start wordpress' EXIT
docker compose stop wordpress
# Logical dump; the root password reaches mysqldump as an option file on stdin,
# never as a command-line argument.
docker compose exec -T db sh -c 'printf "[client]\nuser=root\npassword=%s\n" "$(cat /run/secrets/db_root_password)" |
  mysqldump --defaults-extra-file=/dev/stdin --single-transaction --databases wordpress' > "$dir/wordpress.sql.tmp"
# File-level copy of the WordPress volume: core, themes, plugins, uploads, wp-config.php.
# The archive is written by root in the helper container; hand it back to you, mode 0600.
docker run --rm -v lab-wp_wp_html:/data:ro -v "$dir":/backup alpine:3.24 sh -c \
  "tar -C /data -czf /backup/wp_html.tgz.tmp . && chown $(id -u):$(id -g) /backup/wp_html.tgz.tmp && chmod 600 /backup/wp_html.tgz.tmp"
mv "$dir/wordpress.sql.tmp" "$dir/wordpress.sql"
mv "$dir/wp_html.tgz.tmp" "$dir/wp_html.tgz"

The script stops WordPress so nothing writes during the copy. Then it takes a logical dump with mysqldump, which is consistent while the server runs (--single-transaction). It also takes a tar of the files volume from a throwaway Alpine container that mounts it read-only. The root password reaches mysqldump as an option file on standard input, so it never appears as a command-line argument. The dump is written with umask 077. The archive, created by root in the helper container, is handed back to your user with mode 0600, because the dump contains every user's password hash.

Two details make it safe to run unattended. The trap ... EXIT starts WordPress again whenever the script ends, so a failed dump or a full disk under set -eu doesn't turn a nightly backup into an outage. And both files are written as .tmp and renamed only after every step succeeded. So a failed run never leaves a half-written wordpress.sql that looks like a backup. Point it at a directory it can't write to and check that the site comes back:

The dump's redirection failed, set -e ended the script with status 2, and the trap started WordPress again. It's back up and running its health check. Now the real backup:

The elided lines include a mysqldump warning: MySQL 9.7 runs with GTIDs, and a partial dump carries them. The restore below loads it into a newly initialised server, which accepted it.

Now you'll lose everything. down -v removes the containers, the network and both volumes:

#!/bin/sh
# Usage: ./restore.sh DIR   (into a project with no volumes yet: after down -v, or on a new host)
set -eu
dir=$(realpath "$1")
# Refuse to overlay a live site: tar would mix old and restored files, and the dump would
# load into a database that already has data.
for v in lab-wp_db_data lab-wp_wp_html; do
  if docker volume inspect "$v" >/dev/null 2>&1; then
    echo "refusing: volume $v exists; restore only into a project without volumes" >&2
    exit 1
  fi
done
# Create the network, both volumes and the containers without starting anything.
docker compose create
docker run --rm -v lab-wp_wp_html:/data -v "$dir":/backup:ro alpine:3.24 \
  tar -C /data -xzf /backup/wp_html.tgz
# MySQL initialises an empty db_data from the secrets, then the dump is loaded.
docker compose up -d --wait db
docker compose exec -T db sh -c 'mysql --defaults-extra-file=/dev/fd/3 3<<EOT
[client]
user=root
password=$(cat /run/secrets/db_root_password)
EOT' < "$dir/wordpress.sql"
docker compose up -d --wait

docker compose create makes the project's network, volumes and containers without starting them. So the files can go into the new wp_html volume before WordPress ever starts. With files present, the WordPress entrypoint doesn't copy a fresh core over them. MySQL then initialises an empty db_data from the secrets, which recreates the wp user with the current password, and the dump is loaded. That's why restoring onto a host with different secret values works. Only after the database holds the data does WordPress start. The script first refuses to run when either volume already exists. Extracted over a live wp_html, the archive would mix old and restored files, and the dump would load into a database that already holds a site.

The title set during installation and the uploaded file are back, and both containers are healthy. Until you've run a restore like this, your backup is a hope. Run it on a schedule, against a copy of production, and keep the archives off the host. The guard in restore.sh is the last check here: run it again with the restored volumes in place and it refuses.

Running it for real

Pin both images by digest, as the Compose file does, and move the pins forward deliberately. A moving tag changes your stack without a review. A new WordPress image doesn't update WordPress itself, though. Core, themes and plugins live in the wp_html volume, and the entrypoint copies core only into an empty volume. So a new image digest brings a new PHP, Apache and PHP extensions and leaves the site's code as it was. WordPress updates its own code instead. Its background updater applies minor (security) releases on its own when it can write its files, as it can here. Major releases, themes and plugins are updated from the dashboard or with WP-CLI (wp core update, wp plugin update --all). Take a backup before you update. Treat the wp_html volume as untrusted. It's writable by the web server, which makes wp-content/uploads the first place an attacker drops a PHP file. Put TLS in front, keep your database unpublished, and limit the containers as in "Production best practices".

Try this

On a healthy stack, before cleanup, run chmod 600 secrets/db_password.txt without recreating anything. Predict what the who-reads command and the two curl checks from the 500 step print now, then run them. Since wp-config.php reads the file on every request, compare your result with the 500 above, then restore the file with chmod 644 secrets/db_password.txt.

Takeaway

A secret file outside Swarm keeps its host owner and mode, so you have to make it readable to the user that actually reads it and protect it with a 0700 directory instead. Here the 0600 file passed MySQL's root entrypoint and failed www-data (UID 33) with "Permission denied". The long-syntax uid and mode fields don't help, since Compose 5.6.1 ignores them outside Swarm.

Related