os: ubuntu 16.04
db: mysql 5.7.25

规划如下:

192.168.56.92	node1 # mysql
192.168.56.90	node2 # mysql
192.168.56.88	node3 # mysql

对 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.

查看 error 日志

截取了出错那一部分

# cat /var/log/mysql/error.log


2019-03-01T07:32:10.914832Z 2 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2019-03-01T07:32:10.918830Z 2 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2019-03-01T07:32:10.918883Z 2 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2019-03-01T07:32:10.918896Z 2 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"; group_replication_local_address: "192.168.56.92:24901"; group_replication_group_seeds: "192.168.56.92:24901,192.168.56.90:24901,192.168.56.88:24901"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "192.168.56.0/24,127.0.0.1/8"'
2019-03-01T07:32:10.920569Z 2 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2019-03-01T07:32:10.920585Z 2 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2019-03-01T07:32:10.920599Z 2 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1; member_uuid: "4bd106a1-3bee-11e9-8034-080027c780f8"; single-primary mode: "false"; group_replication_auto_increment_increment: 7; '
2019-03-01T07:32:10.920910Z 3 [Note] '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=''.
2019-03-01T07:32:10.964409Z 2 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2019-03-01T07:32:10.964450Z 2 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2019-03-01T07:32:10.964454Z 2 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1'
2019-03-01T07:32:10.964411Z 6 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql-relay-bin-group_replication_applier.000004' position: 4
2019-03-01T07:32:11.014022Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2019-03-01T07:32:11.014046Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 24901'
2019-03-01T07:32:14.112329Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:32:17.194084Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:32:17.196238Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:32:20.255982Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:32:20.257042Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:32:23.327291Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:32:33.706863Z 0 [Warning] Plugin group_replication reported: 'read failed'
2019-03-01T07:32:33.707120Z 0 [Warning] Plugin group_replication reported: 'read failed'
2019-03-01T07:32:33.714650Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 24901'
2019-03-01T07:33:10.965870Z 2 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2019-03-01T07:33:10.965910Z 2 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2019-03-01T07:33:10.965923Z 2 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
2019-03-01T07:33:10.966089Z 2 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2019-03-01T07:33:10.966103Z 2 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2019-03-01T07:33:10.966417Z 6 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2019-03-01T07:33:10.966438Z 6 [Note] Slave SQL thread for channel 'group_replication_applier' exiting, replication stopped in log 'FIRST' at position 0
2019-03-01T07:33:10.981720Z 3 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'

查询 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> 

手动启动报错

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.

下面为报错的完整日志

2019-03-01T07:52:22.806240Z 7 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2019-03-01T07:52:22.806467Z 7 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2019-03-01T07:52:22.806511Z 7 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2019-03-01T07:52:22.806524Z 7 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"; group_replication_local_address: "192.168.56.92:24901"; group_replication_group_seeds: "192.168.56.92:24901,192.168.56.90:24901,192.168.56.88:24901"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "192.168.56.0/24,127.0.0.1/8"'
2019-03-01T07:52:22.806548Z 7 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2019-03-01T07:52:22.806552Z 7 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2019-03-01T07:52:22.806565Z 7 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1; member_uuid: "4bd106a1-3bee-11e9-8034-080027c780f8"; single-primary mode: "false"; group_replication_auto_increment_increment: 7; '
2019-03-01T07:52:22.806926Z 24 [Note] '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=''.
2019-03-01T07:52:22.848847Z 27 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql-relay-bin-group_replication_applier.000004' position: 4
2019-03-01T07:52:22.848874Z 7 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2019-03-01T07:52:22.848930Z 7 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2019-03-01T07:52:22.848934Z 7 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1'
2019-03-01T07:52:22.850801Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2019-03-01T07:52:22.850846Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 24901'
2019-03-01T07:52:22.851919Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:52:22.852288Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:52:22.852575Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:52:22.852960Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:52:22.853374Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:52:22.854954Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:52:22.855350Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:52:22.856074Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:52:22.857623Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:52:22.858657Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:52:22.858998Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:52:22.859342Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:52:22.859586Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:52:22.859906Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:52:22.860200Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:52:22.860538Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:52:22.861014Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:52:22.861406Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:52:22.861801Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.90:24901 on local port: 24901.'
2019-03-01T07:52:22.862010Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.56.88:24901 on local port: 24901.'
2019-03-01T07:52:22.862030Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 24901'
2019-03-01T07:52:22.862137Z 0 [Warning] Plugin group_replication reported: 'read failed'
2019-03-01T07:52:22.877302Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 24901'
2019-03-01T07:53:22.850477Z 7 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2019-03-01T07:53:22.850541Z 7 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2019-03-01T07:53:22.850559Z 7 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
2019-03-01T07:53:22.850629Z 7 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2019-03-01T07:53:22.850641Z 7 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2019-03-01T07:53:22.850890Z 27 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2019-03-01T07:53:22.850935Z 27 [Note] Slave SQL thread for channel 'group_replication_applier' exiting, replication stopped in log 'FIRST' at position 0
2019-03-01T07:53:22.853590Z 24 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'

