Sign in Subscribe
By Martin Wendler —

MySQL: Configure InnoDB ReplicaSet

MySQL: Configure InnoDB ReplicaSet

What is InnoDB ReplicaSet

MySQL documentation: "Being based on MySQL Replication, an InnoDB ReplicaSet has a single primary, which replicates to one or more secondary instances. An InnoDB ReplicaSet does not provide all of the features which InnoDB Cluster provides, such as automatic failover, or multi-primary mode. But, it does support features such as configuring, adding, and removing instances in a similar way. You can manually switch over or fail over to a secondary instance, for example in the event of a failure."

Prepare instances

We prepare two instances. For this blog entry, I'll use MySQL sandboxes, but if you plan to use InnoDB ReplicaSet in production, you should use physical or virtual servers for that.

Create instances

Use MySQL Shell to create the instances:

# mysqlsh
 MySQL  JS > dba.deploySandboxInstance(3301)
A new MySQL sandbox instance will be created on this host in
C:\Users\MyUserName\MySQL\mysql-sandboxes\3301

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:3301 successfully deployed and started.
Use shell.connect('root@localhost:3301') to connect to the instance.

Create instance 3302 the same way:

MySQL  JS > dba.deploySandboxInstance(3302)
 A new MySQL sandbox instance will be created on this host in
C:\Users\MyUserName\MySQL\mysql-sandboxes\3302

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:3302 successfully deployed and started.
Use shell.connect('root@localhost:3302') to connect to the instance.

Configure instances

Both instances are then configured to support InnoDB ReplicaSet:

dba.configureReplicaSetInstance('root@localhost:3301')
Please provide the password for 'root@localhost:3301': ************
Save password for 'root@localhost:3301'? [Y]es/[N]o/Ne[v]er (default No): y
Configuring local MySQL instance listening at port 3301 for use in an InnoDB ReplicaSet...
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:3301

applierWorkerThreads will be set to the default value of 4.

The instance '127.0.0.1:3301' is valid for InnoDB ReplicaSet usage.
dba.configureReplicaSetInstance('root@localhost:3302')
Configuring local MySQL instance listening at port 3302 for use in an InnoDB ReplicaSet...
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:3302

applierWorkerThreads will be set to the default value of 4.

The instance '127.0.0.1:3302' is valid for InnoDB ReplicaSet usage.

Successfully enabled parallel appliers.

Create ReplicaSet

After the configuration of the instances, the ReplicaSet can be configured:

Login to first instance:

\c root@localhost:3301

Create ReplicaSet:

dba.createReplicaSet('ReplicaSet1');
A new replicaset with instance '127.0.0.1:3301' will be created.

* Checking MySQL instance at 127.0.0.1:3301

This instance reports its own address as 127.0.0.1:3301
127.0.0.1:3301: Instance configuration is suitable.

* Checking connectivity and SSL configuration...
* Updating metadata...

ReplicaSet object successfully created for 127.0.0.1:3301.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

<ReplicaSet:ReplicaSet1>

Add second instance to ReplicaSet

dba.getReplicaSet().addInstance('root@localhost:3302')
You are connected to a member of replicaset 'ReplicaSet1'.
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as 127.0.0.1:3302
127.0.0.1:3302: Instance configuration is suitable.

* Checking async replication topology...

* Checking connectivity and SSL configuration...

* Checking transaction state of the instance...

NOTE: The target instance '127.0.0.1:3302' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state.
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:3302' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset 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):
* Updating topology
Monitoring Clone based state recovery of the new member. Press ^C to abort the operation.
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:3302 is being cloned from 127.0.0.1:3301
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed
* Clone process has finished: 74.70 MB transferred in about 1 second (~74.70 MB/s)

** Changing replication source of 127.0.0.1:3302 to 127.0.0.1:3301
** Waiting for new instance to synchronize with PRIMARY...
** Transactions replicated  ############################################################  100%


