7.mysql8.0版本MGR搭建

搭建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 |
+------------------+-----------+-----------------------+