对 multi primary 模式的三台虚拟机做了掉电重启,发现mysql 启动后,group replication 不正常.
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
查询 replication_group_members
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4bd106a1-3bee-11e9-8034-080027c780f8 | node1 | 3306 | OFFLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
手动启动报错
mysql> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
下面为报错的完整日志
2022-09-27T14:14:12.486708+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql2:33061 on local port: 33061.'
2022-09-27T14:14:12.486954+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql3:33061 on local port: 33061.'
2022-09-27T14:14:12.487551+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql2:33061 on local port: 33061.'
2022-09-27T14:14:12.488017+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql3:33061 on local port: 33061.'
2022-09-27T14:14:12.488379+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql2:33061 on local port: 33061.'
2022-09-27T14:14:12.488788+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql3:33061 on local port: 33061.'
2022-09-27T14:14:12.489109+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql2:33061 on local port: 33061.'
2022-09-27T14:14:12.489346+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql3:33061 on local port: 33061.'
2022-09-27T14:14:12.489582+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql2:33061 on local port: 33061.'
2022-09-27T14:14:12.489819+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql3:33061 on local port: 33061.'
2022-09-27T14:14:12.490139+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql2:33061 on local port: 33061.'
2022-09-27T14:14:12.490370+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql3:33061 on local port: 33061.'
2022-09-27T14:14:12.490600+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql2:33061 on local port: 33061.'
2022-09-27T14:14:12.490814+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql3:33061 on local port: 33061.'
2022-09-27T14:14:12.491047+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql2:33061 on local port: 33061.'
2022-09-27T14:14:12.491247+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql3:33061 on local port: 33061.'
2022-09-27T14:14:12.491466+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to mysql2:33061 on local port: 33061.'
2022-09-27T14:14:12.491752+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported:
分析查看日志信息，节点开始组复制后，会连接其他节点，获取信息，但是无法连接，因为此时集群中其他节点尚未开始组复制，集群中也没有主节点。
在一节点上执行如下命令，让该节点成为主节点，其他节点复制该节点上的数据。
主节点执行
>SET
GLOBAL group_replication_bootstrap_group=ON;
>START
GROUP_REPLICATION;
>SET
GLOBAL group_replication_bootstrap_group=OFF;
启动正常
然后在其它节点依次执行 start group_replication
其中有一个节点提示如下:
2022-09-27T14:15:48.437630+08:00 48 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2022-09-27T14:15:48.439387+08:00 8 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the allowlist. It is mandatory that it is added.'
2022-09-27T14:15:48.439434+08:00 8 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv6 localhost address to the allowlist. It is mandatory that it is added.'
2022-09-27T14:15:48.440104+08:00 49 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2022-09-27T14:15:50.687672+08:00 0 [System] [MY-013471] [Repl] Plugin group_replication reported: 'Distributed recovery will transfer data using: Incremental recovery from a group donor'
2022-09-27T14:15:51.696587+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to mysql1:3306 on view 16642593496850192:1.'
2022-09-27T14:15:51.697043+08:00 0 [System] [MY-011490] [Repl] Plugin group_replication reported: 'This server was declared online within the replication group.'
2022-09-27T14:15:52.699524+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address mysql1:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
2022-09-27T14:15:52.706251+08:00 86 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.'
2022-09-27T14:15:52.706609+08:00 86 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2022-09-27T14:15:52.706821+08:00 92 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'
2022-09-27T14:16:27.856386+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to mysql1:3306, mysql2:3306 on view 16642593496850192:2.'
2022-09-27T14:16:29.009611+08:00 0 [System] [MY-011492] [Repl] Plugin group_replication reported: 'The member with address mysql2:3306 was declared online within the replication group.'
2022-09-27T14:16:49.409288+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to mysql1:3306, mysql2:3306, mysql3:3306 on view 16642593496850192:3.'
2022-09-27T14:16:50.629373+08:00 0 [System] [MY-011492] [Repl] Plugin group_replication reported: 'The member with address mysql3:3306 was declared online within the replication group.
日志也提示要设置 group_replication_allow_local_disjoint_gtids_join 这个参数.
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> start group_replication;
最终的结果
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4bd106a1-3bee-11e9-8034-080027c780f8 | node1 | 3306 | ONLINE |
| group_replication_applier | 523c134d-3bee-11e9-b57a-08002756ee51 | node2 | 3306 | ONLINE |
| group_replication_applier | 56cf559d-3bee-11e9-abb2-080027366485 | node3 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
