(MySQL 8.0.32搭建MGR)
一、环境
192.168.109.144
192.168.109.145
192.168.109.146
MySQL 8.0.32
二、单主模式部署
1、修改my.cnf
server_id=1 #各个节点server_id需要不一样
gtid_mode=ON
enforce_gtid_consistency=ON
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.109.144:33061" #修改为对应节点ip
group_replication_group_seeds= "192.168.109.144:33061,192.168.109.145:33061,192.168.109.146:33061"
group_replication_bootstrap_group=off
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
report_host=192.168.109.144 #对应节点ip,默认使用主机名连接各节点,故要配置通过ip连接
report_port=3306
2、启动mysql
systemctl restart mysqld
3、登录MySQL,设置复制账号
#登录MySQL
mysql -uroot -p
#配置复制账号
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Zkk@123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='Zkk@123456', GET_SOURCE_PUBLIC_KEY=1 FOR CHANNEL 'group_replication_recovery';
特别说明:因MySQL 8.0默认使用的是caching_sha2_password身份验证插件,若CHANGE语句不指定GET_SOURCE_PUBLIC_KEY=1,启动MGR时,replica节点将无法连接到source节点,mysqld.log日志中报错:Au thentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.解决方案如下:
方案一:配置group_replication_recovery_get_public_key=1,使复制用户请求服务器公钥
mysql> SET persist group_replication_recovery_get_public_key = 1;
#或者直接在/etc/my.cnf中添加group_replication_recovery_get_public_key = 1;
方案二:创建复制用户时,指定使用mysql_native_password身份验证插件
CREATE USER 'repl_user'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'XXXX';
4、安装MGR插件
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';#可能会提示已安装
mysql> SHOW PLUGINS;
注:这里可能会提示plugin已经安装,检查若确实已安装,直接下一步即可。
5、启动主节点上的MGR
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members;#查看MGR
6、启动其他节点上的MGR
mysql> START GROUP_REPLICATION;
7、全部启动完成之后查看MGR状态。
mysql> SELECT * FROM performance_schema.replication_group_members;
8、测试
在主节点上创建库、表等操作,查看从节点是否同步。
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'Luis');
三、切换到多主模式
1、所有节点上停止MGR
# 停止组复制(所有节点执行):
mysql> stop group_replication;
2、设置多主模式
mysql> set global group_replication_single_primary_mode=OFF;
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
3、启动MGR
第一个节点(随便哪个节点)上执行:
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
其他节点执行:
mysql> START GROUP_REPLICATION;
4、查看MGR状态
mysql> SELECT * FROM performance_schema.replication_group_members;
5、测试
在所有节点上创建库、表等操作,查看各节点同步情况。
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'Luis');