MySQL MGR配合MySQL-router实现innodb-cluster原创
# MGR 部署
# my.cnf配置参数
单主配置
#--- group replication settings ---
plugin-load = "group_replication.so"
transaction-write-set-extraction = XXHASH64
report_host = 172.31.178.83
binlog_checksum = NONE
loose_slave_preserve_commit_order = on
loose_group_replication = FORCE_PLUS_PERMANENT
loose_group_replication_group_name = "f0d9e877-661b-487c-a955-7fae37a5c2bd"
loose_group_replication_compression_threshold = 100000 # mysql8.0.11后默认值为1000000字节,1M
loose_group_replication_flow_control_mode = 0
loose_group_replication_single_primary_mode = 1
loose_group_replication_transaction_size_limit = 331350016
loose_group_replication_member_expel_timeout = 20
loose_group_replication_unreachable_majority_timeout = 20
loose_group_replication_start_on_boot = off
loose_group_replication_local_address = '172.31.178.83:23502' #端口跟mysql端口不同即可
loose_group_replication_group_seeds = '172.31.178.82:23501,172.31.178.83:23502,172.31.178.84:23503'
loose_group_replication_ip_whitelist= '172.31.178.0/20'
loose_group_replication_bootstrap_group = off
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
多主配置
##--- group replication settings 多主模式---
plugin-load = "group_replication.so"
transaction-write-set-extraction = XXHASH64
report_host = 172.31.178.87
binlog_checksum = NONE
loose_slave_preserve_commit_order = on
loose_group_replication = FORCE_PLUS_PERMANENT
loose_group_replication_group_name = "00000000-0000-0000-0000-000000000000"
loose_group_replication_compression_threshold = 100000 # mysql8.0.11后默认值为1000000字节,1M
loose_group_replication_flow_control_mode = 0
loose_group_replication_single_primary_mode = 0
loose_group_replication_enforce_update_everywhere_checks = ON # 比单主多了这一行
loose_group_replication_transaction_size_limit = 331350016
loose_group_replication_member_expel_timeout = 20
loose_group_replication_unreachable_majority_timeout = 20
loose_group_replication_start_on_boot = off
loose_group_replication_local_address = '172.31.178.87:23306'
loose_group_replication_group_seeds = '172.31.178.87:23306,172.31.178.88:23306,172.31.178.89:23306'
loose_group_replication_ip_whitelist= '0.0.0.0/20'
loose_group_replication_bootstrap_group = off
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 1.先创建一个用户
create user 'repl'@'172.31.178.%' identified by 'xxxxxxxxx';
1
# 再进行授权
grant REPLICATION SLAVE on *.* to 'repl'@'172.31.178.%' ;
1
# 2.安装插件
install plugin group_replication soname 'group_replication.so';
1
show plugins;
1
select * from INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME like '%group_replication%';
1
INSTALL PLUGIN clone SONAME 'mysql_clone.so'; #可选
1
# 3.配置master实例
1）查看当前的group replication相关参数是否配置有误
show global variables like 'group%';
1
2）启动 group_replication_bootstrap_group
SET GLOBAL group_replication_bootstrap_group=ON;
1
3）配置MGR
set global group_replication_ip_whitelist='10.100.101.0/20';
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='EKxxxxxxxxxxxdgJ' FOR CHANNEL 'group_replication_recovery';
1
2
2
4）启动MGR
start group_replication;
1
5）关闭 group_replication_bootstrap_groups
SET GLOBAL group_replication_bootstrap_group=OFF;
1
select * from performance_schema.replication_group_members;
1
# Q&A
show global variables like '%gtid%' ;
set @@GLOBAL.GTID_PURGED='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-1006103';
1
2
2
上次更新: 4/24/2025