From stand-alone to cluster: Deploy MySQL using Docker Compose

introduction

In modern applications, database systems play a vital role, especially MySQL. MySQL is a widely used open source relational database management system, which is favored for its high performance, reliability and flexible scalability. With the rise of microservice architecture, how to effectively deploy and manage MySQL database has become a major challenge for developers. With Docker and Docker Compose, we can quickly deploy MySQL stand-alone and cluster environments to meet different application needs.

Overview of Docker and Docker Compose

Basic concepts of Docker

Docker is an open platform for developing, delivering, and running applications. It packages applications and their dependencies together through container technology, allowing applications to run consistently in any environment. Docker containers are lightweight and fast to start, making them ideal for application deployment under a microservice architecture.

Docker Compose uses and benefits

Docker Compose is a tool provided by Docker for defining and managing multi-container Docker applications. docker-compose.yml Developers can describe the application's services, networks, and volumes in one file, greatly simplifying the application configuration and deployment process.

MySQL stand-alone deployment

Environment Preparation

Before you start deploying MySQL standalone, make sure you have installed Docker and Docker Compose. If you haven't installed them yet, you can refer to the official documentation to install them.

Docker Compose configuration file example

Create a file called docker-compose.yml The content of the file is as follows:

version: '3.8'

services: 
   mysql: 
      image: mysql:5.7
      restart: always 
      environment: 
          # root administrator password 
          MYSQL_ROOT_PASSWORD: 123456 
          MYSQL_DATABASE: testdb 
          # ordinary user name 
          MYSQL_USER: test_user
          # ordinary user password 
          MYSQL_PASSWORD: testPassword 
      ports:
          - "3306:3306"
      volumes:
          - mysql-data:/var/lib/mysql
 
volumes: 
   mysql-data:

In this configuration file, we define a mysql We use the MySQL 5.7 image as a service. We set the root password, database name, and user information through environment variables. We also map the MySQL database port 3306 to the host port 3306 and define a persistent data volume. mysql-data.

Start MySQL standalone version

Including docker-compose.yml In the directory of the file, run the following command to start the MySQL standalone version:

docker-compose up -d

use -d The parameter runs the service in the background. After starting, you can view the running status by using the following command:

docker-compose ps

Here the running mysql container information will be output.

Verify the operation of MySQL stand-alone version

To verify that MySQL is running properly, you can connect to the database using a MySQL client:

# mysql_container_id is replaced with the actual id of your own docker container
docker exec -it    mysql -u root -p

After entering the root password, you will be taken to the MySQL command line interface. You can execute the following SQL statement to view the created database:

SHOW DATABASES;

MySQL Cluster Overview

Definition of Cluster

MySQL Cluster is an architecture designed to improve the availability and scalability of databases. By combining multiple MySQL instances together, users can achieve load balancing, failover and other functions. Clustering can be achieved in many ways, mainly multi-instance deployment and master-slave replication.

The difference between multi-instance and master-slave

1. Multiple instances

   • Multi-instance deployment means running multiple MySQL instances on the same machine. Each instance has independent configuration, data files, and network ports. This method is suitable for scenarios where multiple databases need to be run on the same physical server.
   • Advantages: high resource utilization and easy management.
   • Disadvantages: Single point of failure. If the server fails, all instances will be affected.

2. Master-slave replication

   • Master-slave replication is an asynchronous replication mechanism, usually used for data backup and load balancing. The master database is responsible for processing all write requests, while the slave database is used for read requests. The data of the master database is asynchronously replicated to the slave database.
   • Advantages: Provides data redundancy and load balancing.
   • Disadvantages: There may be data delays, especially under high load conditions.

MySQL Cluster Deployment

Environment Preparation

Make sure Docker and Docker Compose are installed correctly. Next, we will create a MySQL cluster configuration with master-slave replication.

Docker Compose configuration file example

Create a file called docker-compose-cluster.yml The content of the file is as follows:

version: '3.8' 

