How to Create Docker Image with MySQL Database

How to Create Docker Image with MySQL Database

In this tutorial, you will learn how to create Docker images and using bind mounts to customize 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', '[email protected]');

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
[email protected]:/# 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         | [email protected] |
+------------+-----------+------------+-------------------+
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!

WordPress in Docker. Part 1: Dockerization

WordPress in Docker. Part 1: Dockerization

This entry-level guide will tell you why and how to Dockerize your WordPress projects.

This entry-level guide will tell you why and how to Dockerize your WordPress projects.

Introduction Docker With Spring Boot and MySQL

Introduction Docker With Spring Boot and MySQL

Introduction Docker With Spring Boot and MySQL - This article explains the basic concepts of Docker and includes a tutorial that explains how to setup MySQL.

Originally published by Sanjoy Kumer Deb at dzone.com

Docker is a technology where developers or DevOps teams can build, deploy, and manage applications by using containers. Docker is an open source software so that everyone can run this on their own operating system, which should support virtualization and Docker for Mac/Windows/Linux.

Docker also ships the ready images from one computer to another. Docker containers are sets of processes that are isolated from the rest of the processes in the host OS. Docker shares the kernel of the host operating system. On the other hand, VM is a technology that depends on the guest OS, which is completely separated from the host OS. Virtual machines communicate with the host OS through hypervisor. VM requires many hardware resources and processes. It's not a container-based technology.

Docker moves up the abstractions of resources from hardware level to the operating system level. That's why application portability and infrastructure separation is easier.

Container-based technology takes lower resources of the host system. Most of the time, container-based technology uses the kernel of the host machine. The main goal of this article is to implement Docker with a Spring Boot application and MySQL.

You might also enjoy: Spring Boot: Run and Build in Docker

Basic Concepts

Docker Engine works as a client-server architecture. Docker daemon works as a server, which is the core part of Docker and is run on the host operating system. This service exposes some rest APIs that can be used by the client. A command line interface (CLI) client uses the services provided by Docker daemon with Docker command.

Important Terms

Image: Image is the executable application package file inside Docker. It contains everything needed to run an application, including libraries, config files, runtime, etc. It's a snapshot of a container.

Container: An instance of an image is called a container. When an image is executed and takes place in memory, then the instance of this image is called a container. It executes in a completely isolated environment.

In OOP terms, Image is a class, and a container is an instance of this class — a runtime object.

**Registry: **A registry is a storage and content delivery system that stores Docker images. Docker Hub is a popular Docker registry. We can store different versions of images with different tag numbers to Docker Hub.

Dockerfile: It's a text file that contains all the commands to assemble an image.

Dockerfile  > (Build) >  Image  > (Run) >  Container

Installation

In this article, we will use Docker Hub as a Docker registry. Then, we will follow the instructions to set up a desktop Docker application. I am using Mac OS as my host machine. After installation, we can see that the Docker app is running. Then, I have to sign in using my Docker Hub credentials.

After Docker Desktop Install and Login

Now, we can open the terminal to check some Docker commands.

docker --version command should return the Docker version. In my case, this command returns 

Docker version 19.03.2, build 6a30dfc

docker info command should return detailed information of the installed Docker machine of the host machine. Initially, you have zero images and containers.

docker pull hello-world This command should pull a hello-world image from the Docker registry. Now again, if we run the Docker info command, we can see that the image count is 1. But the container count is still zero.

docker run hello-world This command should create an instance of the hello-world image. It will return a long message: 

Hello from Docker!

This message shows that your installation appears to be working correctly.

If we run the Docker info command, we can see that the image count is 1 and the container count is also one. So the Docker installation is complete.

Setup MySQL

Now we will create and run an image of the MySQL database. From our terminal, we will run the below command. Here, -d in this command indicates that the Docker command will run in detached mode.

docker run -d -p 6033:3306 --name=docker-mysql --env="MYSQL_ROOT_PASSWORD=root" --env="MYSQL_PASSWORD=root" --env="MYSQL_DATABASE=book_manager" mysql

Hopefully, the MySQL image is pulled and running as a container. To check this, we can run

docker image ls and docker container ls commands. In my case, the responses of these commands are:

