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?

User · Answer

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

User · Answer

After to struggle a little bit with that, take a look the Dockerfile using named volumes (db-data). It's important declare a plus at final part, where I mentioned that volume is [external]

All worked great this way!

version: "3"

services:

  database:
    image: mysql:5.7
    container_name: mysql
    ports:
      - "3306:3306"
    volumes:
      - db-data:/docker-entrypoint-initdb.d
    environment:
      - MYSQL_DATABASE=sample
      - MYSQL_ROOT_PASSWORD=root

volumes:
  db-data:
    external: true

User · Answer

Below is the Dockerfile I used successfully to install xampp  create a MariaDB with scheme and pre populated with the info used on local server usrs pics orders etc     FROM ubuntu 14 04  COPY Ecommerce sql  root  RUN apt-get update     amp  amp  apt-get install wget -yq     amp  amp  apt-get install nano     amp  amp  wget https   www apachefriends org xampp-files 7 1 11 xampp-linux-x64-7 1 11-0-installer run     amp  amp  mv xampp-linux-x64-7 1 11-0-installer run  opt      amp  amp  cd  opt      amp  amp  chmod  x xampp-linux-x64-7 1 11-0-installer run     amp  amp  printf  y n y n r n y n r n      xampp-linux-x64-7 1 11-0-installer run     amp  amp  cd  opt lampp bin     amp  amp   opt lampp lampp start     amp  amp  sleep 5s      amp  amp    mysql -uroot -e  CREATE DATABASE Ecommerce      amp  amp    mysql -uroot -D Ecommerce  lt   root Ecommerce sql     amp  amp  cd       amp  amp   opt lampp lampp reload     amp  amp  mkdir opt lampp htdocs Ecommerce  COPY  Ecommerce  opt lampp htdocs Ecommerce  EXPOSE 80

User · Answer

The other simple way  use docker-compose with the following 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 will be executed

User · Answer

Another way based on a merge of serveral responses here before :

docker-compose file :

version: "3"
services:
    db:
      container_name: db
      image: mysql
      ports:
       - "3306:3306"  
      environment:
         - MYSQL_ROOT_PASSWORD=mysql
         - MYSQL_DATABASE=db

      volumes:
         - /home/user/db/mysql/data:/var/lib/mysql
         - /home/user/db/mysql/init:/docker-entrypoint-initdb.d/:ro

where /home/user.. is a shared folder on the host

And in the /home/user/db/mysql/init folder .. just drop one sql file, with any name, for example init.sql containing :

CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' IDENTIFIED BY 'mysql';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost' IDENTIFIED BY 'mysql';
USE mydb
CREATE TABLE CONTACTS (
    [ ... ]
);
INSERT INTO CONTACTS VALUES ...
[ ... ]

According to the official mysql documentation, you can put more than one sql file in the docker-entrypoint-initdb.d, they are executed in the alphabetical order

User · Answer

The easiest solution is to use tutum mysql Step1 docker pull tutum mysql 5 5  Step2 docker run -d -p 3306 3306 -v  tmp  tmp  -e STARTUP SQL  quot  tmp to be imported mysql quot  tutum mysql 5 5  Step3 Get above CONTAINER ID and then execute command docker logs to see the generated password information  docker logs   lt CONTAINER ID gt

User · Answer

For the ones not wanting to create an entrypoint script like me, you actually can start mysqld at build-time and then execute the mysql commands in your Dockerfile like so:

RUN mysqld_safe & until mysqladmin ping; do sleep 1; done && \
    mysql -uroot -e "CREATE DATABASE somedb;" && \
    mysql -uroot -e "CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'somepassword';" && \
    mysql -uroot -e "GRANT ALL PRIVILEGES ON somedb.* TO 'someuser'@'localhost';"

The key here is to send mysqld_safe to background with the single & sign.

User · Answer

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!

User · Answer

