1.MGR工作原理拓扑图
2.数据流
3.参数
#常规参数 server_id=853306/863306/873306 bind-address=0.0.0.0 skip_name_resolve = ON innodb_flush_log_at_trx_commit =1 #binlog参数 log_bin=/mysql/log/3306/binlog/fgedudb-binlog binlog_rows_query_log_events=on binlog_gtid_simple_recovery=1 binlog_row_metadata=full binlog_expire_logs_seconds=604800 log_bin_trust_function_creators = 1 sync_binlog = 1 relay-log=/mysql/log/3306/relaylog/fgedudb-relaylog ########group replication settings######## report-host=fgedu85/86/87 report_port=3306 binlog_checksum=none gtid_mode = ON enforce_gtid_consistency = 1 binlog_gtid_simple_recovery=1 #MGR参数 #plugin_dir=/mysql/app/mysql/lib/plugin/ disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" loose-plugin_load_add = 'mysql_clone.so' loose-plugin_load_add = 'group_replication.so' loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1" loose-group_replication_local_address= "192.168.18.85:33006" #loose-group_replication_local_address= "192.168.18.86:33006" #loose-group_replication_local_address= "192.168.18.87:33006" loose-group_replication_group_seeds= "192.168.18.85:33006,192.168.18.86:33006,192.168.18.87:33006" loose-group_replication_start_on_boot=off loose-group_replication_bootstrap_group= off loose-group_replication_member_weight=50 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_autorejoin_tries = 288 loose-group_replication_member_expel_timeout = 5 loose-group_replication_unreachable_majority_timeout = 30 loose-group_replication_communication_max_message_size = 10M group_replication_recovery_get_public_key=on
4.配置MGR
#确认uuid mysql -uroot -prootroot -h192.168.18.85 -P3306 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -prootroot -h192.168.18.86 -P3306 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -prootroot -h192.168.18.87 -P3306 -e "select @@hostname,@@server_id,@@server_uuid" #创建复制用户85，86，87 set sql_log_bin=0; create user repuser@'%' identified by 'repuser123'; grant replication slave,replication client,connection_admin,backup_admin,group_replication_stream on *.* to repuser@'%'; create user repuser@'127.0.0.1' identified by 'repuser123'; grant replication slave,replication client,connection_admin,backup_admin,group_replication_stream on *.* to repuser@'127.0.0.1'; create user repuser@'localhost' identified by 'repuser123'; grant replication slave,replication client,connection_admin,backup_admin,group_replication_stream on *.* to repuser@'localhost'; flush privileges; set sql_log_bin=1; select user,host from mysql.user; #配置复制 85 change replication source to source_user='repuser', source_password='repuser123' for channel 'group_replication_recovery'; #建立group 85 set global group_replication_bootstrap_group=on; start group_replication; set global group_replication_bootstrap_group=off; SELECT * FROM performance_schema.replication_group_members; #创建数据 CREATE USER 'fgedu'@'%' IDENTIFIED BY 'fgedu123'; GRANT ALL PRIVILEGES ON itpuxdb.* TO 'fgedu'@'%' WITH GRANT OPTION; use itpuxdb create table fgedu01( id int auto_increment primary key, name varchar(15) ) engine = InnoDB; INSERT INTO fgedu01 VALUES (1,'fgedu02'); INSERT INTO fgedu01 VALUES (2,'fgedu02'); INSERT INTO fgedu01 VALUES (3,'fgedu03'); INSERT INTO fgedu01 VALUES (4,'fgedu04'); INSERT INTO fgedu01 VALUES (5,'fgedu05'); commit; select * from itpuxdb.fgedu01; #同样的方式配置86，87 change replication source to source_user='repuser', source_password='repuser123' for channel 'group_replication_recovery'; start group_replication; SELECT * FROM performance_schema.replication_group_members;
5.手工克隆
show global variables like '%gtid%'; stop group_replication; set global super_read_only=off; set global clone_valid_donor_list='192.168.18.85:3306'; clone instance from 'root'@'192.168.18.85':3306 identified by 'rootroot'; start group_replication; set global super_read_only=on;
6.MGR集群启动停止
(1).正常情况下 #关闭MGR 先关闭从库，再关闭主库 #启动MGR 先启动主库 set global group_replication_bootstrap_group=on; start group_replication; set global group_replication_bootstrap_group=off; SELECT * FROM performance_schema.replication_group_members; SELECT * FROM performance_schema.replication_group_member_stats; 在启动从库 start group_replication; select @@read_only,@@super_read_only; (2).异常情况下 #主节点宕机 #从节点变成主节点 #原主节点重新加入集群 start group_replication;
7.添加新节点
#修改参数文件 安装插件
#创建用户
set sql_log_bin=0;
create user repuser@'%' identified by 'repuser123';
grant replication slave,replication client,connection_admin,backup_admin,group_replication_stream on *.* to repuser@'%';
create user repuser@'127.0.0.1' identified by 'repuser123';
grant replication slave,replication client,connection_admin,backup_admin,group_replication_stream on *.* to repuser@'127.0.0.1';
create user repuser@'localhost' identified by 'repuser123';
grant replication slave,replication client,connection_admin,backup_admin,group_replication_stream on *.* to repuser@'localhost';
flush privileges;
set sql_log_bin=1;
select user,host from mysql.user;
#从集群主库克隆
show global variables like '%gtid%';
set global super_read_only=off;
set global clone_valid_donor_list='192.168.18.86:3306';
clone instance from 'root'@'192.168.18.86':3306 identified by 'rootroot';
set global super_read_only=on;
#查看克隆
select stage,state,
cast(begin_time as DATETIME) as "START TIME",
cast(end_time as DATETIME) as "FINISH TIME",
lpad(sys.format_time(power(10,12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ') as DURATION,
lpad(concat(format(round(estimate/1024/1024,0), 0), "MB"), 16, ' ') as "Estimate",
case when begin_time is NULL then LPAD('%0', 7, ' ')
when estimate > 0 then
lpad(concat(round(data*100/estimate, 0), "%"), 7, ' ')
when end_time is NULL then lpad('0%', 7, ' ')
else lpad('100%', 7, ' ')
end as "Done(%)"
from performance_schema.clone_progress;
#配置MGR
change replication source to
source_user='repuser',
source_password='repuser123'
for channel 'group_replication_recovery';
#加入集群
start group_replication;
8.删除节点
#停止集群 stop group_replication; #修改参数
