How can I initialize a MySQL database with schema

Question

I am trying to create a container with a MySQL database and add a schema to these database.

My current Dockerfile is:

FROM mysql
MAINTAINER  (me) <email>

# Copy the database schema to the /data directory
COPY files/epcis_schema.sql /data/epcis_schema.sql

# Change the working directory
WORKDIR data

CMD mysql -u $MYSQL_USER -p $MYSQL_PASSWORD $MYSQL_DATABASE < epcis_schema.sql

In order to create the container I am following the documentation provided on Docker and executing this command:

docker run --name ${CONTAINER_NAME} -e MYSQL_ROOT_PASSWORD=${DB_ROOT_PASSWORD} -e MYSQL_USER=${DB_USER} -e MYSQL_PASSWORD=${DB_USER_PASSWORD} -e MYSQL_DATABASE=${DB_NAME} -d mvpgomes/epcisdb

But when I execute this command the Container is not created and in the Container status it is possible to see that the CMD was not executed successfully, in fact only the mysql command is executed.

Anyway, is there a way to initialize the database with the schema or do I need to perform these operations manually?

Answer 1

I've tried Greg's answer with zero success, I must have done something wrong since my database had no data after all the steps: I was using MariaDB's latest image, just in case.

Then I decided to read the entrypoint for the official MariaDB image, and used that to generate a simple docker-compose file:

database:
  image: mariadb
  ports:
     - 3306:3306
  expose:
     - 3306
  volumes:
     - ./docker/mariadb/data:/var/lib/mysql:rw
     - ./database/schema.sql:/docker-entrypoint-initdb.d/schema.sql:ro
  environment:
     MYSQL_ALLOW_EMPTY_PASSWORD: "yes"

Now I'm able to persist my data AND generate a database with my own schema!

Answer 2

I had this same issue where I wanted to initialize my MySQL Docker instance's schema, but I ran into difficulty getting this working after doing some Googling and following others' examples. Here's how I solved it.

1) Dump your MySQL schema to a file.

mysqldump -h <your_mysql_host> -u <user_name> -p --no-data <schema_name> > schema.sql

2) Use the ADD command to add your schema file to the /docker-entrypoint-initdb.d directory in the Docker container. The docker-entrypoint.sh file will run any files in this directory ending with ".sql" against the MySQL database.

Dockerfile:

FROM mysql:5.7.15

MAINTAINER me

ENV MYSQL_DATABASE=<schema_name> \
    MYSQL_ROOT_PASSWORD=<password>

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

EXPOSE 3306

3) Start up the Docker MySQL instance.

docker-compose build
docker-compose up

Thanks to Setting up MySQL and importing dump within Dockerfile for clueing me in on the docker-entrypoint.sh and the fact that it runs both SQL and shell scripts!

Answer 3

The other simple way, use docker-compose with the fallowing lines:

mysql:
  from: mysql:5.7
  volumes:
    - ./database:/tmp/database
  command: mysqld --init-file="/tmp/database/install_db.sql"

Put your database schema into the ./database/install_db.sql. Every time when you build up your container, the install_db.sql executed.

Answer 4

After Aug. 4, 2015, if you are using the official mysql Docker image, you can just ADD/COPY a file into the /docker-entrypoint-initdb.d/ directory and it will run with the container is initialized. See github: https://github.com/docker-library/mysql/commit/14f165596ea8808dfeb2131f092aabe61c967225 if you want to implement it on other container images