1. Overview
Containers are the hottest topic of discussion in the IT Industry because of their many advantages. Organizations are adopting container-based solutions for their business at a remarkable rate. According to 451 Research, the application container market will grow four times larger in the coming years.
Today, we even have databases like MySQL, MongoDB, PostgreSQL, and many more in containerized form. However, this article will explore options for setting and running MySQL containers. To start, we’ll take the backup of our existing MySQL database. Next, we’ll build a container configuration in YAML form and run it using docker-compose, an open-source toolkit for bringing up a stack of application containers together.
Without any further ado, let’s get into the nitty-gritty details of it.
2. Building a MySQL Container Configuration
In this section, we’ll build the MySQL container using the docker-compose tool. However, the YAML also uses the image from the Dockerfile as the base configuration in the current path.
2.1. Docker Compose
First, let’s create the YAML file with version and services tags. We define the file format version under the version tag of the YAML file. The MySQL services use the image information from Dockerfile, which we define in the context.
Further, we also instruct the tool to use the default arguments defined as an environmental variable in the .env file. Last, the ports tag will bind the container and host machine port 3306. Let’s see the contents of the docker-compose YAML file we’re using to bring up the MySQL services:
# cat docker-compose.yml
version: '3.3'
services:
### MySQL Container
mysql:
build:
context: /home/tools/bael/dung/B015
args:
- MYSQL_DATABASE=${MYSQL_DATABASE}
- MYSQL_USER=${MYSQL_USER}
- MYSQL_PASSWORD=${MYSQL_PASSWORD}
- MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
ports:
- "${MYSQL_PORT}:3306"
2.2. Dockerfile Creation
Internally, docker-compose uses the Dockerfile in the specified path to build the image and set the environment for MySQL. Our Dockerfile downloads the image from DockerHub and spins up the container with the defined variables:
# cat Dockerfile
FROM mysql:latest
MAINTAINER baeldung.com
RUN chown -R mysql:root /var/lib/mysql/
ARG MYSQL_DATABASE
ARG MYSQL_USER
ARG MYSQL_PASSWORD
ARG MYSQL_ROOT_PASSWORD
ENV MYSQL_DATABASE=$MYSQL_DATABASE
ENV MYSQL_USER=$MYSQL_USER
ENV MYSQL_PASSWORD=$MYSQL_PASSWORD
ENV MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD
ADD data.sql /etc/mysql/data.sql
RUN sed -i 's/MYSQL_DATABASE/'$MYSQL_DATABASE'/g' /etc/mysql/data.sql
RUN cp /etc/mysql/data.sql /docker-entrypoint-initdb.d
EXPOSE 3306
Now, let’s have a glimpse of all the instructions given in the below Dockerfile snippet:
- FROM – A valid Dockerfile begins with the FROM statement, which describes the image name and version tag. In our case, we use the mysql image with the latest tag.
- MAINTAINER – Set the author information as the container’s metadata that is visible through the docker inspect.
- RUN – Execute the command on top of the mysql image, which subsequently forms a new layer. The resultant- image is committed and used for the next steps defined in the Dockerfile.
- ARG – Pass variables during build time. Here, we are passing four user variables as build arguments.
- ENV – We use the $ symbol to represent the environment variables in Dockerfile. In the above snippet, we use four variables.
- ADD – During build time, it will add the file into the container for future use.
- EXPOSE – Make the services available outside the Docker Container.
2.3. Setting the Environment
Additionally, we can create an environment variable file as .env in the current path. This file contains all the variables involved in the compose file:
# cat .env
MYSQL_DATABASE=my_db_name
MYSQL_USER=baeldung
MYSQL_PASSWORD=pass
MYSQL_ROOT_PASSWORD=pass
MYSQL_PORT=3306
2.4. MySQL Backup File
For the sake of demonstration, let’s take the backup from the existing database table. Here, we import the same Customers table into our MySQL container automatically through the data.sql file.
Below, we’ve showcased the table data using the SELECT query, which fetches data from the requested table:
mysql> select * from Customers;
+--------------+-----------------+---------------+-----------+------------+---------+
| CustomerName | ContactName | Address | City | PostalCode | Country |
+--------------+-----------------+---------------+-----------+------------+---------+
| Cardinal | Tom B. Erichsen | Skagen 21 | Stavanger | 4006 | Norway |
| Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
+--------------+-----------------+---------------+-----------+------------+---------+
2 rows in set (0.00 sec)
As part of the MySQL RDBMS package, the mysqldump utility is used to backup all data in a database into a text file. Using a simple command with inline arguments, we can quickly take the backup of the MySQL tables:
- -u: MySQL username
- -p: MySQL password
# mysqldump -u [user name] –p [password] [database_name] > [dumpfilename.sql]
# mysqldump -u root -p my_db_name > data.sql
Enter password:
At a high level, the backup file will drop any table named Customers in the chosen database and insert all backed-up data into it:
# cat data.sql
-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
...
... output truncated ...
...
DROP TABLE IF EXISTS `Customers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `Customers` (
`CustomerName` varchar(255) DEFAULT NULL,
...
... output truncated ...
...
INSERT INTO `Customers` VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway'),('Wilman Kala','Matti Karttunen','Keskuskatu 45','Helsinki','21240','Finland');
/*!40000 ALTER TABLE `Customers` ENABLE KEYS */;
UNLOCK TABLES;
...
... output truncated ...
...
-- Dump completed on 2022-07-28 1:56:09
However, the database creation or removal isn’t managed in the created dump file. We’ll add the below snippet in the data.sql file, which creates the databases if they do not exist. It makes the circle complete by managing both databases and tables. Finally, it also uses the created database with the USE command:
--
-- Create a database using `MYSQL_DATABASE` placeholder
--
CREATE DATABASE IF NOT EXISTS `MYSQL_DATABASE`;
USE `MYSQL_DATABASE`;
Currently, the directory structure looks like this:
# tree -a
.
├── data.sql
├── docker-compose.yml
├── Dockerfile
└── .env
3. Spinning up a MySQL Server Container
Now, we’re all set to spin up a container through docker-compose. To bring up the MySQL container, we need to execute docker-compose up.
When we skim through the output lines, we can see that they form the new layers in each step on top of the MySQL image.
Subsequently, it also creates the databases and loads the data specified in the data.sql file:
# docker-compose up
Building mysql
Sending build context to Docker daemon 7.168kB
Step 1/15 : FROM mysql:latest
---> c60d96bd2b77
Step 2/15 : MAINTAINER baeldung.com
---> Running in a647bd02b91f
Removing intermediate container a647bd02b91f
---> fafa500c0fac
Step 3/15 : RUN chown -R mysql:root /var/lib/mysql/
---> Running in b37e1d5ba079
...
... output truncated ...
...
Step 14/15 : RUN cp /etc/mysql/data.sql /docker-entrypoint-initdb.d
---> Running in 34f1d9807bad
Removing intermediate container 34f1d9807bad
---> 927b68a43976
Step 15/15 : EXPOSE 3306
---> Running in defb868f4207
Removing intermediate container defb868f4207
---> 6c6f435f52a9
Successfully built 6c6f435f52a9
Successfully tagged b015_mysql:latest
Creating b015_mysql_1 ... done
Attaching to b015_mysql_1
mysql_1 | 2022-07-28 00:49:03+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.26-1debian10 started.
...
... output truncated ...
...
mysql_1 | 2022-07-28 00:49:16+00:00 [Note] [Entrypoint]: Creating database my_db_name
mysql_1 | 2022-07-28 00:49:16+00:00 [Note] [Entrypoint]: Creating user baeldung
mysql_1 | 2022-07-28 00:49:16+00:00 [Note] [Entrypoint]: Giving user baeldung access to schema my_db_name
mysql_1 |
mysql_1 | 2022-07-28 00:49:16+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/data.sql
...
... output truncated ...
...
We can use the -d option to run containers in detached mode:
# docker-compose up -d
Building mysql
Sending build context to Docker daemon 7.168kB
Step 1/15 : FROM mysql:latest
---> c60d96bd2b77
...
... output truncated ...
...
Step 15/15 : EXPOSE 3306
---> Running in 958e1d4af340
Removing intermediate container 958e1d4af340
---> c3516657c4c8
Successfully built c3516657c4c8
Successfully tagged b015_mysql:latest
Creating b015_mysql_1 ... done
#
4. MySQL Client Readiness
It’s mandatory to install a client to get easy access to the MySQL server. Depending on our need, we can install the client on either the host machine or any other machine or container that has IP reachability with the server container:
$ sudo apt install mysql-client -y
Reading package lists... Done
Building dependency tree
Reading state information... Done
mysql-client is already the newest version (5.7.37-0ubuntu0.18.04.1).
...
... output truncated ...
...
Now, let’s extract the installation path and version of the MySQL client:
$ which mysql
/usr/bin/mysql
$ mysql --version
mysql Ver 14.14 Distrib 5.7.37, for Linux (x86_64) using EditLine wrapper
5. Server Client Communication
We can access the deployed MySQL server using the client application. In this section, we’ll see how to access the MySQL server through the client.
Let’s look at the created container id and status using the docker ps command:
# docker ps | grep b015_mysql
9ce4da8eb682 b015_mysql "docker-entrypoint.s…" 21 minutes ago Up 21 minutes 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp b015_mysql_1
Next, let’s get the container IP address to access the database using the installed client service. If we issue the docker inspect command, we’ll see the detailed information about the container in JSON format. We can also pick any field from the resultant JSON. Here, we are taking the IP address from range.NetworkSettings.Networks -> IPAddress:
# docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' 9ce4da8eb682
172.19.0.2
We can then use the client to log in to MySQL Server using the configured host and port information:
# mysql -h 172.17.0.2 -P 3306 --protocol=tcp -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
...
... output truncated ...
...
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| my_db_name |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use my_db_name
...
... output truncated ...
...
Database changed
Here, we can see that the data is restored automatically from the data.sql file:
mysql> select * from Customers;
+--------------+-----------------+---------------+-----------+------------+---------+
| CustomerName | ContactName | Address | City | PostalCode | Country |
+--------------+-----------------+---------------+-----------+------------+---------+
| Cardinal | Tom B. Erichsen | Skagen 21 | Stavanger | 4006 | Norway |
| Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
+--------------+-----------------+---------------+-----------+------------+---------+
2 rows in set (0.00 sec)
Now, let’s try to add a few more rows to the existing database tables. We’ll use an INSERT query to add data to the table:
mysql> INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('White Clover Markets', 'Karl Jablonski', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'USA');
Query OK, 1 row affected (0.00 sec)
We have successfully inserted a new row in the restored table also. Congrats! Let’s see the result:
mysql> select * from Customers;
+----------------------+-----------------+-----------------------------+-----------+------------+---------+
| CustomerName | ContactName | Address | City | PostalCode | Country |
+----------------------+-----------------+-----------------------------+-----------+------------+---------+
| Cardinal | Tom B. Erichsen | Skagen 21 | Stavanger | 4006 | Norway |
| Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
| White Clover Markets | Karl Jablonski | 305 - 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
+----------------------+-----------------+-----------------------------+-----------+------------+---------+
3 rows in set (0.00 sec)
Alternatively, the MySQL server container comes with the MySQL client installation. However, it can be used only within the container for any testing purposes. Now, let’s login to the Docker container and try to access the MySQL server using the default MySQL client.
The docker exec command helps to login to the running container using the container id. The option -i keeps the STDIN open, and -t will allocate the pseudo-TTY, and finally, the /bin/bash at the end lands us in the BASH prompt:
# docker exec -it 9ce4da8eb682 /bin/bash
root@9ce4da8eb682:/# mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
...
... output truncated ...
...
mysql>
6. Conclusion
In summary, we discussed the steps to bring up a MySQL server container using docker-compose. It also automatically restored the database and tables from the backup files. Further, we also accessed the restored data and performed some CRUD operations.