Running a MySQL database with Docker Compose

Running MySQL locally

In this post, I'm going to 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.

You need the Docker app

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's Docker?

Docker lets you run software using lightweight containers, separated from the underlying hardware or operating system. Thus, you don't need to install MySQL directly on your machine.

What's Docker Compose?

Docker Compose allows running multi-container setups using a single YAML file. You can mix containers, networks and volumes, so sharing data between containers is possible. We're going to use this to run the MySQL server and a database client through Docker.

Docker Compose YAML file

Let's dive into the Docker Compose YAML (learn more about YAML here) we're gonna use:

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 db client.

• version: "3.9": The version of docker compose file.

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.

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

Running MySQL with Docker Compose

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, it 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 (that's optional).
• Skip the -d flag, and then you can 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.

Connecting 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)!

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

Stopping containers

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.