Creating a docker mysql container with a prepared database scheme

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

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.

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

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

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/

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

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.