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