以下步骤仅适用新搭建主从复制,历史数据不做处理
1. 准备master库文件
master数据库compose文件, 保存为mysql-master.yml文件
version: '3.8'
services:
mysql-master:
image: mysql:5.7.36
container_name: mysql-master
restart: always
privileged: true
ports:
- "3308:3308"
volumes:
- /home/data-volume/mysql-5.7.36/config/my.cnf:/etc/my.cnf
- /home/data-volume/mysql-5.7.36/data:/var/lib/mysql
environment:
- "MYSQL_ROOT_PASSWORD=123456"
networks:
- my_net
deploy:
placement:
constraints:
- node.role == worker
networks:
my_net:
external: true
master数据库my.cnf文件
[client]
port = 3308
default-character-set=utf8
[mysqld]
datadir = /var/lib/mysql/data
basedir = /var/lib/mysql
port = 3308
character-set-server=utf8
default_storage_engine = InnoDB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
log-bin=mysql-bin #开启日志
server-id=10 #唯一编号
expire_logs_days=15 #日志保存天数
2. 准备slave库文件(和master大同小异)
slave数据库compose文件,保存为mysql-slave.yml文件
version: '3.8'
services:
mysql-slave:
image: mysql:5.7.36
container_name: mysql-slave
restart: always
privileged: true
ports:
- "3308:3308"
volumes:
- /home/mysql-slave/config/my.cnf:/etc/my.cnf
- /home/mysql-slave/data:/var/lib/mysql
environment:
- "MYSQL_ROOT_PASSWORD=123456"
networks:
- my-network
networks:
my-network:
external: true
slave库my.cnf文件
[client]
port = 3308
default-character-set=utf8
[mysqld]
datadir = /var/lib/mysql/data
basedir = /var/lib/mysql
port = 3308
character-set-server=utf8
default_storage_engine = InnoDB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
3. 分别启动两个mysql服务
swarm集群命令启动
docker stack deploy -c mysql-master.yml mysql-master
确保mysql服务启动正常,并可以root连接
4. master数据库创建主从复制账号,并赋予复制权限
-- 创建用户
CREATE USER 'my_slave'@'%' IDENTIFIED BY '123456';
-- 赋予复制数据权限
grant replication slave on *.* to 'my_slave'@'%' IDENTIFIED BY '123456';
-- 刷新授权,使之立即生效
flush privileges;
5. slave库执行
change master to
master_host='master库ip',
master_port=端口,
master_user='my_slave',
master_password='123456',
master_log_file ='mysql-bin.000004',
master_log_pos=2452,
master_connect_retry=120;
*说明:
master_log_file master_log_pos 具体内容可在master数据库执行以下命令查看
show master status;
分别填写查询结果 File,Position字段内容
执行命令,开启复制
start slave;
6. 安装验证
- slave库数据库文件目录会出现 master.info 文件
2. slave 库执行命令
show slave status;
确保 Slave_IO_Running ,Slave_SQL_Running 两个都为YES
7. 取消主从配置
如主从配置有问题,可执行命令取消主从设置,从新配置
stop slave;
reset slave all;
8. 相关命令整理
show master status 显示master二进制日志文件信息
show slave status 显示slave从库复制信息
start slave 开启主从复制
stop slave 关闭主从复制
reset slave all 重置主从复制配置