17

I want to create a docker image on top of the mysql one that already contains the necessary scheme for my app.

I tried adding lines to the Dockerfile that will import my scheme as a sql file. I did so as such (my Dockerfile):

FROM mysql

ENV MYSQL_ROOT_PASSWORD="bagabu"
ENV MYSQL_DATABASE="imhere"

ADD imhere.sql /tmp/imhere.sql

RUN "mysql -u root --password="bagabu" imhere < /tmp/imhere.sql"

To my understanding, that didn't work because the mysql docker image does not contain a mysql client (best practices state "don't add things just because they will be nice to have") (am I wrong about this?)

what might be a good way to do this? I have had a few things in mind, but they all seem like messy workarounds.

  1. install the mysql client, do what I have to do with it, then remove/purge it.
  2. copy the mysql client binary to the image, do what I have to do, then remove it.
  3. Create the schema in another sql server and copy the db file themselves directly (this seems very messy and sounds to me like a contaminated pool of problems)

Any suggestions? Hopefully in a way that will be easy to maintain later and maybe conform with the best practices as well?

14

You should put your init script in a directory mounted as /docker-entrypoint-initdb.d - see "Initializing a fresh instance" section in the MySQL Docker image docs.

  • 2
    That works for bringing up a fresh container and loading it with my schema. Which is a nice way around it, but what if I'm looking for a way to create my own docker image that already has the schema preinstalled? – Tom Klino Aug 15 '16 at 6:13
  • Actually, nevermind :-) I thought the /docker-entrypoint-initdb.d was in the host but it's actually in the container. I changed the ADD command to copy to that directory and removed the RUN command. Docker built the image successfully and I tested it and it works. – Tom Klino Aug 15 '16 at 6:28
14

I had to do this for tests purposes.

Here's how i did by leveraging the actual MySQL/MariaDB images on dockerhub and the multi-stage build:

FROM mariadb:latest as builder

# That file does the DB initialization but also runs mysql daemon, by removing the last line it will only init
RUN ["sed", "-i", "s/exec \"$@\"/echo \"not running $@\"/", "/usr/local/bin/docker-entrypoint.sh"]

# needed for intialization
ENV MYSQL_ROOT_PASSWORD=root

COPY setup.sql /docker-entrypoint-initdb.d/

# Need to change the datadir to something else that /var/lib/mysql because the parent docker file defines it as a volume.
# https://docs.docker.com/engine/reference/builder/#volume :
#       Changing the volume from within the Dockerfile: If any build steps change the data within the volume after
#       it has been declared, those changes will be discarded.
RUN ["/usr/local/bin/docker-entrypoint.sh", "mysqld", "--datadir", "/initialized-db", "--aria-log-dir-path", "/initialized-db"]

FROM mariadb:latest

COPY --from=builder /initialized-db /var/lib/mysql

Full working example here : https://github.com/lindycoder/prepopulated-mysql-container-example

  • 2
    best answer I've found on the whole internet, mine was for postgres, so it was a bit more difficult, but finally got it working. And it works perfectly! – snowe2010 Jul 27 '18 at 1:16
  • Older versions of mysql (5.7.9) don't have /usr/local/bin/docker-entrypoint.sh symlinked to /entrypoint.sh, instead just have the entrypoint.sh in /. Changing /usr/local/bin/docker-entrypoint.sh to /entrypoint.sh works and should presumably work for the "modern" releases too. – Marvin Jul 31 at 14:31
2

Credits to @Martin Roy

Made minor changes to work for mysql...

Content Dockerfile

FROM mysql:latest as builder

# That file does the DB initialization but also runs mysql daemon, by removing the last line it will only init
RUN ["sed", "-i", "s/exec \"$@\"/echo \"not running $@\"/", "/usr/local/bin/docker-entrypoint.sh"]

# needed for intialization
ENV MYSQL_ROOT_PASSWORD=root

COPY setup.sql /docker-entrypoint-initdb.d/

# Need to change the datadir to something else that /var/lib/mysql because the parent docker file defines it as a volume.
# https://docs.docker.com/engine/reference/builder/#volume :
#       Changing the volume from within the Dockerfile: If any build steps change the data within the volume after
#       it has been declared, those changes will be discarded.
RUN ["/usr/local/bin/docker-entrypoint.sh", "mysqld", "--datadir", "/initialized-db"]

FROM mysql:latest

COPY --from=builder /initialized-db /var/lib/mysql

Content setup.sql

CREATE DATABASE myexample;

USE myexample;

CREATE TABLE mytable (myfield VARCHAR(20));

INSERT INTO mytable VALUES ('Hello'), ('Dolly');

Full working example here : https://github.com/iamdvr/prepopulated-mysql-container-example

-1

Management software like Ansible can help you to automate a mysql import easy without need to install and reinstall a client. Ansible has great built-in features to manage docker images and containers and mysql databases.

  • That's interesting - I used Ansible but haven't used it for Docker images. Can you extend your answer with some examples, Patrick? – Greg Dubicki Aug 15 '16 at 12:02
  • Ansible has a great docs page, you can find the Ansible module on this url: docs.ansible.com/ansible/docker_module.html On each page in the module chapter of this guide are some examples. – Patrick Aug 15 '16 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.