services: 
  # 主msyql 
  mysql-master: 
    image: mysql:5.7 
    restart: always 
    environment: 
      # root管理员密码 
      MYSQL_ROOT_PASSWORD: 123456 
      MYSQL_DATABASE: testdb 
      # 普通用户名称 
      MYSQL_USER: test_user 
      # 普通用户密码 
      MYSQL_PASSWORD: testPassword 
      MYSQL_REPLICATION_MODE: master 
      MYSQL_REPLICATION_USER: repl_user 
      MYSQL_REPLICATION_PASSWORD: replpassword 
    ports:
      - "3306:3306" 
    volumes: 
      - master-data:/var/lib/mysql 
  # 从mysql 
  mysql-slave: 
    image: mysql:5.7 
    restart: always 
    environment: 
      # root管理员密码 
      MYSQL_ROOT_PASSWORD: 123456 
      MYSQL_REPLICATION_MODE: slave 
      MYSQL_REPLICATION_USER: repl_user 
      MYSQL_REPLICATION_PASSWORD: replpassword 
      MYSQL_MASTER_HOST: mysql-master 
      MYSQL_MASTER_PORT: 3306 
    ports: 
      - "3307:3306" 
    volumes: 
      - slave-data:/var/lib/mysql 
    
  volumes: 
    master-data: 
    slave-data:

In this configuration, we define two services:mysql-master and mysql-slaveThe master server uses MYSQL_REPLICATION_MODE: master, and the slave server is set to MYSQL_REPLICATION_MODE: slave, and specifies the connection information for the primary server.

Start MySQL Cluster

Including docker-compose-cluster.yml In the directory where you created the MySQL cluster, run the following command to start the MySQL cluster:

docker-compose -f docker-compose-cluster.yml up -d

Verify the operation of MySQL cluster

To verify the cluster status, you can first connect to the master server:

# mysql_container_id is replaced with the actual id of your own docker container 
docker exec -it   mysql -u root -p

In the MySQL command line, enter the following command to check the status of the slave server:

SHOW MASTER STATUS;

Next, connect to the slave server:

# mysql_container_id is replaced with the actual id of your own docker container 
docker exec -it   
 mysql -u root -p

In the MySQL command line of the slave server, enter the following command to check the status of the master server:

SHOW SLAVE STATUS\G;

confirm Slave_IO_Running and Slave_SQL_Running All for Yes, which indicates that replication is working properly.

Summarize

This article describes how to deploy MySQL standalone and cluster environments using Docker Compose. We explain in detail the differences between multi-instance and master-slave replication, and provide corresponding configuration examples. MySQL clusters can not only improve application availability and scalability, but also improve performance through load balancing.

With the continuous development of cloud computing and microservices, the deployment and management of database clusters will become more and more important. In the future, as technology advances, we can expect smarter database solutions to provide higher performance and reliability for our applications.

I hope this article can help you deploy MySQL with Docker. If you have any questions or suggestions, please feel free to communicate!

Of course you can! Here are some examples of reference links and attributions you can include in your blog posts, covering literature on Docker, MySQL, and related technologies.

Reference Links

1. Docker official documentation

   • Docker Documentation
   • Description: Provides detailed instructions for installing, configuring, and using Docker.

2. Docker Compose official documentation

   • Docker Compose Documentation
   • Description: This section describes how to use Docker Compose to define and run multi-container applications.

3. MySQL official documentation

   • MySQL Documentation
   • Description: Covers detailed information on installation, configuration, and administration of MySQL.

4. Official Guide to MySQL Master-Slave Replication

   • MySQL Replication
   • Description: Complete documentation on MySQL master-slave replication.

5. Tutorial on combining Docker and MySQL

   • How to Use MySQL with Docker
   • Description: Provides detailed steps and examples for deploying MySQL using Docker.

6. MySQL Cluster concepts and design

   • MySQL Cluster Overview
   • Description: Introduction to MySQL cluster architecture.