Description:
In an group replication cluster, when secondly member fail-over abnormal, the member state will be UNREACHABLE. But after a long while, it become ONLINE, while this instance still down.
How to repeat:
Step 1: First, init an group replication cluster with 3 instance.
phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13000 -h127.0.0.1 test
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
SET global group_replication_group_seeds='127.0.0.1:33061,127.0.0.1:33062,127.0.0.1:33063';
SET global group_replication_local_address='127.0.0.1:33061';
set global group_replication_group_name=BIN_TO_UUID(CAST('1' as BINARY(16)));
set global group_replication_single_primary_mode=ON;
set GLOBAL group_replication_bootstrap_group=ON;
CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='' FOR CHANNEL 'group_replication_recovery';
reset master;start group_replication;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.04 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (3.19 sec)
mysql> quit
Bye
phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13001 -h127.0.0.1 test
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
SET global group_replication_group_seeds='127.0.0.1:33061,127.0.0.1:33062,127.0.0.1:33063';
SET global group_replication_local_address='127.0.0.1:33062';
set global group_replication_group_name=BIN_TO_UUID(CAST('1' as BINARY(16)));
set global group_replication_single_primary_mode=ON;
set GLOBAL group_replication_bootstrap_group=OFF;
CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='' FOR CHANNEL 'group_replication_recovery';
reset master;start group_replication;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (3.96 sec)
mysql> quit
Bye
phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13002 -h127.0.0.1 test
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
SET global group_replication_group_seeds='127.0.0.1:33061,127.0.0.1:33062,127.0.0.1:33063';
SET global group_replication_local_address='127.0.0.1:33063';
et global group_replication_group_name=BIN_TO_UUID(CAST('1' as BINARY(16)));
set global group_replication_single_primary_mode=ON;
set GLOBAL group_replication_bootstrap_group=OFF;
CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='' FOR CHANNEL 'group_replication_recovery';
reset master;
start group_replication;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (4.38 sec)
mysql> quit
Bye
Step 2: now, check from port 13000, it have 3 members online
phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13000 -h127.0.0.1 test
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | b731e320-3132-11ea-81b4-c8f7507e5048 | 127.0.0.1 | 13000 | ONLINE | PRIMARY | 8.0.18 |
| group_replication_applier | b73ab886-3132-11ea-9dab-c8f7507e5048 | 127.0.0.1 | 13001 | ONLINE | SECONDARY | 8.0.18 |
| group_replication_applier | b747cde5-3132-11ea-a8ce-c8f7507e5048 | 127.0.0.1 | 13002 | ONLINE | SECONDARY | 8.0.18 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)
Step 3: Then, use kill -9 command to shutdown the server with port 13002. From port 13000, it will be 2 members finally.
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | b731e320-3132-11ea-81b4-c8f7507e5048 | 127.0.0.1 | 13000 | ONLINE | PRIMARY | 8.0.18 |
| group_replication_applier | b73ab886-3132-11ea-9dab-c8f7507e5048 | 127.0.0.1 | 13001 | ONLINE | SECONDARY | 8.0.18 |
| group_replication_applier | b747cde5-3132-11ea-a8ce-c8f7507e5048 | 127.0.0.1 | 13002 | UNREACHABLE | SECONDARY | 8.0.18 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | b731e320-3132-11ea-81b4-c8f7507e5048 | 127.0.0.1 | 13000 | ONLINE | PRIMARY | 8.0.18 |
| group_replication_applier | b73ab886-3132-11ea-9dab-c8f7507e5048 | 127.0.0.1 | 13001 | ONLINE | SECONDARY | 8.0.18 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)
step 4: Then use kill -9 for server with port 13001. From port 13000, the state will become below.
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | b731e320-3132-11ea-81b4-c8f7507e5048 | 127.0.0.1 | 13000 | ONLINE | PRIMARY | 8.0.18 |
| group_replication_applier | b73ab886-3132-11ea-9dab-c8f7507e5048 | 127.0.0.1 | 13001 | UNREACHABLE | SECONDARY | 8.0.18 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)
Step 5: That state will be stable for a long time. But I use an quite simple shell script to monitor the state from 13000, after long while (in my test, it takes about 5.5h), the state of 13001 become ONLINE now, while this server still shutdown.
phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13000 -h127.0.0.1 test
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | b731e320-3132-11ea-81b4-c8f7507e5048 | 127.0.0.1 | 13000 | ONLINE | PRIMARY | 8.0.18 |
| group_replication_applier | b73ab886-3132-11ea-9dab-c8f7507e5048 | 127.0.0.1 | 13001 | ONLINE | SECONDARY | 8.0.18 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.02 sec)
mysql> quit
Bye
phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13001 -h127.0.0.1 test
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
Description: In an group replication cluster, when secondly member fail-over abnormal, the member state will be UNREACHABLE. But after a long while, it become ONLINE, while this instance still down. How to repeat: Step 1: First, init an group replication cluster with 3 instance. phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13000 -h127.0.0.1 test mysql> set global group_replication_enforce_update_everywhere_checks=OFF; SET global group_replication_group_seeds='127.0.0.1:33061,127.0.0.1:33062,127.0.0.1:33063'; SET global group_replication_local_address='127.0.0.1:33061'; set global group_replication_group_name=BIN_TO_UUID(CAST('1' as BINARY(16))); set global group_replication_single_primary_mode=ON; set GLOBAL group_replication_bootstrap_group=ON; CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='' FOR CHANNEL 'group_replication_recovery'; reset master;start group_replication; Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.04 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (3.19 sec) mysql> quit Bye phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13001 -h127.0.0.1 test mysql> set global group_replication_enforce_update_everywhere_checks=OFF; SET global group_replication_group_seeds='127.0.0.1:33061,127.0.0.1:33062,127.0.0.1:33063'; SET global group_replication_local_address='127.0.0.1:33062'; set global group_replication_group_name=BIN_TO_UUID(CAST('1' as BINARY(16))); set global group_replication_single_primary_mode=ON; set GLOBAL group_replication_bootstrap_group=OFF; CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='' FOR CHANNEL 'group_replication_recovery'; reset master;start group_replication; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected, 1 warning (0.02 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (3.96 sec) mysql> quit Bye phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13002 -h127.0.0.1 test mysql> set global group_replication_enforce_update_everywhere_checks=OFF; SET global group_replication_group_seeds='127.0.0.1:33061,127.0.0.1:33062,127.0.0.1:33063'; SET global group_replication_local_address='127.0.0.1:33063'; et global group_replication_group_name=BIN_TO_UUID(CAST('1' as BINARY(16))); set global group_replication_single_primary_mode=ON; set GLOBAL group_replication_bootstrap_group=OFF; CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='' FOR CHANNEL 'group_replication_recovery'; reset master; start group_replication; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.03 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (4.38 sec) mysql> quit Bye Step 2: now, check from port 13000, it have 3 members online phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13000 -h127.0.0.1 test mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | b731e320-3132-11ea-81b4-c8f7507e5048 | 127.0.0.1 | 13000 | ONLINE | PRIMARY | 8.0.18 | | group_replication_applier | b73ab886-3132-11ea-9dab-c8f7507e5048 | 127.0.0.1 | 13001 | ONLINE | SECONDARY | 8.0.18 | | group_replication_applier | b747cde5-3132-11ea-a8ce-c8f7507e5048 | 127.0.0.1 | 13002 | ONLINE | SECONDARY | 8.0.18 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.01 sec) Step 3: Then, use kill -9 command to shutdown the server with port 13002. From port 13000, it will be 2 members finally. mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | b731e320-3132-11ea-81b4-c8f7507e5048 | 127.0.0.1 | 13000 | ONLINE | PRIMARY | 8.0.18 | | group_replication_applier | b73ab886-3132-11ea-9dab-c8f7507e5048 | 127.0.0.1 | 13001 | ONLINE | SECONDARY | 8.0.18 | | group_replication_applier | b747cde5-3132-11ea-a8ce-c8f7507e5048 | 127.0.0.1 | 13002 | UNREACHABLE | SECONDARY | 8.0.18 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.01 sec) mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | b731e320-3132-11ea-81b4-c8f7507e5048 | 127.0.0.1 | 13000 | ONLINE | PRIMARY | 8.0.18 | | group_replication_applier | b73ab886-3132-11ea-9dab-c8f7507e5048 | 127.0.0.1 | 13001 | ONLINE | SECONDARY | 8.0.18 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 2 rows in set (0.00 sec) step 4: Then use kill -9 for server with port 13001. From port 13000, the state will become below. mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | b731e320-3132-11ea-81b4-c8f7507e5048 | 127.0.0.1 | 13000 | ONLINE | PRIMARY | 8.0.18 | | group_replication_applier | b73ab886-3132-11ea-9dab-c8f7507e5048 | 127.0.0.1 | 13001 | UNREACHABLE | SECONDARY | 8.0.18 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 2 rows in set (0.00 sec) Step 5: That state will be stable for a long time. But I use an quite simple shell script to monitor the state from 13000, after long while (in my test, it takes about 5.5h), the state of 13001 become ONLINE now, while this server still shutdown. phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13000 -h127.0.0.1 test mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | b731e320-3132-11ea-81b4-c8f7507e5048 | 127.0.0.1 | 13000 | ONLINE | PRIMARY | 8.0.18 | | group_replication_applier | b73ab886-3132-11ea-9dab-c8f7507e5048 | 127.0.0.1 | 13001 | ONLINE | SECONDARY | 8.0.18 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 2 rows in set (0.02 sec) mysql> quit Bye phoenix@phoenix-Latitude-5491:~/gitlab/myrocks$ mysql -uroot -P13001 -h127.0.0.1 test ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)