问题描述:mysql在开启group_replication后,状态显示为RECOVERING,告警日志报错MY-013117、MY-011582、MY-011583,如下所示:
数据库:MySQL 8.0.27
系统:rhel 7.3 64位
1、问题重现
Slave02[(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 5d4170a4-3987-11ee-af25-0050563ccee2 | MySQL-827MGR-Slave02 | 3306 | RECOVERING | SECONDARY | 8.0.27 | XCom |
| group_replication_applier | 9259a41b-3985-11ee-a5e4-000c299ea627 | MySQL-827MGR-Master | 3306 | ONLINE | PRIMARY | 8.0.27 | XCom |
| group_replication_applier | 9e1ab21c-3986-11ee-ad9a-0050562d243d | MySQL-827MGR-Slave01 | 3306 | ONLINE | SECONDARY | 8.0.27 | XCom |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
告警日志:
2023-08-13T08:00:05.619734Z 78 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='MySQL-827MGR-Master', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='MySQL-827MGR-Master', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2023-08-13T08:00:05.627711Z 91 [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.
2023-08-13T08:00:05.627785Z 91 [ERROR] [MY-013117] [Repl] Slave I/O for channel 'group_replication_recovery': Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: MY-013117
2023-08-13T08:00:05.632043Z 78 [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.'
2023-08-13T08:00:05.632085Z 78 [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.'
2023-08-13T08:00:05.641811Z 78 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='MySQL-827MGR-Master', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='MySQL-827MGR-Slave01', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2023-08-13T08:00:05.649568Z 97 [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.
2023-08-13T08:00:05.649672Z 97 [ERROR] [MY-013117] [Repl] Slave I/O for channel 'group_replication_recovery': Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: MY-013117
2023-08-13T08:00:05.652721Z 78 [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.'
2023-08-13T08:00:05.652788Z 78 [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.'
2、异常原因
未在节点2执行change master命令.
3、解决步骤
Slave02[(none)]> stop group_replication;
Query OK, 0 rows affected (1.00 sec)
Slave02[(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 5d4170a4-3987-11ee-af25-0050563ccee2 | MySQL-827MGR-Slave02 | 3306 | OFFLINE | | | XCom |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
Slave02[(none)]> change master to master_user="repl",master_password="repl@12345" for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.00 sec)
Slave02[(none)]> start group_replication;
Query OK, 0 rows affected (6.47 sec)
Slave02[(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 5d4170a4-3987-11ee-af25-0050563ccee2 | MySQL-827MGR-Slave02 | 3306 | ONLINE | SECONDARY | 8.0.27 | XCom |
| group_replication_applier | 9259a41b-3985-11ee-a5e4-000c299ea627 | MySQL-827MGR-Master | 3306 | ONLINE | PRIMARY | 8.0.27 | XCom |
| group_replication_applier | 9e1ab21c-3986-11ee-ad9a-0050562d243d | MySQL-827MGR-Slave01 | 3306 | ONLINE | SECONDARY | 8.0.27 | XCom |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.01 sec)
说明:如上所示,成功开启MGR.