一主多从

1.创建配置文件

  • 基于docker配置
  1. version: '3'
  2. services:
  3.   db1:
  4.     image: mysql
  5.     restart: always
  6.     hostname: db1
  7.     environment:
  8.       MYSQL_ROOT_PASSWORD: 123456
  9.     command:
  10.       --default-authentication-plugin=mysql_native_password
  11.       --character-set-server=utf8mb4
  12.       --collation-server=utf8mb4_general_ci
  13.       --explicit_defaults_for_timestamp=true
  14.       --lower_case_table_names=1
  15.       --max_allowed_packet=128M
  16.       # 表示启用binlog功能
  17.       --log-bin=mysql-bin
  18.       # 表示忽略备份mysql
  19.       --binlog_ignore_db=mysql
  20.       # 唯一的ID
  21.       --server-id=1
  22.     ports:
  23.       - 3306:3306
  24.     volumes:
  25.       - ./data1:/var/lib/mysql
  26.   db2:
  27.     image: mysql
  28.     restart: always
  29.     hostname: db2
  30.     environment:
  31.       MYSQL_ROOT_PASSWORD: 123456
  32.     command:
  33.       --default-authentication-plugin=mysql_native_password
  34.       --character-set-server=utf8mb4
  35.       --collation-server=utf8mb4_general_ci
  36.       --explicit_defaults_for_timestamp=true
  37.       --lower_case_table_names=1
  38.       --max_allowed_packet=128M
  39.       --log-bin=mysql-bin
  40.       --binlog_ignore_db=mysql
  41.       --server-id=2
  42.     ports:
  43.       - 3307:3306
  44.     volumes:
  45.       - ./data2:/var/lib/mysql
  46.   db3:
  47.     image: mysql
  48.     restart: always
  49.     hostname: db3
  50.     environment:
  51.       MYSQL_ROOT_PASSWORD: 123456
  52.     command:
  53.       --default-authentication-plugin=mysql_native_password
  54.       --character-set-server=utf8mb4
  55.       --collation-server=utf8mb4_general_ci
  56.       --explicit_defaults_for_timestamp=true
  57.       --lower_case_table_names=1
  58.       --max_allowed_packet=128M
  59.       --log-bin=mysql-bin
  60.       --binlog_ignore_db=mysql
  61.       --server-id=3
  62.     ports:
  63.       - 3308:3306
  64.     volumes:
  65.       - ./data3:/var/lib/mysql

2.主节点执行

  1. > CREATE USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
  2. > GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
  3. > CREATE USER 'slave2'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
  4. > GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'%';
  5. > FLUSH PRIVILEGES;
  6. > SHOW MASTER STATUS;

3.从节点执行

  1. > STOP REPLICA;
  2. > CHANGE MASTER TO 
  3. MASTER_HOST='db1',
  4. MASTER_USER='slave1',
  5. MASTER_PASSWORD='123456',
  6. MASTER_LOG_FILE='mysql-bin.000003',
  7. MASTER_LOG_POS=0;
  8. > START REPLICA;
  9. > SHOW REPLICA STATUS;

更新时间：{docsify-updated}