Detailed tutorial on deploying MySQL through Docker Compose

1. Advantages of Docker Compose Deployment of MySQL

Docker Compose, as Docker's official container orchestration tool, brings significant advantages to MySQL database deployment:

Declarative configuration management: All service parameters are defined in the YAML file, avoiding complex command line parameters

Multi-service collaboration: Easily implement the integrated deployment of MySQL and Web applications, caching and other services

Environmental consistency: Use the same configuration in the development, testing and production environments to eliminate environmental differences

Quick Rebuild: Complete service stop, rebuild and start with one click, simplifying maintenance process

Version control friendly: Files can be included in version control system management such as Git

2. Environmental preparation and basic configuration

2.1 Project directory structure

Recommended standard directory layout:

mysql-project/
├──
├── config/
│ └──
├── data/
│ └── mysql/
├── initdb/
│ └──
└── .env

2.2 Basics

version: "3.8"

services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: appdb
      MYSQL_USER: appuser
      MYSQL_PASSWORD: userpass
    ports:
      - "3306:3306"
    volumes:
      - ./data/mysql:/var/lib/mysql
      - ./config/:/etc/mysql//
      - ./initdb:/
    restart: unless-stopped

3. Production environment deployment plan

3.1 Complete configuration example

version: "3.8"

services:
  mysql:
    image: mysql:8.0
    container_name: production_mysql
    environment:
      MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
      MYSQL_DATABASE: ${DB_NAME}
      MYSQL_USER: ${DB_USER}
      MYSQL_PASSWORD: ${DB_PASSWORD}
      TZ: Asia/Shanghai
    ports:
      - "${DB_PORT}:3306"
    volumes:
      - mysql_data:/var/lib/mysql
      - ./config/:/etc/mysql//:ro
      - ./backups:/backups
    networks:
      - backend
    deploy:
      resources:
        limits:
          cpus: "2"
          memory: 2G
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      interval: 10s
      timeout: 5s
      retries: 3

volumes:
  mysql_data:
    driver: local
    driver_opts:
      type: none
      device: ./data/mysql
      o: bind

networks:
  backend:
    driver: bridge

3.2 Environment variable file (.env)

DB_ROOT_PASSWORD=your_strong_root_password
DB_NAME=production_db
DB_USER=app_user
DB_PASSWORD=your_app_user_password
DB_PORT=3306

4. Advanced configuration skills

4.1 Master-slave replication configuration

services:
  mysql-master:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: masterpass
      MYSQL_REPLICATION_USER: repl_user
      MYSQL_REPLICATION_PASSWORD: repl_pass
    command:
      - --server-id=1
      - --log-bin=mysql-bin
      - --binlog-format=ROW
    volumes:
      - master_data:/var/lib/mysql
    networks:
      - mysql-cluster

  mysql-slave:
    image: mysql:8.0
    depends_on:
      - mysql-master
    environment:
      MYSQL_ROOT_PASSWORD: slavepass
      MYSQL_MASTER_HOST: mysql-master
      MYSQL_MASTER_USER: repl_user
      MYSQL_MASTER_PASSWORD: repl_pass
    command:
      - --server-id=2
    volumes:
      - slave_data:/var/lib/mysql
    networks:
      - mysql-cluster

volumes:
  master_data:
  slave_data:

networks:
  mysql-cluster:
    driver: bridge

4.2 Multi-environment configuration management

services:
  mysql:
    image: mysql:${MYSQL_VERSION:-8.0}
    volumes:
      - ${DATA_VOLUME:-mysql_data}:/var/lib/mysql

services:
  mysql:
    extends:
      file: 
      service: mysql
    environment:
      - MYSQL_ROOT_PASSWORD=${PROD_DB_PASSWORD}
    deploy:
      resources:
        limits:
          memory: 4G

V. Safety reinforcement plan

5.1 Security Configuration Example

services:
  mysql:
    image: mysql:8.0
    user: "999:999"
    cap_drop:
      - ALL
    security_opt:
      - no-new-privileges:true
    environment:
      - MYSQL_SECURE_TRANSPORT=ON
      - MYSQL_SSL=ON
    volumes:
      - ./ssl:/etc/mysql/ssl

5.2 Network Isolation

networks:
  internal:
    internal: true
  external:
    driver: bridge

services:
  mysql:
    networks:
      - internal
  app:
    networks:
      - internal
      - external

6. Backup and Recovery Plan

6.1 Backup service configuration

services:
  mysql:
    # ...Original configuration...    volumes:
      - ./backups:/backups

  backup:
    image: mysql:8.0
    depends_on:
      - mysql
    volumes:
      - ./backups:/backups
    command: &gt;
      sh -c 'while true; do
        sleep 86400;
        mysqldump -h mysql -u root -p$$MYSQL_ROOT_PASSWORD --all-databases | gzip &gt; /backups/backup_$$(date +%Y%m%d).;
      done'
    environment:
      MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}

6.2 Recovery Process

Stop service:

docker-compose stop mysql

Recover data:

docker-compose run --rm mysql bash -c "gunzip < /backups/backup_20230101. | mysql -u root -p${DB_ROOT_PASSWORD}"

7. Performance optimization suggestions

7.1 Resource limitations and optimization

services:
  mysql:
    deploy:
      resources:
        limits:
          cpus: "4"
          memory: 8G
    environment:
      - MYSQL_INNODB_BUFFER_POOL_SIZE=4G
      - MYSQL_INNODB_LOG_FILE_SIZE=1G
      - MYSQL_TABLE_OPEN_CACHE=4000
      - MYSQL_MAX_CONNECTIONS=500

7.2 Monitoring integration

services:
  mysql:
    environment:
      - MYSQL_PERFORMANCE_SCHEMA=ON
    labels:
      - "=true"

  prometheus:
    image: prom/prometheus
    ports:
      - "9090:9090"
    volumes:
      - ./:/etc/prometheus/

8. Daily operation and maintenance management

8.1 Common Commands

Start the service:

docker-compose up -d

View the log:

docker-compose logs -f mysql

Perform a backup:

docker-compose exec mysql mysqldump -u root -p${DB_ROOT_PASSWORD} --all-databases >

Version 8.2 Upgrade Process

Backup data

Modified mirror version

Perform an update:

docker-compose pull mysql
docker-compose up -d --force-recreate mysql

This is the end of this article about the detailed tutorial on deploying MySQL through Docker Compose. For more related content on deploying MySQL in Docker Compose, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!