How to use MySQL with Docker-Compose

Introduction

In the field of software development and database management, the integration of MySQL with Docker Compose has revolutionized the way we manage databases in containerized environments. This article provides a complete guide on using MySQL effectively with Docker Compose, emphasizing the use of the docker-entrypoint-initdb.d script, implementing persistent storage, and customizing the my.cnf configuration.

Why use MySQL with Docker?

Consistency: Docker containers ensure that MySQL runs the same way, regardless of where it is deployed. Isolation: Each MySQL instance runs in its own container, isolated from the host system and other containers. Scalability: Easily scale your MySQL databases by managing multiple containers with Docker Compose.

Step 1: Defining the Docker Compose file

Create a docker-compose.yml file in your project directory. This file defines the MySQL service and its configurations.

version: '3.8'

 services:
 db:
 image: mysql:8
 command: --default-authentication-plugin=mysql_native_password
 restart: always
 environment:
 MYSQL_ROOT_PASSWORD: secure_password
 MYSQL_DATABASE: mydb
 MYSQL_USER: myuser
 MYSQL_PASSWORD: password
 ports:
 - "13306:3306"
 volumes:
 - mysql_data:/var/lib/mysql
 -./db/init.sql:/docker-entrypoint-initdb.d/init.sql
 -./db/my.cnf:/etc/mysql/my.cnf

 volumes:
 mysql_data:

Step 2: Using the docker-entrypoint-initdb.d script

The docker-entrypoint-initdb.d directory is a special location in the MySQL image. Scripts and SQL files placed in this directory are automatically executed during the first startup of the container. This functionality is critical for initializing a database with a predefined schema or dataset.

1. Create an "initdb" directory in your project folder.
2. Add your SQL scripts or initialization scripts inside this directory.
3. These scripts will run in alphabetical order the first time the container is started.

Step 3: Implement persistent storage

Persistent storage is essential to retain database data even after the Docker container is stopped or deleted. In the docker-compose.yml file, the following line achieves this:

volumes:
 - db_data:/var/lib/mysql

This line specifies a named volume ( db_data ) that ensures data persistence. When the MySQL container is stopped or restarted, the data remains intact in this volume.

Step 4: Customizing your my.cnf configuration

Customizing your MySQL configuration is easy with Docker. To use a custom my.cnf:

Create a my.cnf file in your project directory with your custom configurations.

Mount this file into your container by adding the following line to your service in the docker-compose.yml file:

volumes:
 -./my.cnf:/etc/mysql/my.cnf

This line maps your local my.cnf file to the one inside the container, allowing you to change MySQL configurations as needed.

Step 5: Running and Managing the MySQL Container

Once everything is set up, run your MySQL container using Docker Compose:

docker-compose up -d

This command starts your MySQL instance in detached mode. To stop the container, use docker-compose down.

Step 6: Connect to the MySQL server

To connect to the MySQL database inside a Docker container using the docker exec command, you will first need to find out the name or ID of your running MySQL container. You can then use docker exec to run the MySQL client inside this container, which allows you to connect directly to the MySQL server. Here's how you can do it:

List running containers: First identify the container running MySQL. You can list all running containers using:

docker ps

Look for the container running MySQL (in your case, it should be called something like db or have mysql in the image name).

Connect to the MySQL container - Use docker exec to start a MySQL client session inside the container. The format of the command is:

docker exec -it [container_name_or_id] mysql -u[username] -p

For your setup, assuming your container name is db and you want to connect as root, the command would be:

docker exec -it db mysql -uroot -p

You will be asked to enter your password. For root user, according to your docker-compose.yml file, it is secure_password.

Interact with MySQL: After logging in, you can run any MySQL command to manage databases, tables and data.

Conclusion

MySQL integration with Docker Compose provides a simplified approach to managing relational databases in a containerized environment. By leveraging the docker-entrypoint-initdb.d script, implementing persistent storage, and customizing the my.cnf file, you can effectively manage and scale your MySQL databases with ease and precision. This setup not only simplifies development workflows but also improves the portability and scalability of your applications.