Running a MySQL database with Docker Compose

What will you learn in this post?

In this post, I'm gonna guide you through setting up a MySQL/MariaDB database using Docker. The goal of this guide is for you to be able to run MySQL locally for development purposes.

What software is required to run MySQL?

For this task, we're going to use Docker, Docker Compose together with MariaDB image (MariaDB is an open source version of MySQL). The first step is to download Docker if you don't already have it. You can download it from here.

What is Docker?

Docker is an open-source platform that simplifies the process of creating, deploying, and running applications in containers. Containers are lightweight, portable, and self-sufficient units that contain everything needed to run a piece of software, including the code, runtime, system tools, libraries, and settings.

This makes sure that the application runs consistently, regardless of the underlying infrastructure. This also means you don't need to install MySQL or other software directly on your machine. In theory, this should make it easy to run the same software on all systems.

What is Docker Compose?

Docker Compose is a tool for defining and running multi-container Docker applications. It allows you to create and manage complex application environments using a single YAML file, called a compose file.

This file defines the services (containers), networks, and volumes (for keeping data when containers are stopped or sharing the data between containers) that make up your application, and Docker Compose takes care of creating and starting the necessary containers.

Understanding the Docker Compose YAML File

Let's dive into the example Docker Compose YAML (learn more about YAML here) file provided:

version: "3.9"
services:
  mysql:
    image: mariadb:10.8.3
    # Uncomment below when on Mac M1
    # platform: linux/arm64/v8
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
    ports:
      - 3306:3306
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

This file defines two services, mysql and adminer, which are responsible for running the MySQL database and Adminer, a web-based database management tool.

• version: "3.9": This line specifies the version of the Docker Compose file format being used. The best idea is to use the latest version.

Understanding the MySQL Docker service

I'm referring to the service as mysql, yet it's actually using the mariadb image. Again, mariadb is just an open source version of the MySQL database.

• image: mariadb:10.8.3: This line specifies the Docker image to use for the MySQL database. In this case, we're using the official MariaDB image, version 10.8.3 (MariaDB is an open source alternative to MySQL).
• command: --default-authentication-plugin=mysql_native_password: This line sets the default authentication plugin for MySQL to mysql_native_password. This let's you connect to MySQL using password. In the current MySQL version, the default would be using a key, which while more secure, is inconvenient for beginners or when developing locally.
• restart: always: This line ensures that the MySQL container will always restart if it stops or crashes.
• environment: MYSQL_ROOT_PASSWORD: root: This line sets an environment variable to configure the root password for the MySQL database. To connect to the database you'll use the root as username, and the password you entered here.
• ports: - 3306:3306: This line maps the default MySQL port (3306) from the container to the host machine. It essentially means when the container starts, MySQL would be available at localhost:3306 on your machine.

Understanding the Adminer Docker service

• image: adminer: This line specifies the Docker image to use for Adminer, a web-based database management tool.
• restart: always: This line ensures that the Adminer container will always restart if it stops or crashes.
• ports: - 8080:8080: This line maps port 8080 from the container to the host machine, allowing access to the Adminer web interface http://localhost:8080

How to Use Docker Compose to Run the MySQL Database?

To run the MySQL database using the provided Docker Compose file, follow these steps:

• Install Docker and Docker Compose on your system if you haven't already (download here).
• Save the example Docker Compose YAML file in a directory on your machine as docker-compose.yml, preferably in the project directory you're going to use this for.
• Open a terminal or command prompt, and navigate to the directory containing the docker-compose.yml file.

Run the following command to start the MySQL and Adminer containers: docker compose up -d

This command will:

• Pull the necessary images (if not already present - which might take some time!) and create the containers as defined in the docker-compose.yml file.
• The -d flag runs the containers in detached mode, allowing you to continue using the terminal.
• Skip the -d flag and then you can easily stop the containers later by pressing Ctrl+C.

Once the containers are running, you can access the Adminer web interface by opening a web browser and navigating to http://localhost:8080.

How to connect to the MySQL database?

How to connect to MySQL from Adminer?

In the Adminer web interface, enter the following details to connect to the MySQL database:

• System: MariaDB or MySQL (depending on the Adminer version)
• Server: mysql
• Username: root
• Password: root

Then, click "Login" to connect to the database. It's important to note that within Docker, the host for the MySQL is same as the service name, which is mysql not localhost.

Yet, on your host machine, it's localhost. That's why when logging in to Adminer you set the server to mysql (mysql is just the container name in docker-compose.yml), as Adminer also runs within a Docker container (same network as the MySQL container)!

How to connect from outside Docker?

If you're using a database management tool installed on your system (e.g., MySQL Workbench or TablePlus), use the following connection details:

• Hostname: localhost
• Port: 3306
• Username: root
• Password: root

How to stop a running MySQL container?

To stop the containers created using the Docker Compose file, follow these steps:

• Open a terminal or command prompt, and navigate to the directory containing the docker-compose.yml file.
• Run the following command to stop the containers: docker-compose down

This command will stop and remove the containers, networks, and volumes defined in your docker-compose.yml file.