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 themysql
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 themysql
directory:
mkdir mysql/config
- Next, create a configuration file with the desired settings. For example, let’s create a file named
my.cnf
inside themysql/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 port3306
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 themysql/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 themysql/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 themy.cnf
file.$PWD/mysql/log:/var/log/mysql
: Maps themysql/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 port80
within thephpmyadmin
container. This allows you to access the phpMyAdmin interface through your web browser by visitinghttp://localhost:8090
. - To establish the connection between phpMyAdmin and the MySQL server, we set the
PMA_HOST
environment variable to${MYSQL_IP_Address}
andPMA_PORT
to${MYSQL_PORT}
. These variables ensure that phpMyAdmin knows where to connect to the MySQL server running in themysql
container. - Additionally, we configure other environment variables such as
MAX_EXECUTION_TIME
,UPLOAD_LIMIT
, andMEMORY_LIMIT
to customize the behavior of phpMyAdmin. - By including the
depends_on
directive with the valuemysql
, we ensure that thephpmyadmin
container starts only after themysql
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 tomysql
, which is the service name defined in thedocker-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!