一、单主模式
环境:
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;