MySQL in Docker Compose

In Docker Compose, MySQL keeps its data in a volume mounted at /var/lib/mysql: the line mysql-data:/var/lib/mysql under the service’s volumes. MYSQL_ROOT_PASSWORD is mandatory unless you set MYSQL_ALLOW_EMPTY_PASSWORD or MYSQL_RANDOM_ROOT_PASSWORD instead.

The docker run command, converted

The storage command from the image’s Docker Hub page, with a named volume for the host folder and the tag filled in:

docker run --name some-mysql -v mysql-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:9.7

services:
  some-mysql:
    image: mysql:9.7
    volumes:
      - mysql-data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: my-secret-pw
volumes:
  mysql-data:
    name: mysql-data

A database the app waits for

services:
  db:
    image: mysql:9.7
    environment:
      MYSQL_ROOT_PASSWORD: my-secret-pw
      MYSQL_DATABASE: app
      MYSQL_USER: app
      MYSQL_PASSWORD: app-secret
    volumes:
      - mysql-data:/var/lib/mysql
    ports:
      - "127.0.0.1:3306:3306"
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1"]
      interval: 5s
      timeout: 5s
      retries: 10
      start_period: 30s
  adminer:
    image: adminer:6
    ports:
      - "127.0.0.1:8080:8080"
    depends_on:
      db:
        condition: service_healthy
volumes:
  mysql-data:

MYSQL_USER and MYSQL_PASSWORD create a second account with every privilege on MYSQL_DATABASE. The healthcheck says 127.0.0.1, not localhost, on purpose: localhost means the Unix socket to mysqladmin, and on first start the entrypoint runs a temporary server with networking off (its log says port: 0) while it creates the users and runs the init scripts. Over the socket that server already answers the ping; over TCP nothing does until the real server is up. No password is needed: mysqladmin ping exits 0 even when the server answers Access denied, because the server is running.

Gotchas

• The variables and the .sql, .sql.gz and .sh files in /docker-entrypoint-initdb.d only act on an empty data directory. With an existing database, MYSQL_ROOT_PASSWORD is ignored.
• MYSQL_USER: root stops the container: MYSQL_USER and MYSQL_PASSWORD are for configuring a regular user and cannot be used for the root user.
• docker compose down -v deletes mysql-data along with the containers.
• mysql:latest is the 26.7 innovation release today, and mysql:lts is 9.7. Pin a series such as 9.7 or 8.4: a newer server upgrades the data directory in place the first time it starts on it.
• Other services connect to db:3306. Inside a container, localhost is that container itself.

GitHub Actions

services:
  mysql:
    image: mysql:9.7
    env:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test
    ports:
      - 3306:3306
    options: >-
      --health-cmd "mysqladmin ping -h 127.0.0.1"
      --health-interval 5s
      --health-retries 20