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 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 &
mysql_pid=$!

until mysqladmin ping >/dev/null 2>&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 < /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)" ] && 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) <email>

# 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
 ---> 461d07d927e6
Step 1 : MAINTAINER (me) <email>
 ---> Running in 963e8de55299
 ---> 2fd67c825c34
Removing intermediate container 963e8de55299
Step 2 : ADD files/run_db files/init_db files/epcis_schema.sql /tmp/
 ---> 81871189374b
Removing intermediate container 3221afd8695a
Step 3 : RUN /tmp/init_db
 ---> 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
 ---> 885ec2f1a7d5
Removing intermediate container 8dbdf74b2a79
Step 4 : ENTRYPOINT "/tmp/run_db"
 ---> Running in 717ed52ba665
 ---> 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

Answer 2

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

Answer 3

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 \
 && apt-get install wget -yq \
 && apt-get install nano \
 && wget https://www.apachefriends.org/xampp-files/7.1.11/xampp-linux-x64-7.1.11-0-installer.run \
 && mv xampp-linux-x64-7.1.11-0-installer.run /opt/ \
 && cd /opt/ \
 && chmod +x xampp-linux-x64-7.1.11-0-installer.run \
 && printf 'y\n\y\n\r\n\y\n\r\n' | ./xampp-linux-x64-7.1.11-0-installer.run \
 && cd /opt/lampp/bin \
 && /opt/lampp/lampp start \
 && sleep 5s \

 && ./mysql -uroot -e "CREATE DATABASE Ecommerce" \
 && ./mysql -uroot -D Ecommerce < /root/Ecommerce.sql \
 && cd / \
 && /opt/lampp/lampp reload \
 && mkdir opt/lampp/htdocs/Ecommerce

COPY /Ecommerce /opt/lampp/htdocs/Ecommerce

EXPOSE 80

Answer 4

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

Answer 5

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="/tmp/to_be_imported.mysql" tutum/mysql:5.5

Step3

Get above CONTAINER_ID and then execute command docker logs to see the generated password information.

docker logs #<CONTAINER_ID>

Answer 6

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.