DB-유

Running MySQL in Docker with Persistent Volumes

· mysql, docker, linux, containers

I lost a dev database the first time I ran docker rm on a MySQL container. A container's writable layer disappears with it, so a database living there is gone with it. Since then I always put the data directory on a named volume, and the container becomes disposable while the data stays.

I use MySQL in containers for development, CI and small internal services. The official image makes it a one-line start. Here it is with MySQL 5.7 and 8.0 under Docker CE on Ubuntu 18.04, first with plain docker run and then with docker-compose, plus the config, backup and upgrade details that follow. I'm on Docker CE 18.09 and docker-compose 1.23, with my user in the docker group.

Named volume, then container

docker volume create mysql57_data
docker run -d --name mysql57 \
  -e MYSQL_ROOT_PASSWORD='RootPassw0rd!' \
  -e MYSQL_DATABASE=appdb \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD='AppPassw0rd!' \
  -v mysql57_data:/var/lib/mysql \
  -p 127.0.0.1:3306:3306 \
  mysql:5.7

The -v mysql57_data:/var/lib/mysql line is the whole point. /var/lib/mysql is declared as a VOLUME in the image, so even without -v Docker creates an anonymous volume, but anonymous volumes are hard to find and easy to prune. Name it.

Publishing on 127.0.0.1:3306 rather than -p 3306:3306 matters: Docker inserts its own iptables rules ahead of ufw, so -p 3306:3306 exposes the port to the world regardless of your firewall.

The MYSQL_* variables are read only on the very first start, when the data directory is empty. Changing them later has no effect on an existing volume.

Custom configuration

The image reads /etc/mysql/conf.d/*.cnf. Bind-mount a directory of your own:

mkdir -p /srv/mysql57/conf.d
cat > /srv/mysql57/conf.d/custom.cnf <<'EOF'
[mysqld]
innodb_buffer_pool_size = 512M
character-set-server    = utf8mb4
collation-server        = utf8mb4_unicode_ci
slow_query_log          = 1
long_query_time         = 1
EOF

Then add -v /srv/mysql57/conf.d:/etc/mysql/conf.d:ro to the run command. Because MYSQL_* initialisation only happens once, it is worth getting the config right before the first start so the system tables are created with the character set you want.

docker-compose for anything you'll keep

I stopped typing long docker run lines once a container lived past a week. A compose file records everything and makes recreation predictable. Save as /srv/mysql57/docker-compose.yml:

version: '3.3'
services:
  db:
    image: mysql:5.7
    container_name: mysql57
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: 'RootPassw0rd!'
      MYSQL_DATABASE: appdb
      MYSQL_USER: appuser
      MYSQL_PASSWORD: 'AppPassw0rd!'
    ports:
      - "127.0.0.1:3306:3306"
    volumes:
      - mysql57_data:/var/lib/mysql
      - ./conf.d:/etc/mysql/conf.d:ro
    command: --default-authentication-plugin=mysql_native_password
volumes:
  mysql57_data:
    external: true

external: true tells compose to use the volume you already created instead of prefixing a new one with the project name. The command line is harmless on 5.7 and essential if you switch the image to mysql:8.0 and have older clients.

cd /srv/mysql57
docker-compose up -d
docker-compose logs -f db

Wait for [Note] mysqld: ready for connections to appear twice; the first is the temporary server used for initialisation.

Backups

Two workable approaches. A logical dump through the running container:

docker exec mysql57 sh -c 'exec mysqldump --all-databases --single-transaction -uroot -p"$MYSQL_ROOT_PASSWORD"' \
  | gzip > /srv/backups/mysql57-$(date +%F).sql.gz

Or a cold copy of the volume using a throwaway container, which is exact but requires the database to be stopped:

docker-compose stop db
docker run --rm -v mysql57_data:/from:ro -v /srv/backups:/to alpine \
  tar czf /to/mysql57_data-$(date +%F).tar.gz -C /from .
docker-compose start db

Upgrading

Because the data is on a volume, moving to a newer image is a matter of changing the tag and letting mysql_upgrade run. From 5.7.25 to a newer 5.7 point release:

docker-compose pull
docker-compose up -d
docker exec mysql57 mysql_upgrade -uroot -p'RootPassw0rd!'

For 5.7 to 8.0, take a backup first, change image: mysql:8.0, and run docker-compose up -d. MySQL 8.0 upgrades the data dictionary on first start; mysql_upgrade then finishes the system tables. Downgrading afterwards is not supported, which is why the backup comes first.

Does the data survive?

docker volume inspect mysql57_data --format '{{ .Mountpoint }}'
sudo ls /var/lib/docker/volumes/mysql57_data/_data | head
docker exec -it mysql57 mysql -uappuser -p'AppPassw0rd!' appdb -e "SELECT VERSION(), @@datadir;"

Then the real test: destroy the container and bring it back.

docker-compose down
docker-compose up -d
docker exec -it mysql57 mysql -uappuser -p'AppPassw0rd!' appdb -e "SHOW TABLES;"

docker-compose down removes the container and network but not external volumes. If your tables are still there, persistence works.

Gotchas

[ERROR] --initialize specified but the data directory has files in it. Aborting. The volume already holds data and you passed new MYSQL_* variables expecting them to apply. They will not. Either use the existing credentials or remove the volume with docker volume rm mysql57_data if you really want a fresh database.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' from the host. The host's mysql client is looking for a socket; use mysql -h 127.0.0.1 -P 3306 to go over TCP to the published port.

Authentication plugin 'caching_sha2_password' cannot be loaded after switching to the 8.0 image. Keep the command: --default-authentication-plugin=mysql_native_password line and alter existing users with ALTER USER ... IDENTIFIED WITH mysql_native_password BY '...'.

Container restarts in a loop with [ERROR] InnoDB: Cannot allocate memory for the buffer pool. Your custom.cnf asks for more than the container or host has. Check with docker stats and lower innodb_buffer_pool_size.

Keep the compose file and config in version control, and run the destroy-and-recreate test once. After that, a containerized MySQL is just another service, and upgrading is a tag change.