The instance '127.0.0.1:3302' was added to the replicaset and is replicating from 127.0.0.1:3301.

* Waiting for instance '127.0.0.1:3302' to synchronize the Metadata updates with the PRIMARY...
** Transactions replicated  ############################################################  100%

Check the status

To see if everything works as expected, we'll have a look at the ReplicaSet status:

dba.getReplicaSet().status()
You are connected to a member of replicaset 'ReplicaSet1'.
{
    "replicaSet": {
        "name": "ReplicaSet1",
        "primary": "127.0.0.1:3301",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "127.0.0.1:3301": {
                "address": "127.0.0.1:3301",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            },
            "127.0.0.1:3302": {
                "address": "127.0.0.1:3302",
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Waiting for an event from Coordinator",
                    "applierWorkerThreads": 4,
                    "receiverStatus": "ON",
                    "receiverThreadState": "Waiting for source to send event",
                    "replicationLag": null,
                    "replicationSsl": "TLS_AES_128_GCM_SHA256 TLSv1.3",
                    "replicationSslMode": "REQUIRED"
                },
                "status": "ONLINE"
            }
        },
        "type": "ASYNC"
    }
}

Access the databases

The databases are now accessible using localhost:3301. And read/only access is also available using localhost:3302.

If you want to have read/write access to the ReplicaSet, no matter which one is the primary node, you'll have to configure MySQL Router.

Switchover

If you want to switch over your primary MySQL instance to the node formerly known as the standby node, you can do it like that:

dba.getReplicaSet().setPrimaryInstance('root@localhost:3302')
You are connected to a member of replicaset 'ReplicaSet1'.
127.0.0.1:3302 will be promoted to PRIMARY of 'ReplicaSet1'.
The current PRIMARY is 127.0.0.1:3301.

* Connecting to replicaset instances
** Connecting to 127.0.0.1:3301
** Connecting to 127.0.0.1:3302
** Connecting to 127.0.0.1:3301
** Connecting to 127.0.0.1:3302

* Performing validation checks
** Checking async replication topology...
** Checking transaction state of the instance...

* Synchronizing transaction backlog at 127.0.0.1:3302
** Transactions replicated  ############################################################  100%


* Updating metadata

* Acquiring locks in ReplicaSet instances
** Pre-synchronizing SECONDARIES
** Acquiring global lock at PRIMARY
** Acquiring global lock at SECONDARIES

* Updating replication topology
** Changing replication source of 127.0.0.1:3301 to 127.0.0.1:3302

127.0.0.1:3302 was promoted to PRIMARY.

The status now looks like that:

dba.getReplicaSet().status()
You are connected to a member of replicaset 'ReplicaSet1'.
{
    "replicaSet": {
        "name": "ReplicaSet1",
        "primary": "127.0.0.1:3302",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "127.0.0.1:3301": {
                "address": "127.0.0.1:3301",
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Waiting for an event from Coordinator",
                    "applierWorkerThreads": 4,
                    "receiverStatus": "ON",
                    "receiverThreadState": "Waiting for source to send event",
                    "replicationLag": null,
                    "replicationSsl": "TLS_AES_128_GCM_SHA256 TLSv1.3",
                    "replicationSslMode": "REQUIRED"
                },
                "status": "ONLINE"
            },
            "127.0.0.1:3302": {
                "address": "127.0.0.1:3302",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            }
        },
        "type": "ASYNC"
    }
}

Update

Updating MySQL is quite easy, especially on Linux, where you'll just have to yum update or apt upgrade.

To keep your availability, you could just update the standby site, and at a short downtime window, you could just switchover. After that, update the MySQL instance that is the new standby site. And then, you're up to date, and the downtime was just the time between starting and ending of the switchover command...

Subscribe to Martin's Blog

Don’t miss out on the latest issues. Sign up now to get access to the library of members-only issues.
jamie@example.com
Subscribe