How to save mysql subdirectory on local system from a docker container

I need to save my database from a docker mysql container so other team members can also work on it. Until now we were using this, which worked just fine:

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

However the /db folder was really messy, because the /mysql folder contains all the mysql files, not just the database. So I tried to save only the database folder:

    volumes:
      - ./db/:/var/lib/mysql/database/

But unfortunately i keep getting this error:

[ERROR] --initialize specified but the data directory has files in it. Aborting.
[ERROR] Aborting

However despite what the error says, the /db folder is empty (no hidden files either), so I have no idea what the error actually is.

I have also tried other subdirectories inside /mysql and got the same result. When I revert the changes back to - ./db/:/var/lib/mysql/ the error goes away.

Full docker-compose.yml:

  mysql:
    image: mysql:${MYSQL_VERSION:-latest}
    restart: always
    ports:
      - "3306:3306"
    volumes:
      - ./db/:/var/lib/mysql/${DB_NAME}
    networks:
      - backend
    environment:
      MYSQL_ROOT_PASSWORD: "${DB_ROOT_PASSWORD}"
      MYSQL_DATABASE: "${DB_NAME}"
      MYSQL_USER: "${DB_USERNAME}"
      MYSQL_PASSWORD: "${DB_PASSWORD}"
    container_name: ${PROJECT_PREFIX}_mysql

Answer

There are a few things to note with the example you’ve shared:

  • The MySQL image that you are using does not actually ‘initialize’ the database server until it is first brought up (a common practice as it keeps the image size relatively small)
  • Docker containers do not persist any data unless you explictly mount it using volumes. Therefore, each time you cycle with down/up, you are reseting the container to the image’s original state.
  • When you cycle through down/up, the order of operations is like this:
    1. image original state is used
    2. volume mounts are mounted in next
    3. the entrypoint scripts are ran to bring up the container.
  • For the case of point 3, MySQL’s entrypoint scripts will ‘initialize’ the database server if the /var/lib/mysql directory is empty, otherwise, it will just load it from existing files.
  • When you mount /var/lib/mysql, it works becase:
    • if the directory is empty, then it is initialized
    • if the directory is not empty, then it loads the state.
  • However, when you mount in /var/lib/mysql/database, only this directory is mounted and other important data is not included in the /var/lib/mysql because it does not exist in the original image. Turns out the other important data that is missing is what MySQL checks to verify if it needs to ‘initialize’ the database server or not. It finally attempts to initialize due to the missing data but if the /var/lib/mysql is not empty, it throws an error (your error). In this case, it is not empty because you’ve mounted in a database directory into /var/lib/mysql.

Your options are basically:

  • Live with mounting in /var/lib/mysql and backing up the entire directory (make sure to only take backups when you’ve docker-compose down!)
  • Create your own Dockerfile with additional RUN steps to initialize MySQL and bring it down so that the database server is ‘initialized’ and stored in the image. However, I would not recommend this as: 1) your docker image size will be much larger, and 2) this option is prone to corruption issues.