怎么解决mysql MGR无法连接主节点的问题
这篇文章主要讲解了“怎么解决mysql MGR无法连接主节点的问题”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“怎么解决mysql MGR无法连接主节点的问题”吧!
1.现象2020-07-15T16:36:24.238903+08:00126[ERROR][MY-010584][Repl]SlaveI/Oforchannel'group_replication_recovery':errorconnectingtomaster'repl@smdw:12000'-retry-time:60retries:1message:Authenticationplugin'caching_sha2_password'reportederror:Authenticationrequiressecureconnection.Error_code:MY-0020612020-07-15T16:36:24.245949+08:0071[ERROR][MY-011582][Repl]Plugingroup_replicationreported:'Therewasanerrorwhenconnectingtothedonorserver.Pleasecheckthatgroup_replication_recoverychannelcredentialsandallMEMBER_HOSTcolumnvaluesofperformance_schema.replication_group_memberstablearecorrectandDNSresolvable.'2020-07-15T16:36:24.246011+08:0071[ERROR][MY-011583][Repl]Plugingroup_replicationreported:'Fordetailspleasecheckperformance_schema.replication_connection_statustableanderrorlogmessagesofSlaveI/Oforchannelgroup_replication_recovery.'2020-07-15T16:36:24.246227+08:0071[ERROR][MY-011574][Repl]Plugingroup_replicationreported:'Maximumnumberofretrieswhentryingtoconnecttoadonorreached.Abortinggroupreplicationincrementalrecovery.'2020-07-15T16:36:24.246332+08:0071[ERROR][MY-011620][Repl]Plugingroup_replicationreported:'FatalerrorduringtheincrementalrecoveryprocessofGroupReplication.Theserverwillleavethegroup.'2020-07-15T16:36:24.246648+08:0071[ERROR][MY-011712][Repl]Plugingroup_replicationreported:'Theserverwasautomaticallysetintoreadonlymodeafteranerrorwasdetected.'2.排查
mysql>select*fromperformance_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|||+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+1rowinset(0.00sec)mysql>select*fromperformance_schema.replication_connection_status\G;***************************1.row***************************CHANNEL_NAME:group_replication_applierGROUP_NAME:aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaaSOURCE_UUID:aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaaTHREAD_ID:NULLSERVICE_STATE:OFFCOUNT_RECEIVED_HEARTBEATS:0LAST_HEARTBEAT_TIMESTAMP:0000-00-0000:00:00.000000RECEIVED_TRANSACTION_SET:aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-26,b2d6c335-c4b1-11ea-b3a6-3868dd209ff0:1-143778LAST_ERROR_NUMBER:0LAST_ERROR_MESSAGE:LAST_ERROR_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION:LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION:QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP:0000-00-0000:00:00.000000***************************2.row***************************CHANNEL_NAME:group_replication_recoveryGROUP_NAME:SOURCE_UUID:THREAD_ID:NULLSERVICE_STATE:OFFCOUNT_RECEIVED_HEARTBEATS:0LAST_HEARTBEAT_TIMESTAMP:0000-00-0000:00:00.000000RECEIVED_TRANSACTION_SET:LAST_ERROR_NUMBER:2061LAST_ERROR_MESSAGE:errorconnectingtomaster'repl@smdw:12000'-retry-time:60retries:1message:Authenticationplugin'caching_sha2_password'reportederror:Authenticationrequiressecureconnection.LAST_ERROR_TIMESTAMP:2020-07-1516:36:24.238879LAST_QUEUED_TRANSACTION:LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION:QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP:0000-00-0000:00:00.0000002rowsinset(0.00sec)mysql_error.log中显示:2020-07-15T16:36:24.237434+08:00126[Warning][MY-010897][Repl]StoringMySQLusernameorpasswordinformationinthemasterinforepositoryisnotsecureandisthereforenotrecommended.PleaseconsiderusingtheUSERandPASSWORDconnectionoptionsforSTARTSLAVE;seethe'STARTSLAVESyntax'intheMySQLManualformoreinformation.2020-07-15T16:36:24.238903+08:00126[ERROR][MY-010584][Repl]SlaveI/Oforchannel'group_replication_recovery':errorconnectingtomaster'repl@smdw:12000'-retry-time:60retries:1message:Authenticationplugin'caching_sha2_password'reportederror:Authenticationrequiressecureconnection.Error_code:MY-0020612020-07-15T16:36:24.245949+08:0071[ERROR][MY-011582][Repl]Plugingroup_replicationreported:'Therewasanerrorwhenconnectingtothedonorserver.Pleasecheckthatgroup_replication_recoverychannelcredentialsandallMEMBER_HOSTcolumnvaluesofperformance_schema.replication_group_memberstablearecorrectandDNSresolvable.'2020-07-15T16:36:24.246011+08:0071[ERROR][MY-011583][Repl]Plugingroup_replicationreported:'Fordetailspleasecheckperformance_schema.replication_connection_statustableanderrorlogmessagesofSlaveI/Oforchannelgroup_replication_recovery.'2020-07-15T16:36:24.246227+08:0071[ERROR][MY-011574][Repl]Plugingroup_replicationreported:'Maximumnumberofretrieswhentryingtoconnecttoadonorreached.Abortinggroupreplicationincrementalrecovery.'2020-07-15T16:36:24.246332+08:0071[ERROR][MY-011620][Repl]Plugingroup_replicationreported:'FatalerrorduringtheincrementalrecoveryprocessofGroupReplication.Theserverwillleavethegroup.'2020-07-15T16:36:24.246648+08:0071[ERROR][MY-011712][Repl]Plugingroup_replicationreported:'Theserverwasautomaticallysetintoreadonlymodeafteranerrorwasdetected.'3.原因
万事不决看官方文档,官方文档有很明确说明:
Replication User With The Caching SHA-2 Authentication Plugin
Bydefault,userscreatedinMySQL8useSection6.4.1.2,“CachingSHA-2PluggableAuthentication”.IfthereplicationuseryouconfigurefordistributedrecoveryusesthecachingSHA-2authenticationplugin,andyouarenotusingSSLfordistributedrecoveryconnections,RSAkey-pairsareusedforpasswordexchange.FormoreinformationonRSAkey-pairs,seeSection6.3.3,“CreatingSSLandRSACertificatesandKeys”.Inthissituation,youcaneithercopythepublickeyoftherpl_usertothejoiningmember,orconfigurethedonorstoprovidethepublickeywhenrequested.Themoresecureapproachistocopythepublickeyofthereplicationuseraccounttothejoiningmember.Thenyouneedtoconfigurethegroup_replication_recovery_public_key_pathsystemvariableonthejoiningmemberwiththepathtothepublickeyforthereplicationuseraccount.Thelesssecureapproachistosetgroup_replication_recovery_get_public_key=ONondonorssothattheyprovidethepublickeyofthereplicationuseraccounttojoiningmembers.Thereisnowaytoverifytheidentityofaserver,thereforeonlysetgroup_replication_recovery_get_public_key=ONwhenyouaresurethereisnoriskofserveridentitybeingcompromised,forexamplebyaman-in-the-middleattack
可以看到,当确认环境安全以及没人任何人攻击集群时,如果不配置ssl,可以最低配置下面参数来在请求复制用户密钥时
给公钥:
group_replication_recovery_get_public_key=ON4.解决
mysql>startgroupreplication;mysql>startgroupreplication;mysql>setglobalgroup_replication_recovery_get_public_key=on;mysql>startgroupreplication;5.检查状态
mysql>select*fromperformance_schema.replication_connection_status\G;***************************1.row***************************CHANNEL_NAME:group_replication_applierGROUP_NAME:aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaaSOURCE_UUID:aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaaTHREAD_ID:NULLSERVICE_STATE:ONCOUNT_RECEIVED_HEARTBEATS:0LAST_HEARTBEAT_TIMESTAMP:0000-00-0000:00:00.000000RECEIVED_TRANSACTION_SET:aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-26:29,b2d6c335-c4b1-11ea-b3a6-3868dd209ff0:1-143778LAST_ERROR_NUMBER:0LAST_ERROR_MESSAGE:LAST_ERROR_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION:aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:29LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP:2020-07-1516:47:36.329298LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP:2020-07-1516:47:36.329384QUEUEING_TRANSACTION:QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP:0000-00-0000:00:00.000000***************************2.row***************************CHANNEL_NAME:group_replication_recoveryGROUP_NAME:SOURCE_UUID:THREAD_ID:NULLSERVICE_STATE:OFFCOUNT_RECEIVED_HEARTBEATS:0LAST_HEARTBEAT_TIMESTAMP:0000-00-0000:00:00.000000RECEIVED_TRANSACTION_SET:LAST_ERROR_NUMBER:0LAST_ERROR_MESSAGE:LAST_ERROR_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION:LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP:0000-00-0000:00:00.000000LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION:QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP:0000-00-0000:00:00.000000QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP:0000-00-0000:00:00.0000002rowsinset(0.00sec)ERROR:Noqueryspecifiedmysql>mysql>mysql>select*fromperformance_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|+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+2rowsinset(0.00sec)
感谢各位的阅读,以上就是“怎么解决mysql MGR无法连接主节点的问题”的内容了,经过本文的学习后,相信大家对怎么解决mysql MGR无法连接主节点的问题这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。