I have to create a specific schema for each service. First, I will connect with the database as a root user with the help of docker-compose file docker-compose.yml Second, I will create schema.sql file under the root path of the spring boot project. schema.sql 3.Finally, Connect with the specific schema on the database from the project with the help of

create specific schema in mysql with docker-compose

I have to create a specific schema for each service.

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:

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.

Advertisement

Answer