Introduction MySQL is a well-known open-source relational database management system and one of the most popular web server solutions. It stores and structures data in a meaningful manner, ensuring easy accessibility. Docker is a set of platform-as-a-service products that support CI/CD development. It allows users to develop and deploy applications inside virtual environments, called containers. With a single image, Docker can boot up an application with all its libraries and dependencies. In this tutorial, learn how to deploy a MySQL Docker container and start working with the containerized database. Tutorial on How to Install MySQL Docker Containers Prerequisites

  • Access to a command line/terminal window
  • A user account with sudo privileges or access to the root account
  • An existing Docker installation

If you need to set up a database quickly and without using up too many resources, deploying MySQL in a container is a fast and efficient solution. This is only appropriate for small and medium-sized applications. Enterprise-level applications would not find a MySQL Docker container sufficient for their workload. Using the Docker software for setting up your database is becoming increasingly popular for small-scale apps. Instead of having a separate server for database hosting, you can deploy a MySQL database container. Multiple containers can run on your computer. The containers share the same kernel and libraries of the host while packaging the deployed application or software into single units. This makes the database extraordinarily lightweight and fast to spin up.

Installing a MySQL Docker Container

Setting up a database in Docker is simply building a container based on a MySQL image. Follow the steps outlined below to get your MySQL container up and running.

Step 1: Pull the MySQL Docker Image

1. Start by pulling the appropriate Docker image for MySQL. You can download a specific version or opt for the latest release as seen in the following command:

sudo docker pull mysql/mysql-server:latest

If you want a particular version of MySQL, replace latest with the version number. Pull MySQL Docker image for MySQL Docker container. 2. Verify the image is now stored locally by listing the downloaded Docker images:

sudo docker images

The output should include mysql/mysql-server among the listed images. Checking whether MySQL Docker image is part of local repository.

Step 2: Deploy the MySQL Container

1. Once you have the image, move on to deploying a new MySQL container with:

sudo docker run --name=[container_name] -d [image_tag_name]
  • Replace [container_name] with the name of your choice. If you do not provide a name, Docker generates a random one.
  • The -d option instructs Docker to run the container as a service in the background.
  • Replace [image_tag_name] with the name of the image downloaded in Step 1.

In this example, we create a container named mysql_docker with the latest version tag:

sudo docker run --name=[container_name] -d mysql/mysql-server:latest

2. Then, check to see if the MySQL container is running:

docker ps

You should see the newly created container listed in the output. It includes container details, one being the status of this virtual environment. The status changes from health: starting to healthy, once the setup is complete. A command for running a MySQL container and checking the container state.

Step 3: Connect to the MySQL Docker Container

1. Before you can connect the MySQL server container with the host, you need to make sure the MySQL client package is installed:

apt-get install mysql-client

2. Then, open the logs file for the MySQL container to find the generated root password:

sudo docker logs [container_name]

For the mysql_docker container, we run:

sudo docker logs mysql_docker

3. Scroll through the output and find the line [Entrypoint] GENERATED ROOT PASSWORD: , copy and paste the password in a notepad or text editor so you can use it later. Find generated root password for MySQL Docker container. 4. Next, go to the bash shell of the MySQL container by typing:

sudo docker exec -it [container_name] bash

For the container created as an example, we run:

sudo docker -it mysql_docker bash

3. Provide the root password you copied from the logs file, when prompted. With that, you have connected the MySQL client to the server. A command for connecting to a MySQL Docker container. 4. Finally, change the server root password to protect your information:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '[newpassword]';

Replace [newpassword] with a strong password of your choice. ChangeMySQL root password for MySQL container.

Configure MySQL Container

When you install a MySQL container, you will find its configuration options in the /etc/mysql/my.cnf= directory. If you need to modify the configuration, create an alternative config file on the host machine and mount them inside the container. 1. First, create a new directory on the host machine:

sudo mkdir -p /root/docker/[container_name]/conf.d

2. Create a custom MySQL config file inside that directory:

sudo nano /root/docker/[container_name]/conf.d/my-custom.cnf

3. Once in the file, you can add lines with the desired configuration. For example, if you want to increase the maximum number of connections to 250 (instead of the default 151), add the following lines to the configuration file:

[mysqld] max_connections=250

File changing the configuration of a MySQL container by expanding the maximum number of connections. 4. Save and exit the file. 5. For the changes to take place, you need to remove and rerun the MySQL container. This time, the container uses a combination of configuration settings from the newly created file and the default config files. To do this, run the container and map the volume path with the command:

docker run \ --detach \ --name=[container_name] \ --env="MYSQL_ROOT_PASSWORD=[my_password]" \ --publish 6603:3306 \ --volume=/root/docker/[container_name]/conf.d:/etc/mysql/conf.d \ mysql

6. To check whether the container loaded the configuration from the host, run the following command:

mysql -uroot -pmypassword -h227.0.0.1 -P6603 -e 'show global variables like "max_connections"';

You should see that the maximum number of connections is now 250.

Manage Data Storage

By default, Docker stores data in its internal volume. To check the location of the volumes, use the command:

sudo docker inspect [container_name]

You will see the /var/lib/mysql mounted in the internal volume. List details of MySQL Docker container and data storage location. You can also change the location of the data directory and create one on the host. Having a volume outside the container allows other applications and tools to access the volumes when needed. 1. First, find an appropriate volume on the host and create a data directory on it:

sudo mkdir -p /storage/docker/mysql-data

2. Now start the container again, mounting the previously made directory:

docker run \ --detach \ --name=[container_name] \ --env="MYSQL_ROOT_PASSWORD=my_password" \ --publish 6603:3306 \ --volume=/root/docker/[container_name]/conf.d:/etc/mysql/conf.d \ --volume=/storage/docker/mysql-data:/var/lib/mysql \ mysql

