Docker Compose Demo: MySQL and phpMyAdmin with a Persistent Volume

Docker Compose describes a set of containers in one file and starts them with one command. A database with a web admin tool is a good first example: two services, one network, one volume. This post builds that, and shows how to prove the data survives a restart.

Versions in use on this date. Docker Engine 24.0 and Compose v2 (the docker compose plugin, not the old docker-compose script). MySQL 8.0 is the long-supported line; the 8.1 and 8.2 releases are short-lived “innovation” releases, so do not pick them for a server you want to leave alone. phpMyAdmin is at 5.2.1. The old version: key at the top of a compose file is obsolete and can be left out.

The layout

flowchart TB
  U[Browser<br/>localhost 8080] --> PMA[phpmyadmin container]
  PMA -->|port 3306 on compose network| DB[(mysql container)]
  DB --> V[(Named volume db_data<br/>mounted at /var/lib/mysql)]
  ENV[.env file and db_password.txt] -.-> DB

Compose puts both services on one private network and lets them reach each other by service name, so phpMyAdmin connects to the host db. Only phpMyAdmin is published to the host. MySQL itself does not need a published port.

The files

mkdir -p ~/mysql-demo && cd ~/mysql-demo
printf '%s' "$(openssl rand -base64 24)" > db_root_password.txt
chmod 600 db_root_password.txt

compose.yaml:

services:
  db:
    image: mysql:8.0
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/db_root_password
      MYSQL_DATABASE: appdb
    secrets:
      - db_root_password
    volumes:
      - db_data:/var/lib/mysql
    healthcheck:
      test: ["CMD-SHELL", "mysqladmin ping -h localhost -uroot -p$$(cat /run/secrets/db_root_password) --silent"]
      interval: 10s
      timeout: 5s
      retries: 10
      start_period: 30s

  phpmyadmin:
    image: phpmyadmin:5.2
    restart: unless-stopped
    depends_on:
      db:
        condition: service_healthy
    environment:
      PMA_HOST: db
    ports:
      - "127.0.0.1:8080:80"

secrets:
  db_root_password:
    file: ./db_root_password.txt

volumes:
  db_data:

Points worth noticing:

• The official MySQL image reads MYSQL_ROOT_PASSWORD_FILE, so the password sits in a file with mode 600 and not in the compose file or in docker inspect.
• A named volume (db_data) is managed by Docker and survives docker compose down. A bind mount to a folder works too but brings file ownership problems.
• depends_on with condition: service_healthy makes phpMyAdmin wait until MySQL answers, instead of failing on its first start.
• Publishing as 127.0.0.1:8080 keeps phpMyAdmin off the network. To reach it from your laptop, use an SSH tunnel: ssh -L 8080:127.0.0.1:8080 user@server. Exposing phpMyAdmin to the internet is a common way to get a database broken into.

Run it

docker compose up -d
docker compose ps
docker compose logs -f db      # wait for "ready for connections"

Open http://localhost:8080 and log in as root with the contents of db_root_password.txt. The first start takes longer because MySQL initialises its data directory.

Prove the data survives

docker compose exec db sh -c 'mysql -uroot -p"$(cat /run/secrets/db_root_password)" appdb \
  -e "CREATE TABLE t(id INT PRIMARY KEY, note VARCHAR(50)); INSERT INTO t VALUES (1,\"still here\");"'

docker compose down            # removes containers, keeps the volume
docker compose up -d
docker compose exec db sh -c 'mysql -uroot -p"$(cat /run/secrets/db_root_password)" appdb -e "SELECT * FROM t;"'

The row is still there. docker compose down -v is the command that deletes the volume and the data, so use it only when you mean to start over.

Back it up

docker compose exec -T db sh -c 'mysqldump -uroot -p"$(cat /run/secrets/db_root_password)" \
  --single-transaction --routines --triggers appdb' | gzip > appdb-$(date +%F).sql.gz

A volume is not a backup. It lives on the same disk as the host, so copy dumps off the machine.

Common problems

Symptom	Fix
phpMyAdmin says “cannot connect”	PMA_HOST must be the service name db, and the database must be healthy
Changing the password has no effect	The password is only read when the data directory is first created; change it with ALTER USER
Port 8080 already in use	Change the left side of the port mapping
Container restarts forever	docker compose logs db; a half-initialised volume needs down -v if it holds no data you need