Are you tired of manually setting up MySQL/Mariadb and phpMyAdmin for your development environment? Docker Compose comes to the rescue! In this tutorial, we'll walk you through the steps to simplify the setup process using Docker Compose. By the end of this guide, you'll have a fully functional MySQL server and phpMyAdmin running in separate containers, ready for your development needs.

Prerequisites

Before we begin, make sure you have the following software installed on your machine:

• Docker: Visit the official Docker website (https://www.docker.com) to download and install Docker for your operating system.
• Docker Compose: Docker Compose usually comes bundled with Docker installation. However, double-check by running the command docker-compose --version in your terminal to ensure it's installed.

Setting up the Project

Let's start by creating a new directory for our project and navigating into it:

mkdir mysql-phpmyadmin
cd mysql-phpmyadmin

Next, create a file named docker-compose.yml in the project directory and open it in a text editor. We'll use this file to define our Docker services.

A. MySQL Dockerfile

To customize the MySQL server configuration, we'll create a Dockerfile specifically for the MySQL service.

• Create a directory named mysql in the project directory and navigate into it:

mkdir mysql
cd mysql

• Create a file named Dockerfile in the mysql directory and open it in a text editor.

• Copy the following content into the Dockerfile:

#####################
# Update the line below to use a different mysql/mariadb image
#####################
FROM mysql:debian
##################
# Add Custom installations on top of the base image, if you need to install anything
# Example installing wget, curl & vim for extending the base image
# Remove/Indent everything below, if you don't need any additional customisations
##################
RUN apt-get update \
    && apt-get install -y \
        wget \
        apt-transport-https \
        ca-certificates \
        curl \
        software-properties-common \
        vim

########################################################################
# Example, lets say you want to install mysql tuner to check the performance of your database
# Add MySQL Tuner && Add it to bashrc
########################################################################
WORKDIR /root
RUN wget http://mysqltuner.pl/  -O mysqltuner.pl
RUN wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
RUN wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
RUN chmod +x /root/mysqltuner.pl

In this Dockerfile, we extend the base mysql:debian image. You can update the line to use a different MySQL or MariaDB image if needed. Make sure to use a Debian-based image to ensure compatibility with the shell scripts.

Additionally, the Dockerfile demonstrates how to install additional packages on top of the base image. You can customize this section based on your specific requirements. For example, we included the installation of wget, curl, and vim for extending the base image. Feel free to remove or modify these packages based on your needs.

Please note that if you don't need any customizations, you can skip this section and proceed to the next step.

B. Create MySQL Configurations inside mysql/config Folder

To customize the MySQL server configurations, we'll create a separate folder named config inside the mysql directory. This folder will hold all the necessary configuration files that we want to apply to our MySQL server.

• Start by creating a new directory named config inside the mysql directory:

mkdir mysql/config

• Next, create a configuration file with the desired settings. For example, let’s create a file named my.cnf inside the mysql/config directory:

touch mysql/config/my.cnf

Open the my.cnf file in a text editor and add the desired MySQL configurations. You can customize various settings such as innodb_buffer_pool_size, innodb_buffer_pool_instances, and more. Here’s a sample configuration to get you started:

[mysqld]
# bind-address = 0.0.0.0
skip-name-resolve=1

# consider activating performance schema across your
# configuration file:
performance_schema=ON ## ON/OFF 

# some example mysql configurations 
# (please update the file and restart the container to make the changes take effect)
# innodb_buffer_pool_instances   = 10
# innodb_buffer_pool_size        = 10G 	### How much innodb data to store in memory. Higher = faster performance
# innodb_buffer_pool_chunk_size	= 128M

In this example, we provide a basic configuration for the MySQL server. Uncomment and modify the settings according to your requirements. Don’t forget to restart the MySQL container after making changes to the configuration file for the changes to take effect.

By placing the custom configuration file inside the mysql/config folder, we can mount it as a read-only volume in the MySQL container using the volumes section in the docker-compose.yml file.

C. Setting up the Docker-compose file

Copy the following content into the docker-compose.yml file:

version: "3.3"
services:
  mysql:
    container_name: "mysql"
    # platform: linux/x86_64 #If are on apple silicon, you need to add this line
    ports:
      - "${MYSQL_PORT}:3306"
    environment:
      MYSQL_ROOT_PASSWORD: "${MYSQL_ROOT_PASSWORD}"
      MYSQL_DATABASE: "${MYSQL_DATABASE}"
      MYSQL_USER: "${MYSQL_USER}"
      MYSQL_PASSWORD: "${MYSQL_PASSWORD}"
      MYSQL_ALLOW_EMPTY_PASSWORD: "no"
    volumes:
      - "$PWD/mysql/db:/var/lib/mysql"
      - "$PWD/mysql/config:/etc/mysql/conf.d:ro"
      - "$PWD/mysql/log:/var/log/mysql"
    security_opt:
      - seccomp:unconfined

  phpmyadmin:
    image: "phpmyadmin"
    container_name: phpmyadmin
    ports:
      - "${phpmyadminPort}:80"
    environment:
      - PMA_HOST=${MYSQL_IP_Address}
      - PMA_PORT=${MYSQL_PORT}
      - MAX_EXECUTION_TIME=30000
      - UPLOAD_LIMIT=2048M
      - MEMORY_LIMIT=2048M

In this docker-compose.yml file, we define two services: mysql and phpmyadmin. The mysql service sets up the MySQL server, while the phpmyadmin service runs the phpMyAdmin interface. Each service is configured with the necessary environment variables and ports.

i. Details of the MySQL Service in the docker-compose file

Following is a further breakdown of the mysql service, which sets up the MySQL server.

• The container_name sets the name of the container to "mysql".
• The ports section maps the ${MYSQL_PORT} variable defined in the .env file to port 3306 in the container.
• The environment section sets the environment variables for the MySQL container, including the root password, database name, username, and password. (we will set it up in the next section using the .env file)
• The volumes section mounts three directories from the host to the container:
  • $PWD/mysql/db:/var/lib/mysql: Maps the mysql/db directory in the project to the /var/lib/mysql directory in the container, allowing data persistence.
  • $PWD/mysql/config:/etc/mysql/conf.d:ro: Maps the mysql/master/config directory in the project to the /etc/mysql/conf.d directory in the container as a read-only volume. This is useful for providing custom configuration files, such as the my.cnf file.
  • $PWD/mysql/log:/var/log/mysql: Maps the mysql/log directory in the project to the /var/log/mysql directory in the container, again allowing data persistence.

ii. Details of the Phpmyadmin Service in the docker-compose file

Following is a further breakdown of the phpmyadmin service, which is responsible for setting up the phpMyAdmin web interface, allowing you to conveniently manage your MySQL databases.

• We expose port 8090 on the host machine, which maps to port 80 within the phpmyadmin container. This allows you to access the phpMyAdmin interface through your web browser by visiting http://localhost:8090.
• To establish the connection between phpMyAdmin and the MySQL server, we set the PMA_HOST environment variable to ${MYSQL_IP_Address} and PMA_PORT to ${MYSQL_PORT}. These variables ensure that phpMyAdmin knows where to connect to the MySQL server running in the mysql container.
• Additionally, we configure other environment variables such as MAX_EXECUTION_TIME, UPLOAD_LIMIT, and MEMORY_LIMIT to customize the behavior of phpMyAdmin.
• By including the depends_on directive with the value mysql, we ensure that the phpmyadmin container starts only after the mysql container is up and running.

Save the docker-compose.yml file.

D. Creating a .env file for our custom docker configurations

Now, let's create a new file named .env in the project directory and open it in a text editor. This file will contain environment variables used in the docker-compose.yml file. Copy the following contents into the  .env file

################################
# MYSQL Config
# Automatically create database and user
################################
MYSQL_PORT=3306
MYSQL_ROOT_PASSWORD=rootpwd
MYSQL_DATABASE=example_db
MYSQL_USER=admin
MYSQL_PASSWORD=adminpassword

################################
# PHPMYADMIN Config
################################
phpmyadminPort=8090
MYSQL_IP_Address=mysql 

In this .env file, you can customize the values of environment variables according to your preferences. These variables will be used in the docker-compose.yml file to configure the MySQL and phpMyAdmin containers.

Let's go through the variables:

• MYSQL_PORT: Specifies the port number on which the MySQL server will run.
• MYSQL_ROOT_PASSWORD: Sets the root password for the MySQL server.
• MYSQL_DATABASE: Specifies the name of the initial database to be created.
• MYSQL_USER: Sets the username for the MySQL user.
• MYSQL_PASSWORD: Specifies the password for the MySQL user.
• phpmyadminPort: Specifies the port number on which phpMyAdmin will be accessible.
• MYSQL_IP_Address: Sets the hostname or IP address of the MySQL server. In this case, it is set to mysql, which is the service name defined in the docker-compose.yml file. Docker's internal DNS resolution allows the phpMyAdmin container to access the MySQL server using this service name.

Save the .env file.

Starting the Containers

Now, we are good to go to spin on our containers using Docker Compose.

In your terminal, navigate to the project directory root, and run the following command:

docker-compose up -d

Docker Compose will start building the MySQL and phpMyAdmin containers based on the configuration files we created. The -d flag runs the containers in detached mode, meaning they will continue to run in the background.

Accessing phpMyAdmin

Once the containers are up and running, you can access phpMyAdmin by opening your web browser and visiting http://localhost:8090. You should see the phpMyAdmin login page.

Use the following credentials to log in:

• Server: mysql
• Username: root
• Password: rootpwd

Congratulations! You now have a fully functional MySQL server and phpMyAdmin setup running in Docker containers. You can create databases, manage tables, and perform various database-related tasks using the user-friendly phpMyAdmin interface.

Conclusion

In this tutorial, we have simplified the setup of MySQL and phpMyAdmin using Docker Compose. By defining the services, environment variables, and volumes in the docker-compose.yml file, we achieved a streamlined and portable development environment. Docker Compose allows for easy container orchestration, enabling hassle-free management of your MySQL server and phpMyAdmin setup.

Feel free to explore and experiment with the setup according to your specific requirements. Docker Compose offers a wide range of configuration options and flexibility to adapt to different development scenarios.

Happy coding and database management!