2026-05-27T08:45:43.566ZDockerMysql
搭建高可用Mysql数据库，2主3从，读写分离
高可用Mysql数据库
多节点 MySQL 最常见的是 2主3从，读写分离（主从复制）：主库负责写，从库负责读或备份。
目录结构
mysql/
├── docker-compose.yaml
├── .env
├── master1/
│ ├── mysql.cnf
│ └── data/
├── master2/
│ ├── mysql.cnf
│ └── data/
├── slave1/
│ ├── mysql.cnf
│ └── data/
└── slave2/
│ ├── mysql.cnf
│ └── data/
└── slave3/
├── mysql.cnf
└── data/
- 配置.env
# ===== 必填：数据库密码 =====
MYSQL_ROOT_PASSWORD=root密码
MYSQL_PASSWORD=用户密码
# ===== 主库初始化（仅 master 使用）=====
MYSQL_DATABASE=数据库
MYSQL_USER=数据库用户
# ===== 端口映射（宿主机端口）=====
MYSQL_MASTER1_PORT=3306
MYSQL_MASTER2_PORT=3307
MYSQL_SLAVE1_PORT=3308
MYSQL_SLAVE2_PORT=3309
MYSQL_SLAVE3_PORT=3310
# ===== 主从复制用户（手动 SQL）=====
MYSQL_REPL_USER=repl
MYSQL_REPL_PASSWORD=复制密码
- 创建各节点 mysql.cnf，一定要启用 GTID（推荐长期使用）
- master1/mysql.cnf
[mysqld]
server-id=1
relay-log=relay-bin
read-only=1
super-read-only=1
gtid_mode=ON
enforce-gtid-consistency=ON
bind-address =0.0.0.0
# 双主自增 ID 错开（两主都写时建议开启）
auto_increment_increment=2
auto_increment_offset=1
max_connections=500
innodb_buffer_pool_size=1024M
sync_binlog=1
innodb_flush_log_at_trx_commit=1
- master2/mysql.cnf
[mysqld]
server-id=2
relay-log=relay-bin
read-only=1
super-read-only=1
gtid_mode=ON
enforce-gtid-consistency=ON
bind-address =0.0.0.0
auto_increment_increment=2
auto_increment_offset=1
max_connections=500
innodb_buffer_pool_size=1024M
sync_binlog=1
innodb_flush_log_at_trx_commit=1
- slave1/mysql.cnf
[mysqld]
server-id=3
relay-log=relay-bin
read-only=1
super-read-only=1
gtid_mode=ON
enforce-gtid-consistency=ON
bind-address =0.0.0.0
max_connections=500
innodb_buffer_pool_size=1024M
replica_parallel_workers=4
- slave2/mysql.cnf
[mysqld]
server-id=4
relay-log=relay-bin
read-only=1
super-read-only=1
gtid_mode=ON
enforce-gtid-consistency=ON
bind-address =0.0.0.0
max_connections=500
innodb_buffer_pool_size=1024M
replica_parallel_workers=4
- slave3/mysql.cnf
[mysqld]
server-id=5
relay-log=relay-bin
read-only=1
super-read-only=1
gtid_mode=ON
enforce-gtid-consistency=ON
bind-address =0.0.0.0
max_connections=500
innodb_buffer_pool_size=1024M
replica_parallel_workers=4
- 创建compose配置
services:
mysql-master-1:
image: mysql:8
container_name: mysql-master-1
restart: unless-stopped
env_file:
- .env
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:?请在 .env 中设置 MYSQL_ROOT_PASSWORD}
volumes:
- ./master1/my.cnf:/etc/mysql/conf.d/my.cnf:ro
- ./master1/data:/var/lib/mysql
ports:
- "${MYSQL_MASTER1_PORT:-3306}:3306"
networks:
- mysql
mysql-master-2:
image: mysql:8
container_name: mysql-master-2
restart: unless-stopped
env_file:
- .env
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:?请在 .env 中设置 MYSQL_ROOT_PASSWORD}
volumes:
- ./master2/my.cnf:/etc/mysql/conf.d/my.cnf:ro
- ./master2/data:/var/lib/mysql
depends_on:
- mysql-master-1
ports:
- "${MYSQL_MASTER2_PORT:-3307}:3306"
networks:
- mysql
mysql-slave-1:
image: mysql:8
container_name: mysql-slave-1
restart: unless-stopped
env_file:
- .env
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:?请在 .env 中设置 MYSQL_ROOT_PASSWORD}
volumes:
- ./slave1/my.cnf:/etc/mysql/conf.d/my.cnf:ro
- ./slave1/data:/var/lib/mysql
depends_on:
- mysql-master-1
- mysql-master-2
ports:
- "${MYSQL_SLAVE1_PORT:-3308}:3306"
networks:
- mysql
mysql-slave-2:
image: mysql:8
container_name: mysql-slave-2
restart: unless-stopped
env_file:
- .env
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:?请在 .env 中设置 MYSQL_ROOT_PASSWORD}
volumes:
- ./slave2/my.cnf:/etc/mysql/conf.d/my.cnf:ro
- ./slave2/data:/var/lib/mysql
depends_on:
- mysql-master-1
- mysql-master-2
ports:
- "${MYSQL_SLAVE2_PORT:-3309}:3306"
networks:
- mysql
mysql-slave-3:
image: mysql:8
container_name: mysql-slave-3
restart: unless-stopped
env_file:
- .env
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:?请在 .env 中设置 MYSQL_ROOT_PASSWORD}
volumes:
- ./slave3/my.cnf:/etc/mysql/conf.d/my.cnf:ro
- ./slave3/data:/var/lib/mysql
depends_on:
- mysql-master-1
- mysql-master-2
ports:
- "${MYSQL_SLAVE3_PORT:-3310}:3306"
networks:
- mysql
networks:
mysql:
driver: bridge
- 初始化配置(核心配置)
- 启动容器
docker compose up -d
- 在master1创建复制用户
docker exec -it mysql-master /bin/bash
mysql -uroot -p
CREATE USER 'synchronization'@'%' IDENTIFIED WITH caching_sha2_password BY '复制密码';
GRANT REPLICATION SLAVE ON *.* TO 'synchronization'@'%';
FLUSH PRIVILEGES
- 主库查看GID
SHOW BINARY LOG STATUS\G
- master2 从 master1 同步（双主第一步）
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='mysql-master-1',
SOURCE_USER='synchronization',
SOURCE_PASSWORD='复制密码',
SOURCE_AUTO_POSITION=1,
GET_SOURCE_PUBLIC_KEY=1;
- 开启同步
START REPLICA;
- 查看同步状态
SHOW REPLICA STATUS\G
- 确保从节点几个字段是正常的，那就配置正确，其他节点一样
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Last_Errno: 0
Seconds_Behind_Source: 0
Retrieved_Gtid_Set: eed21157-xxx:1-18
Executed_Gtid_Set: eed21157-xxx:1-18
Auto_Position: 1
- master1 反向指向 master2（双主第二步）
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='mysql-master-2',
SOURCE_USER='synchronization',
SOURCE_PASSWORD='FKy2Wc5wMkBMjPjadbkQaN3zkpUpmBHY',
SOURCE_AUTO_POSITION=1,
GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;
SHOW REPLICA STATUS\G
- mysql-slave-1和mysql-slave-1从master1复制
STOP REPLICA;
RESET REPLICA ALL;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='mysql-master-1',
SOURCE_USER='synchronization',
SOURCE_PASSWORD='复制密码',
SOURCE_AUTO_POSITION=1,
GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;
SHOW REPLICA STATUS\G
- mysql-slave-3从master2复制
STOP REPLICA;
RESET REPLICA ALL;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='mysql-master-2',
SOURCE_USER='synchronization',
SOURCE_PASSWORD='复制密码',
SOURCE_AUTO_POSITION=1,
GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;
SHOW REPLICA STATUS\G
- 测试数据同步效果
- 在master1库里面创建一个demo库
CREATE DATABASE demo;
use demo;
- 创建example_table 表
CREATE TABLE example_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
- 插入一条数据
INSERT INTO example_table (name) VALUES ('Demo Data');
- 确保数据插入成功
SELECT * FROM example_table;
- 登录mysql-master-2/mysql-slave-1/mysql-slave-2/mysql-slave-3查看数据是否复制过来
