MySQL MGR 集群搭建（单主模式&多主模式）

# 关闭防火墙
systemctl stop firewalld.service
# 禁用防火墙
systemctl disable firewalld.service


#关闭selinux
vi etc/selinux/config
SELINUX=disabled
复制

[mysqld]
port=3306
basedir=/usr/local/mysql8.0
datadir=/mysql/data_mgr_8.0/
socket=/mysql/data_mgr_8.0/mysql.sock
pid_file=/mysql/data_mgr_8.0/mysql.pid
server_id=1
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
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.1.2.101:33061"
loose-group_replication_group_seeds= "192.1.2.101:33061,192.1.2.102:33061,192.1.2.103:33061"
group_replication_ip_whitelist='192.1.2.101,192.1.2.102,192.1.2.103'
loose-group_replication_bootstrap_group=OFF
report_host=192.1.2.101
report_port=3306
复制

mkdir -p mysql/data_mgr_8.0
sudo chown -R mysql.mysql mysql/data_mgr_8.0/
sudo chown -R mysql.mysql home/mysql/etc/
/usr/local/mysql8.0/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql8.0 --datadir=/mysql/data_mgr_8.0 --user=mysql
复制

# 启动数据库


su - mysql -c "/usr/local/mysql8.0/bin/mysqld_safe --defaults-file=/home/mysql/etc/my_mgr_8.0.cnf &"


# 登录数据库


/usr/local/mysql8.0/bin/mysql -S mysql/data_mgr_8.0/mysql.sock


# 安装MGR插件


mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';


#设置复制账号
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
复制

# 启动MGR，在主库(192.1.2.101)上执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;


# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;




# 其他节点加入MGR，在从库（192.1.2.102，192.1.2.103）上执行
mysql> START GROUP_REPLICATION;


# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
复制

mysql> SET SQL_LOG_BIN=0;
mysql> alter user repl@'%' IDENTIFIED with sha256_password by 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
mysql>  SET SQL_LOG_BIN=1;
复制

# 停止组复制(所有节点执行)：
mysql> stop group_replication;
mysql> set global group_replication_single_primary_mode=OFF;
mysql> set global group_replication_enforce_update_everywhere_checks=ON;


# 随便选择某个节点执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON; 
mysql> START GROUP_REPLICATION; 
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;


# 其他节点执行
mysql> START GROUP_REPLICATION;


# 查看组信息，所有节点的 MEMBER_ROLE 都为 PRIMARY
mysql> SELECT * FROM performance_schema.replication_group_members;
复制

# 所有节点执行
mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;


# 主节点（192.168.56.101）执行
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;




# 从节点（192.168.56.102、192.168.56.103）执行
START GROUP_REPLICATION;


# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
复制