Using MySQL with Docker-Compose

In the realm of software development and database management, the integration of MySQL with Docker Compose has revolutionized the way we handle databases in containerized environments. This article provides a comprehensive guide on effectively using MySQL 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.

Advertisement

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: Utilizing 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 container’s first launch. This feature is crucial for initializing a database with a schema or default datasets.

1. Create a directory `initdb` in your project folder.
2. Add your SQL scripts or initialization scripts inside this directory.
3. These scripts will be executed in alphabetical order when the container starts for the first time.

Step 3: Implementing Persistent Storage

Persistent storage is essential to retain your 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 the my.cnf Configuration

Customizing the MySQL configuration is straightforward with Docker. To use a custom my.cnf:

1. Create a my.cnf file in your project directory with your custom configurations.
2. Mount this file into the 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 modify MySQL configurations as needed.

Step 5: Running and Managing the MySQL Container

With everything 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 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:

1. 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 named something like db or have mysql in its image name).

2. Connect to the MySQL Container: Use docker exec to start a MySQL client session inside the container. The command format 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 prompted to enter the password. For the root user, according to your docker-compose.yml file, it is secure_password.

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

Conclusion

Integrating MySQL with Docker Compose provides a streamlined 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 enhances the portability and scalability of your applications.