mysqlcredentialsnotconfigured_一个MySQL8MGR的用户密码问题

最近在学习MySQL MGR&＃xff0c;折腾了一套环境&＃xff0c;在切换的时候发现了一个问题&＃xff0c;

(root&＃64;localhost) [performance_schema]> SELECT * FROM performance_schema.replication_group_members;

&＃43;---------------------------&＃43;--------------------------------------&＃43;-------------&＃43;-------------&＃43;--------------&＃43;-------------&＃43;----------------&＃43;

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |

&＃43;---------------------------&＃43;--------------------------------------&＃43;-------------&＃43;-------------&＃43;--------------&＃43;-------------&＃43;----------------&＃43;

| group_replication_applier | 815e5732-f199-11ea-93c4-000c29244a19 | s2 | 3306 | RECOVERING | SECONDARY | 8.0.20 |

| group_replication_applier | 9fad5b49-f199-11ea-b424-000c2929ae10 | s3 | 3306 | RECOVERING | SECONDARY | 8.0.20 |

| group_replication_applier | d8582b22-f1ba-11ea-8618-000c2971a5e8 | s1 | 3306 | ONLINE | PRIMARY | 8.0.20 |

&＃43;---------------------------&＃43;--------------------------------------&＃43;-------------&＃43;-------------&＃43;--------------&＃43;-------------&＃43;----------------&＃43;

发现主机s2 s3的两个状态都是RECOVERING。

查看日志发现如下所示&＃xff1a;

2020-09-09T08:27:42.705458Z 231 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the &＃39;START SLAVE Syntax&＃39; in the MySQL Manual for more information.

2020-09-09T08:27:43.172979Z 231 [ERROR] [MY-010584] [Repl] Slave I/O for channel &＃39;group_replication_recovery&＃39;: error connecting to master &＃39;rpl&＃64;s3:3306&＃39; - retry-time: 60 retries: 1 message: Authentication plugin &＃39;caching_sha2_password&＃39; reported error: Authentication requires secure connection. Error_code: MY-002061

2020-09-09T08:27:43.258495Z 230 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: &＃39;There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.&＃39;

2020-09-09T08:27:43.258550Z 230 [ERROR] [MY-011583] [Repl] Plugin group_replication reported: &＃39;For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.&＃39;

分别查看了三个库的rpl用户信息&＃xff0c;如下所示&＃xff1a;

发现和admin的authentication_string字段不同&＃xff0c;

admin用户是之前用navicat链接时出现的问题&＃xff0c;是因为密码加密规则的变化。

难道rpl复制用户也是因为这个问题&＃xff1f;&＃xff1f;&＃xff1f;

于是停掉组复制&＃xff0c;做以下测试&＃xff1a;

SET SQL_LOG_BIN&＃61;0;

set global super_read_only&＃61;off ; ##关闭group_replicationz后&＃xff0c;该值会设置为on

drop user &＃39;rpl&＃39;&＃64;&＃39;%&＃39; ; ##删除原rpl用户

CREATE USER rpl&＃64;&＃39;%&＃39; IDENTIFIED WITH &＃39;mysql_native_password&＃39; BY &＃39;MyNewPass4!&＃39;; ##创建用户&＃xff0c;使用mysql_native_password

GRANT REPLICATION SLAVE ON *.* TO rpl&＃64;&＃39;%&＃39;; ##赋予权限

GRANT BACKUP_ADMIN ON *.* TO rpl&＃64;&＃39;%&＃39;;

set global super_read_only&＃61;on ; ##恢复ON值

SET SQL_LOG_BIN&＃61;1;

以上&＃xff0c;设置完之后&＃xff0c;重新启动(start group_replication)&＃xff0c;检查组成员状态&＃xff0c;发现已经完全正常。

为了验证这个问题&＃xff0c;我又把当前的rpl账号删除掉&＃xff0c;以官方文档上给出的创建用户语句又建了一遍&＃xff0c;再次启动组复制&＃xff0c;发现还是同样的问题。

因此可以在搭建的过程中&＃xff0c;还是需要注意使用mysql_native_password。

但是有一个疑问&＃xff1a;

这个问题是今天才发现&＃xff0c;前两天也切换过&＃xff0c;没有发现该问题&＃xff0c;不知道是哪里的原因&＃xff0c;回想了一下&＃xff0c;关于密码设置这块好像也没有做过改动。

以此记录一下。

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

20200917更新

这几天又发现上次写的好像不对&＃xff0c;因为后面又出现了类似的问题&＃xff0c;启动group_replication之后连接不到主节点&＃xff0c;原因还是在复制用户rpl的认证插件。

解决方法&＃xff1a;

mysql> stop group replication;

mysql> set global group_replication_recovery_get_public_key&＃61;on;

mysql> start group replication;

下一篇文章了解下mysql用户认证插件相关问题。