Deploying Group Replication

安装 mysql

  • 配置要求
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# 必须有 server id
server_id=1
# 必须开启 gtid
gtid_mode=ON
enforce-gtid-consistency=true
# 8.0.22 及以前版本 binlog_checksum 需设为 NONE，之后版本可设为 CRC32
binlog_checksum=NONE
 
# 禁用除 INNODB 外其他存储引擎(8.0.16之前版本禁用会影响 mysql_upgrade 运行)
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
 
# 开启 binlog，并设置级别为 row
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW

# 8.0.23 及以后版本无需配置下两个参数
master_info_repository=TABLE
relay_log_info_repository=TABLE

各节点配置文件增加 mgr 配置

  • 使用 loose 表示配置文件仅在插件安装后生效，不影响正常启动
 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

#mgr settings
## 启动时自动加载 plugin
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
#自定义集群 uuid,可以使用 SELECT UUID() 或 Linux 的 uuidgen 命令生成 UUID
loose-group_replication_group_name = "51f7d615-f76c-403e-a6fe-960f3fa5c7df"

#MGR本地节点IP:PORT，请自行替换，port 为独立端口，不可与业务端口和 admin port 共用
loose-group_replication_local_address = "172.16.2.181:26033"
#MGR集群所有节点IP:PORT，请自行替换
loose-group_replication_group_seeds = "172.16.2.181:26033,172.16.2.182:26033,172.16.2.183:26033"
# 配置服务器是否在服务器启动期间自动启动组复制
# 可以在配置好成员后改为 on，如果集群全部停止，第一个节点启动时设为 OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_bootstrap_group = OFF
# 8.0.12+，当一个节点计划外离开集群式，节点变为 super read only
loose-group_replication_exit_state_action = READ_ONLY
# 关闭流控
loose-group_replication_flow_control_mode = "DISABLED"
# 单主模式
loose-group_replication_single_primary_mode = ON
# 指定复制通信的最大消息大小，超过此大小的消息会自动拆分
loose-group_replication_communication_max_message_size = 10M
# MGR 节点由 ONLINE 状态进入 UNREACHABLE 状态后（一般是由于网络抖动、节点异常等引起），等待相应的时 间，如果仍保持 UNREACHABLE，则将节点置为 ERROR 状态
loose-group_replication_unreachable_majority_timeout = 30
# 将suspicious节点踢出集群的等待时长，如果网络环境一般，可以适当调大30-60，不要太大
loose-group_replication_member_expel_timeout = 5
# 成员被驱逐后尝试重新加入集群的次数，尝试间隔为 5min
loose-group_replication_autorejoin_tries = 288
# 指定 mgr 节点见使用 caching_sha2_password 加密认证时要用到的证书
loose-group_replication_recovery_public_key_path="public_key.pem"
# 节点自己的 ip，如果不配置需要配置 hosts
report_host = "172.16.2.181"
  • 重启服务
1
2

sudo ./stop_6033.sh
sudo ./start_6033.sh
  • 查看插件状态
1
2
3
4
5
6
7
8
9

mysql> show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name                            | Status   | Type               | Library              | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog                          | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password           | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
*********************
| group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+---------------------------------+----------+--------------------+----------------------+---------+
  • 如果插件未安装可手动安装插件
1

install plugin group_replication soname 'group_replication.so';

创建复制用户

1
2
3
4
5
6
7
8

SET SQL_LOG_BIN=0;
CREATE USER mgr_repl@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE,CONNECTION_ADMIN,BACKUP_ADMIN,GROUP_REPLICATION_STREAM ON *.* TO mgr_repl@'%';
SET SQL_LOG_BIN=1;
-- CHANGE MASTER TO MASTER_USER='mgr_repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
-- MySQL 8.0.23+
reset master;
CHANGE REPLICATION SOURCE TO SOURCE_USER='mgr_repl', SOURCE_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

主节点启动 mgr

  • 启动
1
2
3

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
  • 确认组复制是否启动成功
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

mysql> select * from performance_schema.replication_group_members \G
*************************** 1. row ***************************
              CHANNEL_NAME: group_replication_applier
                 MEMBER_ID: 008ecb42-2dad-11ed-8880-000c29388746
               MEMBER_HOST: mgr01
               MEMBER_PORT: 6033
              MEMBER_STATE: ONLINE
               MEMBER_ROLE: PRIMARY
            MEMBER_VERSION: 8.0.28
MEMBER_COMMUNICATION_STACK: XCom
1 row in set (0.01 sec)

其他成员加入组

  • 更改配置文件
    • 除 report_host 和 group_replication_local_address 配置需更改，其他配置一致即可
  • 拷贝主节点 data 目录下 8 个证书至成员节点
    • 如果配置文件中配置了 default_authentication_plugin=mysql_native_password，则无需拷贝证书
1
2
3
4
5

# mgr01
cd /data/mysql3306/mysql_data
scp *.pem root@172.16.2.181:/data/mysql3306/mysql_data/
# mgr02
cd /data/mysql3306/mysql_data/ && chown mysql.mysql *.pem
  • 创建复制用户
1
2
3
4
5
6
7
8

SET SQL_LOG_BIN=0;
CREATE USER mgr_repl@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE,CONNECTION_ADMIN,BACKUP_ADMIN,GROUP_REPLICATION_STREAM ON *.* TO mgr_repl@'%';
SET SQL_LOG_BIN=1;
reset master;
-- CHANGE MASTER TO MASTER_USER='mgr_repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
-- MySQL 8.0.23+
CHANGE REPLICATION SOURCE TO SOURCE_USER='mgr_repl', SOURCE_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
  • 加入组
1

start group_replication;

测试

查看 mgr 状态

 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

mysql> select * from performance_schema.replication_group_members \G
*************************** 1. row ***************************
              CHANNEL_NAME: group_replication_applier
                 MEMBER_ID: 80ba0221-2f1a-11ed-bffc-000c29bb90ab
               MEMBER_HOST: 172.16.2.182
               MEMBER_PORT: 3306
              MEMBER_STATE: ONLINE
               MEMBER_ROLE: SECONDARY
            MEMBER_VERSION: 8.0.28
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************
              CHANNEL_NAME: group_replication_applier
                 MEMBER_ID: ae8df369-2f1a-11ed-a1ee-000c2968ba03
               MEMBER_HOST: 172.16.2.183
               MEMBER_PORT: 3306
              MEMBER_STATE: ONLINE
               MEMBER_ROLE: PRIMARY
            MEMBER_VERSION: 8.0.28
MEMBER_COMMUNICATION_STACK: XCom
*************************** 3. row ***************************
              CHANNEL_NAME: group_replication_applier
                 MEMBER_ID: e00291e2-2f19-11ed-bec9-000c29388746
               MEMBER_HOST: 172.16.2.181
               MEMBER_PORT: 3306
              MEMBER_STATE: ONLINE
               MEMBER_ROLE: SECONDARY
            MEMBER_VERSION: 8.0.28
MEMBER_COMMUNICATION_STACK: XCom
3 rows in set (0.00 sec)

测试同步

  • 下载 mysql 官方测试库初始化sql：https://downloads.mysql.com/docs/sakila-db.tar.gz
1
2

wget https://downloads.mysql.com/docs/sakila-db.tar.gz
tar -zxvf sakila-db.tar.gz
  • PRIMARY 节点导入数据
1
2

source /root/sakila-db/sakila-schema.sql
source /root/sakila-db/sakila-data.sql
  • SECONDARY 节点查看数据
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
mysql> select count(*) from sakila.actor;
+----------+
| count(*) |
+----------+
|      200 |
+----------+