MGR無法連線主節點Authentication plugin 'caching_sha2_password'
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-002061 2020-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.000000 LAST_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.000000 LAST_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 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-002061 2020-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 authentication plugin, and you are not using SSL for distributed recovery connections, RSA key-pairs are used for password exchange. For more information on RSA key-pairs, see Section 6.3.3, “Creating SSL and RSA Certificates and Keys”. In this situation, you can either copy the public key of the rpl_user to the joining member, or configure the donors to provide the public key when requested. The more secure approach is to copy the public key of the replication user account to the joining member. Then you need to configure the group_replication_recovery_public_key_path system variable on the joining member with the path to the public key for the replication user account. The less secure approach is to set group_replication_recovery_get_public_key=ON on donors so that they provide the public key of the replication user account to joining members. There is no way to verify the identity of a server, therefore only set group_replication_recovery_get_public_key=ON when you are sure there is no risk of server 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.000000 LAST_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.000000 LAST_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 rows in set (0.00 sec) ERROR: No query specified mysql> 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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31439444/viewspace-2704763/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL authentication plugin 'caching_sha2_password' cannot be loaded 問題MySqlPlugin
- RAC節點啟動失敗--ASM無法連線ASM
- 遠端連線MySQL錯誤“plugin caching_sha2_password could not be loaded”的解決辦法MySqlPlugin
- ray叢集work節點無法在不同wifi遠端連線的問題WiFi
- SQLyog連線資料庫報錯：錯誤號碼2058 plugin caching_sha2_password could not be loadedSQL資料庫Plugin
- authentication plugin caching_sha2Plugin
- SQLServer 2012 AG主節點無法截斷事務日誌SQLServer
- ProxySQL+MGR實現讀寫分離和主節點故障無感知切換 - 完整操作記錄SQL
- 如何確定Single-Primary模式下的MGR主節點(文件 ID 2214438.1)模式
- mysql 遠端無法連線MySql
- bitbucket無法使用ssh連線
- centos無法建立ssl連線CentOS
- milvus 異常——無法連線
- 無法連線遠端，Windows系統電腦無法遠端連線怎麼辦Windows
- 網路卡-熱點搜尋不到或者無法連線問題
- 軟連線、硬連結 和 i節點（inode）
- 本地無法連線Mysql的原因MySql
- linux 下 github ssh 無法連線LinuxGithub
- springboot連線hive無法啟動Spring BootHive
- 使用Docker Context連線遠端節點DockerContext
- 以太坊公鏈節點連線節點超時問題排查
- 如何讓 MGR 不從 Primary 節點克隆資料？
- [Kubernetes]node節點pod無法啟動/節點刪除網路重置
- link/Extended dependency 無法顯示連線
- 無法連線java visualvm外掛中心JavaLVM
- 連線上網路無法上網
- Windows無法配置此無線連線這個問題的解決辦法Windows
- windowsXP使用者無法遠端桌面連線天翼雲2008雲主機？Windows
- 用putty連線AWS，報錯“No supported authentication methods available"AI
- 貝塞爾曲線(Bezier curve)實現節點連線
- wget下載提示：無法建立SSL連線wget
- 遠端桌面無法連線伺服器？伺服器
- 無法連線到web伺服器iisWeb伺服器
- mysql8 無法連線navicat問題MySql
- raw.githubusercontent.com 無法建立 SSL 連線Github
- 印表機無法連線怎麼辦 印表機連線不了的方法
- win10突然無法連線wifi怎麼辦_win10突然無法連線到wifi如何解決Win10WiFi
- 沃趣微講堂 | PXC、MGC&MGR原理與實踐對比（五）：MGR新增節點流程GC