年度归档 07-31

mysql主从数据库 docker环境

1.配置主库

master.cnf

[mysqld]
# 同一局域网内注意要唯一
server-id=100
# 开启二进制日志功能，可以随便取
log-bin=mysql-bin

查看二进制日志是否开启 
SHOW GLOBAL VARIABLES LIKE '%log%';
查看主节点的server id
SHOW GLOBAL VARIABLES LIKE '%server%';
创建有复制权限的用户slave
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%' IDENTIFIED by '123456';
刷新配置
FLUSH PRIVILEGES;
查看File和Position字段的值
show master status;

2.配置从库

slave.cnf

[mysqld]
server-id=101
#开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin
#relay_log配置中继日志
relay_log=edu-mysql-relay-bin

停止主从复制
stop slave;
设置主数据库信息
change master to master_host='172.17.0.5', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=856, master_connect_retry=30;
启动主从复制
start slave;
查看主从同步状态
show slave status;

设置从库只读

# 开启只读
show global variables like "%read_only%";
# 对所有用户生效，包括super用户（不建议使用）
flush tables with read lock;
# 只对普通用户生效，如slave用户
set global read_only=1;
show global variables like "%read_only%";
# 关闭只读
unlock tables;
set global read_only=0;

Docker配置

创建 /app/docker_files/mysql 文件夹

docker-compose.yml 配置文件 放在 /app/docker_files目录下

version: '3'
services:    
  db-master:
    build: './mysql'
    image: 'db-server:1.0'
    container_name: 'db-master'
    ports:
      - "3366:3306"
    volumes:
      - "/app/docker_files/mysql/conf/master.cnf:/etc/mysql/conf.d/master.cnf"
      - "/app/docker_files/mysql/data:/var/lib/mysql"
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: qwe123!@#
    command: ["mysqld", "--server-id=1", "--log-bin=mysql-bin"]
    networks:
      - lnmp  
  db-slave:
    build: './mysql'
    image: 'db-server:1.0'
    container_name: 'db-slave'
    ports:
      - "3307:3306"
    volumes:
      - "/app/docker_files/mysql/conf/slave.cnf:/etc/mysql/conf.d/slave.cnf"
      - "/app/docker_files/mysql/slave-data:/var/lib/mysql"
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: qwe123!@#
    command: ["mysqld", "--server-id=2"]
    networks:
      - lnmp  
networks:
  lnmp:
    driver: bridge