mysql8 mgr一主多从部署

一、单主模式

环境：

rockylinux:9

mysql：8.0.31 MySQL Community Server - GPL

主机	ip	类型
node1	10.11.12.13	主
node2	10.11.13.13	从
node3	10.11.13.53	从

1.配置hosts

测试发现host要和hostname一致才行

10.11.12.13  k8s-master
10.11.13.13 automatic_server
10.11.13.53  node3

2.配置/etc/my.cnf

主库配置

#mgr
#mgr
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=219
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'
group_replication_group_name="7c96bd57-4386-461d-b309-54ea9f7c6d22"
group_replication_start_on_boot=off
group_replication_local_address="k8s-master:33061"
group_replication_group_seeds="k8s-master:33061,automatic_server:33061,node3:33061"
group_replication_bootstrap_group=off
transaction_write_set_extraction ='XXHASH64'
group_replication_ip_whitelist = '10.11.12.13/24,10.11.13.13/24,10.11.13.53/24'

#report_host=10.11.12.13
#report_port=3309
#loose-group_replication_start_on_boot = off
#loose-group_replication_local_address = '10.11.13.13:10061'
#loose-group_replication_group_seeds ='10.11.12.13:10061,10.11.13.13'
#loose-group_replication_bootstrap_group = off
#loose-group_replication_ip_whitelist = '10.11.12.13/24,10.11.13.13/24'
#loose-group_replication_ip_whitelist = '192.168.112.131/24,192.168.112.132/24,192.168.112.135/24' #白名单

插件相关的参数只能在插件加载之后设置，如果想启动时设置这些参数并且启动后加载插件到配置文件中，可以在参数前加上“loose-”前缀。

从1配置

enforce_gtid_consistency = 1
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON

binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'
group_replication_group_name="7c96bd57-4386-461d-b309-54ea9f7c6d22"
group_replication_start_on_boot=off
group_replication_local_address="automatic_server:33061"
group_replication_group_seeds="k8s-master:33061,automatic_server:33061,node3:33061"
group_replication_bootstrap_group=off
#report_host=10.11.13.13
#report_port=3309
group_replication_ip_whitelist='10.11.12.13/24,10.11.13.13/24,10.11.13.53/24' #白名单

从2配置

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
 
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
 
plugin_load_add='group_replication.so'
group_replication_group_name="7c96bd57-4386-461d-b309-54ea9f7c6d22"
group_replication_start_on_boot=off
group_replication_local_address="10.11.12.13:33061"
group_replication_group_seeds="10.11.12.13:33061,10.11.13.13:33061，10.11.13.53"
group_replication_bootstrap_group=off
report_host=10.11.13.53
report_port=3309

3.配置检查

3.1检查是否开启gtid模式

show global variables like 'gtid_mode';
show global variables like 'group_replication_group_name';
show global variables like 'group_replication_local_address';
show global variables like 'group_replication_group_seeds';

3.2查看插件是否开启

show plugins;
| group_replication                | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |

手动安装(配置文件有了，可选)：

install plugin group_replication soname 'group_replication.so';

4.创建复制账号

主、从1、从2执行

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'Mgr-0330';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
 
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Mgr-0330' FOR CHANNEL 'group_replication_recovery';

5.启动组复制

主：

RESET MASTER;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

从1，从2：

RESET MASTER;
START GROUP_REPLICATION;

6.检查

主、从1、从2

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 4f05ad6f-5378-11ed-a6ad-005056bff418 | automatic_server |        3306 | ONLINE       | SECONDARY   | 8.0.31         | XCom                       |
| group_replication_applier | 9224a578-534d-11ed-ad53-005056bf6787 | k8s-master       |        3306 | ONLINE       | PRIMARY     | 8.0.31         | XCom                       |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)

FAQ

[GCS] Error connecting to all peers. Member join failed. Local port: 3306

默认情况下，是无法让本地网络外的连接的访问的，所以在跨网段、机房的情况下，需要设置 group_replication_ip_allowlist

原因: 是因为之前没有设置group_replication_ip_whitelist这个参数，默认值为127.0.0.1/8, 因为集群中的所有的节点为同一网段，因此在这里设置为"192.168.153.44/24". 但实际上可以更严格，指定集群内的所有的IP地址。

set global group_replication_ip_allowlist="10.11.12.13,10.11.13.13,10.11.13.53";

ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
查看到主的日志：
: '[GCS] Connection attempt from IP address ::ffff:10.11.13.13 refused. Address is not in the IP allowlist.

重新增加白名单

3.修改密码
主：

alter user rpl_user identified by 'Mgr-0369&Cmiot'; 
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Mgr-0369&Cmiot' FOR CHANNEL 'group_replication_recovery';
STOP GROUP_REPLICATION;
RESET MASTER;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

从：
STOP GROUP_REPLICATION;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Mgr-0369&Cmiot' FOR CHANNEL 'group_replication_recovery';
RESET MASTER;
START GROUP_REPLICATION;