How do I initialize a MYSQL docker container with a .sql script?

Question

I have a .sql file that I want to run to fill the DB with data when I start up the container. How do I do it?

My docker scripts are below:

docker-compose.yml

version: '3'
services:
  php:
    build:
      context: .
      dockerfile: Dockerfile
    volumes:
      - ./src:/var/www/
    ports:
      - 8080:80
    depends_on:
      - mysql
  mysql:
    image: mysql:latest
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: mydatabase
      MYSQL_USER: user
      MYSQL_PASSWORD: userpassword
    volumes:
      - mysql_data:/var/lib/mysql
volumes:
  mysql_data:

Dockerfile

FROM php:8.1-apache
COPY src/ /var/www/

I have tried using this line in my Dockerfile

RUN mysql -usecret -pmydatabase mysql < /var/www/DB_Setup.sql

But I get the error

/bin/sh: 1: mysql: not found ERROR: Service 'php' failed to build: The command '/bin/sh -c mysql -usecret -pmydatabase mysql < /var/www/DB_Setup.sql' returned a non-zero code: 127

So I guess this is because the php container doesn't have mysql installed?

I guess I could use a Dockerfile with contents like this? But I'm not sure how to add it to docker-compose.yml? Taken from here

FROM mysql:latest

MAINTAINER me

ENV MYSQL_DATABASE=mysql \
    MYSQL_ROOT_PASSWORD=mydatabase

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

EXPOSE 3306

Thanks.

score 0 · Accepted Answer · answered Jul 21 '23 at 09:46

From the docs:

Initializing a fresh instance

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

So, you can add another volume mapping to your mysql container:

- ./my-init-scripts:/docker-entrypoint-initdb.d/:ro

where my-init-scripts is a directory within the same directory that contains your composer file. Move your DB_Setup.sql file into my-init-scripts and it will be run when your container is started for the first time.

How do I initialize a MYSQL docker container with a .sql script?

1 Answers1

Initializing a fresh instance