Building a MySQL 8.0 Environment with Docker

This article will walk you through the process of creating a MySQL 8.0 environment using Docker. MySQL is one of the most popular open-source relational database management systems. By using Docker, we can quickly set up a MySQL server for development or testing purposes without going through the lengthy process of manually installing and configuring a MySQL server on a host OS.

Setting Up docker-compose.yml

To create a MySQL server container, we will first create a docker-compose.yml file. This file will contain the necessary configuration to pull the MySQL image from Docker Hub, create a new MySQL container, and set up the necessary environment variables.

Here is the docker-compose.yml file:

version: '3.9'

services:
  mysql:
    image: mysql:8.0.28
    platform: linux/amd64
    container_name: mysql-container
    ports:
      - '3306:3306'
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: demo
      TZ: 'Asia/Tokyo'

Starting MySQL Container

Once the docker-compose.yml file has been set up, we can start our MySQL server container. Docker Compose allows us to manage our application's services with just a single command. In the terminal, navigate to the directory where your docker-compose.yml file is located, then run the following command:

$ docker compose up -d

To confirm that our MySQL server container is up and running, we can use the docker ps command. This command lists all running Docker containers. Here is the command and the expected output:

$ docker ps

CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS          PORTS                               NAMES
baf6d46af198   mysql:8.0.28   "docker-entrypoint.s…"   26 seconds ago   Up 24 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql-container

After confirming that the MySQL container is running, you can access the MySQL container's shell by using the docker exec command:

$ docker exec -it mysql-container

root@baf6d46af198:/#

Now that we are inside the MySQL container, we can connect to the MySQL server by using the MySQL client CLI. To connect as the root user, use the following command:

root@baf6d46af198:/# mysql -u root -p

Enter password: root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>

Operating MySQL

Once you're connected to the MySQL server, you can check the available databases by using the show databases; command:

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| demo               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.11 sec)

To perform operations on the demo database, you need to select it first by using the use command:

mysql> use demo

Database changed

You can confirm which database is currently selected by using the select database(); command:

mysql> select database();

+------------+
| database() |
+------------+
| demo       |
+------------+
1 row in set (0.00 sec)

With the demo database selected, you can now perform various database operations such as creating tables, inserting data, and querying data. Here's an example of creating a table, inserting data into it, and then retrieving that data:

mysql> create table test(id int, name varchar(10));

Query OK, 0 rows affected (0.13 sec)

mysql> insert into test(id, name) values (1,"john");

Query OK, 1 row affected (0.10 sec)

mysql> select * from test;

+------+--------+
| id   | name   |
+------+--------+
|    1 | john   |
+------+--------+
1 row in set (0.04 sec)

Cleanup

Once you're done with your operations, it's good practice to terminate the MySQL connection:

mysql> exit

Bye

This command will log you out from the MySQL server.

After exiting the MySQL connection, you will be back in the Docker container's shell. You can also exit the Docker container:

root@baf6d46af198:/# exit

exit

To ensure that no unnecessary resources are consumed, it's best to stop and remove the Docker container when you're done using it. Use the following command to stop and remove the Docker container:

$ docker compose down

[+] Running 2/2
 ⠿ Container mysql-container  Removed                    2.8s
 ⠿ Network my_proj_default    Removed