docker搭建mysql主从集群

docker mysql 主从复制

docker-compose.yml

version: "3"
services:
  master:
    image: mysql:8.0
    container_name: master
    restart: always
    volumes:
      - ./master/conf:/etc/mysql/conf.d:ro
      - ./master/data:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=123456
    ports:
      - 3316:3316
  slave:
    image: mysql:8.0
    container_name: slave
    restart: always
    volumes:
      - ./slave/conf:/etc/mysql/conf.d:ro
      - ./slave/data:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=123456
    ports:
      - 3317:3317

docker-compose up -d 

主库 master.conf

[mysqld]

bind_address = 0.0.0.0
## read-only=0 ## 1可读写0可读
server-id = 23 #IP 需要唯一
read-only=0 # 可读写
log_bin = mysql-bin # bin log
port = 3316
expire_logs_days = 20 #过期时间

max_binlog_size = 200M # 日志大小

binlog_do_db = test # 需要备份的库

# 不需要备份的库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

从库 slave.cnf

[mysqld]

bind_address = 0.0.0.0
## read-only=0 ## 1可读写0可读
server-id = 230 #IP 需要唯一
read-only=1 # 可读
log_bin = mysql-bin # bin log
port = 3317
expire_logs_days = 20 #过期时间

max_binlog_size = 200M # 日志大小

binlog_do_db = test # 需要备份的库

# 不需要备份的库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

由上述 master.cnf 和 slave.cnf 配置文件中可知，两者的区别主要在于 server_id 和 read-only 值不同
server_id 标识服务实例，master 和 slave 值需要保证唯一
read-only 中值为 0 表示支持可读写、1 的话表明仅支持可读

主库

创建用户

create user 'slave'@'%' identified by '123456';
GRANT REPLICATION slave ON *.* to 'slave'@'%';
## 修改 Authentication requires secure connection.
SELECT plugin FROM `user` where user = 'slave';
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

查看状态，记住File、Position的值，在Slave中将用到

show master status \G;

从库

• 连接到主库

change master to master_host='192.168.0.254',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=692,master_port=3316;

• 启动从库同步

start slave;

• 查看状态

show slave status\G;

slave_io_runing与slave_sql_running ：为yes说明正常工作

测试

• 主库

use test;
create  table test(id int primary key auto_increment,username varchar(100) default '');
show tables;
insert into test(username)values("a");
select * from test;

• 从库

use test;
show tables;
select * from test;