MySQL MGR
本文所演示的多主MGR三个节点均部署在同一台虚拟机，所以需先搭建MySQL多实例具体步骤可参考本人另一篇blog
#所有节点执行
#安装MGR插件
修改server_id,server_uuid确保同一复制组的各个服务器的值都不一样
chown -R mysql:mysql /mysql
install plugin group_replication soname 'group_replication.so';
create user 'replica_user'@'%' identified with mysql_native_password by 'Pas5W@rd';
grant replication slave on *.* to 'replica_user'@'%';
change master to
master_user='replica_user',
master_password='Pas5W@rd'
for channel 'group_replication_recovery';
reset master
#主节点
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
#其他从节点
START GROUP_REPLICATION;
#结果验证及问题排查
select * from performance_schema.replication_group_members;
select * from performance_schema.replication_group_member_stats \G ;
select * from performance_schema.replication_connection_status\G ;
my.cnf配置
[client] user=root password=Wjb123 socket=/data/p3306/mysql3306.sock [mysqld] # basic settings # user = mysql basedir = /mysql autocommit = 1 character_set_server = utf8 collation_server = utf8_general_ci # innodb settings # innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 16 innodb_page_size = 16384 max_binlog_size = 100M innodb_flush_log_at_trx_commit = 1 default-storage-engine = INNODB lower_case_table_names = 1 max_connections = 2000 log-bin-trust-function-creators = 1 #skip-grant-tables # log settings # #expire_logs_days = 7 #604800=7*24*60*60 binlog_expire_logs_seconds = 604800 # replication settings # log-bin = mysql-bin gtid_mode = on enforce_gtid_consistency = on binlog_checksum = none log_slave_updates = ON binlog_format = ROW master_info_repository=TABLE relay_log_info_repository=TABLE disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" group_replication_single_primary_mode=OFF #配置单主模式切换这两个配置即可 group_replication_enforce_update_everywhere_checks=ON #report_host=127.0.0.1 [mysqld_multi] log = /data/mysqld_multi.err mysqld = /mysql/bin/mysqld_safe mysqladmin = /mysql/bin/mysqladmin [mysqld3306] server-id = 1026 port = 3306 mysqlx_port = 33060 basedir = /mysql datadir = /data/p3306 pid-file = /data/p3306/mysql3306.pid log-error = /data/p3306/mysql3306.err socket = /data/p3306/mysql3306.sock mysqlx_socket = /data/p3306/mysqlx3306.sock [mysqld3307] server-id = 1027 port = 3307 mysqlx_port = 33070 basedir = /mysql datadir = /data/p3307 socket = /data/p3307/mysql3307.sock pid-file = /data/p3307/mysql3307.pid log-error = /data/p3307/mysql3307.err mysqlx_socket = /data/p3307/mysqlx3307.sock plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 group_replication_group_name="671aba90-3a9a-11eb-a702-000c295e6111" group_replication_start_on_boot=off group_replication_bootstrap_group=off group_replication_local_address= "127.0.0.1:33071" group_replication_group_seeds= "127.0.0.1:33071,127.0.0.1:33081,127.0.0.1:33091" [mysqld3308] server-id = 1028 port = 3308 mysqlx_port = 33080 basedir = /mysql datadir = /data/p3308 socket = /data/p3308/mysql3308.sock pid-file = /data/p3308/mysql3308.pid log-error = /data/p3308/mysql3308.err mysqlx_socket = /data/p3308/mysqlx3308.sock plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 group_replication_group_name="671aba90-3a9a-11eb-a702-000c295e6111" group_replication_start_on_boot=off group_replication_bootstrap_group=off group_replication_local_address= "127.0.0.1:33081" group_replication_group_seeds= "127.0.0.1:33071,127.0.0.1:33081,127.0.0.1:33091" [mysqld3309] server-id = 1029 port = 3309 mysqlx_port = 33090 basedir = /mysql datadir = /data/p3309 socket = /data/p3309/mysql3309.sock pid-file = /data/p3309/mysql3309.pid log-error = /data/p3309/mysql3309.err mysqlx_socket = /data/p3309/mysqlx3309.sock plugin_load_add='group_replication.so' transaction_write_set_extraction=XXHASH64 group_replication_group_name="671aba90-3a9a-11eb-a702-000c295e6111" group_replication_start_on_boot=off group_replication_bootstrap_group=off group_replication_local_address= "127.0.0.1:33091" group_replication_group_seeds= "127.0.0.1:33071,127.0.0.1:33081,127.0.0.1:33091"