If you inspect the container, you should see that the MySQL container now stores its data on the host system. Run the command:

sudo docker inspect [container_name]

Start, Stop, and Restart MySQL Container

The container automatically stops when the process running in it stops. To start the MySQL container run:

sudo docker start [container_name]

Stop the MySQL container, use the command:

sudo docker stop [container_name]

To restart the MySQL container run:

sudo docker restart [container_name]

Delete MySQL Container

Before deleting a MySQL container, make sure you stop it first. Then, remove the docker container with:

sudo docker rm [container_name]

example of stopping and deleting a MySQL container Conclusion After reading this article, you should have successfully deployed a MySQL container. Combining Docker and MySQL can be an excellent solution for a small-scale application. Now you can start exploring all the possibilities of a MySQL container.

Run MySQL with less effort

Photo by Twitter: @jankolario If you’re a database administrator, data scientist, or developer, you may have to use MySQL to store and manipulate data. Since Docker has become a popular DevOps tool in recent years, you will eventually need to know how to run MySQL inside a Docker container. Running your application on a different machine can be nerve-wracking because of environment management. Docker solves this problem by using its containerization technology, which uses Docker images. If you are new to Docker or want to brush your skills, read the following post where I’ve covered some basics. Once you’ve finished this tutorial, you’ll be on your way to testing any MySQL query using Docker. This method will save you time and effort since you don’t have to install or configure MySQL.

Setup

First, you will need to install Docker. For this example, we will use a Docker compose file, a SQL file containing bootstrap data, also known as mysql-dump and macOS. We need to put both docker-compose.yml and school.sql files inside the same folder. The folder name used in this setup is MySQL-Snippets.

  • Docker Compose: We can use Docker compose to run multiple containers and define their properties inside a YAML file. These containers are known as services. It’s helpful when your application has multiple stacks, such as a web server and a database server.
  • MySQL-dump: A mysql-dump contains MySQL queries in plain text. The MySQL command-line client can run these commands and send them to the MySQL server.

Let’s break down the individual ingredients of the docker-compose.yml file.

version: '3.1'services: db: image: mysql restart: always environment: MYSQL_ROOT_PASSWORD: root MYSQL_DATABASE: test_db ports: - "3307:3306" volumes: - $HOME/Desktop/MySQL-Snippets/school.sql:/school.sql

First, we are using a version tag to define the Compose file format, which is 3.1. There are other file formats — 1, 2, 2.x, and 3.x. Get more information on Compose file formats from Docker’s documentation here. We follow our version tag by the services hash. Inside this, we have to define the services we want to use for our application. For our application, we only have one service called db. To make our setup process quick and easy, we are using the pre-built official image of MySQL for the image tag. When we use always for the restart tag, the container always restarts. It can save time. For example, you don’t have to start the container every time you reboot your machine manually. It restarts the container when either the Docker daemon restarts or the container itself is manually restarted. Under the environment tag, we have defined the environment variables, which we will use for database authentication. The ports tag is used to define both host and container ports. It maps the port 3307 on the host to port 3306 on the container. Finally, the volume tag is used to mount a folder from the host machine to the container. It comprises two fields separated by a colon. The first part is the path in the host machine. The second part is the path in the container. Remove this portion if you don’t want to mount the mysql-dump into the container. Great, you’ve accomplished the setup for this post. Now run the following command from the same directory where the docker-compose.yml file is located. The command docker compose up starts and runs your entire app.

docker compose up

Now run docker ps command on the host machine to see a list of your running containers. As we can see, we have a running container called mysql-snippets_db_1. docker ps We can also see an image is created by running docker images command. docker images Access the running container mysql-snippets_db_1 by running the following command.

docker exec -it mysql-snippets_db_1 bash

The docker exec command allows us to enter the running container. The flags -i -t (often written as -it) are used to access the container in an interactive mode. Now we are providing the name of the container we want to access, which in this case mysql-snippets_db. The bash command is used to get a bash shell inside the container, so we can access MySQL command line and execute MySQL queries. You’ve just finished setting up the container. Now, we’ll walk through how to connect to MySQL server.

Connect to MySQL server

Connect to MySQL server using your username and password as root. Once connected to the server, you can run MySQL commands.

mysql - root - proot

Here’s an example you can try: SHOW DATABASES; We can see our database called test_db, defined inside our Compose file. To select the database, run USE test_db; command. Use SHOW TABLES; command to show all the tables of our database test_db. Type ctrl + L to clear the console. Since we don’t have any tables, the command SHOW TABLES; returned an empty set. Now you’ve confirmed you’ve connected to MYSQL server, and you can go onto the next step.

Load data from a file

Now we can load the MySQL dump into our test_db database. Which in this case school.sql. It is accessible inside the container because we have mounted from the host machine.

mysql -uroot -p test_db < school.sql

Connect to the MySQL server using the username root. Enter the password root to authenticate. Now we are setting the default database to test_db. The < along with the filename is used to send the commands to the server. import mysql dump to sql server If we run the MySQL command SHOW TABLES, we can see two tables called marks and students inside our database test_db, which are defined inside the school.sql.

Wrap Up

A relational database such as MySQL is used in the industry often, so it’s a topic you’ll want to understand thoroughly. Now that you’ve completed this basic tutorial, you can dive deeper into exploring MySQL with Docker. You can find more MySQL commands here. Docker has revolutionized the way we manage environments, including development, testing, and production. That’s why you must acquire this skill for both personal and industrial purposes. I hope this will help you get started with MySQL and Docker.

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


Leave a comment

Your email address will not be published. Required fields are marked *