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>’)