Learning to read: The MySQL InnoDB Cluster & Read Replicas

With the brand-new Innovation Release 8.1.0, Oracle has once again given the MySQL InnoDB Cluster a new “feature”: “Read Replicas”. This allows an InnoDB cluster to be supplemented with additional read-only instances. This article explains how this works and why it can make sense.

A, B, C

An InnoDB cluster usually consists of three nodes that provide highly available data in a “shared nothing” architecture. The failure of a node, for example, does not create a problem for the operation of an application from a database perspective. The price for this type of availability is bought via latency. Since transactions in a cluster always have to be processed by all participating nodes, longer runtimes can occur. We have already reported extensively on the InnoDB cluster here.

This is problematic, for example, if the nodes of a cluster are geographically far apart and thus the latency is naturally higher. In such cases, it can be helpful to build a cluster of nodes that are close together locally and replicate it to a geographically more distant system. Until now, this was not easily possible because the replicating systems must have knowledge of the cluster. In the event of the failure of a node in the cluster, the replica should “simply” continue replicating from another node. This is now possible.

Finding the right words

For demonstration purposes, we set up a “European” InnoDB cluster from the systems “frankfurt”, “hamburg” and "berlin” using the MySQL shell. Our cluster is called “ordix”. For reasons of clarity, we have shortened the output of the commands. However, the commands themselves have been highlighted in bold type:

MySQL  frankfurt:3306 ssl  JS > dba.createCluster('ordix')

A new InnoDB Cluster will be created on instance 'frankfurt:3306'.
Validating instance configuration at frankfurt:3306...
...
MySQL  frankfurt:3306 ssl  JS > var clu = dba.getCluster('ordix')
MySQL  frankfurt:3306 ssl  JS > clu.addInstance('root:root@berlin:3306')
...
Adding instance to the cluster...
...
The instance 'berlin:3306' was successfully added to the cluster.

MySQL  frankfurt:3306 ssl  JS > clu.addInstance('root:root@hamburg:3306')
...
The instance 'hamburg:3306' was successfully added to the cluster. 

Learn to read!

To extend the cluster with a “read replica”, we once again use the comfort of the MySQL shell. The basic call consists of only one command.

If necessary, however, very fine-grained parameters can be used to manipulate the structure of the replica. In addition to the name of the replica, the source of the cloning process can be determined, to which systems a “failover” should take place and from which system the replication should generally read data.

clu.addReplicaInstance('newyork:3306', {label: 'ReplicateUSA', replicationSources: ['hamburg:3306','Berlin:3306'], recoveryMethod: 'clone', cloneDonor: 'hamburg:3306'});
…
* Configuring Read-Replica managed replication channel...
** Changing replication source of newyork:3306 to hamburg:3306

* Waiting for Read-Replica 'newyork:3306' to synchronize with Cluster...
** Transactions replicated  ############################################################  100%

'newyork:3306' successfully added as a Read-Replica of Cluster 'ordix'. 

We can review the result of our efforts, as usual, with the cluster status command:

MySQL  frankfurt:3306 ssl  JS > clu.status()
{
    "clusterName": "ordix",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "frankfurt:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "berlin:3306": {
                "address": "berlin:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.1.0"
            },
            "frankfurt:3306": {
                "address": "frankfurt:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.1.0"
            },
            "hamburg:3306": {
                "address": "hamburg:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {
                    "ReplicateUSA": {
                        "address": "newyork:3306",
                        "role": "READ_REPLICA",
                        "status": "ONLINE",
                        "version": "8.1.0"
                    }
                },
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.1.0"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "frankfurt:3306"
}
 

Final words

Oracle continues to develop the MySQL InnoDB Cluster. After the recently introduced “Replica Sets”, the “Read Replicas” now follow in consequence. They combine the cluster with the advantages of “normal” replication. We ourselves already have the first customer who is eagerly waiting for this “feature” to make his environment more secure.

Do you have questions about the operation of MySQL? Talk to us.