mysql 8.4.2 主从配置

mysql 8.4.2 主从配置

背景知识

密码方式

mysql 的默认的密码方式"caching_sha2_password"，以前常用的“mysql_native_password”方式在mysql v8.4中是disabled。使用“caching_sha2_password” 做主从的时候是比较复杂的，需要开启ssl，所以开启“mysql_native_password”是一个偷懒的选择。

log-bin

用docker启动master和slave

docker-compose.yml：

services:
  master:
    image: mysql:8.4.2
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: your_password
    volumes:
      - ./master/data:/var/lib/mysql
      - ./master/log:/var/log
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --mysql_native_password=on # 指定密码方式
      --server-id=1
      --log-bin=binlog
      --binlog_do_db=test # 可以指定多个数据库，逗号分割，或者删除这一条目，不做限制      
    restart: always
  slave:
    image: mysql:8.4.2
    ports:
      - "3307:3306"
    environment:
      MYSQL_ROOT_PASSWORD: your_password
    volumes:
      - ./slave/data:/var/lib/mysql
      - ./slave/log:/var/log
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --mysql_native_password=on
      --server-id=2
      --log-bin=binlog
    restart: always

执行：

sudo docker-compose up -d

master 配置

# 创建可以进行备份的账户
CREATE USER 'repl'@'%' IDENTIFIED with mysql_native_password BY 'repl-password';
# CREATE USER 'repl'@'%' IDENTIFIED with caching_sha2_password BY 'repl-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
# 查看LOG信息
SHOW BINARY LOG STATUS;
# 查看server-id
show variables like '%server%';

slave配置

# 启动备份服务
STOP REPLICA;
RESET REPLICA;  
# 指向master
CHANGE REPLICATION SOURCE TO 
	SOURCE_USER='repl', 
	SOURCE_PASSWORD='repl-password', 
	SOURCE_HOST='10.200.60.99',
	SOURCE_PORT=3306,
	SOURCE_LOG_FILE='binlog.000005',
	SOURCE_LOG_POS=1735,
	SOURCE_SSL = 1; # 使用caching_sha2_password，需要开启
	
# 启动从机	
START REPLICA;
# 查看从机状态，Replica_IO_Runing && Replica_SQL_Runing == true, 代表配置成功，就可以操作验证了。
SHOW REPLICA STATUS;
# 查看server-id
show variables like '%server%';

gtid

用docker启动master和slave

docker-compose.yml：

services:
  master:
    image: mysql:8.4.2
    ports:
      - "3310:3306"
    environment:
      MYSQL_ROOT_PASSWORD: your_password
    volumes:
      - ./master/data:/var/lib/mysql
      - ./master/log:/var/log
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --mysql_native_password=on
      --gtid_mode=ON
      --enforce_gtid_consistency=true
      --server_id=1
      --log-bin=binlog
      --binlog_format=row
      --log-slave-updates=1
    restart: always
  slave:
    image: mysql:8.4.2
    ports:
      - "3311:3306"
    environment:
      MYSQL_ROOT_PASSWORD: your_password
    volumes:
      - ./slave/data:/var/lib/mysql
      - ./slave/log:/var/log
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --gtid_mode=ON
      --enforce_gtid_consistency=true
      --server_id=2
      --log-bin=biglog
      --binlog_format=row
      --log-slave-updates=1
    restart: always

执行：

sudo docker-compose up -d

master 配置

# 创建可以进行备份的账户
CREATE USER 'repl'@'%' IDENTIFIED with mysql_native_password BY 'repl-password';
# CREATE USER 'repl'@'%' IDENTIFIED with caching_sha2_password BY 'repl-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
# 查看LOG信息
SHOW BINARY LOG STATUS;
# 查看server-id
show variables like '%server%';

slave配置

# 启动备份服务
STOP REPLICA;
RESET REPLICA;  
# 指向master
CHANGE REPLICATION SOURCE TO 
	SOURCE_USER='repl', 
	SOURCE_PASSWORD='repl-password', 
	SOURCE_HOST='10.200.60.99',
	SOURCE_PORT=3310,
	SOURCE_AUTO_POSITION=1,
	SOURCE_SSL = 1; # 使用caching_sha2_password，需要开启
	
# 启动从机	
START REPLICA;
# 查看从机状态，Replica_IO_Runing && Replica_SQL_Runing == true, 代表配置成功，就可以操作验证了。
SHOW REPLICA STATUS;
# 查看server-id
show variables like '%server%';