Docker搭建MySQL 5.7一主两从主从复制架构

Docker搭建MySQL 5.7一主两从主从复制架构

本篇教程将带你从零开始，用Docker Compose快速搭建MySQL 5.7的一主两从主从复制架构，详细且易懂，帮你一步步实现数据自动同步与高可用监控。

一、环境准备

1. Docker 和 Docker Compose 安装

Docker和Compose必须预先安装，参考官方文档：Docker安装文档

检查安装：

docker -v
docker-compose -v

2. 创建项目结构

创建以下目录结构：

mysql-cluster/
├── docker-compose.yml
├── master/
│   └── my.cnf
├── slave1/
│   └── my.cnf
├── slave2/
│   └── my.cnf
└── init/
    └── init-replica.sql

二、核心配置

1. 主库配置（master/my.cnf）

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=row

2. 从库配置（slave1/my.cnf 和 slave2/my.cnf）

[mysqld]
server-id=2 # slave2 改为 server-id=3
relay-log=relay-log
read-only=1
super_read_only=1

3. 初始化从库SQL脚本（init/init-replica.sql）

CHANGE MASTER TO
  MASTER_HOST='mysql-master',
  MASTER_USER='replica',
  MASTER_PASSWORD='replica_pass',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;

注意：MASTER_LOG_FILE和MASTER_LOG_POS稍后更新。

三、Docker Compose 文件配置

version: '3.8'

services:
  master:
    image: mysql:5.7
    container_name: mysql-master
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
    ports:
      - "3307:3306"
    volumes:
      - ./master/my.cnf:/etc/mysql/conf.d/my.cnf
    networks:
      - mysql-net

  slave1:
    image: mysql:5.7
    container_name: mysql-slave1
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
    depends_on:
      - master
    volumes:
      - ./slave1/my.cnf:/etc/mysql/conf.d/my.cnf
      - ./init/init-replica.sql:/docker-entrypoint-initdb.d/init-replica.sql
    networks:
      - mysql-net

  slave2:
    image: mysql:5.7
    container_name: mysql-slave2
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
    depends_on:
      - master
    volumes:
      - ./slave2/my.cnf:/etc/mysql/conf.d/my.cnf
      - ./init/init-replica.sql:/docker-entrypoint-initdb.d/init-replica.sql
    networks:
      - mysql-net

networks:
  mysql-net:
    driver: bridge

四、启动并配置主库

启动容器：

docker-compose up -d

创建复制用户并查询主库binlog位置：

docker exec -it mysql-master mysql -uroot -prootpass

mysql> CREATE USER 'replica'@'%' IDENTIFIED BY 'replica_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

记下 File 和 Position 值，更新init-replica.sql。

五、初始化从库同步

清理并重启从库让它们重新执行初始化SQL：

docker-compose rm -svf slave1 slave2 && docker-compose up -d slave1 slave2

验证同步状态：

docker exec -it mysql-slave1 mysql -uroot -prootpass -e "SHOW SLAVE STATUS\G"
docker exec -it mysql-slave2 mysql -uroot -prootpass -e "SHOW SLAVE STATUS\G"

出现以下表示成功：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

六、主从健康监控

创建监控脚本check_slave_status.sh：

#!/bin/bash
SLAVES=("mysql-slave1" "mysql-slave2")
MYSQL_USER="root"
MYSQL_PASS="rootpass"
LAG_THRESHOLD=30

for SLAVE in "${SLAVES[@]}"; do
  STATUS=$(docker exec -i "$SLAVE" mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW SLAVE STATUS\G")

  IO_RUNNING=$(echo "$STATUS" | awk -F': ' '/Slave_IO_Running:/ {print $2}' | tr -d '\r')
  SQL_RUNNING=$(echo "$STATUS" | awk -F': ' '/Slave_SQL_Running:/ {print $2}' | tr -d '\r')
  SECONDS_BEHIND=$(echo "$STATUS" | awk -F': ' '/Seconds_Behind_Master:/ {print $2}' | tr -d '\r')

  if [[ "$IO_RUNNING" != "Yes" || "$SQL_RUNNING" != "Yes" ]]; then
    echo "[ALERT] $SLAVE replication thread error!"
  elif [[ "$SECONDS_BEHIND" -gt "$LAG_THRESHOLD" ]]; then
    echo "[WARNING] $SLAVE replication lagging ($SECONDS_BEHIND sec)!"
  else
    echo "$SLAVE replication is healthy."
  fi
done

赋权并执行：

chmod +x check_slave_status.sh
./check_slave_status.sh

加入crontab定时监控即可。

通过以上步骤，你就成功搭建了一套稳定可靠、可监控的MySQL一主两从复制集群！