InnoDB ReplicaSet in MySQL 8.0.19 ( configuration + Switchover the primary member )

At 13th January 2020, MySQL Community has released the very latest MySQL 8.x version ( 8.0.19 ). The MySQL 8.0.19 has released with the good amount of bug fixes and the cool features . InnoDB ReplicaSet is one of them and most interesting feature for me . In this blog I am going to explain about the configuration of the MySQL InnoDB ReplicaSet and how to do the switchover smoothly with InnoDB ReplicaSet .

What is InnoDB ReplicaSet ?

from MySQL document,

The AdminAPI includes support for InnoDB ReplicaSet, that enables you to administer a set of MySQL instances running asynchronous GTID-based replication in a similar way to InnoDB cluster. A InnoDB ReplicaSet consists of a single primary and multiple secondaries 

Note : For configure the InnoDB ReplicaSet , the servers should be configured with the GTID .

InnoDB ReplicaSet configuration :

First of all, I have created two machines with MySQL 8.0.19 for the ReplicaSet configuration .

• 192.168.33.14 ( sakthilabs14 )
• 192.168.33.15 ( sakthilabs15 )

Below I am providing the each and every steps which involved in the InnoDB ReplicaSet configuration .

Step 1 : ( allow the communication between hosts )

Allow the hostname based communications between the servers , which are involving in the InnoDB ReplicaSet .

[root@sakthilabs14 ~]# cat /etc/hosts | grep -i sakthi
192.168.33.14 sakthilabs14 sakthilabs14
192.168.33.15 sakthilabs15 sakthilabs15

Step 2: ( preparing the server for ReplicaSet configuration )

The servers should be configured with the GTID and the separate server-id

MySQL localhost:33060+ ssl JS > dba.configureReplicaSetInstance(‘root@localhost’,{clusterAdmin: “‘sakthiRepl’@’%'”, clusterAdminPassword: ‘Repl@321’});
Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet…

This instance reports its own address as sakthilabs14:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

NOTE: Some configuration options need to be fixed:
+————————–+—————+—————-+————————————————–+
| Variable | Current Value | Required Value | Note |
+————————–+—————+—————-+————————————————–+
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
| server_id | 1 | | Update read-only variable and restart the server |
+————————–+—————+—————-+————————————————–+

Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Cluster admin user ‘sakthiRepl’@’%’ created.
Configuring instance…
The instance ‘sakthilabs14:3306’ was configured to be used in an InnoDB ReplicaSet.
Restarting MySQL…
NOTE: MySQL server at sakthilabs14:3306 was restarted.

Note : This step needs to be performed on all the servers, which are going to participate in InnoDB ReplicaSet .

Step 3 : ( creating the ReplicaSet )

This step helps to initiating the ReplicaSet on the server .

Step 4 : ( adding the second machine as the secondary server )

MySQL localhost:33060+ ssl JS > rs.addInstance(‘sakthiRepl@192.168.33.15:3306’);
Adding instance to the replicaset…

NOTE: The target instance ‘sakthilabs15:3306’ 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 ‘sakthilabs15:3306’ 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): clone

Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.

Waiting for clone to finish…
NOTE: sakthilabs15:3306 is being cloned from sakthilabs14:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed

NOTE: sakthilabs15:3306 is shutting down…

Waiting for server restart… ready

sakthilabs15:3306 has restarted, waiting for clone to finish…
** Stage RESTART: Completed

Clone process has finished: 59.63 MB transferred in about 1 second (~59.63 MB/s)

** Configuring sakthilabs15:3306 to replicate from sakthilabs14:3306
** Waiting for new instance to synchronize with PRIMARY…

The instance ‘sakthilabs15:3306’ was added to the replicaset and is replicating from sakthilabs14:3306.

Step 5 : ( check the ReplicaSet status )

MySQL localhost:33060+ ssl JS > rs.status();
{
"replicaSet": {
"name": "firstReplicaset",
"primary": "sakthilabs14:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"sakthilabs14:3306": {
"address": "sakthilabs14:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"sakthilabs15:3306": {
"address": "sakthilabs15:3306",
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Slave has read all relay log; waiting for more updates",
"receiverStatus": "ON",
"receiverThreadState": "Waiting for master to send event",
"replicationLag": null
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}

from the ReplicaSet status, you can monitor the complete replication status including the replication log .

How to switchover the Primary member in InnoDB ReplicaSet ?

from the output ,

• sakthilabs14 ( PRIMARY )
• sakthilabs15 ( SECONDARY )

With this example, I am going to change the sakthilabs15 as PRIMARY and sakthilabs14 as SECONDARY member .

changing the PRIMARY member ,

MySQL localhost:33060+ ssl JS > rs.setPrimaryInstance(‘sakthilabs15:3306’)
sakthilabs15:3306 will be promoted to PRIMARY of ‘firstReplicaset’.
The current PRIMARY is sakthilabs14:3306.

Connecting to replicaset instances
** Connecting to sakthilabs14:3306
** Connecting to sakthilabs15:3306
** Connecting to sakthilabs14:3306
** Connecting to sakthilabs15:3306

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

Synchronizing transaction backlog at sakthilabs15:3306

Updating metadata

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

Updating replication topology
** Configuring sakthilabs14:3306 to replicate from sakthilabs15:3306

sakthilabs15:3306 was promoted to PRIMARY.

There is also the option ReplicaSet.forcePrimaryInstance to forcefully configure ( failover ) the PRIMARY instance .

Limitations :

• InnoDB ReplicaSet cannot support for auto failover
• Data loss is possible in crash

Thanks !!