MySQL 8.0.19 InnoDB ReplicaSet Configuration and Manual Switchover

Manual SwitchoverInnoDB ReplicaSet was introduced from MySQL 8.0.19. It works based on the MySQL asynchronous replication. Generally, InnoDB ReplicaSet does not provide high availability on its own like InnoDB Cluster, because with InnoDB ReplicaSet we need to perform the manual failover. AdminAPI includes the support for the InnoDB ReplicaSet. We can operate the InnoDB ReplicaSet using the MySQL shell. 

  • InnoDB cluster is the combination of MySQL shell and Group replication and MySQL router
  • InnoDB ReplicaSet is the combination of MySQL shell and MySQL traditional async replication and MySQL router

Why InnoDB ReplicaSet?

  • You can manually perform the switchover and failover with InnoDB ReplicaSet
  • You can easily add the new node to your replication environment. InnoDB ReplicaSet helps with data provisioning (using MySQL clone plugin) and setting up the replication.

In this blog, I am going to explain the process involved in the following topics

  • How to set up the InnoDB ReplicaSet in a fresh environment?
  • How to perform the manual switchover with ReplicaSet?

Before going into the topic, I am summarising the points which should be made aware to work on InnoDB ReplicaSet. 

  • ReplicaSet only supports GTID based replication environments. 
  • MySQL version should be 8.x +.
  • It has support for only Row-based replication.
  • Replication filters are not supported with InnoDB ReplicaSet
  • InnoDB ReplicaSet should have one primary node ( master ) and one or multiple secondary nodes ( slaves ). All the secondary nodes should be configured under the primary node. 
  • There is no limit for secondary nodes, you can configure many nodes under ReplicaSet.
  • It supports only manual failover.
  • InnoDB ReplicaSet should be completely managed with MySQL shell. 

How to set up the InnoDB ReplicaSet in a fresh environment?

I have created two servers (replicaset1, replicaset2) for testing purposes. My goal is to create the InnoDB ReplicaSet with one primary node and one secondary node. I installed Percona Server for MySQL 8.0.20 for my testing.

Step 1 :

Allow hostname based communication. Make sure that you configured this on all the servers, which participated in the ReplicaSet.

Step 2 :

In this step, I am going to prepare the MySQL instances for InnoDB ReplicaSet. Below are the major tasks that need to be performed as part of this operation.

  • Create a dedicated user account to effectively manage the ReplicaSet. The account will be automatically created with sufficient privileges.
  • MySQL parameters changes which need to be updated for InnoDB ReplicaSet (persisting settings).
  • Restart the MySQL instance to apply the changes.

Command : dba.configureReplicaSetInstance()

Connecting the shell,

Configuring the instance,

Once you triggered the command, it will start to interact with you. You have to choose the needed options. 

You can find the updated parameters from the file “mysqld-auto.cnf”. The blog by Marco Tusa has more details about the PERSIST configuration. 

Note :

  • Make sure that this step is executed on all the MySQL instances which are going to participate in the ReplicaSet group.
  • Make sure that the cluster account name and password are the same on all MySQL instances.

Step 3 :

In this step, I am going to switch my login to the ReplicaSet account which was created in Step 2. 

Step 4:

Now, all are set to create the ReplicaSet.

Command : dba.createReplicaSet(‘<ReplicaSet Name>’)

ReplicaSet is created with the name “PerconaReplicaSet”

Step 5:

In this step, I am going to assign the ReplicaSet to the variable and check the ReplicaSet status. Assigning to the variable can be done while creating the ReplicaSet as well  (i.e. var replicaset = dba.createReplicaSet(‘<ReplicaSet Name>’)  

The ReplicaSet status states the Instance replicaset1 is operational and is the PRIMARY member. 

Step 6:

Now, I need to add the secondary instance “replicaset2” to the ReplicaSet.

When adding the new instance, it should be fulfilled with all the ReplicaSet requirements. We have two recovery methods when joining the new node.

Clone: It will take the snapshot from the ONLINE instance and build the target node with a snapshot and finally add to the ReplicaSet. This method is always recommended when adding fresh nodes.

Incremental: This method relies on MySQL replication and applies all the transactions which are missed on the new instance. This can be faster when the missing transaction amount is small.

Command : replicaset.addInstance(‘<instance name>:<port>’)

Here I have chosen the clone method for recovery. 

The second instance has been added to the ReplicaSet. 

How to perform the manual switchover with ReplicaSet? 

As per the current topology,

  • replicaset1 is the PRIMARY
  • replicaset2 is the SECONDARY

Requirement: Regarding the maintenance activity, I am planning to remove the server “replicaset1” from the ReplicaSet. This needs to be performed in a safe manner and the secondary instance “replicaset2” should be available for application writes and reads.

  • First, I need to promote “replicaset2” as the PRIMARY.
  • Then, remove the “replicaset1” from the group.

Switching the “replicaset2” as the PRIMARY.

Command : replicaset.setPrimaryInstance(‘host:port’)

You can see the “replicaset2” has been promoted as PRIMARY.

Removing “replicaset1” from the group,

Command : replicaset.removeInstance(‘host:port’)

We can perform the forced failover using “ReplicaSet.forcePrimaryInstance()”. This is dangerous and only recommended to use on the disaster type of scenario.

MySQL InnoDB ReplicaSet is a very good feature to manage the MySQL asynchronous replication environment. It has the CLONE plugin support and it greatly helps on data provisioning and setting up the replication. But, still it has some limitations when compared with the MySQL InnoDB Cluster. 

Share this post

Leave a Reply