How can I initialize a MySQL database with schema in a Docker container?

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 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!