分析查看日志信息，节点开始组复制后，会连接其他节点，获取信息，但是无法连接，因为此时集群中其他节点尚未开始组复制，集群中也没有主节点。
在一节点上执行如下命令，让该节点成为主节点，其他节点复制该节点上的数据。

mysql> set global group_replication_bootstrap_group = ON; 
mysql> start group_replication; 

启动正常

2019-03-01T08:23:40.582795Z 7 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2019-03-01T08:23:40.582915Z 7 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2019-03-01T08:23:40.582959Z 7 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2019-03-01T08:23:40.582972Z 7 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"; group_replication_local_address: "192.168.56.92:24901"; group_replication_group_seeds: "192.168.56.92:24901,192.168.56.90:24901,192.168.56.88:24901"; group_replication_bootstrap_group: true; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "192.168.56.0/24,127.0.0.1/8"'
2019-03-01T08:23:40.582997Z 7 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2019-03-01T08:23:40.583002Z 7 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2019-03-01T08:23:40.583014Z 7 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1; member_uuid: "4bd106a1-3bee-11e9-8034-080027c780f8"; single-primary mode: "false"; group_replication_auto_increment_increment: 7; '
2019-03-01T08:23:40.583316Z 44 [Note] '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=''.
2019-03-01T08:23:40.621040Z 47 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql-relay-bin-group_replication_applier.000004' position: 4
2019-03-01T08:23:40.621078Z 7 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2019-03-01T08:23:40.621096Z 7 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2019-03-01T08:23:40.621100Z 7 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1'
2019-03-01T08:23:40.623123Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2019-03-01T08:23:40.623348Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 24901'
2019-03-01T08:23:41.626552Z 50 [Note] Plugin group_replication reported: 'Only one server alive. Declaring this server as online within the replication group'
2019-03-01T08:23:41.626645Z 0 [Note] Plugin group_replication reported: 'Group membership changed to node1:3306 on view 15514286216262210:1.'
2019-03-01T08:23:41.630509Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'

然后在其它节点依次执行 start group_replication

其中有一个节点提示如下:

2019-03-01T08:25:23.403839Z 7 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2019-03-01T08:25:23.403969Z 7 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2019-03-01T08:25:23.404016Z 7 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2019-03-01T08:25:23.404030Z 7 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"; group_replication_local_address: "192.168.56.88:24901"; group_replication_group_seeds: "192.168.56.92:24901,192.168.56.90:24901,192.168.56.88:24901"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "192.168.56.0/24,127.0.0.1/8"'
2019-03-01T08:25:23.404043Z 7 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2019-03-01T08:25:23.404048Z 7 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2019-03-01T08:25:23.404061Z 7 [Note] Plugin group_replication reported: 'Member configuration: member_id: 3; member_uuid: "56cf559d-3bee-11e9-abb2-080027366485"; single-primary mode: "false"; group_replication_auto_increment_increment: 7; '
2019-03-01T08:25:23.404374Z 20 [Note] '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=''.
2019-03-01T08:25:23.431752Z 23 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql-relay-bin-group_replication_applier.000004' position: 4
2019-03-01T08:25:23.431755Z 7 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2019-03-01T08:25:23.432006Z 7 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2019-03-01T08:25:23.432049Z 7 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 3'
2019-03-01T08:25:23.434643Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2019-03-01T08:25:23.435183Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 24901'
2019-03-01T08:25:25.850123Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 4bd106a1-3bee-11e9-8034-080027c780f8:1,
56cf559d-3bee-11e9-abb2-080027366485:1-2,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-7:1000005-1000006:2000005-2000006 > Group transactions: 4bd106a1-3bee-11e9-8034-080027c780f8:1-3,
523c134d-3bee-11e9-b57a-08002756ee51:1-2,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10:1000005-1000006:2000005-2000006'
2019-03-01T08:25:25.850225Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
2019-03-01T08:25:25.850257Z 0 [Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'
2019-03-01T08:25:25.850295Z 0 [Note] Plugin group_replication reported: 'Group membership changed to node1:3306, node2:3306, node3:3306 on view 15514286216262210:5.'
2019-03-01T08:25:25.850324Z 7 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2019-03-01T08:25:29.259746Z 0 [Note] Plugin group_replication reported: 'Group membership changed: This member has left the group.'
2019-03-01T08:25:34.260804Z 7 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2019-03-01T08:25:34.260898Z 7 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2019-03-01T08:25:34.261221Z 23 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2019-03-01T08:25:34.261242Z 23 [Note] Slave SQL thread for channel 'group_replication_applier' exiting, replication stopped in log 'FIRST' at position 0
2019-03-01T08:25:34.266360Z 20 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'

日志也提示要设置 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)

参考:
https://dev.mysql.com/doc/refman/5.7/en/group-replication.html
https://dev.mysql.com/doc/refman/5.7/en/group-replication-options.html
https://dev.mysql.com/doc/refman/5.7/en/group-replication-options.html#sysvar_group_replication_bootstrap_group
https://dev.mysql.com/doc/refman/5.7/en/group-replication-options.html#sysvar_group_replication_allow_local_disjoint_gtids_join