环境

hostname ip os mysql
mysql_11 192.168.1.11 centos7.7 8.0.19
mysql_22 192.168.1.22 centos7.7 8.0.19
mysql_33 192.168.1.33 centos7.7 8.0.19

安装 mysql

  • 懒得写了 …

修改 my.cnf

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
# mysql_11(选择一个)
server-id = 11
# mysql_22(选择一个)
server-id = 22
# mysql_33(选择一个)
server-id = 33

# 开启 binlog
log-bin = /var/log/mysql-bin/master
binlog-format = ROW
# 关闭 binlog 校验
binlog-checksum = NONE
# 保留 2 天的 binlog
binlog-expire-logs-seconds = 172800
# 开启 gtid
gtid-mode = ON
enforce-gtid-consistency = TRUE
# 指定 relay-log 存储位置
relay-log = /var/lib/mysql-bin/slave
# relay-log 更新计入 binlog
log-slave-updates = TRUE

# 多线程执行从库日志(可选)
slave-parallel-workers = 2
slave-parallel-type = LOGICAL_CLOCK
slave-preserve-commit-order = ON

# 存储引擎只能用 InnoDB
disabled-storage-engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
# 加载插件,克隆插件用于快速 state transfer
plugin-load-add = "group_replication.so;mysql_clone.so"
# 集群 uuid
group-replication-group-name = "aaaa1111-bbbb-2222-cccc-3333dddd4444"

# mysql_11(选择一个)
group-replication-local-address = "192.168.1.11:33061"
# mysql_22(选择一个)
group-replication-local-address = "192.168.1.22:33061"
# mysql_33(选择一个)
group-replication-local-address = "192.168.1.33:33061"

# 种子节点
group-replication-group-seeds = "192.168.1.11:33061,192.168.1.22:33061,192.168.1.33:33061"
# 新主库在执行完自己的从库日志后,再处理用户的写请求
group-replication-consistency = BEFORE_ON_PRIMARY_FAILOVER
# 启动时,不自动创建/初始化新集群
group-replication-bootstrap-group = OFF
# 新节点启动时,先不启动组复制,待手动配置完成并确认正常后,再把 OFF 改成 ON
group-replication-start-on-boot = OFF

# 怀疑某节点不可用,2秒内,如果该嫌疑节点依旧无响应,则开除它(可选)
group-replication-member-expel-timeout = 2
# 2秒内,依旧连接不上主网(majority),则退出组复制,进入 ERROR 状态(可选)
group-replication-unreachable-majority-timeout = 2
# 退出组复制后,不再尝试重新加入组复制,直接执行指定的退出动作(默认)
group-replication-autorejoin-tries = 0
# 指定退出动作: 数据库设置超级只读并关闭客户端连接(推荐)
group-replication-exit-state-action = OFFLINE_MODE

初始化集群

  • 重新启动节点 mysql_11

    1
    
    systemctl restart mysqld
  • 创建同步用户

    1
    2
    3
    4
    5
    6
    
    SET SQL_LOG_BIN = 0;
    CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
    GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
    GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
    FLUSH PRIVILEGES;
    SET SQL_LOG_BIN = 1;
  • 配置同步信息

    1
    2
    3
    4
    
    CHANGE MASTER TO 
        MASTER_USER='rpl_user', 
        MASTER_PASSWORD='password' 
        FOR CHANNEL 'group_replication_recovery';
  • 启动集群

    1
    2
    3
    
    SET GLOBAL group_replication_bootstrap_group=ON;
    START GROUP_REPLICATION;
    SET GLOBAL group_replication_bootstrap_group=OFF;
  • 查看集群成员(只有一个)

    1
    
    SELECT * FROM performance_schema.replication_group_members;
  • 修改 my.cnf,配置 group-replication-start-on-boot = ON

增加节点

  • 重新启动节点 mysql_22

    1
    
    systemctl restart mysql_22
  • 创建同步用户,与 mysql_11 相同

    1
    2
    3
    4
    5
    6
    
    SET SQL_LOG_BIN = 0;
    CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
    GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
    GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
    FLUSH PRIVILEGES;
    SET SQL_LOG_BIN = 1;
  • 配置同步信息

    1
    2
    3
    4
    
    CHANGE MASTER TO 
        MASTER_USER='rpl_user', 
        MASTER_PASSWORD='password' 
        FOR CHANNEL 'group_replication_recovery';
  • 启动组复制

    1
    
    START GROUP_REPLICATION;
  • 在 mysql_33 上重复 mysql_22 的步骤

  • 查看集群成员(有三个)

    1
    
    SELECT * FROM performance_schema.replication_group_members;
  • 修改 mysql_22 和 mysql_33 的 my.cnf,配置 group-replication-start-on-boot = ON

注意

  • 每张表都必须显式指定主键