How to use MySQL with Docker and Docker Compose

Introduction

As the world of software development continues to evolve, the need for flexible and scalable infrastructure becomes increasingly vital. Docker and Docker Compose are powerful tools that help to create reproducible and consistent development environments. In this tutorial, we will dive into using MySQL with Docker, guiding you through the process of containerizing a MySQL database and setting up a service with Docker Compose.

Prerequisites

• Docker installed on your system
• Docker Compose installed on your system
• Basic understanding of Docker concepts (images, containers, Dockerfiles)
• Familiarity with MySQL

Basic MySQL Container Setup

Let’s start by running a basic MySQL instance as a Docker container. Here, we’ll skip the volume for data persistence and the environment variables for customization, to keep it simple:

docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

Here, -d runs the container in detached mode. Running docker ps should now show your MySQL container running.

Customizing MySQL Container

In a real-world scenario, you would want to persist data and perhaps customize the root password. Here’s how you do it:

docker run --name mysql-container \
 -e MYSQL_ROOT_PASSWORD=my-strong-password \
 -v mysql-data:/var/lib/mysql \
 -d mysql:latest

This command creates a volume called mysql-data that persists even if the container stops or is removed.

Creating a Dockerfile

You might need to add customization to the MySQL image, for which you would write a Dockerfile. In this simple example, we create a custom Dockerfile to change the MySQL character set and collation:

FROM mysql:latest
RUN echo '[mysqld]\ncharacter-set-server=utf8\ncollation-server=utf8_unicode_ci' >> /etc/mysql/conf.d/custom.cnf

Using Docker Compose

Docker Compose allows you to define and run multi-container Docker applications. Create a docker-compose.yml file to define a simple MySQL service:

version: '3.1'
services:
  db:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: example
    ports:
      - '3306:3306'
    volumes:
      - db_data:/var/lib/mysql
volumes:
  db_data:

Start the service using docker-compose up and access your MySQL instance on the default port 3306.

Advanced Configuration

For more complex setups, you may need multiple services. Here’s an example with a web application and MySQL:

version: '3.1'
services:
  db:
    image: mysql
    environment:
      MYSQL_ROOT_PASSWORD: example
    volumes:
      - db_data:/var/lib/mysql
  web:
    image: custom-web-app
    depends_on:
      - db
    ports:
      - '3000:3000'
volumes:
  db_data:

The depends_on field ensures that your web application starts once MySQL is ready.

Backups and Restoration

To backup your MySQL database, you can use the following command:

docker exec mysql-container /usr/bin/mysqldump -u root --password=my-secret-pw databasename > backup.sql

To restore data:

cat backup.sql | docker exec -i mysql-container /usr/bin/mysql -u root --password=my-secret-pw databasename

Note: Replace my-secret-pw and databasename with your actual password and database name.

Networking in Docker

If you need your MySQL server to be reachable from other containers or the host, you can configure Docker networking. This can be defined in the Docker Compose file under the networks key.

Error Handling and Debugging

It’s common to face issues while setting up containers. For troubleshooting, use docker logs mysql-container to see MySQL logs. Debugging may require viewing the container’s internal state, which you can do using docker exec.

Conclusion

This tutorial covered the essentials of Dockerizing a MySQL database and utilizing Docker Compose for service definition and orchestration. With the knowledge gained from these steps, you can streamline development workflows and manage databases with confidence. Remember, learning by doing is the best approach; experiment with Docker and MySQL to master their integration.