create specific schema in mysql with docker-compose

I have to create a specific schema for each service.

1. First, I will connect with the database as a root user with the help of docker-compose file

docker-compose.yml

version: "3"

services:
  patient-service:
    image: patient-service:1.0
    container_name: patient-service
    ports:
    - 9090:9090
    restart: on-failure
    networks:
      - patient-mysql
    depends_on:
      - mysql-db
    links:
      - mysql-db
  mysql-db:
    image: mysql:8
    container_name: mysql-db
    ports:
    - 3306:3306
    networks:
      - patient-mysql
    environment:
      - MYSQL_ROOT_PASSWORD=14292
      - MYSQL_USER=root

networks:
  patient-mysql:

2. Second, I will create schema.sql file under the root path of the spring boot project.

schema.sql

create user 'patient_db'@'%' identified by '67546';

grant all privileges on *.* to 'patient_db'@'%';

flush privileges;

create database patient_db;

use patient_db;

create table if not exists holiday(holiday_date varchar(100),holiday_name varchar(100),created_by varchar(100),
modified_by varchar(100),created_date_time timestamp,modified_date_time timestamp,constraint pk_date_holiday primary key(holiday_date));

3.Finally, Connect with the specific schema on the database from the project with the help of properties-mysql.yml file.

 spring:
      datasource:
        url: 'jdbc:mysql://mysql-db/patient_db?createDatabaseIfNotExists=true&autoReconnect=true&llowPublicKeyRetrieval=true&useSSL=false&allowPublicKeyRetrieval=true'
        username: patient_db
        password: 67546
        driver-class-name: com.mysql.cj.jdbc.Driver
        initialization-mode: always
        tomcat:
          test-while-idle: true
          validation-query: SELECT 1
      jpa:
        database-platform: org.hibernate.dialect.MySQL8Dialect
        show-sql: true
        hibernate:
          ddl-auto: validate

then the following exception has thrown

access denied for user 'patient_db'@'%' (using password yes)

what is wrong with my compose file? Can not we create a different user inside the schema.sql file which is loaded with the application?

github – github repo of app

Answer

I can suggest you more simple way. Create new folder (e.g. init_scripts) and move schema.sql to this folder. And mount this folder to mysql docker image to folder /docker-entrypoint-initdb.d

volumes:
  - "./init_scripts:/docker-entrypoint-initdb.d"

For more information check it here.