MySQL 8.0.19 InnoDB ReplicaSet Configuration and Manual Switchover

Manual Switchover InnoDB 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.

#vi /etc/hosts
172.28.128.20 replicaset1 replicaset1
172.28.128.21 replicaset2 replicaset2

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,

[root@replicaset1 ~]# mysqlsh --uri root@localhost
Please provide the password for 'root@localhost': *************
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): y
MySQL Shell 8.0.20

Configuring the instance,

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

MySQL  localhost:33060+ ssl  JS > dba.configureReplicaSetInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet...
This instance reports its own address as replicaset1:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, thereport_host MySQL system variable should be changed.
ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.

1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB ReplicaSet with minimal required grants
3) Ignore and continue
4) Cancel

Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: InnodbReplicaSet
Password for new account: ********
Confirm password: ********

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             | <unique ID>    | 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 'InnodbReplicaSet'@'%' created.
Configuring instance...
The instance 'replicaset1:3306' was configured to be used in an InnoDB ReplicaSet.
Restarting MySQL...
NOTE: MySQL server at replicaset1:3306 was restarted.

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

[root@replicaset1 mysql]# cat mysqld-auto.cnf 

{ "Version" : 1 , "mysql_server" : { "server_id" : { "Value" : "3391287398" , "Metadata" : { "Timestamp" : 1598084590766958 , "User" : "root" , "Host" : "localhost" } } , "read_only" : { "Value" : "OFF" , "Metadata" : { "Timestamp" : 1598084718849667 , "User" : "InnodbReplicaSet" , "Host" : "localhost" } } , "super_read_only" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1598084898510380 , "User" : "InnodbReplicaSet" , "Host" : "localhost" } } , "mysql_server_static_options" : { "enforce_gtid_consistency" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1598084590757563 , "User" : "root" , "Host" : "localhost" } } , "gtid_mode" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1598084590766121 , "User" : "root" , "Host" : "localhost" } } } } }

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.

MySQL  localhost:33060+ ssl  JS > \connect InnodbReplicaSet@replicaset1
Creating a session to 'InnodbReplicaSet@replicaset1'
Please provide the password for 'InnodbReplicaSet@replicaset1': ********
Save password for 'InnodbReplicaSet@replicaset1'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 8 (X protocol)
Server version: 8.0.20-11 Percona Server (GPL), Release 11, Revision 5b5a5d2

Step 4:

Now, all are set to create the ReplicaSet.

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

MySQL  replicaset1:33060+ ssl  JS > dba.createReplicaSet('PerconaReplicaSet')
A new replicaset with instance 'replicaset1:3306' will be created.

* Checking MySQL instance at replicaset1:3306
This instance reports its own address as replicaset1:3306
replicaset1:3306: Instance configuration is suitable.

* Updating metadata...
ReplicaSet object successfully created for replicaset1:3306.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.
<ReplicaSet:PerconaReplicaSet>

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

MySQL  replicaset1:33060+ ssl  JS > replicaset = dba.getReplicaSet()
You are connected to a member of replicaset 'PerconaReplicaSet'.
<ReplicaSet:PerconaReplicaSet>
 MySQL  replicaset1:33060+ ssl  JS > 
 MySQL  replicaset1:33060+ ssl  JS > replicaset.status()
{
    "replicaSet": {
        "name": "PerconaReplicaSet", 
        "primary": "replicaset1:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "replicaset1:3306": {
                "address": "replicaset1:3306", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

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

MySQL replicaset1:33060+ ssl JS > replicaset.addInstance('replicaset2:3306')
Adding instance to the replicaset...
* Performing validation checks
This instance reports its own address as replicaset2:3306
replicaset2:3306: Instance configuration is suitable.
* Checking async replication topology...
* Checking transaction state of the instance...

NOTE: The target instance 'replicaset2: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 'replicaset2: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): C
* Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: replicaset2:3306 is being cloned from replicaset1:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
** Stage RECOVERY: |
NOTE: replicaset2:3306 is shutting down...

* Waiting for server restart... ready
* replicaset2:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 60.68 MB transferred in about 1 second (~60.68 MB/s)
** Configuring replicaset2:3306 to replicate from replicaset1:3306
** Waiting for new instance to synchronize with PRIMARY...
The instance 'replicaset2:3306' was added to the replicaset and is replicating from replicaset1:3306.

Here I have chosen the clone method for recovery.

MySQL  replicaset1:33060+ ssl  JS > replicaset.status()
{
    "replicaSet": {
        "name": "PerconaReplicaSet", 
        "primary": "replicaset1:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "replicaset1:3306": {
                "address": "replicaset1:3306", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }, 
            "replicaset2:3306": {
                "address": "replicaset2: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"
    }
}

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

MySQL  replicaset1:33060+ ssl  JS > replicaset.setPrimaryInstance('replicaset2:3306')
replicaset2:3306 will be promoted to PRIMARY of 'PerconaReplicaSet'.
The current PRIMARY is replicaset1:3306.

* Connecting to replicaset instances
** Connecting to replicaset1:3306
** Connecting to replicaset2:3306
** Connecting to replicaset1:3306
** Connecting to replicaset2:3306
* Performing validation checks
** Checking async replication topology...
** Checking transaction state of the instance...
* Synchronizing transaction backlog at replicaset2: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 replicaset1:3306 to replicate from replicaset2:3306
replicaset2:3306 was promoted to PRIMARY.

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

MySQL  replicaset1:33060+ ssl  JS > replicaset.status()
{
    "replicaSet": {
        "name": "PerconaReplicaSet", 
        "primary": "replicaset2:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "replicaset1:3306": {
                "address": "replicaset1: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"
            }, 
            "replicaset2:3306": {
                "address": "replicaset2:3306", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

Removing “replicaset1” from the group,

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

MySQL  replicaset1:33060+ ssl  JS > replicaset.removeInstance('replicaset1:3306')
The instance 'replicaset1:3306' was removed from the replicaset.
MySQL  replicaset1:33060+ ssl  JS > 
MySQL  replicaset1:33060+ ssl  JS > replicaset.status()
{
    "replicaSet": {
        "name": "PerconaReplicaSet", 
        "primary": "replicaset2:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "replicaset2:3306": {
                "address": "replicaset2:3306", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

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.

by Sri Sakthivel via Percona Database Performance Blog

Search This Blog

Small Business News