MySQL :: MySQL Shell 9.1 :: 7.11.2 Creating Read Replicas

Examples

The examples in this and subsequent sections assume a simple Cluster of three instances, a primary and two secondaries.

host1:4100: primary.
host2:4101: secondary.
host3:4102: secondary.

Default Read Replica

This section describes adding a Read Replica to the Cluster. By default, the Read Replica replicates from the primary.

The following example adds an instance, host4:4110 to the Cluster, with the label RReplica1:

Cluster.addReplicaInstance('host4:4110', {label: 'RReplica1'})

Note

Labels must be unique within the Cluster and can only contain alphanumeric, _ (underscore), . (period), - (hyphen), or : (colon) characters.

Defining a Replication Source for the Read Replica

The replicationSources option of addReplicaInstance enables you to specify one or more preferred replication sources for the Read Replica. This option accepts the following values:

primary: Defines the Cluster primary as the replication source. In the event of a failover, the Read Replica waits until a new primary is promoted, then resumes replication with the new primary as the source.
secondary: Defines one of the Cluster secondaries as the replication source. The selection is managed by Group Replication. The source will always be a secondary member of the Cluster, unless it becomes a single-member Cluster. In which case, the Read Replica uses the only other member as the source.
hostname:port: Defines a specific Cluster member, primary or secondary, as replication source.
Comma-separated list of hosts: Defines a weighted list of Cluster members, primary or secondary, as potential replication sources. The first instance in the list has the highest priority; when the Read Replica's replication channel is activated, it is the first connection attempted. The other list members are connected to in the event of a failover or if the connection attempt fails.

The following example defines the secondary, host2:4101 as the source:

Cluster.addReplicaInstance('host4:4110', {label: 'RReplica1', replicationSources: ['host2:4101']})

It is also possible to specify a number of replication sources, by providing a comma-separated list of hosts, using the host:port format. The list is weighted, with the first entry having the largest weight. This enables you to define a source failover list. If the first defined source fails, the Read Replica attempts to restore replication with the second source defined, and so on.

The following example defines the secondary, host2:4101 as the first source, and host3:4102 as the next:

Cluster.addReplicaInstance('host4:4110', {label: 'RReplica1', replicationSources: ['host2:4101','host3:4102']]})

You can use the same option to define either only primary or only secondary sources, without defining individual instances. For example:

Cluster.addReplicaInstance('host4:4110', {label: 'RReplica1', replicationSources: 'secondary']})

defines the replication sources as secondaries only. To define replication source as primary, only, use replicationSources: 'primary'.

By default, the source list is managed by MySQL Group Replication which defaults to primary sources.

You can test this operation using the dryRun option. Including this option, set to true runs the command but makes no changes. This enables you to test your changes.

Note

It is also possible to set the replicationSources, and the label, of a Read Replica with the cluster.setInstanceOption() method.

If the target is a Read Replica, the only options the cluster.setInstanceOption() method accepts are tags (reserved tags, only), replicationSources, and label.

Defining the Recovery Method for Read Replicas

recoveryMethod defines how the Read Replica's data is acquired during provisioning.

clone: Use to completely replace the state of the target instance with a full snapshot of another cluster member before distributed recovery starts. Requires MySQL 8.0.17 or newer.

If cloneDonor is defined, the defined instance is used as the source.

If replicationSources is set to primary or secondary, the Cluster Primary is used as the source.

If replicationSources contains one or more named Cluster members, the first in the list is used as the source, if available. If the first is not available, the second is used, and so on.
incremental: Uses distributed state recovery to apply missing transactions copied from another cluster member. Clone is disabled.
auto: Default. Group Replication selects whether a full snapshot is taken, based on what the target server supports and group_replication_clone_threshold value. A prompt is displayed if it is not possible to safely determine a safe way to proceed. If interaction is disabled, the operation is canceled.

In addition to the recoveryMethod: clone option, addReplicaInstance provides a cloneDonor option, enabling you to define the specific instance to clone to the new Read Replica. If cloneDonor is not defined, but clone is the selected recovery method, either by explicitly specifying clone or if auto selects clone as the best method for data provisioning, the best instance is chosen for cloning by the cluster. The process used is identical to that used by Cluster.addInstance(). See Section 7.4.6.1, “Working with a Cluster that uses MySQL Clone” for more information.

In the following example, the new Read Replica host5:4113 is added with the label RReplica5, using host2:4101, a secondary instance, as the data source. host2:4101's data is cloned to the new Read Replica.

JS> cluster.addReplicaInstance('host5:4113',{label: 'RReplica5', recoveryMethod: "clone", cloneDonor: "host2:4101"})

A successful command returns information similar to the following:

Setting up 'host5:4113' as a Read Replica of Cluster 'myCluster'.

Validating instance configuration at host5:4113...

This instance reports its own address as host5:4113

Instance configuration is suitable.
* Checking transaction state of the instance...


Clone based recovery selected through the recoveryMethod option

* Waiting for the donor to synchronize with PRIMARY...
** Transactions replicated  ############################################################  100%


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: host5:4113 is being cloned from host2:4101
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ============================================================    0%  In Progress

NOTE: host5:4113 is shutting down...

* Waiting for server restart... ready
* host5:4113 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 8.64 GB transferred in 11 sec (785.30 MB/s)

* Configuring Read-Replica managed replication channel...
** Changing replication source of host5:4113 to host1:4100

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


'host5:4113' successfully added as a Read-Replica of Cluster 'myCluster'.

Certificate-based Authentication

Certificate-based authentication for Read Replicas is defined in the following way:

certSubject option of cluster.addReplicaInstance(): specifies the certificate subject of the instance, used if the Cluster's memberAuthType is CERT_SUBJECT or CERT_SUBJECT_PASSWORD.
The Cluster's memberSslMode value is used to configure the authentication type of the Read Replica's replication channel.
The method performs a connectivity check, using the configured memberSslMode before updating the topology.
If the Cluster's memberAuthType is CERT_SUBJECT or CERT_SUBJECT_PASSWORD, the method verifies the server's certificate.

cluster.options() was updated to return certSubject in the topology array.