31

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?

1
27

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

3
  • 3
    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 '19 at 14:31
  • @snowe2010 I was wondering if you would share your postgres solution. Thanks in advance!
    – stevo
    May 15 '20 at 16:46
24

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
  • 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
7

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

2

This works and it's follows the interface with the mariadb entry point script. Files with settings, tables, data and users are copied to folders. When the container starts the entry point script finds the files and creates an new database.

FROM mariadb:10.5.5

ENV MYSQL_ROOT_PASSWORD=blablablablablabla

COPY settings/my.cnf /etc/mysql/conf.d
# comment out !includedir /etc/mysql/conf.d/ to stop recursion
RUN sed -i 's/!includedir \/etc\/mysql\/conf\.d\//#!includedir \/etc\/mysql\/conf\.d\//' /etc/mysql/conf.d/my.cnf
COPY db_scripts/db_setup.sql /docker-entrypoint-initdb.d/
COPY db_scripts/db_data.sql.template /docker-entrypoint-initdb.d/db_template.sql
COPY db_scripts/db_users.sql /docker-entrypoint-initdb.d/

# Note the port 3306 can be exposed
2
  • Hello Jonas, and welcome to serverfault. Your answer looks like it might have something that would help, but you mention settings and scripts that the original poster and other readers might not have. Could you provide some explanation about those? Oct 3 '20 at 12:39
  • I have updated my explanation. The main goal is to work with the entry point script, not hacking it. Oct 4 '20 at 23:14
0

Here is my docker file working absolutely fine. /sql-scripts/ will contain your custom sql file (containing your prepared db) which is executed once the container runs. You may want to look at volume mounting though.

FROM mysql:5.6
COPY ./sql-scripts/ /docker-entrypoint-initdb.d/
0

@Venkateswara Rao and @Martin Roy 's answer work great if you don't care if changes to dockerized DB are still put in a Volume. In my case, I wanted a Docker Image prepopulated with data, but in a project where we have frequent Database Migrations. Thus, i wanted to occasionally make a DB Container, run migrations, then somehow push those changes back up so that the next user to use the DB image would already have migrations applied. Since the DB files live in a Volume, they don't get rolled into an image with docker commmit my-migrated-db-container my-new-db-image.

My solution was to use the official MySQL (5.6 in my. case) Dockerfile (and entrypoint file) to recreate their work after removing the VOLUME /var/lib/mysql line. From the resulting image, I then used Venkateswara's files to make a pre-loaded DB image where even all future DB data is stored in the container itself (not on a Volume).

NOTE: The linked Dockerfile file is old refers to obsolete GPG keyservers (ie keys.openpgp.org). You can replace these server names with, say, keyserver.ubuntu.com.

-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.

2
  • That's interesting - I used Ansible but haven't used it for Docker images. Can you extend your answer with some examples, Patrick? 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.