Creating a Custom MySQL Docker Image with initialization Scripts

Containerisation has many advantages.  One advantage among these is getting up and running quickly with a production-ready data source, which has predefined data in it. This way we can share and use the application data state in local development environments easily.

In this article, we are going to create such a customized data source container, in order to speed up our development activities. Even though MySQL is used as the database system in this blog post, similar concepts are applicable to other database systems as well.

The steps that we’re going to follow to reach our goal are as follows:

1. Creating a Dockerfile for our base container
2. Implementing the scripts for database/table creation and sample data insertion. 
3. Building our customized MySQL docker image and verifying the container state

1. Creating the Dockerfile for a customized Mysql Docker Image

Initially,  create the directory structure to store configuration files for your MySQL docker container. 

$mkdir mysql-docker
$cd mysql-docker
$mkdir init-scripts

Now we can start writing our custom MySQL docker image file.  Create a Dockerfile under the root folder “mysql-docker”. We are going to use the official Mysql Docker Image as our base image. Add the following content to the file that you’ve created:

FROM mysql:5.7
LABEL description="My Custom Mysql Docker Image"

# Add a database
ENV MYSQL_DATABASE CARDS

#Check out docker entry point for further configuration :
# https://github.com/docker-library/mysql
COPY ./init-scripts/ /docker-entrypoint-initdb.d/

As mentioned earlier, we have used the official MySQL Docker image for MySQL version 5.7 as our parent image. We also added some metadata information for our image. On the line starting with ENV MYSQL_DATABASE CONTACTS, we informed the base MySQL container that we are going to create and use the “CONTACTS” database in this customized docker image. MYSQL_DATABASE is an environment variable provided by the parent image. This step is not a mandatory step. We could have also used our initialization scripts to achieve the same goal, yet for demonstration purposes, it’s easier to implement it like this.

 The COPY ./scripts/ /docker-entrypoint-initdb.d/ instruction on the last line of our docker file will be used to copy the initialization scripts, which we are going to create in the next step, to the filesystem of the container.
The important point here is the destination folder “/docker-entrypointpinitdb.d/”.  Mysql base image defines this folder as the startup scripts folder and executes every SQL file under this folder while the container is starting up. It’s not clearly documented in the official container documentation page (at least at the time of writing); however, you can see that this is the case by checking out docker entry point script implementation in the official MySQL Docker image repository.

We’re done with creating our image file description.  We can now add our initialisation scripts.

2. Implementing the scripts for table creation and sample data insertion

Add the following SQL scripts under the folder “init-scripts”, which we created before:

-- create_tables.sql
CREATE TABLE card (
  id bigint(20) NOT NULL,
  back varchar(255) NOT NULL,
  front varchar(255) NOT NULL,
  hint varchar(255) DEFAULT NULL,
   PRIMARY KEY (id)
  );

-- insert_data.sql
INSERT INTO card (id, front, back, hint) values (1, 'Front 1', 'Back 1', 'Hint 1');
INSERT INTO card (id, front, back, hint) values (2, 'Front 2', 'Back 2', 'Hint 2');
INSERT INTO card (id, front, back, hint) values (3, 'Front 3', 'Back 3', 'Hint 3');
INSERT INTO card (id, front, back, hint) values (4, 'Front 4', 'Back 4', 'Hint 4');
INSERT INTO card (id, front, back, hint) values (5, 'Front 5', 'Back 5', 'Hint 5');

3. Building and running the container

The Dockerfile and database initialization scripts are ready for our image. It’s now time to build and run our container.   

In order to build the image run the following command: 

$ docker build -t mysql-docker .
Sending build context to Docker daemon   7.68kB
Step 1/4 : FROM mysql:8.0
 ---> 98455b9624a9
Step 2/4 : LABEL description="My Custom Mysql Docker Image"
 ---> Using cache
 ---> 6bb1abb1370e
Step 3/4 : ENV MYSQL_DATABASE CARDS
 ---> Using cache
 ---> fda7d70507cb
Step 4/4 : COPY ./init-scripts/ /docker-entrypoint-initdb.d/
 ---> Using cache
 ---> a5b2181dd613
Successfully built a5b2181dd613
Successfully tagged mysql-docker:latest

If the image is built successfully, you can run your container with the following command:

$ docker run -d -p 63306:3306 --name mysql-docker \
-e MYSQL_ROOT_PASSWORD=secret mysql-docker

Since we are inside our container right now, we can execute database commands and verify that our initial state is as expected:

root@ed27a9ae3b98:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use CARDS
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_CARDS |
+-----------------+
| card            |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from card;
+----+--------+---------+--------+
| id | back   | front   | hint   |
+----+--------+---------+--------+
|  1 | Back 1 | Front 1 | Hint 1 |
|  2 | Back 2 | Front 2 | Hint 2 |
|  3 | Back 3 | Front 3 | Hint 3 |
|  4 | Back 4 | Front 4 | Hint 4 |
|  5 | Back 5 | Front 5 | Hint 5 |
+----+--------+---------+--------+
5 rows in set (0.00 sec)ur initial state is as expected:

As you can see, we have our initial database state in our container now. As a next step, we can push this image to our docker hub to share it with our development team. This way, every member of our team can run this image locally and play around with the data without affecting other developers.