Correct Implementation of MySQL Data Persistence in Docker-Compose

Problem Background and Common Misconceptions

In Docker containerized deployments, data persistence is a critical concern. Many developers encounter situations where MySQL data is lost after executing docker-compose down, typically due to insufficient understanding of Docker volume mechanisms.

The original configuration employed a data container pattern:

version: '2'
services:
  data:
    container_name: flask_data
    image: mysql:latest
    volumes:
      - /var/lib/mysql
    command: "true"

  mysql:
    container_name: flask_mysql
    restart: always
    image: mysql:latest
    environment:
      MYSQL_ROOT_PASSWORD: 'test_pass'
      MYSQL_USER: 'test'
      MYSQL_PASS: 'pass'
    volumes_from:
      - data
    ports:
      - "3306:3306"

This configuration presents several issues: first, the volumes: - /var/lib/mysql in the data container creates anonymous volumes that don't map to host directories; second, the volumes_from mechanism is considered an unnecessarily complex solution in modern Docker.

In-depth Analysis of Permission Issues

When attempting to improve the configuration using host directory mapping:

volumes:
  - ./data:/var/lib/mysql

Permission errors occur: mysqld: Can't create/write to file '/var/lib/mysql/is_writable' (Errcode: 13 - Permission denied). This happens because the mysql user inside the MySQL container (UID 999) lacks write permissions to the host directory.

The referenced article confirms this issue: even when files are created with 660 permissions, MySQL fails to start properly, showing Table 'mysql.host' doesn't exist errors. This indicates that permission and file ownership issues require special attention.

Optimal Solution: Docker Volumes

The most recommended solution is using Docker-managed named volumes:

version: '2'
services:
  mysql:
    container_name: flask_mysql
    restart: always
    image: mysql:latest
    environment:
      MYSQL_ROOT_PASSWORD: 'test_pass'
      MYSQL_USER: 'test'
      MYSQL_PASS: 'pass'
    volumes:
      - my-datavolume:/var/lib/mysql
volumes:
  my-datavolume:

Advantages of this approach include:

• Automatic permission handling - Docker ensures container users have appropriate access rights
• Data persistence - data remains intact unless explicitly removed with docker-compose down -v
• Better performance - Docker volumes typically offer superior I/O performance compared to host directory mappings
• Simplified configuration - eliminates the need for additional data containers

Alternative Solutions Comparison

Other viable approaches include:

Host Directory Mapping:

volumes:
  - /opt/mysql_data:/var/lib/mysql

This method requires manual permission management, typically needing:

sudo chown -R 999:999 /opt/mysql_data

Container Committing: Executing docker commit my_data_container before running docker-compose down, but this increases operational complexity.

Stopping Instead of Destroying: Using docker-compose stop instead of docker-compose down, though this is only a temporary solution.

Implementation Details and Technical Principles

Docker volumes operate based on union filesystem characteristics. When a container starts, Docker creates dedicated storage areas under /var/lib/docker/volumes. These areas are not directly affected by container lifecycle, providing independent storage space for data.

In the MySQL context, the /var/lib/mysql directory contains database files, log files, and configuration files. Using volumes ensures these critical files remain available after container restarts while avoiding complex permission and ownership issues.

Best Practice Recommendations

Based on practical experience, we recommend:

1. Prioritize Docker-managed named volumes as the most reliable and straightforward solution
2. In development environments, consider host directory mapping for direct data file access
3. In production environments, implement backup strategies with regular volume backups
4. Avoid deprecated volumes_from instructions to maintain configuration simplicity
5. Ensure all team members use consistent data persistence strategies in collaborative projects

By correctly configuring Docker volumes, developers can focus on application logic without worrying about data loss, thereby improving development efficiency and system reliability.