How to init MySql Database in Docker Compose

Question

Scenario: I developed a microservice in Spring which uses a mysql 8 database. This db has to be initalized (create a Database, some tables and data). On my host machine I initialized the database with data.sql and schema.sql script. The Problem is, that I have to set:

spring.datasource.initialization-mode=always

for the first execute. This initializes my db the way I want to. For future runs I have to comment this command. Very ugly soltion but I could not find a better one and I got no reponse right now to this question. I thought for testing it is ok but I definetly have to improve that. Currently I want to run my service with docker by a docker compose.

Expected: This is the docker-compose file. Fairly simple. I'm totally new in the world of docker and so I want to go on step by step.

version: '3' 
services:usermanagement-service:
build:
  ./UserManagementService
restart:
  on-failure
ports:
  - "7778:7778"
links:
  - mysqldb
depends_on:
  - mysqldb   mysqldb:
build:
  ./CustomMySql
volumes:
  - ./mysql-data:/var/lib/mysql
restart:
  on-failure
environment:
  MYSQL_ROOT_PASSWORD: root
  MYSQL_DATABASE: userdb
  MYSQL_USER: testuser
  MYSQL_PASSWORD: testuser
expose:
  - "3600"

I was expecting, that my database gets initialized with a user and that in the first run my microservice initializes the db with data. So before the next start of compose I have to comment the command and rebuild the image. (I know , ugly)

Problem: So besides this ugly solution I run into runtime problems. On docker-compose up my Microservice is faster than the init of the database. So it tries to call the db what results in en error. Because of the restart on failure the microservice comes up again. Now it works because the init of the db has finished.

Solution: I searched the www and for it seems like a known problem which might be solved within a wait-for-it.sh file. This has to be included with COPY in the Dockerfile. So I'm no expert but I am searching for a good solution to either:

• init database from within spring und make my service wait till mysql is ready
• or init the database from withn my container via a volume on the first run and of course solve this init problem.

I don't know what is best practice here I and I would be very thankful for some help how to build this up.

Answer 1

For loading your sql files only on the first run:

You can use the below compose file

version: '2.1'
services:

  usermanagement-service:
    build: ./UserManagementService
    restart: on-failure
    ports:
      - "7778:7778"
    depends_on:
      mysqldb:
        condition: service_healthy

  mysqldb:
    image: mysql
    volumes:
      - ./mysql-data:/var/lib/mysql
      - ./mysql-init-files:/docker-entrypoint-initdb.d
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: userdb
      MYSQL_USER: testuser
      MYSQL_PASSWORD: testuser
    ports:
      - "3600:3306"
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
      timeout: 20s
      retries: 10

You have to place your data.sql and schema.sql files under ./docker-entrypoint-initdb.d directory using Volumes for more info.

SQL files in this folder will be loaded only if the DB's data directory is empty (the very first run of db service). (i.e) in your case ./mysql-data folder should be empty

For your second problem:

Instead of using wait-for-it.sh, you can use healthcheck and service_healthy. Here usermanagement-service will be started once mysqldb successfully executes ["CMD", "mysqladmin" ,"ping", "-h", "localhost"] in the specified interval. For more details on healthcheck and depends_on, refer here.

Got test command from here.