mysql 主从复制

web

• linux粘贴保持格式 :set paste

一、成功案例

1. 创建一个 docker-compose.yml

2. docker-compose.yml 的内容

version: "3"
services:
  mysql-master:
    image: mysql:5.7.22
    restart: always
    container_name: mysql_master
    ports:
      - 8086:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    volumes:
      - ./master/mysql/data:/var/lib/mysql
      - ./master/mysql/log:/var/log/mysql
    command:
      --server-id=8086
      --log-bin=mysql-master-bin
    links:
      - mysql-slave-read
  mysql-slave-read:
    image: mysql:5.7.22
    restart: always
    container_name: mysql-slave-read
    ports:
      - 8087:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    volumes:
      - ./slave/mysql/data:/var/lib/mysql
      - ./slave/mysql/log:/var/log/mysql
    command:
      --server-id=8087
      --read_only=1

3. 运行 docker-compose

docker-compose up -d

• 运行成功
  

4. 主数据库配置流程

# 创建用户
grant replication slave on *.* to 'slave'@'%' identified by '123456';
# 锁库
FLUSH TABLES WITH  READ LOCK
# 查看状态
SHOW MASTER STATUS
# 解锁
UNLOCK TABLES

• 查看状态（拿到两个有用的值）
  

5. 从数据库配置流程

#停止
STOP SLAVE 
# 主数据库配置
change master to master_host='127.0.0.1',
master_user='root',
master_password='root',
master_port=3316,
# 主数据库状态中的值
master_log_file='mysql-master-bin.000004',
master_log_pos=430;
# 开启
START SLAVE
# 查看链接状态
SHOW SLAVE STATUS

• 成功的连接状态（两个yes就成功）
  

二、添加一个从数据库

1. 在 docker-compose 添加新的数据库容器

  # 服务名要改
  mysql-slave-read2:
    image: mysql:5.7.22
    restart: always
    # 容器名要改
    container_name: mysql-slave-read2
    ports:
      # 映射端口要改
      - 8087:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    volumes:
      # 映射目录要改
      - ./slave/mysql/data:/var/lib/mysql
      - ./slave/mysql/log:/var/log/mysql
    command:
      # id要改
      --server-id=8087
      --read_only=1

2. 主数据库 links 属性添加

    links:
      # 原有的
      - mysql-slave-read
      # 新增的
      - mysql-slave-read2

3. 重新运行 docker-compose

docker-compose up -d

三、 更多配置

version: "3"
services:
  mysql-master:
    image: mysql:5.7.22
    restart: always
    hostname: mysql-master
    container_name: mysql_master
    ports:
      - 3316:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    volumes:
      - ./master/mysql/data:/var/lib/mysql
      - ./master/mysql/log:/var/log/mysql
    command:
      --server_id=100
      --log-bin=mysql-master-bin
      --sync_binlog=1
      --binlog-ignore-db=mysql
      --binlog_format=mixed
      --expire_logs_days=7
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
    links:
      -  mysql-slave-read
  mysql-slave-read:
    image: mysql:5.7.22
    restart: always
    hostname: mysql-slave-read
    container_name: mysql-slave-read
    ports:
      - 3317:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    volumes:
      - ./slave/mysql/data:/var/lib/mysql
      - ./slave/mysql/log:/var/log/mysql
    command:
      --server_id=200
      --read_only=1
      --binlog-ignore-db=mysql
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
  mysql-slave-back:
    image: mysql:5.7.22
    restart: always
    hostname: mysql-slave-back
    container_name: mysql-slave-back
    ports:
      - 3318:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    volumes:
      - ./slave-back/mysql/data:/var/lib/mysql
      - ./slave-back/mysql/log:/var/log/mysql
    command:
      --server_id=300
      --log-bin=slave-back-bin
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1