搭建MGR
1、配置文件
loose-group_replication_ip_whitelist = 192.168.124.0/24
loose-group_replication_start_on_boot = OFF
loose-group_replication_bootstrap_group = OFF
loose-group_replication_group_name = 0b773c1c-a24c-11ea-8520-5144005e8630
loose-group_replication_local_address = 192.168.124.101:33061
loose-group_replication_group_seeds = 192.168.124.101:33061,192.168.124.102:33061,192.168.124.103:33061
loose-group_replication_single_primary_mode = ON
loose-group_replication_member_weight = 8
loose-group_replication_unreachable_majority_timeout = 10
2、安装插件
在各个mgr节点执行
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.13 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
3、创建集群
在主库执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.31 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
在从库执行
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.31 sec)
搭建过程中遇到的问题
1、集群同步用户连接失败
Slave I/O for channel 'group_replication_recovery': error connecting to master 'repl@192.168.124.101:3306' - retry-time: 60 retries: 1 message:
Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection
- 原因:
MySQL8.0的首选默认认证插件是caching_sha2_password,
而不是mysql_native_password(5.7使用)。group_replication_recovery 还不支持caching_sha2_password这种认证方式。 - 解决方法:
1)把mysql用户登录密码加密规则还原成mysql_native_password
mysql> ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| repl | % | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+