database - How to create populated MySQL Docker Image on build time

Question

I would like to create a MySQL Docker image with data already populated.

I want to create 3 layers like this:

        |---------------------|---------------------|
Layer 3 | Customer 1 Database | Customer 2 Database |
        |---------------------|---------------------|
Layer 2 |   Database image with tables but no data  |
        |-------------------------------------------|
Layer 1 |                mysql:5.6.26               |
        |-------------------------------------------|

My question is now how to create a correct Dockerfile for layer 2 and 3? Where my empty_with_tables.sql file is loaded into layer 2 and customer1.sql and customer2.sql is loaded into two images in layer 3. I read something about putting SQL files into '/docker-entrypoint-initdb.d'. But this would result in the data being when the image is started for the first time. This not what I want. I want the data to be ready in the image (for example to be quickly available in testing).

I could start the mysql image, load the data from commandline and do a 'commit' but this is not reproducible, requiring doing that again when data in the SQL files are changed.

How can this be done?

Answer 1

I ran into the same problem this week. I've found a working solution without the need for --volumes-from

The problem that's already stated is that /var/lib/mysql is a volume, and since Docker is not going to support UNVOLUME in it's Dockerfile in the near future, you can't use this location for your database storage if you want to start off with an empty database by default. (https://github.com/docker/docker/issues/18287). That's why I overwrite etc/mysqld.my.cnf, giving mysql a new datadir.

Together with pwes' his answer, you can create a Dockerile like this:

FROM mysql:5.6

ENV MYSQL_DATABASE db
ENV MYSQL_ROOT_PASSWORD pass
COPY db.sql /docker-entrypoint-initdb.d/db.sql
COPY my.cnf /etc/mysql/my.cnf
RUN /entrypoint.sh mysqld & sleep 30 && killall mysqld
RUN rm /docker-entrypoint-initdb.d/db.sql

The only change there is in my.cnf is the location of the datadir:

.... 
[mysqld]
skip-host-cache
skip-name-resolve
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql2 <-- can be anything except /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
....