mysql主从

机器两台

ip: 192.168.18.200 master
ip: 192.168.18.201 slave

1master 1slave

master

docker exec -ti mysql8_master /bin/bash
mysql -uroot -p

# 创建用户,设置主从同步的账户名
create user 'syncuser'@'%' identified with mysql_native_password by '123456';
# 授权
grant replication slave on *.* to 'syncuser'@'%';
# 刷新权限
flush privileges;

show variables like 'server_id';

# 查询 Master 状态，并记录 File 和 Position 的值，这两个值用于和下边的从数据库中的 change 那条 sql 中的 master_log_file，master_log_pos 参数对齐使用
show master status;
# 重置下 master 的 binlog 位点
reset master;

show master status;

slave

docker exec -ti mysql8_slave /bin/bash
mysql -uroot -p
# 这里master_host就是刚刚看到的master的ip，master_user就是我们创建用于同步的账号，master_log_file和master_log_pos就是通过show master status获得到的
CHANGE MASTER TO MASTER_HOST='192.168.18.200', MASTER=3306, MASTER_USER='syncuser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=156;
#开启同步
START SLAVE;
#查看slave同步状态
SHOW SLAVE STATUS\G;

# 最后需要查看是否配置成功
# 查看参数 Slave_IO_Running 和 Slave_SQL_Running 是否都为 yes，则证明配置成功。若为 no，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异 常值。

docker-compose.yml见：

version: "3"

services:
  mysql8_master:
    image: mysql:8.0.27
    container_name: mysql8_master
    restart: always
    ports:
      - "3306:3306"
    command:
      --server-id=200
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_unicode_ci
      --log-bin=mysql-bin
      --sync_binlog=1
      --binlog-ignore-db="mysql,information_schema,performance_schema,sys"
      --max_allowed_packet=128M
      --lower_case_table_names=1
      --performance_schema=off
      --innodb_buffer_pool_size=64M
      --expire_logs_days=7
      --sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_BACKSLASH_ESCAPES"
    environment:
      - TZ=Asia/Shanghai
      - LANG=en_US.utf8
      - MYSQL_ROOT_PASSWORD=123456
      - MYSQL_DATABASE=db0
#      - MYSQL_USER=abcdweg
#      - MYSQL_PASSWORD=123456
    volumes:
      - /opt/docker_data/mysql8/var_lib_mysql:/var/lib/mysql

version: "3"

services:
  mysql8_slave:
    image: mysql:8.0.27
    container_name: mysql8_slave
    restart: always
    ports:
      - "3306:3306"
    command:
      --server-id=201
      --log-bin=mysql-slave-bin
      --relay-log=mysql-slave-relay-bin
      --read-only=1
      --binlog-ignore-db="mysql,information_schema,performance_schema,sys"
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_unicode_ci
      --max_allowed_packet=128M
      --lower_case_table_names=1
      --performance_schema=off
      --innodb_buffer_pool_size=64M
      --expire_logs_days=7
      --sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_BACKSLASH_ESCAPES"
    environment:
      - TZ=Asia/Shanghai
      - LANG=en_US.utf8
      - MYSQL_ROOT_PASSWORD=123456
      - MYSQL_DATABASE=db0
#      - MYSQL_USER=abcdweg
#      - MYSQL_PASSWORD=123456
    volumes:
      - /opt/docker_data/mysql8/var_lib_mysql:/var/lib/mysql