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

节点长时间RECOVERING 状态

MYSQL database replication Error MY-002061

start group_replication;

ysql> select * from replication_group_members;

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

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

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

| group_replication_applier | c2840554-f56b-11e9-8d9b-6cb3113192fc | 192.xxx.xxx.xxx | 3316 | ONLINE | PRIMARY | 8.0.18 |

| group_replication_applier | e0c97c95-f571-11e9-933b-001b785baebb | 192.xxx.xxx.xxx | 3316 | RECOVERING | PRIMARY | 8.0.18 |

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

2 rows in set (0.00 sec)

查看日志:

ication recovery donor c2840554-f56b-11e9-8d9b-6cb3113192fc at 192.xxx.xxx.xxx port: 3316.'

2019-10-29T06: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.

2019-10-29T06:49:42.237406Z 17 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'username@192.xxx.xxx.xxx:3316' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

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

2019-10-29T06: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

2019-10-29T06: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

2019-10-29T06: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

2019-10-29T06: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.'

2019-10-29T06: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.'

2019-10-29T06: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;

"Incompatible Change: The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption

than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these

superior security and performance characteristics of caching_sha2_password, it is now the preferred authentication plugin,

and is also the default authentication plugin rather than mysql_native_password. ..."

来源：oschina

链接：https://my.oschina.net/u/4380991/blog/3359612