How to create a initialised MySQL Docker image?

Question

Using Docker image mysql:5.7.21 I want to create a new image based of it that will already have its database initialised. (I want to use this for my acceptation environment).

I know about the /docker-entrypoint-initdb.d and I could do the fallowing:

FROM mysql:5.7.21

COPY ./init /docker-entrypoint-initdb.d

But the problem is that it will only copy the tar/sql files, but it will not execute until the first time it's run. Another disadvantage is also the image size. If you a copying for example 200MB of data inside the container then those files will stay in the image (as a layer).

So I was wondering if there is a combination I haven't thought of with multi-stage or the new --squash flag (that would enable, adding the files, executing the files, removing the files).

In my search for this problem I also found about the --datadir flag. Not sure how this could help.

edit:

So far I have the fallowing:

FROM mysql:5.7.21 as builder

ENV MYSQL_ALLOW_EMPTY_PASSWORD yes
ENV MYSQL_DATABASE example

COPY ./init /docker-entrypoint-initdb.d

RUN head -n-2 < /usr/local/bin/docker-entrypoint.sh > /usr/local/bin/docker-entrypoint.sh \
    && docker-entrypoint.sh mysqld \
    && /etc/init.d/mysql start \
    && mysql -uroot -e 'FLUSH TABLES;' \
    && mysql -uroot -e 'show tables;' mysql \
    && mysql -uroot -e 'show tables;' example \
    && /etc/init.d/mysql stop \
    # This shows the files!
    && ls -la /var/lib/mysql

# This shows no files?!!
RUN ls -la /var/lib/mysql

FROM mysql:5.7.21

ENV MYSQL_ALLOW_EMPTY_PASSWORD yes
ENV MYSQL_DATABASE example

COPY --from=builder /var/lib/mysql /var/lib/mysql
RUN ls -la 

RUN ls -la /var/lib/mysql \
    && /etc/init.d/mysql start \
    && mysql -uroot -e 'show tables;' mysql \
    && mysql -uroot -e 'show tables;' example

See the above inline comments... something really strange is happening? So in the same RUN when I perform an ls -la on /var/lib/mysql I can see the files. But in a new layer (new RUN) it's empty :S

Do you want the container to store the data in the container itself? It is usual to use a volume for the data. — halfer
– halfer, Commented Apr 13, 2018 at 10:48
Yes, I want to store the data in the container it self. (In this case it's just a 10mb db, but it might increase in future). It going to be deployed to a Docker Swarm env and I want to make that as easy as possible (no setting up of persistent volumes). — Ilyes512
– Ilyes512, Commented Apr 13, 2018 at 11:02

Ilyes512 · Accepted Answer · 2018-04-13 15:17:14Z

2

So I found the solution!!! My problem lied in the fact that /var/lib/mysql is an volume: https://github.com/docker-library/mysql/blob/ad625c64a06e16683e997e5a0147508d115f4989/5.7/Dockerfile#L71

A working multistage Dockerfile:

FROM mysql:5.7.21 as builder

ENV MYSQL_ALLOW_EMPTY_PASSWORD yes
ENV MYSQL_DATABASE example

COPY ./init /docker-entrypoint-initdb.d

RUN head -n-2 < /usr/local/bin/docker-entrypoint.sh > /usr/local/bin/docker-entrypoint.sh
RUN mkdir -p /var/lib/mysql_tmp
RUN docker-entrypoint.sh mysqld --datadir /var/lib/mysql_tmp

FROM mysql:5.7.21

ENV MYSQL_ALLOW_EMPTY_PASSWORD yes
ENV MYSQL_DATABASE example

COPY --from=builder /var/lib/mysql_tmp /var/lib/mysql_tmp

CMD ["mysqld", "--datadir", "/var/lib/mysql_tmp"]

So what we do here is make sure to remove the last 2 lines (could be changed to remove just the last line) because it contains exec "$@" which basically means exec msqld. That's the MySQL deamon and so it would get stuck there.

Further I changed the datadir from var/lib/mysql (which is a volume) to var/lib/mysql_tmp (for lack of a better name).

In the last stage I copy var/lib/mysql_tmp and make sure mysqld uses that dir.

Now I can bake (for me readonly) db's for my acceptation env.

answered Apr 13, 2018 at 15:17

Ilyes512

2,7875 gold badges26 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kapil Malhotra Over a year ago

I am looking to setup var/lib/mysql by doing following:- a) Run flyway migrate command b) run sql script as root user which would create another user and grant privileges c) Run another bootstrap.sql as the newly created user. And, then in next stage, very similar to your Dockerfile, use the "COPY --from" command to copy /var/lib/mysql. The problem is that I need a mysql daemon running to be able to do a), b) and c). And, if I run the mysqld daemon in first stage, the RUN command gets stuck

Jose Fernandez-Alba · Accepted Answer · 2018-04-13 11:10:26Z

0

If you check the Dockerfile for the mysql:5.7.21 image (https://hub.docker.com/r/library/mysql/), you will see that the entrypoint is set to ["docker-entrypoint.sh"], so this is the first command that is executed when the image is run.

If you want to execute it during image creation (thus, making image size huge, but ok) you just have to put this in your Dockerfile:

RUN docker-entrypoint.sh /bin/true

This entrypoint will execute the scripts found in /docker-entrypoint-initdb.d but, as you well noted, the scripts also take up space. In order to discard the files, you can use multistage build to just copy the raw database.

Check again the Dockerfile to see the only volume in the image is /var/lib/mysql. This will supposedly be the directory containing all the information on the database, and it should be what you copy. The result is something like this:

FROM mysql:5.7.21 as base

COPY ./init /docker-entrypoint-initdb.d
RUN docker-entrypoint.sh /bin/true

FROM mysql:5.7.21

COPY --from=base /var/lib/mysql /var/lib/mysql

All this said, I wouldn't recommend to create "initialized" images, as it is somewhat against the spirit of the use of containers, but that's another question ;)

edited Apr 13, 2018 at 11:10

answered Apr 13, 2018 at 11:03

Jose Fernandez-Alba

515 bronze badges

3 Comments

Ilyes512 Over a year ago

Have you tried this? Because I have before even asking this question... It will get stuck because of the last exec command. I even used tail to remove the last 2 lines, but the directory stayed empty somehow.

Jose Fernandez-Alba Over a year ago

You are right, you should add something to the docker-entrypoint.sh somthething like: RUN docker-entrypoint.sh /bin/true

Ilyes512 Over a year ago

That wont work either, because of if [ "$1" = 'mysqld' -a -z "$wantHelp" ]; then. It checks if the first argument is mysqld. And in this case it wont even work because you would get an error telling you that you need to set some env variables. Please don't create answers without testing it yourself.

Collectives™ on Stack Overflow

How to create a initialised MySQL Docker image?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related