-- Setup 3 node cluster hod03 ================= rm -rf data/ mkdir data bin/mysqld --defaults-file=./89141.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/data/ bin/mysqld --defaults-file=./89141.cnf --log-error=hod03.err --core-file 2>&1 & hod04 ================= rm -rf data/ mkdir data bin/mysqld --defaults-file=./89141.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/data/ bin/mysqld --defaults-file=./89141.cnf --log-error=hod04.err --core-file 2>&1 & hod06 ================= rm -rf data/ mkdir data bin/mysqld --defaults-file=./89141.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/data/ bin/mysqld --defaults-file=./89141.cnf --log-error=hod06.err --core-file 2>&1 & ################# Configure, Start GR - hod03(node 1) CREATE USER 'rpl_user'@'%' IDENTIFIED BY 'rpl_pass'; GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; SHOW GLOBAL VARIABLES LIKE 'group_replication%'; SET GLOBAL group_replication_bootstrap_group=1; CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=0; SELECT * FROM performance_schema.replication_connection_status\G SELECT * FROM performance_schema.replication_group_member_stats\G SELECT * FROM performance_schema.replication_group_members; - hod04/hod06(node 2 and 3) SHOW GLOBAL VARIABLES LIKE 'group_replication%'; CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'; START GROUP_REPLICATION; SELECT * FROM performance_schema.replication_connection_status\G SELECT * FROM performance_schema.replication_group_member_stats\G SELECT * FROM performance_schema.replication_group_members; -- hod03 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: bin/mysql -uroot -S /tmp/mysql_hod03.sock --prompt='hod03>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.20-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. hod03> CREATE USER 'rpl_user'@'%' IDENTIFIED BY 'rpl_pass'; Query OK, 0 rows affected (0.00 sec) hod03> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; Query OK, 0 rows affected (0.00 sec) hod03> SHOW GLOBAL VARIABLES LIKE 'group_replication%'; +----------------------------------------------------+----------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------------------------+----------------------------------------------------------------------------+ | group_replication_allow_local_disjoint_gtids_join | OFF | | group_replication_allow_local_lower_version_join | OFF | | group_replication_auto_increment_increment | 7 | | group_replication_bootstrap_group | OFF | | group_replication_components_stop_timeout | 31536000 | | group_replication_compression_threshold | 1000000 | | group_replication_enforce_update_everywhere_checks | OFF | | group_replication_flow_control_applier_threshold | 25000 | | group_replication_flow_control_certifier_threshold | 25000 | | group_replication_flow_control_mode | QUOTA | | group_replication_force_members | | | group_replication_group_name | 9d7f8c28-c02c-11e6-9829-08002715584a | | group_replication_group_seeds | hod03.no.oracle.com:6606,hod04.no.oracle.com:6607,hod06.no.oracle.com:6608 | | group_replication_gtid_assignment_block_size | 1000000 | | group_replication_ip_whitelist | AUTOMATIC | | group_replication_local_address | hod03.no.oracle.com:6606 | | group_replication_member_weight | 50 | | group_replication_poll_spin_loops | 0 | | group_replication_recovery_complete_at | TRANSACTIONS_APPLIED | | group_replication_recovery_reconnect_interval | 60 | | group_replication_recovery_retry_count | 10 | | group_replication_recovery_ssl_ca | | | group_replication_recovery_ssl_capath | | | group_replication_recovery_ssl_cert | | | group_replication_recovery_ssl_cipher | | | group_replication_recovery_ssl_crl | | | group_replication_recovery_ssl_crlpath | | | group_replication_recovery_ssl_key | | | group_replication_recovery_ssl_verify_server_cert | OFF | | group_replication_recovery_use_ssl | OFF | | group_replication_single_primary_mode | ON | | group_replication_ssl_mode | DISABLED | | group_replication_start_on_boot | OFF | | group_replication_transaction_size_limit | 0 | | group_replication_unreachable_majority_timeout | 0 | +----------------------------------------------------+----------------------------------------------------------------------------+ 35 rows in set (0.00 sec) hod03> SET GLOBAL group_replication_bootstrap_group=1; Query OK, 0 rows affected (0.00 sec) hod03> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.01 sec) hod03> START GROUP_REPLICATION; Query OK, 0 rows affected (2.52 sec) hod03> SET GLOBAL group_replication_bootstrap_group=0; Query OK, 0 rows affected (0.00 sec) hod03> SELECT * FROM performance_schema.replication_connection_status\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier GROUP_NAME: 9d7f8c28-c02c-11e6-9829-08002715584a SOURCE_UUID: 9d7f8c28-c02c-11e6-9829-08002715584a THREAD_ID: NULL SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: 9d7f8c28-c02c-11e6-9829-08002715584a:1 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 1 row in set (0.00 sec) hod03> SELECT * FROM performance_schema.replication_group_member_stats\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 15155857453602698:1 MEMBER_ID: f376b3a4-f5fc-11e7-8f09-0010e05f3e06 COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 0 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: LAST_CONFLICT_FREE_TRANSACTION: 1 row in set (0.00 sec) hod03> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | f376b3a4-f5fc-11e7-8f09-0010e05f3e06 | hod03 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 1 row in set (0.00 sec) hod03> -- hod04 [umshastr@hod04]/export/home/ushastry/mysql-5.7.20: bin/mysql -uroot -S /tmp/mysql_hod04.sock --prompt='hod04>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.20-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. hod04> SHOW GLOBAL VARIABLES LIKE 'group_replication%'; +----------------------------------------------------+----------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------------------------+----------------------------------------------------------------------------+ | group_replication_allow_local_disjoint_gtids_join | OFF | | group_replication_allow_local_lower_version_join | OFF | | group_replication_auto_increment_increment | 7 | | group_replication_bootstrap_group | OFF | | group_replication_components_stop_timeout | 31536000 | | group_replication_compression_threshold | 1000000 | | group_replication_enforce_update_everywhere_checks | OFF | | group_replication_flow_control_applier_threshold | 25000 | | group_replication_flow_control_certifier_threshold | 25000 | | group_replication_flow_control_mode | QUOTA | | group_replication_force_members | | | group_replication_group_name | 9d7f8c28-c02c-11e6-9829-08002715584a | | group_replication_group_seeds | hod03.no.oracle.com:6606,hod04.no.oracle.com:6607,hod06.no.oracle.com:6608 | | group_replication_gtid_assignment_block_size | 1000000 | | group_replication_ip_whitelist | AUTOMATIC | | group_replication_local_address | hod04.no.oracle.com:6607 | | group_replication_member_weight | 50 | | group_replication_poll_spin_loops | 0 | | group_replication_recovery_complete_at | TRANSACTIONS_APPLIED | | group_replication_recovery_reconnect_interval | 60 | | group_replication_recovery_retry_count | 10 | | group_replication_recovery_ssl_ca | | | group_replication_recovery_ssl_capath | | | group_replication_recovery_ssl_cert | | | group_replication_recovery_ssl_cipher | | | group_replication_recovery_ssl_crl | | | group_replication_recovery_ssl_crlpath | | | group_replication_recovery_ssl_key | | | group_replication_recovery_ssl_verify_server_cert | OFF | | group_replication_recovery_use_ssl | OFF | | group_replication_single_primary_mode | ON | | group_replication_ssl_mode | DISABLED | | group_replication_start_on_boot | OFF | | group_replication_transaction_size_limit | 0 | | group_replication_unreachable_majority_timeout | 0 | +----------------------------------------------------+----------------------------------------------------------------------------+ 35 rows in set (0.00 sec) hod04> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.00 sec) hod04> START GROUP_REPLICATION; Query OK, 0 rows affected (7.19 sec) hod04> SELECT * FROM performance_schema.replication_connection_status\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_recovery GROUP_NAME: SOURCE_UUID: THREAD_ID: NULL SERVICE_STATE: OFF COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier GROUP_NAME: 9d7f8c28-c02c-11e6-9829-08002715584a SOURCE_UUID: 9d7f8c28-c02c-11e6-9829-08002715584a THREAD_ID: NULL SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: 9d7f8c28-c02c-11e6-9829-08002715584a:2 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 2 rows in set (0.00 sec) hod04> SELECT * FROM performance_schema.replication_group_member_stats\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 15155857453602698:2 MEMBER_ID: f62774f2-f5fc-11e7-ab5b-0010e05f4178 COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 0 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: LAST_CONFLICT_FREE_TRANSACTION: 1 row in set (0.00 sec) hod04> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | f376b3a4-f5fc-11e7-8f09-0010e05f3e06 | hod03 | 3306 | ONLINE | | group_replication_applier | f62774f2-f5fc-11e7-ab5b-0010e05f4178 | hod04 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 2 rows in set (0.00 sec) hod04> -- hod06 [umshastr@hod06]/export/home/ushastry/mysql-5.7.20: bin/mysql -uroot -S /tmp/mysql_hod06.sock --prompt='hod06>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.20-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. hod06> SHOW GLOBAL VARIABLES LIKE 'group_replication%'; +----------------------------------------------------+----------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------------------------+----------------------------------------------------------------------------+ | group_replication_allow_local_disjoint_gtids_join | OFF | | group_replication_allow_local_lower_version_join | OFF | | group_replication_auto_increment_increment | 7 | | group_replication_bootstrap_group | OFF | | group_replication_components_stop_timeout | 31536000 | | group_replication_compression_threshold | 1000000 | | group_replication_enforce_update_everywhere_checks | OFF | | group_replication_flow_control_applier_threshold | 25000 | | group_replication_flow_control_certifier_threshold | 25000 | | group_replication_flow_control_mode | QUOTA | | group_replication_force_members | | | group_replication_group_name | 9d7f8c28-c02c-11e6-9829-08002715584a | | group_replication_group_seeds | hod03.no.oracle.com:6606,hod04.no.oracle.com:6607,hod06.no.oracle.com:6608 | | group_replication_gtid_assignment_block_size | 1000000 | | group_replication_ip_whitelist | AUTOMATIC | | group_replication_local_address | hod06.no.oracle.com:6608 | | group_replication_member_weight | 50 | | group_replication_poll_spin_loops | 0 | | group_replication_recovery_complete_at | TRANSACTIONS_APPLIED | | group_replication_recovery_reconnect_interval | 60 | | group_replication_recovery_retry_count | 10 | | group_replication_recovery_ssl_ca | | | group_replication_recovery_ssl_capath | | | group_replication_recovery_ssl_cert | | | group_replication_recovery_ssl_cipher | | | group_replication_recovery_ssl_crl | | | group_replication_recovery_ssl_crlpath | | | group_replication_recovery_ssl_key | | | group_replication_recovery_ssl_verify_server_cert | OFF | | group_replication_recovery_use_ssl | OFF | | group_replication_single_primary_mode | ON | | group_replication_ssl_mode | DISABLED | | group_replication_start_on_boot | OFF | | group_replication_transaction_size_limit | 0 | | group_replication_unreachable_majority_timeout | 0 | +----------------------------------------------------+----------------------------------------------------------------------------+ 35 rows in set (0.00 sec) hod06> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.00 sec) hod06> START GROUP_REPLICATION; Query OK, 0 rows affected (3.84 sec) hod06> SELECT * FROM performance_schema.replication_connection_status\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_recovery GROUP_NAME: SOURCE_UUID: THREAD_ID: NULL SERVICE_STATE: OFF COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier GROUP_NAME: 9d7f8c28-c02c-11e6-9829-08002715584a SOURCE_UUID: 9d7f8c28-c02c-11e6-9829-08002715584a THREAD_ID: NULL SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: 9d7f8c28-c02c-11e6-9829-08002715584a:3 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 2 rows in set (0.00 sec) hod06> SELECT * FROM performance_schema.replication_group_member_stats\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 15155857453602698:3 MEMBER_ID: f8597c78-f5fc-11e7-b0ca-0010e0734b98 COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 0 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: LAST_CONFLICT_FREE_TRANSACTION: 1 row in set (0.00 sec) hod06> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | f376b3a4-f5fc-11e7-8f09-0010e05f3e06 | hod03 | 3306 | ONLINE | | group_replication_applier | f62774f2-f5fc-11e7-ab5b-0010e05f4178 | hod04 | 3306 | ONLINE | | group_replication_applier | f8597c78-f5fc-11e7-b0ca-0010e0734b98 | hod06 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec) hod06> -- hod03 hod03> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | f376b3a4-f5fc-11e7-8f09-0010e05f3e06 | hod03 | 3306 | ONLINE | | group_replication_applier | f62774f2-f5fc-11e7-ab5b-0010e05f4178 | hod04 | 3306 | ONLINE | | group_replication_applier | f8597c78-f5fc-11e7-b0ca-0010e0734b98 | hod06 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec) -- hod04 hod04> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | f376b3a4-f5fc-11e7-8f09-0010e05f3e06 | hod03 | 3306 | ONLINE | | group_replication_applier | f62774f2-f5fc-11e7-ab5b-0010e05f4178 | hod04 | 3306 | ONLINE | | group_replication_applier | f8597c78-f5fc-11e7-b0ca-0010e0734b98 | hod06 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec) -- hod06 hod06> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | f376b3a4-f5fc-11e7-8f09-0010e05f3e06 | hod03 | 3306 | ONLINE | | group_replication_applier | f62774f2-f5fc-11e7-ab5b-0010e05f4178 | hod04 | 3306 | ONLINE | | group_replication_applier | f8597c78-f5fc-11e7-b0ca-0010e0734b98 | hod06 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec) -- hod03 is primary node, hod04/hod04 - secondary hod03>show global variables like 'group_replication_single_primary_mode'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | ON | +---------------------------------------+-------+ 1 row in set (0.01 sec) hod04>show global variables like 'group_replication_single_primary_mode'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | ON | +---------------------------------------+-------+ 1 row in set (0.00 sec) hod06>show global variables like 'group_replication_single_primary_mode'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | ON | +---------------------------------------+-------+ 1 row in set (0.01 sec) -- group_replication_primary_member exposes which one is primary member in the group hod03>show global status like 'group_replication_primary_member'; +----------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------+ | group_replication_primary_member | f376b3a4-f5fc-11e7-8f09-0010e05f3e06 | +----------------------------------+--------------------------------------+ 1 row in set (0.00 sec) hod04>show global status like 'group_replication_primary_member'; +----------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------+ | group_replication_primary_member | f376b3a4-f5fc-11e7-8f09-0010e05f3e06 | +----------------------------------+--------------------------------------+ 1 row in set (0.00 sec) hod06>show global status like 'group_replication_primary_member'; +----------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------+ | group_replication_primary_member | f376b3a4-f5fc-11e7-8f09-0010e05f3e06 | +----------------------------------+--------------------------------------+ 1 row in set (0.01 sec) hod03>select * from performance_schema.replication_group_members\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: f376b3a4-f5fc-11e7-8f09-0010e05f3e06 MEMBER_HOST: hod03 MEMBER_PORT: 3306 MEMBER_STATE: ONLINE *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: f62774f2-f5fc-11e7-ab5b-0010e05f4178 MEMBER_HOST: hod04 MEMBER_PORT: 3306 MEMBER_STATE: ONLINE *************************** 3. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: f8597c78-f5fc-11e7-b0ca-0010e0734b98 MEMBER_HOST: hod06 MEMBER_PORT: 3306 MEMBER_STATE: ONLINE 3 rows in set (0.00 sec) ### Run the following on the primary member of the group i.e on hod03 hod03>CREATE DATABASE test_jfg_ws; Query OK, 1 row affected (0.00 sec) hod03>CREATE TABLE test_jfg_ws.test_jfg_ws ( id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,str varchar(80) NOT NULL UNIQUE); Query OK, 0 rows affected (0.01 sec) hod03>INSERT INTO test_jfg_ws.test_jfg_ws (str) VALUES ('c'); Query OK, 1 row affected (0.01 sec) hod03> ## Run the following on a secondary member of the group, with the "STOP GROUP_REPLICATION; START GROUP_REPLICATION;" to rotate the relay logs of the applier hod04>STOP GROUP_REPLICATION; START GROUP_REPLICATION; Query OK, 0 rows affected (9.46 sec) Query OK, 0 rows affected (3.20 sec) hod04> ## Run the following on the primary i.e on hod03 hod03>DELETE FROM test_jfg_ws.test_jfg_ws WHERE str = 'c'; Query OK, 1 row affected (0.00 sec) hod03>INSERT INTO test_jfg_ws.test_jfg_ws (str) VALUES ('C'); Query OK, 1 row affected (0.00 sec) -- on hod04 secondary We will have the following in the relay logs of the secondary (notice the overlapping intervals for the DELETE and the INSERT - comment at the end of the lines added for clarity): [umshastr@hod04]/export/home/ushastry/mysql-5.7.20: bin/mysqlbinlog -vvv data/hod04-relay-bin-group_replication_applier.000003 | grep -e last_ | sed -e 's/server id.*last/[...] last/' -e 's/.rbr_only.*/ [...]/' #180110 13:18:46 [...] last_committed=0 sequence_number=0 [...] #180110 13:18:46 [...] last_committed=1 sequence_number=2 [...] #180110 13:18:46 [...] last_committed=1 sequence_number=3 [...] [umshastr@hod04]/export/home/ushastry/mysql-5.7.20: ## With the above intervals, the DELETE and the INSERT can be run in parallel. If the INSERT is run before the row is actually deleted, there will be a duplicate key violation. ## To trigger the duplicate key violation, you can create a table with many rows (one million in my case - doubled as not to repeat :)) by running the following commands in a Linux shell on the primary: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: bin/mysql -uroot -S /tmp/mysql_hod03.sock <<< " > CREATE TABLE test_jfg_ws.test_jfg_ws2 ( > id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > str VARCHAR(80) NOT NULL)" [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: sql="INSERT INTO test_jfg_ws.test_jfg_ws2 (str) VALUES (RAND());" [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: for i in $(seq 100); do > ( echo "BEGIN;"; yes "$sql" | head -n 20000; echo "COMMIT;"; ) | bin/mysql -uroot -S /tmp/mysql_hod03.sock; > done [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: ## The following ALTER needs to take more than two seconds so we have a chance to trigger the duplicate key violation. If it does not take more than two seconds, add more rows in the table by running the above for loop again. ## on primary node hod03 hod03>ALTER TABLE test_jfg_ws.test_jfg_ws2 MODIFY COLUMN str VARCHAR(60); Query OK, 2000000 rows affected (13.62 sec) Records: 2000000 Duplicates: 0 Warnings: 0 hod03> ## Enable parallel replication on a secondary member of the group and look at the status of the applier - on hod04 hod04>STOP GROUP_REPLICATION; START GROUP_REPLICATION; Query OK, 0 rows affected (9.30 sec) Query OK, 0 rows affected (3.11 sec) hod04>STOP GROUP_REPLICATION; Query OK, 0 rows affected (9.10 sec) hod04>SET GLOBAL slave_parallel_type = LOGICAL_CLOCK; Query OK, 0 rows affected (0.01 sec) hod04>SET GLOBAL slave_parallel_workers = 8; Query OK, 0 rows affected (0.00 sec) hod04>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. hod04> hod04>set global slave_preserve_commit_order=ON; Query OK, 0 rows affected (0.01 sec) hod04>START GROUP_REPLICATION; Query OK, 0 rows affected (3.34 sec) hod04>SELECT * FROM performance_schema.replication_applier_status WHERE CHANNEL_NAME = 'group_replication_applier'\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier SERVICE_STATE: ON REMAINING_DELAY: NULL COUNT_TRANSACTIONS_RETRIES: 0 1 row in set (0.00 sec) hod04>SELECT * FROM performance_schema.replication_applier_status_by_coordinator WHERE CHANNEL_NAME = 'group_replication_applier'\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier THREAD_ID: 56 SERVICE_STATE: ON LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 1 row in set (0.00 sec) ## Try triggering, on the secondary, Bug#86078 by running the following commands in a Linux shell of the primary i.e on hod03 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: for i in $(seq 5); do > bin/mysql -uroot -S /tmp/mysql_hod03.sock <<< " > SET autocommit = ON; > ALTER TABLE test_jfg_ws.test_jfg_ws2 MODIFY COLUMN str VARCHAR(80); > DELETE FROM test_jfg_ws.test_jfg_ws WHERE str = 'c'; > INSERT INTO test_jfg_ws.test_jfg_ws (str) VALUES ('C'); > ALTER TABLE test_jfg_ws.test_jfg_ws2 MODIFY COLUMN str VARCHAR(60); > DELETE FROM test_jfg_ws.test_jfg_ws WHERE str = 'C'; > INSERT INTO test_jfg_ws.test_jfg_ws (str) VALUES ('c');" > done [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: ## After running above, I get the following on the secondary where I enabled parallel replication i.e on hod04 node hod04>SELECT * FROM performance_schema.replication_applier_status WHERE CHANNEL_NAME = 'group_replication_applier'\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier SERVICE_STATE: ON REMAINING_DELAY: NULL COUNT_TRANSACTIONS_RETRIES: 5 1 row in set (0.00 sec) hod04>SELECT * FROM performance_schema.replication_applier_status_by_coordinator WHERE CHANNEL_NAME = 'group_replication_applier'\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier THREAD_ID: 56 SERVICE_STATE: ON LAST_ERROR_NUMBER: 1062 LAST_ERROR_MESSAGE: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 2 failed executing transaction '9d7f8c28-c02c-11e6-9829-08002715584a:143' at master log , end_log_pos 241. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. LAST_ERROR_TIMESTAMP: 2018-01-10 13:35:41 1 row in set (0.00 sec) -- extract from secondary i.e hod04 node's error log 2018-01-10T12:34:41.083972Z 55 [ERROR] Slave SQL for channel 'group_replication_applier': Worker 2 failed executing transaction '9d7f8c28-c02c-11e6-9829-08002715584a:119' at master log , end_log_pos 241; Could not execute Write_rows event on table test_jfg_ws.test_jfg_ws; Duplicate entry 'c' for key 'str', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 241, Error_code: 1062 2018-01-10T12:34:56.162990Z 55 [ERROR] Slave SQL for channel 'group_replication_applier': Worker 2 failed executing transaction '9d7f8c28-c02c-11e6-9829-08002715584a:125' at master log , end_log_pos 241; Could not execute Write_rows event on table test_jfg_ws.test_jfg_ws; Duplicate entry 'c' for key 'str', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 241, Error_code: 1062 2018-01-10T12:35:11.256545Z 55 [ERROR] Slave SQL for channel 'group_replication_applier': Worker 2 failed executing transaction '9d7f8c28-c02c-11e6-9829-08002715584a:131' at master log , end_log_pos 241; Could not execute Write_rows event on table test_jfg_ws.test_jfg_ws; Duplicate entry 'c' for key 'str', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 241, Error_code: 1062 2018-01-10T12:35:26.354266Z 55 [ERROR] Slave SQL for channel 'group_replication_applier': Worker 2 failed executing transaction '9d7f8c28-c02c-11e6-9829-08002715584a:137' at master log , end_log_pos 241; Could not execute Write_rows event on table test_jfg_ws.test_jfg_ws; Duplicate entry 'c' for key 'str', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 241, Error_code: 1062 2018-01-10T12:35:41.363249Z 55 [ERROR] Slave SQL for channel 'group_replication_applier': Worker 2 failed executing transaction '9d7f8c28-c02c-11e6-9829-08002715584a:143' at master log , end_log_pos 241; Could not execute Write_rows event on table test_jfg_ws.test_jfg_ws; Duplicate entry 'c' for key 'str', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 241, Error_code: 1062 ## On hod04 - secondary node - FR Bug #89142 hod04>FLUSH RELAY LOGS for channel 'group_replication_applier'; ERROR 3139 (HY000): FLUSH RELAY LOGS cannot be performed on channel 'group_replication_applier'. hod04> ### Conf files used for tests #hod03 [mysqld] basedir = /export/umesh/server/binaries/GABuilds/mysql-5.7.20 datadir = /export/umesh/server/binaries/GABuilds/mysql-5.7.20/data/ plugin-load = group_replication.so port = 3306 socket = /tmp/mysql_hod03.sock log_bin disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,ARCHIVE ssl skip_name_resolve server_id = 571706 binlog_format = ROW binlog_rows_query_log_events = ON gtid_mode = ON enforce_gtid_consistency = ON log_slave_updates = ON master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = ON transaction_write_set_extraction = XXHASH64 binlog_checksum = NONE #group_replication = FORCE_PLUS_PERMANENT group_replication_start_on_boot = ON group_replication_group_name = 9d7f8c28-c02c-11e6-9829-08002715584a group_replication_local_address = hod03.no.oracle.com:6606 group_replication_group_seeds = hod03.no.oracle.com:6606,hod04.no.oracle.com:6607,hod06.no.oracle.com:6608 group_replication_single_primary_mode=TRUE group_replication_start_on_boot=FALSE #hod04 [mysqld] basedir = /export/home/ushastry/mysql-5.7.20 datadir = /export/home/ushastry/mysql-5.7.20/data/ plugin-load = group_replication.so port = 3306 log_bin socket = /tmp/mysql_hod04.sock disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,ARCHIVE ssl skip_name_resolve server_id = 571707 binlog_format = ROW binlog_rows_query_log_events = ON gtid_mode = ON enforce_gtid_consistency = ON log_slave_updates = ON master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = ON transaction_write_set_extraction = XXHASH64 binlog_checksum = NONE #group_replication = FORCE_PLUS_PERMANENT group_replication_start_on_boot = ON group_replication_group_name = 9d7f8c28-c02c-11e6-9829-08002715584a group_replication_local_address = hod04.no.oracle.com:6607 group_replication_group_seeds = hod03.no.oracle.com:6606,hod04.no.oracle.com:6607,hod06.no.oracle.com:6608 group_replication_single_primary_mode=TRUE group_replication_start_on_boot=FALSE #hod06 [mysqld] basedir = /export/home/ushastry/mysql-5.7.20 datadir = /export/home/ushastry/mysql-5.7.20/data/ plugin-load = group_replication.so port = 3306 log_bin socket = /tmp/mysql_hod06.sock disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,ARCHIVE ssl skip_name_resolve server_id = 571708 binlog_format = ROW binlog_rows_query_log_events = ON gtid_mode = ON enforce_gtid_consistency = ON log_slave_updates = ON master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = ON transaction_write_set_extraction = XXHASH64 binlog_checksum = NONE #group_replication = FORCE_PLUS_PERMANENT group_replication_start_on_boot = ON group_replication_group_name = 9d7f8c28-c02c-11e6-9829-08002715584a group_replication_local_address = hod06.no.oracle.com:6608 group_replication_group_seeds = hod03.no.oracle.com:6606,hod04.no.oracle.com:6607,hod06.no.oracle.com:6608 group_replication_single_primary_mode=TRUE group_replication_start_on_boot=FALSE ## 5.7.20 - Build used cat docs/INFO_SRC commit: 0441472e19e9a2e41df5d61098eb97c3e211547c date: 2017-09-13 17:44:44 +0200 build-date: 2017-09-13 17:48:35 +0200 short: 0441472 branch: mysql-5.7.20-release MySQL source 5.7.20 ## OS details # cat /etc/*release Oracle Linux Server release 7.1 NAME="Oracle Linux Server" VERSION="7.1" ID="ol" VERSION_ID="7.1" PRETTY_NAME="Oracle Linux Server 7.1" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:7:1" HOME_URL="https://linux.oracle.com/" BUG_REPORT_URL="https://bugzilla.oracle.com/" ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7" ORACLE_BUGZILLA_PRODUCT_VERSION=7.1 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=7.1 Red Hat Enterprise Linux Server release 7.1 (Maipo) Oracle Linux Server release 7.1