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

Question

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

My current docker file:

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 

To create the container, I follow 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 state you can see that the CMD was not successfully executed, in fact only the mysql command is executed.

In any case, is there a way to initialize the database using a schema, or do I need to perform these operations manually?

Answer 1

I apologize for this long answer, but you have a little way to get to where you want. I will say that, as a rule, you would not put the storage for the database in the same container as the database itself, you would either mount the host volume so that the data would be stored on the docker host, or perhaps the container could be used to hold data (/ var / lib / mysql). Also, I am new to mysql, so this may not be very efficient. However...

I think there may be several problems. A Docker file is used to create an image. You need to complete the build step. At a minimum, from the directory containing the Dockerfile, you will do something like:

 docker build . 

Dockerfile describes the created image. I am not very good at mysql (I'm a fan of posts), but I did a search on the intervals for "how to initialize the mysql docker container". First I created a new directory to work, I called it mdir, and then created a file directory in which I wrote the epcis_schema.sql file, which creates a database and one 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 file 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 eg `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 taken from here: https://gist.github.com/pda/9697520 )

Here are the / run _db script files 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, a Dockerfile to bind them:

 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 connected to my mdir directory (which has a Dockerfile along with a file directory). Then I ran the command:

 docker build --no-cache . 

You should see the output as follows:

 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 of '7f6d5215fe8d'. I could run this image:

 docker run -d 7f6d5215fe8d 

and the image starts, I see the instance line:

 4b377ac7397ff5880bc9218abe6d7eadd49505d50efb5063d6fab796ee157bd3 

I could “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 messages from the / tmp / run _db script, the first indicates that the database was unpacked from the saved (initial) version, the second indicates that the database already exists, so the existing copy was used.

Here is the ls -lR directory structure described above. Note that init_db and run_db are scripts with the execution 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

I had the same problem when I wanted to initialize my MySQL Docker instance schema, but I ran into difficulties getting this work after doing some actions on Google and others. This is 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 the 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 in ".sql" into 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) Launch an instance of Docker MySQL.

 docker-compose build docker-compose up 

Thanks to setting up MySQL and importing the dump into the Dockerfile to get to know me at docker -entrypoint.sh and the fact that it runs both SQL scripts and shells!

Answer 3

I tried Greg to respond with no success, I must have done something wrong, since my database did not have data after all the steps: I used the latest MariaDB image just in case.

Then I decided to read the entry point for the official MariaDB image and used this to create a simple file to create docker:

 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 can save my data and create a database with my own schema!

Answer 4

Another method based on merging server responses here earlier:

File to create a docker 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 the shared folder on the host

And in the folder /home/user/db/mysql/init .. 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 place more than one sql file in docker-entrypoint-initdb.d , they are executed in alphabetical order

Answer 5

After August 4, 2015, if you are using the official Docker mysql image, you can simply add the ADD / COPY file to the / docker-entrypoint-initdb.d / directory and start it with container initialization. See Github: https://github.com/docker-library/mysql/commit/14f165596ea8808dfeb2131f092aabe61c967225 if you want to implement it on other container images.

Answer 6

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 run the docker logs to view the generated password information.

 docker logs #<CONTAINER_ID> 

Answer 7

Another easy way is to 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 in. /database/install_db.sql. Each time you build your container, install_db.sql will execute.

Answer 8

For those who do not want to create an entry point script like me, you can run mysqld during build and then execute mysql commands in your Dockerfile as follows:

 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 point here is sending mysqld_safe to the background with a single & .

Answer 9

To work a bit on this, take a look at the Dockerfile using named volumes (db-data). It is very important to declare a plus in the final part, where I mentioned that the volume is [external]

Everything is so cool!

 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 10

Below is the Docker file that I used to install xampp, create a MariaDB schema and pre-populated with the information used on the 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