- Setup 3 node cluster export PATH=$PATH:/export/umesh/server/binaries/GABuilds/mysql-8.0.21/bin:/export/umesh/server/binaries/GABuilds/mysql-shell-8.0.21/bin bin/mysqlsh --log-level=debug3 MySQL Shell 8.0.21 Copyright (c) 2016, 2020, 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 '\?' for help; '\quit' to exit. MySQL JS > dba.deploySandboxInstance(3310) A new MySQL sandbox instance will be created on this host in /home/umshastr/mysql-sandboxes/3310 Warning: Sandbox instances are only suitable for deploying and running on your local machine for testing purposes and are not accessible from external networks. Please enter a MySQL root password for the new instance: Deploying new MySQL instance... Instance localhost:3310 successfully deployed and started. Use shell.connect('root@localhost:3310') to connect to the instance. MySQL JS > dba.deploySandboxInstance(3320) A new MySQL sandbox instance will be created on this host in /home/umshastr/mysql-sandboxes/3320 Warning: Sandbox instances are only suitable for deploying and running on your local machine for testing purposes and are not accessible from external networks. Please enter a MySQL root password for the new instance: Deploying new MySQL instance... Instance localhost:3320 successfully deployed and started. Use shell.connect('root@localhost:3320') to connect to the instance. MySQL JS > dba.deploySandboxInstance(3330) A new MySQL sandbox instance will be created on this host in /home/umshastr/mysql-sandboxes/3330 Warning: Sandbox instances are only suitable for deploying and running on your local machine for testing purposes and are not accessible from external networks. Please enter a MySQL root password for the new instance: Deploying new MySQL instance... Instance localhost:3330 successfully deployed and started. Use shell.connect('root@localhost:3330') to connect to the instance. MySQL JS > \connect root@localhost:3310 Creating a session to 'root@localhost:3310' Please provide the password for 'root@localhost:3310': Save password for 'root@localhost:3310'? [Y]es/[N]o/Ne[v]er (default No): Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 11 Server version: 8.0.21 MySQL Community Server - GPL No default schema selected; type \use to set one. MySQL localhost:3310 ssl JS > cluster = dba.createCluster("myCluster") A new InnoDB cluster will be created on instance 'localhost:3310'. Validating instance configuration at localhost:3310... NOTE: Instance detected as a sandbox. Please note that sandbox instances are only suitable for deploying test clusters for use within the same host. This instance reports its own address as 127.0.0.1:3310 Instance configuration is suitable. NOTE: Group Replication will communicate with other members using '127.0.0.1:33101'. Use the localAddress option to override. Creating InnoDB cluster 'myCluster' on '127.0.0.1:3310'... Adding Seed Instance... Cluster successfully created. Use Cluster.addInstance() to add MySQL instances. At least 3 instances are needed for the cluster to be able to withstand up to one server failure. MySQL localhost:3310 ssl JS > cluster.addInstance("root@localhost:3320") Please provide the password for 'root@localhost:3320': Save password for 'root@localhost:3320'? [Y]es/[N]o/Ne[v]er (default No): NOTE: The target instance '127.0.0.1:3320' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it. The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of '127.0.0.1:3320' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'. The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'. Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): NOTE: Group Replication will communicate with other members using '127.0.0.1:33201'. Use the localAddress option to override. Validating instance configuration at localhost:3320... NOTE: Instance detected as a sandbox. Please note that sandbox instances are only suitable for deploying test clusters for use within the same host. This instance reports its own address as 127.0.0.1:3320 Instance configuration is suitable. A new instance will be added to the InnoDB cluster. Depending on the amount of data on the cluster this might take from a few seconds to several hours. Adding instance to the cluster... Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background. Clone based state recovery is now in progress. NOTE: A server restart is expected to happen as part of the clone process. If the server does not support the RESTART command or does not come back after a while, you may need to manually start it back. * Waiting for clone to finish... NOTE: 127.0.0.1:3320 is being cloned from 127.0.0.1:3310 ** Stage DROP DATA: Completed ** Clone Transfer FILE COPY ############################################################ 100% Completed PAGE COPY ############################################################ 100% Completed REDO COPY ############################################################ 100% Completed NOTE: 127.0.0.1:3320 is shutting down... * Waiting for server restart... ready * 127.0.0.1:3320 has restarted, waiting for clone to finish... ** Stage RESTART: Completed * Clone process has finished: 59.62 MB transferred in about 1 second (~59.62 MB/s) State recovery already finished for '127.0.0.1:3320' The instance 'localhost:3320' was successfully added to the cluster. MySQL localhost:3310 ssl JS > cluster.addInstance("root@localhost:3330") Please provide the password for 'root@localhost:3330': Save password for 'root@localhost:3330'? [Y]es/[N]o/Ne[v]er (default No): NOTE: The target instance '127.0.0.1:3330' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it. The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of '127.0.0.1:3330' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'. The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'. Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): NOTE: Group Replication will communicate with other members using '127.0.0.1:33301'. Use the localAddress option to override. Validating instance configuration at localhost:3330... NOTE: Instance detected as a sandbox. Please note that sandbox instances are only suitable for deploying test clusters for use within the same host. This instance reports its own address as 127.0.0.1:3330 Instance configuration is suitable. A new instance will be added to the InnoDB cluster. Depending on the amount of data on the cluster this might take from a few seconds to several hours. Adding instance to the cluster... Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background. Clone based state recovery is now in progress. NOTE: A server restart is expected to happen as part of the clone process. If the server does not support the RESTART command or does not come back after a while, you may need to manually start it back. * Waiting for clone to finish... NOTE: 127.0.0.1:3330 is being cloned from 127.0.0.1:3320 ** Stage DROP DATA: Completed ** Clone Transfer FILE COPY ############################################################ 100% Completed PAGE COPY ############################################################ 100% Completed REDO COPY ############################################################ 100% Completed NOTE: 127.0.0.1:3330 is shutting down... * Waiting for server restart... ready * 127.0.0.1:3330 has restarted, waiting for clone to finish... ** Stage RESTART: Completed * Clone process has finished: 59.62 MB transferred in about 1 second (~59.62 MB/s) State recovery already finished for '127.0.0.1:3330' The instance 'localhost:3330' was successfully added to the cluster. MySQL localhost:3310 ssl JS > cluster.status() { "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "127.0.0.1:3310", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "127.0.0.1:3310": { "address": "127.0.0.1:3310", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.21" }, "127.0.0.1:3320": { "address": "127.0.0.1:3320", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.21" }, "127.0.0.1:3330": { "address": "127.0.0.1:3330", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.21" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "127.0.0.1:3310" } #### Ensure this is set on all 3 nodes mysql> SET GLOBAL group_replication_consistency=before_and_after; Query OK, 0 rows affected (0.01 sec) mysql> set global group_replication_unreachable_majority_timeout=30; Query OK, 0 rows affected (0.00 sec) #### on node 3310 bin/mysql -uroot -S /home/umshastr/mysql-sandboxes/3310/sandboxdata/mysqld.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 57 Server version: 8.0.21 MySQL Community Server - GPL Copyright (c) 2000, 2020, 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. mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test Database changed mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO t1 VALUES (1,2); Query OK, 1 row affected (0.01 sec) #### Kill node 3320 and 3330 umshastr 16523 0.9 0.1 3138748 457588 pts/8 Sl+ 11:50 0:04 /home/umshastr/mysql-sandboxes/3320/bin/mysqld --defaults-file=/home/umshastr/mysql-sandboxes/3320/my.cnf umshastr 16632 0.9 0.1 3052720 455208 pts/8 Sl+ 11:51 0:03 /home/umshastr/mysql-sandboxes/3330/bin/mysqld --defaults-file=/home/umshastr/mysql-sandboxes/3330/my.cnf kill -9 16523 16632 ### Connect to node 3310 and wait until MEMBER_STATE becomes Error and then query I_S mysql> SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | d2126ca6-fcb8-11ea-9877-0010e05f3e06 | 127.0.0.1 | 3310 | ONLINE | PRIMARY | 8.0.21 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | d2126ca6-fcb8-11ea-9877-0010e05f3e06 | 127.0.0.1 | 3310 | ONLINE | PRIMARY | 8.0.21 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | d2126ca6-fcb8-11ea-9877-0010e05f3e06 | 127.0.0.1 | 3310 | ONLINE | PRIMARY | 8.0.21 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | d2126ca6-fcb8-11ea-9877-0010e05f3e06 | 127.0.0.1 | 3310 | ONLINE | PRIMARY | 8.0.21 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | d2126ca6-fcb8-11ea-9877-0010e05f3e06 | 127.0.0.1 | 3310 | ONLINE | PRIMARY | 8.0.21 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | d2126ca6-fcb8-11ea-9877-0010e05f3e06 | 127.0.0.1 | 3310 | ONLINE | PRIMARY | 8.0.21 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | d2126ca6-fcb8-11ea-9877-0010e05f3e06 | 127.0.0.1 | 3310 | ERROR | | 8.0.21 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) mysql> select * from information_schema.tables; ERROR 3796 (HY000): The option group_replication_consistency cannot be used on the current member state. mysql>