怎么解决mysql MGR无法连接主节点的问题
发表于：2025-02-23 作者：千家信息网编辑
千家信息网最后更新 2025年02月23日，这篇文章主要讲解了"怎么解决mysql MGR无法连接主节点的问题"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"怎么解决mysql MGR无法连接主节
千家信息网最后更新 2025年02月23日怎么解决mysql MGR无法连接主节点的问题2.排查
这篇文章主要讲解了"怎么解决mysql MGR无法连接主节点的问题"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"怎么解决mysql MGR无法连接主节点的问题"吧！
1.现象
2020-07-15T16:36:24.238903+08:00 126 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'repl@smdw:12000' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-0020612020-07-15T16:36:24.245949+08:00 71 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: '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.'2020-07-15T16:36:24.246011+08:00 71 [ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.'2020-07-15T16:36:24.246227+08:00 71 [ERROR] [MY-011574] [Repl] Plugin group_replication reported: 'Maximum number of retries when trying to connect to a donor reached. Aborting group replication incremental recovery.'2020-07-15T16:36:24.246332+08:00 71 [ERROR] [MY-011620] [Repl] Plugin group_replication reported: 'Fatal error during the incremental recovery process of Group Replication. The server will leave the group.'2020-07-15T16:36:24.246648+08:00 71 [ERROR] [MY-011712] [Repl] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'
2.排查
mysql> select * from performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| group_replication_applier | b2d6c335-c4b1-11ea-b3a6-3868dd209ff0 | mdw | 12000 | OFFLINE | | |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+1 row in set (0.00 sec)mysql> select * from performance_schema.replication_connection_status\G;*************************** 1. row *************************** CHANNEL_NAME: group_replication_applier GROUP_NAME: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa SOURCE_UUID: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa THREAD_ID: NULL SERVICE_STATE: OFF COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000 RECEIVED_TRANSACTION_SET: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-26,b2d6c335-c4b1-11ea-b3a6-3868dd209ff0:1-143778 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION: LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000*************************** 2. row *************************** CHANNEL_NAME: group_replication_recovery GROUP_NAME: SOURCE_UUID: THREAD_ID: NULL SERVICE_STATE: OFF COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 2061 LAST_ERROR_MESSAGE: error connecting to master 'repl@smdw:12000' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. LAST_ERROR_TIMESTAMP: 2020-07-15 16:36:24.238879 LAST_QUEUED_TRANSACTION: LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.0000002 rows in set (0.00 sec)mysql_error.log中显示：2020-07-15T16:36:24.237434+08:00 126 [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 'START SLAVE Syntax' in the MySQL Manual for more information.2020-07-15T16:36:24.238903+08:00 126 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'repl@smdw:12000' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-0020612020-07-15T16:36:24.245949+08:00 71 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: '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.'2020-07-15T16:36:24.246011+08:00 71 [ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.'2020-07-15T16:36:24.246227+08:00 71 [ERROR] [MY-011574] [Repl] Plugin group_replication reported: 'Maximum number of retries when trying to connect to a donor reached. Aborting group replication incremental recovery.'2020-07-15T16:36:24.246332+08:00 71 [ERROR] [MY-011620] [Repl] Plugin group_replication reported: 'Fatal error during the incremental recovery process of Group Replication. The server will leave the group.'2020-07-15T16:36:24.246648+08:00 71 [ERROR] [MY-011712] [Repl] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'
3.原因
万事不决看官方文档，官方文档有很明确说明：
Replication User With The Caching SHA-2 Authentication Plugin
By default, users created in MySQL 8 use Section 6.4.1.2, "Caching SHA-2 Pluggable Authentication".If the replication user you configure for distributed recovery uses the caching SHA-2 authenticationplugin, and you are not using SSL for distributed recovery connections, RSA key-pairs are used forpassword exchange. For more information on RSA key-pairs, see Section 6.3.3, "Creating SSL andRSA Certificates and Keys".In this situation, you can either copy the public key of the rpl_user to the joining member, orconfigure the donors to provide the public key when requested. The more secure approach is to copythe public key of the replication user account to the joining member. Then you need to configure thegroup_replication_recovery_public_key_path system variable on the joining member withthe path to the public key for the replication user account.The less secure approach is to set group_replication_recovery_get_public_key=ONon donors so that they provide the public key of the replication user account tojoining members. There is no way to verify the identity of a server, therefore only setgroup_replication_recovery_get_public_key=ON when you are sure there is no risk ofserver identity being compromised, for example by a man-in-the-middle attack
可以看到，当确认环境安全以及没人任何人攻击集群时，如果不配置ssl，可以最低配置下面参数来在请求复制用户密钥时
给公钥：
group_replication_recovery_get_public_key=ON
4.解决
mysql> start group replication;mysql> start group replication;mysql> set global group_replication_recovery_get_public_key=on;mysql> start group replication;
5.检查状态
mysql> select * from performance_schema.replication_connection_status\G;*************************** 1. row *************************** CHANNEL_NAME: group_replication_applier GROUP_NAME: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa SOURCE_UUID: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa THREAD_ID: NULL SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000 RECEIVED_TRANSACTION_SET: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-26:29,b2d6c335-c4b1-11ea-b3a6-3868dd209ff0:1-143778 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:29 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2020-07-15 16:47:36.329298 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2020-07-15 16:47:36.329384 QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000*************************** 2. row *************************** CHANNEL_NAME: group_replication_recovery GROUP_NAME: SOURCE_UUID: THREAD_ID: NULL SERVICE_STATE: OFF COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION: LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.0000002 rows in set (0.00 sec)ERROR: No query specifiedmysql> mysql> mysql> select * from performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| group_replication_applier | 6c48e4d6-c651-11ea-a94d-3868dd209dd0 | smdw | 12000 | ONLINE | PRIMARY | 8.0.20 || group_replication_applier | b2d6c335-c4b1-11ea-b3a6-3868dd209ff0 | mdw | 12000 | ONLINE | SECONDARY | 8.0.20 |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+2 rows in set (0.00 sec)
感谢各位的阅读，以上就是"怎么解决mysql MGR无法连接主节点的问题"的内容了，经过本文的学习后，相信大家对怎么解决mysql MGR无法连接主节点的问题这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！
问题
节点
学习
内容
官方
文档
配置
最低
安全
万事
公钥
原因
参数
密钥
就是
思路
情况
文章
更多
状态
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
服务器的2个g的安全包
重庆物流软件开发流程
wms仓储软件开发
党政网络安全大赛
数据库如何打开ddl
天龙八部刀锋破晓服务器怎么样
工业互联网网络安全资料
如何通过我的世界服务器验证
网络安全教育的黑板报图片
计算机网络技术社会实践
如何加载sybase数据库
西门子上位机软件开发
软件开发工程师java证
vr科普小游戏软件开发公司
辽宁推广网络技术哪家好
工信部网络安全保险
新建数据库用户并赋权
代理服务器的实现
网络安全与信息化产业联盟
电脑当服务器方法
苹果软件开发哪个学校好
北京软件开发骗局
即时通讯软件开发哪里
mysql数据库覆盖
软件开发项目技术部分
暑假网络安全采访
静安区新型网络技术概念设计
海湾数据库
现在软件开发那个号
辽宁网络安全知识点
