How to Build a Docker Image with MySQL Database
In this tutorial, you will learn how to create Docker images and using bind mounts to customize MySQL database
Many developers use Docker to spin up a local database. This makes it easy to test their code and write data without installing and configuring a lot of tools. In this piece, I’ll explain how you can customize your Docker database. While I’ll be explaining the steps for MySQL, it’s the same for PostgreSQL!
Follow along and let’s create a custom MySQL which contains your desired tables and data.
Creating SQL scripts
We will create SQL scripts which contain the SQL statements we want to execute on our database. Create a directory in which we will work. Also, create a subdirectory where we will store our .sql scripts.
$ mkdir -p ~/my-mysql/sql-scripts
$ cd ~/my-mysql/sql-scripts
I want to customize my database with a table called employees. The table needs to contain one row with an employee (first name, last name, department, and email).
Write a CreateTable.sql. This file contains the SQL statement to create a table called employees. We will add four columns to our table
CREATE TABLE employees (
first_name varchar(25),
last_name varchar(25),
department varchar(15),
email varchar(50)
);
Write a InsertData.sql. This file contains our statement to insert data in the table ‘employees’.
INSERT INTO employees (first_name, last_name, department, email)
VALUES ('Lorenz', 'Vanthillo', 'IT', 'lvthillo@mail.com');
Execute the tree command to verify that your two scripts exist and are saved in the right directory.
$ cd ~/my-mysql
$ tree
└── sql-scripts
├── CreateTable.sql
└── InsertData.sql
Creating a Docker Image for Your Customized MySQL Database
Now that the scripts are ready, we can write a Dockerfile to create our own Docker image (based on the official image of MySQL).
$ cd ~/my-mysql/
$ vi Dockerfile
Content of Dockerfile:
# Derived from official mysql image (our base image)
FROM mysql
# Add a database
ENV MYSQL_DATABASE company
# Add the content of the sql-scripts/ directory to your image
# All scripts in docker-entrypoint-initdb.d/ are automatically
# executed during container startup
COPY ./sql-scripts/ /docker-entrypoint-initdb.d/
Create your Docker image:
$ cd ~/my-mysql/
$ docker build -t my-mysql .
Sending build context to Docker daemon 4.608kB
Step 1/2 : FROM mysql
latest: Pulling from library/mysql
Digest: sha256:691c55aabb3c4e3b89b953dd2f022f7ea845e5443954767d321d5f5fa394e28c
Status: Downloaded newer image for mysql:latest
---> 5195076672a7
Step 2/2 : ADD sql-scripts/ /docker-entrypoint-initdb.d/
---> 25065c3d93c0
Successfully built 25065c3d93c0
Successfully tagged my-mysql:latest
And start your MySQL container from the image:
$ docker run -d -p 3306:3306 --name my-mysql \
-e MYSQL_ROOT_PASSWORD=supersecret my-mysql
Now we can verify. We will exec inside the container:
$ docker exec -it my-mysql bash
root@c86ff80d7524:/# mysql -uroot -p
Enter password: (supersecret)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use company;
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_company |
+-------------------+
| employees |
+-------------------+
1 row in set (0.00 sec)
mysql> show columns from employees;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(25) | YES | | NULL | |
| last_name | varchar(25) | YES | | NULL | |
| department | varchar(15) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from employees;
+------------+-----------+------------+-------------------+
| first_name | last_name | department | email |
+------------+-----------+------------+-------------------+
| Lorenz | Vanthillo | IT | lvthillo@mail.com |
+------------+-----------+------------+-------------------+
1 row in set (0.01 sec)
It works! We have our customized MySQL database Docker image! This is a great solution for local development between multiple developers. By sharing the Docker image, every developer can use the database by just starting a container from the image.
It’s important to note, however, that this is not always the best solution:
- If you insert a lot of data your image size will grow significantly
- Need to build a new image when you want to update the data
That’s why there is another way to customize your Docker MySQL.
Use Bind Mounts to Customize Your MySQL Database in Docker
In this last section, we will simply mount the scripts inside the official MySQL Docker container.
$ docker run -d -p 3306:3306 --name my-mysql \
-v ~/my-mysql/sql-scripts:/docker-entrypoint-initdb.d/ \
-e MYSQL_ROOT_PASSWORD=supersecret \
-e MYSQL_DATABASE=company \
mysql
And we can verify again! Use the same steps as we did before: exec inside the container and check if the table and data exist!
This method is more flexible but it will be a little bit harder to distribute among the developers. They all need to store the scripts in a certain directory on their local machine and they need to point to that directory when they execute the docker run command.
Conclusion
In this piece, I’ve described how you can create your custom MySQL database in Docker. We’ve explored the two methods:
- Custom MySQL Docker image
- Bind mounts to mount scripts inside a container
We also discussed some of the advantages and disadvantages of each method.
We hope you enjoyed it!
#docker #mysql #database #postgresql
