在Docker Compose上执行SQL脚本

Question

在Docker Compose上执行SQL脚本

9

我有一个项目，当在项目的根目录下运行./entrypoint.sh或docker-compose up时会运行，并生成swagger API接口，但调用返回的是入口响应没有数据。

如果在本地主机上使用MySQL而没有使用docker进行运行，则完全正常。如何加载数据呢？

entrypoint.sh

#!/bin/bash

docker network create turingmysql
docker container run -p  3306:3306 --name mysqldb --network turingmysql -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=tshirtshop -d mysql:5.7
docker-compose build
docker-compose up

Docker文件

FROM mysql:5.7

ADD ./database/tshirtshop.sql /docker-entrypoint-initdb.d

#### Stage 1: Build the application
FROM openjdk:8-jdk-alpine as build

# Set the current working directory inside the image
WORKDIR /app

# Copy maven executable to the image
COPY mvnw .
COPY .mvn .mvn

# Copy the pom.xml file
COPY pom.xml .

# Build all the dependencies in preparation to go offline. 
# This is a separate step so the dependencies will be cached unless 
# the pom.xml file has changed.
RUN ./mvnw dependency:go-offline -B

# Copy the project source
COPY src src


# Package the application
RUN ./mvnw package -DskipTests
RUN mkdir -p target/dependency && (cd target/dependency; jar -xf ../*.jar)

#### Stage 2: A minimal docker image with command to run the app 
FROM openjdk:8-jre-alpine

ARG DEPENDENCY=/app/target/dependency

# Copy project dependencies from the build stage
COPY --from=build ${DEPENDENCY}/BOOT-INF/lib /app/lib
COPY --from=build ${DEPENDENCY}/META-INF /app/META-INF
COPY --from=build ${DEPENDENCY}/BOOT-INF/classes /app

ENTRYPOINT ["java","-cp","app:app/lib/*","com.turing.ecommerce.TuringApplication"]

docker-compose.yml

version: '3.7'

# Define services
services:
  # App backend service
  app-server:
    # Configuration for building the docker image for the backend service
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8080:8080" # Forward the exposed port 8080 on the container to port 8080 on the host machine
    restart: always
    depends_on: 
      - mysqldb # This service depends on mysql. Start that first.
    environment: # Pass environment variables to the service
      SPRING_DATASOURCE_URL: jdbc:mysql://mysqldb:3306/tshirtshop?useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC
      SPRING_DATASOURCE_USERNAME: root
      SPRING_DATASOURCE_PASSWORD: root   
    networks: # Networks to join (Services on the same network can communicate with each other using their name)
      - turingmysql

      # Database Service (Mysql)
  mysqldb:
    image: mysql:5.7
    ports:
      - "3306:3306"
    restart: always

    environment:
      MYSQL_DATABASE: tshirtshop
      MYSQL_USER: root
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - db-data:/var/lib/mysql

    networks:
      - turingmysql  


# Volumes
volumes:
  db-data:

# Networks to be created to facilitate communication between containers
networks:
  turingmysql:

- tksilicon

tshirtshop.sql 是否向数据库插入任何值？您的应用程序是否正确配置以查询数据库并不返回空结果？ - OneCricketeer

是的，有很多创建表格和插入语句。 - tksilicon

你是否在API中插入了日志语句以验证应该返回的内容？ - OneCricketeer

如果我不使用docker运行，API调用会返回数据。我已经部署在heroku上，我单独创建了数据库并进行了部署，它可以正常工作，但是我需要将其docker化。 - tksilicon

3个回答

2

如果你已经将sql脚本复制到docker构建中，那么你不需要在docker-compose中再次映射它，如果你有docker-compose，则不需要使用bash脚本单个命令docker-compose up --build就可以完成工作。

因此，请根据你的Dockerfile修改你的docker-compose文件。

Dockerfile

FROM mysql

ADD init.sql /docker-entrypoint-initdb.d

docker-compose

version: '3.7'

services:
  # App backend service
  app-server:
    # Configuration for building the docker image for the backend service
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8080:8080" # Forward the exposed port 8080 on the container to port 8080 on the host machine
    restart: always
    depends_on: 
      - mysqldb # This service depends on mysql. Start that first.
    environment: # Pass environment variables to the service
      SPRING_DATASOURCE_URL: jdbc:mysql://mysqldb:3306/tshirtshop?useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC
      SPRING_DATASOURCE_USERNAME: root
      SPRING_DATASOURCE_PASSWORD: root   
    networks: # Networks to join (Services on the same network can communicate with each other using container name)
      - uringmysql

      # Database Service (Mysql)

  mysql:
    build: .
    environment:
      MYSQL_ROOT_PASSWORD: root123
      MYSQL_DATABASE: appdata
      MYSQL_USER: test
      MYSQL_PASSWORD: root123
    volumes:
      - db-data:/var/lib/mysql
    tty: true
# Volumes
volumes:
  db-data:

# Networks to be created to facilitate communication between containers
networks:
  turingmysql:

现在只需要运行。

docker-compose up --build

这将构建并启动容器，您无需映射主机 init 脚本，因为它已经在 Docker 镜像中。

目录结构将如下所示。

现在您的应用将能够使用jdbc:mysql://mysqldb:3306/tshirtshop?这个端点访问数据库，因为它们都在同一网络中，并且可以使用名称相互引用。

- Adiii

2

感谢cricket_007和Adii的回复。他们让我朝正确的方向前进。我想记录一下我的经验以及问题是如何解决的。我刚开始接触docker化，所以正在通过实践学习。对于任何新手在Spring Boot、MySQL和docker中遇到相同问题的人来说，这肯定会有所帮助。

首先，我的entrypoint.sh已经改变了。docker-compose down是为了重启服务。

     #!/bin/bash

docker-compose down -v
docker-compose up --build

第二，我必须使用现有的mysql镜像而不是构建一个新的。

version: '3.7'

# Define services
services:
  # App backend service
  app-server:
    # Configuration for building the docker image for the backend service
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8080:8080" # Forward the exposed port 8080 on the container to port 8080 on the host machine
    restart: always
    depends_on: 
      - mysql # This service depends on mysql. Start that first.
    environment: # Pass environment variables to the service
      SPRING_DATASOURCE_URL: jdbc:mysql://mysql:3306/tshirtshop?useSSL=false&allowPublicKeyRetrieval=true&useLegacyDatetimeCode=false&serverTimezone=UTC
      SPRING_DATASOURCE_USERNAME: turing
      SPRING_DATASOURCE_PASSWORD: pass  
    networks: # Networks to join (Services on the same network can communicate with each other using their name)
      - turingmysql

      # Database Service (Mysql)
  mysql:
    image: mysql/mysql-server
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: tshirtshop
      MYSQL_USER: turing
      MYSQL_PASSWORD: pass


    volumes:
      - db-data:/var/lib/mysql
      - ./database:/docker-entrypoint-initdb.d
    tty: true
    networks: # Networks to join (Services on the same network can communicate with each other using their name)
      - turingmysql

# Volumes
volumes:
  db-data:

 # Networks to be created to facilitate communication between containers
networks:
  turingmysql:
    driver: bridge

需要指定网络是桥接的。我的 SQL 文件从相对于 docker-compose.yml 的文件夹中挂载。同时，我还需要在我的 jdbc url 中添加 allowPublicKeyRetrieval=true。创建了一个用户来访问数据库 tshirtshop。

以下是 Dockerfile。

#### Stage 1: Build the application
FROM openjdk:8-jdk-alpine as build

# Set the current working directory inside the image
WORKDIR /app

# Copy maven executable to the image
COPY mvnw .
COPY .mvn .mvn

# Copy the pom.xml file
COPY pom.xml .

# Build all the dependencies in preparation to go offline. 
# This is a separate step so the dependencies will be cached unless 
# the pom.xml file has changed.
RUN ./mvnw dependency:go-offline -B

# Copy the project source
COPY src src


# Package the application
RUN ./mvnw package -DskipTests
RUN mkdir -p target/dependency && (cd target/dependency; jar -xf ../*.jar)

#### Stage 2: A minimal docker image with command to run the app 
FROM openjdk:8-jre-alpine

ARG DEPENDENCY=/app/target/dependency

# Copy project dependencies from the build stage
COPY --from=build ${DEPENDENCY}/BOOT-INF/lib /app/lib
COPY --from=build ${DEPENDENCY}/META-INF /app/META-INF
COPY --from=build ${DEPENDENCY}/BOOT-INF/classes /app

ENTRYPOINT ["java","-cp","app:app/lib/*","com.turing.ecommerce.TuringApplication"]

在Mac上从项目的根目录运行./entrypoint.sh，剩下的就是历史了。

- tksilicon

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- OneCricketeer · Accepted Answer

你是否有两个Dockerfiles？看起来你构建了自己的MySQL容器？

否则，这些不应该是你Java多阶段构建的一部分。

FROM mysql:5.7

ADD ./database/tshirtshop.sql /docker-entrypoint-initdb.d

假设您已经为mysql构建了一个单独的镜像，在Docker-Compose中，您没有使用它，因为您仍在引用image: mysql:5.7 相反，您应该将SQL脚本挂载到它上面。

例如：

  mysqldb:
    image: mysql:5.7
    ...
    volumes:
      - db-data:/var/lib/mysql
      - ./database/tshirtshop.sql:/docker-entrypoint-initdb.d/0_init.sql

那么，先暂时忘记Java服务，使用MySQL Workbench或mysql CLI验证数据是否存在。一旦验证成功，再启动API。