概述
本文档详细介绍了如何使用 Docker Compose 搭建 MySQL 8.0 的主从复制集群，包含 1 个主节点（Master）和 2 个从节点（Slave），实现数据自动复制、读写分离和高可用。
系统要求
- Docker Engine 19.03+
- Docker Compose v2.0+
- 至少 4GB 可用内存
- 有足够的磁盘空间存储数据
文件结构
准备以下文件：
mysql.yml– Docker Compose 配置文件setup-users.sql– 初始化用户脚本init-replication.sh– 初始化复制脚本
步骤 1：创建 Docker Compose 配置文件
创建 mysql.yml 文件：
# MySQL 主从复制集群 Docker Compose 配置
# 📅 创建日期：2026-04-01
# 📌 端口：3306 (主), 3307 (从 1), 3308 (从 2)
# 🎯 架构：1 主节点 + 2 从节点
# 🔗 特性：数据自动复制、读写分离、高可用
version: '3.8'
services:
# ==================== 主节点 (Master) ====================
mysql-master:
image: mysql:8.0
container_name: mysql-master
restart: always
environment:
MYSQL_ROOT_PASSWORD: root123456
MYSQL_DATABASE: default_db
MYSQL_USER: dbuser
MYSQL_PASSWORD: dbpass123
TZ: Asia/Shanghai
ports:
- "0.0.0.0:3306:3306"
volumes:
- ~/docker-data/mysql/master/data:/var/lib/mysql:z
- ~/docker-data/mysql/master/logs:/var/log/mysql:z
- ~/docker-data/mysql/master/conf:/etc/mysql/conf.d:z
- ./setup-users.sql:/docker-entrypoint-initdb.d/setup-users.sql:ro
command:
- --server-id=1
- --log-bin=mysql-bin
- --binlog-format=ROW
- --gtid-mode=ON
- --enforce-gtid-consistency=ON
- --character-set-server=utf8mb4
- --collation-server=utf8mb4_unicode_ci
networks:
- mysql-network
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-proot123456"]
interval: 30s
timeout: 10s
retries: 3
# ==================== 从节点 1 (Slave 1) ====================
mysql-slave-1:
image: mysql:8.0
container_name: mysql-slave-1
restart: always
environment:
MYSQL_ROOT_PASSWORD: root123456
MYSQL_DATABASE: default_db
MYSQL_USER: dbuser
MYSQL_PASSWORD: dbpass123
TZ: Asia/Shanghai
ports:
- "0.0.0.0:3307:3306"
volumes:
- ~/docker-data/mysql/slave-1/data:/var/lib/mysql:z
- ~/docker-data/mysql/slave-1/logs:/var/log/mysql:z
- ~/docker-data/mysql/slave-1/conf:/etc/mysql/conf.d:z
- ./setup-users.sql:/docker-entrypoint-initdb.d/setup-users.sql:ro
command:
- --server-id=2
- --relay-log=mysql-relay-bin
- --read-only=ON
- --gtid-mode=ON
- --enforce-gtid-consistency=ON
- --character-set-server=utf8mb4
- --collation-server=utf8mb4_unicode_ci
networks:
- mysql-network
depends_on:
- mysql-master
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-proot123456"]
interval: 30s
timeout: 10s
retries: 3
# ==================== 从节点 2 (Slave 2) ====================
mysql-slave-2:
image: mysql:8.0
container_name: mysql-slave-2
restart: always
environment:
MYSQL_ROOT_PASSWORD: root123456
MYSQL_DATABASE: default_db
MYSQL_USER: dbuser
MYSQL_PASSWORD: dbpass123
TZ: Asia/Shanghai
ports:
- "0.0.0.0:3308:3306"
volumes:
- ~/docker-data/mysql/slave-2/data:/var/lib/mysql:z
- ~/docker-data/mysql/slave-2/logs:/var/log/mysql:z
- ~/docker-data/mysql/slave-2/conf:/etc/mysql/conf.d:z
- ./setup-users.sql:/docker-entrypoint-initdb.d/setup-users.sql:ro
command:
- --server-id=3
- --relay-log=mysql-relay-bin
- --read-only=ON
- --gtid-mode=ON
- --enforce-gtid-consistency=ON
- --character-set-server=utf8mb4
- --collation-server=utf8mb4_unicode_ci
networks:
- mysql-network
depends_on:
- mysql-master
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-proot123456"]
interval: 30s
timeout: 10s
retries: 3
networks:
mysql-network:
driver: bridge
步骤 2：创建用户初始化脚本
创建 setup-users.sql 文件：
-- 初始化脚本，确保root用户可以从任何主机连接
-- 并创建普通用户
-- 为root用户创建从任意主机访问的账户
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY 'root123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
-- 确保dbuser在所有节点上都存在
CREATE USER IF NOT EXISTS 'dbuser'@'%' IDENTIFIED BY 'dbpass123';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'dbuser'@'%';
FLUSH PRIVILEGES;
-- 也保留localhost访问权限
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123456';
FLUSH PRIVILEGES;
步骤 3：创建复制初始化脚本
创建 init-replication.sh 文件：
#!/bin/bash
# MySQL 主从配置初始化脚本
# 📅 创建日期：2026-04-01
# 用途：配置主从复制关系
set -e
echo "🔧 开始配置 MySQL 主从复制..."
echo ""
# 等待主节点就绪
echo "⏳ 等待主节点就绪..."
for i in {1..30}; do
if docker exec mysql-master mysql -u root -proot123456 -e "SELECT 1" > /dev/null 2>&1; then
echo "✅ 主节点已就绪"
break
fi
sleep 2
done
# 等待从节点就绪
echo "⏳ 等待从节点就绪..."
for i in {1..30}; do
if docker exec mysql-slave-1 mysql -u root -proot123456 -e "SELECT 1" > /dev/null 2>&1; then
echo "✅ 从节点1已就绪"
break
fi
sleep 2
done
# 等待从节点2就绪
echo "⏳ 等待从节点2就绪..."
for i in {1..30}; do
if docker exec mysql-slave-2 mysql -u root -proot123456 -e "SELECT 1" > /dev/null 2>&1; then
echo "✅ 从节点2已就绪"
break
fi
sleep 2
done
# 创建复制用户
echo "🔐 创建复制用户..."
docker exec mysql-master mysql -u root -proot123456 <<EOF
CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'repl123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
EOF
# 获取主节点位置
echo "📊 获取主节点位置..."
MASTER_STATUS=$(docker exec mysql-master mysql -u root -proot123456 -e "SHOW MASTER STATUS\G")
MASTER_FILE=$(echo "$MASTER_STATUS" | grep "File:" | awk '{print $2}')
MASTER_POS=$(echo "$MASTER_STATUS" | grep "Position:" | awk '{print $2}')
if [ -z "$MASTER_FILE" ] || [ -z "$MASTER_POS" ]; then
echo "❌ 无法获取主节点状态"
exit 1
fi
echo "✅ 主节点位置：$MASTER_FILE:$MASTER_POS"
# 配置从节点 1
echo "🔗 配置从节点 1 (mysql-slave-1)..."
docker exec mysql-slave-1 mysql -u root -proot123456 <<EOF
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='repl',
MASTER_PASSWORD='repl123456',
MASTER_LOG_FILE='$MASTER_FILE',
MASTER_LOG_POS=$MASTER_POS,
GET_MASTER_PUBLIC_KEY=1;
START SLAVE;
EOF
# 配置从节点 2
echo "🔗 配置从节点 2 (mysql-slave-2)..."
docker exec mysql-slave-2 mysql -u root -proot123456 <<EOF
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='repl',
MASTER_PASSWORD='repl123456',
MASTER_LOG_FILE='$MASTER_FILE',
MASTER_LOG_POS=$MASTER_POS,
GET_MASTER_PUBLIC_KEY=1;
START SLAVE;
EOF
# 等待同步
echo "⏳ 等待复制同步..."
sleep 5
# 验证复制状态
echo ""
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo "📊 验证主从状态"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo ""
echo "📌 主节点状态:"
docker exec mysql-master mysql -u root -proot123456 -e "SHOW MASTER STATUS\G"
echo ""
echo "📌 从节点 1 状态:"
docker exec mysql-slave-1 mysql -u root -proot123456 -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Master_Host"
echo ""
echo "📌 从节点 2 状态:"
docker exec mysql-slave-2 mysql -u root -proot123456 -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Master_Host"
echo ""
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo "✅ 主从配置完成！"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
步骤 4：设置文件权限
chmod +x init-replication.sh
步骤 5：启动集群
# 创建数据目录
mkdir -p ~/docker-data/mysql/{master,slave-1,slave-2}/{data,logs,conf}
chmod -R 777 ~/docker-data/mysql
# 启动集群
docker compose -f mysql.yml up -d
步骤 6：初始化主从复制
./init-replication.sh
连接信息
账户信息
- Root 用户密码:
root123456 - 普通用户:
dbuser/dbpass123
端口映射
- 主节点 (读写): 3306
- 从节点1 (只读): 3307
- 从节点2 (只读): 3308
连接示例
# 主节点（写操作）
mysql -h 127.0.0.1 -P 3306 -u root -proot123456
# 从节点（读操作）
mysql -h 127.0.0.1 -P 3307 -u root -proot123456
mysql -h 127.0.0.1 -P 3308 -u root -proot123456
# 普通用户连接
mysql -h 127.0.0.1 -P 3306 -u dbuser -pdbpass123
验证复制
检查主节点状态
docker exec mysql-master mysql -u root -proot123456 -e "SHOW MASTER STATUS;"
检查从节点状态
docker exec mysql-slave-1 mysql -u root -proot123456 -e "SHOW SLAVE STATUS\G;"
测试数据同步
在主节点创建表并插入数据，然后在从节点验证数据是否同步。
常见问题
1. Public Key Retrieval 错误
如果应用程序连接时出现 “Public Key Retrieval is not allowed” 错误，请在连接字符串中添加 allowPublicKeyRetrieval=true 参数。
2. 从节点无法连接
检查复制用户权限和网络连接，确保主节点的复制用户允许从从节点IP连接。
3. 复制延迟
使用 SHOW SLAVE STATUS\G; 检查 Seconds_Behind_Master 字段确认延迟。
管理命令
停止集群
docker compose -f mysql.yml down
查看日志
docker logs mysql-master
docker logs mysql-slave-1
docker logs mysql-slave-2
备份数据
docker exec mysql-master mysqldump -u root -proot123456 --all-databases > backup.sql
注意事项
- 安全性: 生产环境请使用更强的密码
- 备份: 定期备份主节点数据
- 监控: 监控复制延迟和错误
- 容量: 确保从节点有足够存储空间
- 网络: 保证主从节点间网络稳定
这个配置提供了高可用的 MySQL 主从复制集群，适合读多写少的应用场景，如 WordPress 博客等。