mysql latest b8fd9553f1f0 3 days ago 445MB 
hello-world latest fce289e99eb9 8 months ago 1.84kB
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 
5db66654ba6a mysql "docker-entrypoint.s…" 13 minutes ago Up 13 minutes 33060/tcp, 0.0.0.0:6033->3306/tcp docker-mysql

Now we can check by logging in to MySQL.

 docker exec -it docker-mysql bash;

It will take us inside the docker-mysql container. Then we will log in to MySQL using mysql -uroot -p  using password root. Then, we will run the show databases; command to see if the database setup is complete or not. 

In my case, it returns the below result:

+--------------------+ | Database | +--------------------+ | book_manager | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)

So we can say that the book_manager database was created inside the docker container. We can externally use this database from our host machine by using port 6033. Now we have to import the database script to the Docker MySQL database. The SQL script is available here. Run the following command to import this script to docker-mysql.

docker exec -i docker-mysql mysql -uroot -proot book_manager <book_manager.sql 

Hopefully, the book_manager script executed successfully. You can confirm by executing the following command.

$ docker exec -it docker-mysql bash; 
[email protected]:/# mysql -uroot -p 
Enter password: 
mysql> show databases; 
+--------------------+ | Database | +--------------------+ | book_manager | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use book_manager Database changed 
mysql> show tables; 
+------------------------+ | Tables_in_book_manager | +------------------------+ | author | | book | | book_author | | book_publisher | | book_tag | | bookshelf | | publisher | | tag | +------------------------+ 8 rows in set (0.01 sec) mysql> 
Application Clone and Build Project

I have already pushed my code to my GitHub repository. Anyone can clone the codebase from here. I think your host machine has Gradle setup. So now we run the gradle build commandto build the project. So the executable jar file is created at the build/jar directory of your cloned project.

Now open the Dockerfile. We can see that the file contains the following commands:

FROM java:8
VOLUME /tmp 
EXPOSE 10222 
ADD /build/libs/book-manager-1.0-SNAPSHOT.jar book-manager-1.0-SNAPSHOT.jar 
ENTRYPOINT ["java","-jar","book-manager-1.0-SNAPSHOT.jar"]

This file contains sequential commands to execute in docker. It will create an image of java 8. and also it will copy jar file from host machine to docker image and then run command which is given at entrypoint arguments. Now we will build a docker image by using this Dockerfile.

docker build -f Dockerfile -t book_manager_app .

This command will create a Docker image named book_manager_app to the Docker machine. Here, the -f  command indicates the Docker file name. Now we will run this image as a container.

docker run -t --link docker-mysql:mysql -p 10222:10222 book_manager_app 

The --link command will allow the book_manager_app container to use the port of MySQL container and -t  stands for--tty, which will allocate a pseudo-terminal.

After running this command, we will hit http://localhost:10222/book from our host machine browser, and it will return a list of books.

That's all for introductory knowledge of Docker with a simple Spring Boot implementation and MySQL database.

Happy coding!

Further Reading

How To Set Up Laravel, Nginx, and MySQL with Docker Compose

Docker Basics: Docker Compose

Tutorial Laravel 6 with Docker and Docker-Compose

List all containers in Docker(Docker command)

List all containers in Docker(Docker command)

We can get a list of all containers in docker using `docker container list` or `docker ps` commands.

We can get a list of all containers in docker using docker container list or docker ps commands.

List Docker Containers

To list down docker containers we can use below two commands

  • docker container list
  • docker ps

docker container ls command introduced in docker 1.13 version. In older versions we have to use docker ps command.

List all Containers in docker, using docker ls command

The below command returns a list of all containers in docker.

docker container list -all

or

docker container ls -all

List all containers in docker, using docker ps command

In older version of docker we can use docker ps command to list all containers in docker.

$ docker ps -all

or

$ docker ps -a

List all Running docker containers

The default docker container ls command shows all running docker containers.

$ docker container list

or

$ docker container ls

or

To get list of all running docker containers use the below command

$ docker ps

List all stopped docker containers command

To get list of all stopped containers in docker use the below commands

$ docker container list -f "status=exited"

or

$ docker container ls -f "status=exited"

or you can use docker ps command

$ docker ps -f "status=exited"

List all latest created docker containers

To list out all latest created containers in docker use the below command.

$ docker container list --latest

Show n last created docker containers

To display n last created containers in docker use the below command.

$ docker container list --last=n