【docker】搭建mysql一主多从集群

首先创建网络
docker 创建 网络

docker network create mysql_db

启动mysql

docker run -d -p 3307:3306 --name mysql_masterA \
-v /Users/myData/mysql_masterA/log:/var/log/mysql \
-v /Users/myData/mysql_masterA/data:/var/lib/mysql \
-v /Users/myData/mysql_masterA/conf:/etc/mysql/conf.d \
--restart=always \
 --network mysql_db \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:latest

进入容器

docker exec -it mysql_masterA bash

--更改root用户登录地点限制
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

--刷新权限
FLUSH PRIVILEGES;

--本地连接出现报错 
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

FLUSH PRIVILEGES;

ALTER USER 'root'@'%' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

--创建slave账号
create user 'slave'@'%' identified with mysql_native_password by 'slave123456';

grant replication slave on *.* to 'slave'@'%';

flush privileges;

-- 查看master状态，并记录file和position的值
show master status;

启动slaveA服务

docker run -d -p 3308:3306 --name mysql_slaveA \
-v /Users/myData/mysql_slaveA/log:/var/log/mysql \
-v /Users/myData/mysql_slaveA/data:/var/lib/mysql \
-v /Users/myData/mysql_slaveA/conf:/etc/mysql/conf.d \
--restart=always \
 -e MYSQL_ROOT_PASSWORD=123456 \
 -e MYSQL_REPLICATION_USER=slave:slave123456 \
 -e MYSQL_MASTER_HOST=mysql-master \
 -e MYSQL_MASTER_PORT=3306 \
 -e MYSQL_MASTER_LOG_FILE=binlog.000002 \
 -e MYSQL_MASTER_LOG_POS=1727 \
 --network mysql_db \
 -d mysql:latest

启动slaveB服务

docker run -d -p 3309:3306 --name mysql_slaveB \
-v /Users/myData/mysql_slaveB/log:/var/log/mysql \
-v /Users/myData/mysql_slaveB/data:/var/lib/mysql \
-v /Users/myData/mysql_slaveB/conf:/etc/mysql/conf.d \
--restart=always \
 -e MYSQL_ROOT_PASSWORD=123456 \
 -e MYSQL_REPLICATION_USER=slave:slave123456 \
 -e MYSQL_MASTER_HOST=mysql-master \
 -e MYSQL_MASTER_PORT=3306 \
 -e MYSQL_MASTER_LOG_FILE=binlog.000002 \
 -e MYSQL_MASTER_LOG_POS=1727 \
 --network mysql_db \
 -d mysql:latest

进入 slaveA和slaveB服务，配置master信息

CHANGE MASTER TO MASTER_HOST='mysql_masterA', MASTER_USER='slave', MASTER_PASSWORD='slave123456', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1727;
START SLAVE;
SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes，Slave_SQL_Running: Yes，两个yes则同步成功，否则查看相应的报错

macOS查看容器网络ip
docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mysql_masterA

master的my.cnf文件配置

[client]
default-character-set=utf8mb4
 
[mysql]
default-character-set=utf8mb4
 
[mysqld]
init_connect="SET collation_connection = utf8mb4_unicode_ci"
init_connect="SET NAMES utf8mb4"
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
# 主服务器唯一ID
server-id=1

slave的my.cnf文件配置

[client]
default-character-set=utf8mb4
 
[mysql]
default-character-set=utf8mb4
 
[mysqld]
init_connect="SET collation_connection = utf8mb4_unicode_ci"
init_connect="SET NAMES utf8mb4"
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
# 主服务器唯一ID,每个配置文件都需要修改server-id
server-id=3
# 只读
read_only=1