mysql mgr recovering_mysql 8.0.18 mgr节点状态长时间处于RECOVERING 状态

最新推荐文章于 2024-05-28 10:40:39 发布

力力nevergg

最新推荐文章于 2024-05-28 10:40:39 发布

阅读量742

点赞数

文章标签： mysql mgr recovering

本文链接：https://blog.csdn.net/weixin_34673225/article/details/113395187

版权

在MySQL 8.0.18中，遇到MGR（Group Replication）节点状态长时间显示为RECOVERING。错误日志显示由于Authentication plugin 'caching_sha2_password'导致连接失败。解决方法包括修改认证方式为sha256_password，更新用户权限，并启用/禁用SQL日志以进行修复。

摘要由CSDN通过智能技术生成

MYSQL database replication Error MY-002061

start group_replication;

ysql> select * from replication_group_members;

+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+

+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+

| group_replication_applier | c2840554-f56b-11e9-8d9b-6cb3113192fc | 192.168.150.11 | 3316 | ONLINE | PRIMARY | 8.0.18 |

| group_replication_applier | e0c97c95-f571-11e9-933b-001b785baebb | 192.168.150.12 | 3316 | RECOVERING | PRIMARY | 8.0.18 |

+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+

2 rows in set (0.00 sec)

查看日志/var/log/mysql/mysql.log

2020-03-08T06:49:42.234078Z 17 [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-03-08T06:49:42.237406Z 17 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'username@192.168.150.12:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

2020-03-08T06:49:42.237448Z 17 [Note] [MY-010563] [Repl] Slave I/O thread for channel 'group_replication_recovery' killed while connecting to master

2020-03-08T06:49:42.237465Z 17 [Note] [MY-010570] [Repl] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4

2020-03-08T06:49:42.240670Z 18 [Note] [MY-010581] [Repl] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log '/mysqldata/mysql_3316/logs/relaylog/relay-bin-group_replication_recovery.000001' position: 4

2020-03-08T06:49:42.240730Z 18 [Note] [MY-010587] [Repl] Slave SQL thread for channel 'group_replication_recovery' exiting, replication stopped in log 'FIRST' at position 0

2020-03-08T06:49:42.246999Z 16 [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-03-08T06:49:42.247044Z 16 [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-03-08T06:49:42.247241Z 16 [Note] [MY-011577] [Repl] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 2/10'

报错

解决

SET SQL_LOG_BIN=0; 不记录日志

alter USER username@'xxx.xxx.xxx.%' IDENTIFIED WITH sha256_password BY 'password'; 修改认证方式

GRANT REPLICATION SLAVE ON *.* TO 'username'@'192.xxx.xxx.%';

SET SQL_LOG_BIN=1; 记录日志