I am sorry for this super long answer  but  you have a little way to go to get where you want   I will say that normally you wouldn t put the storage for the database in the same container as the database itself  you would either mount a host volume so that the data persists on the docker host  or  perhaps a container could be used to hold the data   var lib mysql    Also  I am new to mysql  so  this might not be super efficient  That said     I think there may be a few issues here   The Dockerfile is used to create an image   You need to execute the build step   At a minimum  from the directory that contains the Dockerfile you would do something like    docker build     The Dockerfile describes the image to create   I don t know much about mysql  I am a postgres fanboy   but  I did a search around the interwebs for  how do i initialize a mysql docker container    First I created a new directory to work in  I called it mdir  then I created a files directory which I deposited a epcis schema sql file which creates a database and a single table   create database test  use test   CREATE TABLE testtab   id INTEGER AUTO INCREMENT  name TEXT  PRIMARY KEY  id    COMMENT  this is my test table     Then I created a script called init db in the files directory      bin bash    Initialize MySQL database    ADD this file into the container via Dockerfile    Assuming you specify a VOLUME    var lib mysql   or  -v  var lib mysql  on the  docker run  command      Once built  do e g   docker run your image  path to docker-mysql-initialize sh    Again  make sure MySQL is persisting data outside the container for this to have any effect   set -e set -x  mysql install db    Start the MySQL daemon in the background   usr sbin mysqld  amp  mysql pid     until mysqladmin ping  gt  dev null 2 gt  amp 1  do   echo -n      sleep 0 2 done    Permit root login without password from outside container  mysql -e  GRANT ALL ON     TO root     IDENTIFIED BY    WITH GRANT OPTION     create the default database from the ADDed file  mysql  lt   tmp epcis schema sql    Tell the MySQL daemon to shutdown  mysqladmin shutdown    Wait for the MySQL daemon to exit  wait  mysql pid    create a tar file with the database as it currently exists tar czvf default mysql tar gz  var lib mysql    the tarfile contains the initialized state of the database    when the container is started  if the database is empty   var lib mysql    then it is unpacked from default mysql tar gz from   the ENTRYPOINT  tmp run db script    most of this script was lifted from here  https   gist github com pda 9697520   Here is the files run db script I created     start db  set -e set -x    first  if the  var lib mysql directory is empty  unpack it from our predefined db      ls -A  var lib mysql      amp  amp  echo  Running with existing database in  var lib mysql       echo  Populate initial db   tar xpzvf default mysql tar gz     usr sbin mysqld   Finally  the Dockerfile to bind them all   FROM mysql MAINTAINER   me   lt email gt     Copy the database schema to the  data directory ADD files run db files init db files epcis schema sql  tmp     init db will create the default   database from epcis schema sql  then   stop mysqld  and finally copy the  var lib mysql directory   to default mysql db tar gz RUN  tmp init db    run db starts mysqld  but first it checks   to see if the  var lib mysql directory is empty  if   it is it is seeded with default mysql db tar gz before   the mysql is fired up  ENTRYPOINT   tmp run db    So  I cd ed to my mdir directory  which has the Dockerfile along with the files directory   I then run the command   docker build --no-cache     You should see output like this   Sending build context to Docker daemon 7 168 kB Sending build context to Docker daemon  Step 0   FROM mysql  --- gt  461d07d927e6 Step 1   MAINTAINER  me   lt email gt   --- gt  Running in 963e8de55299  --- gt  2fd67c825c34 Removing intermediate container 963e8de55299 Step 2   ADD files run db files init db files epcis schema sql  tmp   --- gt  81871189374b Removing intermediate container 3221afd8695a Step 3   RUN  tmp init db  --- gt  Running in 8dbdf74b2a79   mysql install db 2015-03-19 16 40 39 12  Note  InnoDB  Using atomics to ref count buffer pool pages      var lib mysql ib logfile0  --- gt  885ec2f1a7d5 Removing intermediate container 8dbdf74b2a79 Step 4   ENTRYPOINT   tmp run db   --- gt  Running in 717ed52ba665  --- gt  7f6d5215fe8d Removing intermediate container 717ed52ba665 Successfully built 7f6d5215fe8d   You now have an image  7f6d5215fe8d    I could run this image   docker run -d 7f6d5215fe8d   and the image starts  I see an instance string   4b377ac7397ff5880bc9218abe6d7eadd49505d50efb5063d6fab796ee157bd3   I could then  stop  it  and restart it   docker stop 4b377 docker start 4b377   If you look at the logs  the first line will contain   docker logs 4b377  Populate initial db var lib mysql        Then  at the end of the logs   Running with existing database in  var lib mysql   These are the messages from the  tmp run db script  the first one indicates that the database was unpacked from the saved  initial  version  the second one indicates that the database was already there  so the existing copy was used   Here is a ls -lR of the directory structure I describe above   Note that the init db and run db are scripts with the execute bit set   gregs-air   gfausak  ls -Rl mdir total 8 -rw-r--r--  1 gfausak  wheel  534 Mar 19 11 13 Dockerfile drwxr-xr-x  5 gfausak  staff  170 Mar 19 11 24 files  mdir files  total 24 -rw-r--r--  1 gfausak  staff   126 Mar 19 11 14 epcis schema sql -rwxr-xr-x  1 gfausak  staff  1226 Mar 19 11 16 init db -rwxr-xr-x  1 gfausak  staff   284 Mar 19 11 23 run db

User · Answer

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

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

The answer is

Step1

Step2

Step3

Examples related to mysql

Examples related to docker

Examples related to database-schema

Tags