Docker Compose 部署 MySQL 集群

1. 基础配置文件

1.1. .env

MYSQL_VERSION=8.4.4

1.2. ./mysql1/conf.d/my.cnf

[mysqld]
server_id = 1

1.3. ./mysql2/conf.d/my.cnf

[mysqld]
server_id = 2

1.4. ./mysql3/conf.d/my.cnf

[mysqld]
server_id = 3

1.5. compose.yaml

name: mysql-cluster

services:
  mysql1:
    image: mysql:${MYSQL_VERSION}
    container_name: mysql1
    networks:
      - mysql-cluster
    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
    ports:
      - "3306:3306"
    volumes:
      - ./mysql1/conf.d:/etc/mysql/conf.d
      - ./mysql1/datadir:/var/lib/mysql
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci
    restart: on-failure:3
    healthcheck:
      test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
      interval: 5s
      timeout: 10s
      retries: 10
      start_period: 20s

  mysql2:
    image: mysql:${MYSQL_VERSION}
    container_name: mysql2
    networks:
      - mysql-cluster
    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
    ports:
      - "3307:3306"
    volumes:
      - ./mysql2/conf.d:/etc/mysql/conf.d
      - ./mysql2/datadir:/var/lib/mysql
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci
    restart: on-failure:3
    healthcheck:
      test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
      interval: 5s
      timeout: 10s
      retries: 10
      start_period: 20s

  mysql3:
    image: mysql:${MYSQL_VERSION}
    container_name: mysql3
    networks:
      - mysql-cluster
    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
    ports:
      - "3308:3306"
    volumes:
      - ./mysql3/conf.d:/etc/mysql/conf.d
      - ./mysql3/datadir:/var/lib/mysql
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci
    restart: on-failure:3
    healthcheck:
      test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
      interval: 5s
      timeout: 10s
      retries: 10
      start_period: 20s

networks:
  mysql-cluster:

2. GTID 三主半同步复制集群

Warning 使用建议

• 不要在多个实例上同时进行写操作。而是当作一主两从使用，或通过业务代码实现写分片；
• 搭配 VIP 实现自动故障转移；
• 不要依赖于数据库的自增 Id，改用分布式 Id 作为主键；

2.1. build.sh

#!/bin/bash
set -eo pipefail

for var in "MYSQL_ROOT_PASSWORD" "MYSQL_REPL_PASSWORD"; do
  [[ -z "${!var}" ]] && { echo "The environment variable $var is not set"; exit 1; }
done

wait_for_healthy() {
  for container in ${@:-$(docker compose ps --format '{{.Name}}' 2>/dev/null)}; do
    until [ "$(docker inspect -f '{{.State.Health.Status}}' "$container")" == "healthy" ]; do
      echo "Waiting for $container container to be healthy ..."
      sleep 1
    done
  done
}

# 启动所有实例
echo "Staring services ..."
docker compose up -d

wait_for_healthy

echo "Delay 5s ..."
sleep 5

for service in $(docker compose ps --services 2>/dev/null); do
  echo "Configuring $service ..."
  docker compose exec -T $service mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" << SQL
    CREATE USER 'repl'@'%' IDENTIFIED BY '${MYSQL_REPL_PASSWORD}';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
    FLUSH PRIVILEGES;
    INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
    INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SQL
done

for idx in $(seq 1 3); do
  echo "Modifying ./mysql${idx}/conf.d/my.cnf"

  cat << EOF >> ./mysql${idx}/conf.d/my.cnf

log_bin = binlog
binlog_format = ROW
log_replica_updates = ON
read_only = OFF

gtid_mode = ON
enforce_gtid_consistency = ON
auto_increment_offset = ${idx}
auto_increment_increment = 3

rpl_semi_sync_source_enabled = 1
rpl_semi_sync_source_timeout = 10000
rpl_semi_sync_source_wait_for_replica_count = 1
rpl_semi_sync_replica_enabled = 1
EOF
done

echo "Restarting services ..."
docker compose restart

wait_for_healthy

configure_replication() {
  local target=$1
  local source=$2

  echo "Changing $target replication source ..."
  docker compose exec -T $target mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" << SQL
    CHANGE REPLICATION SOURCE TO
      SOURCE_HOST='$source',
      SOURCE_USER='repl',
      SOURCE_PASSWORD='${MYSQL_REPL_PASSWORD}',
      SOURCE_SSL=0,
      GET_SOURCE_PUBLIC_KEY=1,
      SOURCE_AUTO_POSITION=1;
    START REPLICA;
SQL
}

configure_replication mysql2 mysql1
configure_replication mysql3 mysql2
configure_replication mysql1 mysql3

echo "Built successful."

2.2. 搭建集群

$ MYSQL_ROOT_PASSWORD=gFNQy6YPWCc3RgTLqj5N \
  MYSQL_REPL_PASSWORD=2NHBxbcfoYjP5VL0dd0A \
  sudo -E ./build.sh