Monday, July 29, 2019

Setting up a InnoDB Sandbox Cluster in SIX steps

I have not used the new InnoDB clone feature that is now part of MySQL 8.0.17 but wanted to see how it worked.  Setting a sandbox cluster with the new shell is easy and quick.  How easy and quick?
 well, it takes six commands and just a few minutes.

The Steps


1. mysqlsh> dba.deploySandboxInstance(3310)
A new MySQL sandbox instance will be created on this host in
C:\Users\dstokes\MySQL\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  localhost:33060+ ssl  so  JS > dba.deploySandboxInstance(3320)
A new MySQL sandbox instance will be created on this host in
C:\Users\dstokes\MySQL\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.

 2.  mysqlsh> dba.deploySandboxInstance(3330)
A new MySQL sandbox instance will be created on this host in
C:\Users\dstokes\MySQL\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.


3. mysqlsh> \c 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): y
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 12
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

4. mysqlsh > var cluster = dba.createCluster('DaveTest')
A new InnoDB cluster will be created on instance 'localhost:3310'.

Validating instance 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.
Creating InnoDB cluster 'DaveTest' on 'localhost: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.

 5. mysqlsh > 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): y

NOTE: The target instance 'localhost:3320' has not been pre-provisioned (GTID set is
empty). The Shell is unable to decide whether incremental distributed 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
'localhost:3320' with a physical snapshot from an existing cluster member. To
use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental distributed 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): c
Validating instance 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: localhost:3320 is being cloned from 127.0.0.1:3310
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ============================================================    0%  In Progress
    REDO COPY  ============================================================    0%  Not Started

NOTE: localhost: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: 58.51 MB transferred in about 1 second (~inf TB/s)

State recovery already finished for 'localhost:3320'

The instance 'localhost:3320' was successfully added to the cluster.

 6. mysqlsh>cluster.addInstance('root@localhost:3320')
<output omitted but much like the above in step 5>

And the Sandbox Cluster is Ready!

With those five steps we have a sandbox InnoDB Cluster we can use for testing.

mysqlsh > cluster.status()
{
    "clusterName": "DaveTest",
    "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": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "127.0.0.1:3320": {
                "address": "127.0.0.1:3320",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "127.0.0.1:3330": {
                "address": "127.0.0.1:3330",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "127.0.0.1:3310"
}


Now we can fire up MySQL Router and start using our sandbox cluster.