How to perform Switchover/Failover in MySql InnoDB ReplicaSet

In the previous blog, we saw Part I: How to Configure InnoDB ReplicaSet MySQL Click here to read more. We will use the same configuration to perform Switchover & Failover in InnoDB ReplicaSet.

Below are the setup information.

Hostname	IP	Role	Server_ID	Port	Installation Link
test-machine01	192.168.114.177	Master	1	3306	Click Here
test-machine01	192.168.114.177	Replica-1	3	3307	Click Here
test-machine02	192.168.114.176	Replica-2	2	3306	Click Here
test-machine02	192.168.114.176	Replica-3	4	3307

We will use two function Replicaset.setPrimaryInstance for planned Switchover and Replicaset.forcePrimaryInstance for failover in case of the master is inaccessible.

Step 1. Login to mysqlshell: Login to mysqlshell using clusteradmin user and check the status of InnoDB ReplicaSet.

[root@test-machine01 ~]# mysqlsh
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS >
 MySQL  JS > \connect  rsadmin@test-machine01:3306
Creating a session to 'rsadmin@test-machine01:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 120
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  test-machine01:3306 ssl  JS >



 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > var rs =dba.getReplicaSet()
You are connected to a member of replicaset 'TestReplicaSet'.
 MySQL  test-machine01:3306 ssl  JS >

 MySQL  test-machine01:3306 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "TestReplicaSet",
        "primary": "test-machine01:3306",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "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"
            },
            "test-machine02:3306": {
                "address": "test-machine02: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"
            },
            "test-machine02:3307": {
                "address": "test-machine02:3307",
                "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"
    }
}
 MySQL  test-machine01:3306 ssl  JS >

Step 2. Perform DryRun: Perform dryrun of switchover process to new master ‘test-machine02:3306’ to check for any potential issues before performing actual switchover. You can use \help Replicaset.setPrimaryInstance to get full commands options.

 MySQL  test-machine01:3306 ssl  JS > \help Replicaset.setPrimaryInstance
NAME
      setPrimaryInstance - Performs a safe PRIMARY switchover, promoting the
                           given instance.

SYNTAX
      <ReplicaSet>.setPrimaryInstance(instance, options)

 MySQL  test-machine01:3306 ssl  JS > rs.setPrimaryInstance('test-machine02:3306',{dryRun: true})
test-machine02:3306 will be promoted to PRIMARY of 'TestReplicaSet'.
The current PRIMARY is test-machine01:3306.

* Connecting to replicaset instances
** Connecting to test-machine01:3306
** Connecting to test-machine01:3307
** Connecting to test-machine02:3306
** Connecting to test-machine02:3307
** Connecting to test-machine01:3306
** Connecting to test-machine01:3307
** Connecting to test-machine02:3306
** Connecting to test-machine02:3307

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

* Synchronizing transaction backlog at test-machine02: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 test-machine01:3306 to replicate from test-machine02:3306
** Changing replication source of test-machine01:3307 to test-machine02:3306
** Changing replication source of test-machine02:3307 to test-machine02:3306

test-machine02:3306 was promoted to PRIMARY.

dryRun finished.

 MySQL  test-machine01:3306 ssl  JS >

Step 3. Actual Switchover: Use the below command to perform switchover. Once completion of switchover, process is done. Use rs.status() function to check for replicaset status. You will notice “primary”: “test-machine02:3306”

MySQL  test-machine01:3306 ssl  JS > rs.setPrimaryInstance('test-machine02:3306');
test-machine02:3306 will be promoted to PRIMARY of 'TestReplicaSet'.
The current PRIMARY is test-machine01:3306.

* Connecting to replicaset instances
** Connecting to test-machine01:3306
** Connecting to test-machine01:3307
** Connecting to test-machine02:3306
** Connecting to test-machine02:3307
** Connecting to test-machine01:3306
** Connecting to test-machine01:3307
** Connecting to test-machine02:3306
** Connecting to test-machine02:3307

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

* Synchronizing transaction backlog at test-machine02: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 test-machine01:3306 to replicate from test-machine02:3306
** Changing replication source of test-machine01:3307 to test-machine02:3306
** Changing replication source of test-machine02:3307 to test-machine02:3306

test-machine02:3306 was promoted to PRIMARY.

 MySQL  test-machine01:3306 ssl  JS >



 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > rs.status();
{
    "replicaSet": {
        "name": "TestReplicaSet",
        "primary": "test-machine02:3306",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01: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"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "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"
            },
            "test-machine02:3306": {
                "address": "test-machine02:3306",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            },
            "test-machine02:3307": {
                "address": "test-machine02:3307",
                "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"
    }
}
 MySQL  test-machine01:3306 ssl  JS >

Step 4: Let simulate Failover: Let stop mysql instance : test-machine02:3306 which is new master after switchover to simulate failover scenarios. Once mysql instance : test-machine02:3306 is stopped check replicaset status using function rs.status(). You will notice “test-machine02:3306” : “status”: “UNAVAILABLE”.

[root@test-machine02 ~]# ps -ef|grep mysqld
mysql     26028      1  0 10:25 ?        00:01:37 /u01/mysql-8/bin/mysqld --defaults-group-suffix=@inst2
mysql     27425      1  0 Mar18 ?        00:15:00 /u01/mysql-8/bin/mysqld --defaults-group-suffix=@inst1

[root@test-machine02 ~]#
[root@test-machine02 ~]# systemctl stop mysqld@inst1
[root@test-machine02 ~]#

 MySQL  test-machine01:3306 ssl  JS > rs.status();
ReplicaSet.status: Failed to execute query on Metadata server test-machine02:3306: Lost connection to MySQL server during query (MySQL Error 2013)
 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS >


 MySQL  test-machine01:3306 ssl  JS > \quit
Bye!
[root@test-machine01 ~]# mysqlsh
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \connect rsadmin@test-machine01:3306
Creating a session to 'rsadmin@test-machine01:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 11709
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > dba.getReplicaSet()
You are connected to a member of replicaset 'TestReplicaSet'.
<ReplicaSet:TestReplicaSet>
 MySQL  test-machine01:3306 ssl  JS >  var rs =dba.getReplicaSet()
You are connected to a member of replicaset 'TestReplicaSet'.
 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > rs.status();
ERROR: Unable to connect to the PRIMARY of the replicaset TestReplicaSet: MySQL Error 2003: Could not open connection to 'test-machine02:3306': Can't connect to MySQL server on 'test-machine02' (111)
Cluster change operations will not be possible unless the PRIMARY can be reached.
If the PRIMARY is unavailable, you must either repair it or perform a forced failover.
See \help forcePrimaryInstance for more information.
WARNING: MYSQLSH 51118: PRIMARY instance is unavailable
{
    "replicaSet": {
        "name": "TestReplicaSet",
        "primary": "test-machine02:3306",
        "status": "UNAVAILABLE",
        "statusText": "PRIMARY instance is not available, but there is at least one SECONDARY that could be force-promoted.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "fenced": true,
                "instanceErrors": [
                    "ERROR: Replication I/O thread (receiver) has stopped with an error."
                ],
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Slave has read all relay log; waiting for more updates",
                    "expectedSource": "test-machine02:3306",
                    "receiverLastError": "error reconnecting to master 'mysql_innodb_rs_1@test-machine02:3306' - retry-time: 60 retries: 3 message: Can't connect to MySQL server on 'test-machine02' (111)",
                    "receiverLastErrorNumber": 2003,
                    "receiverLastErrorTimestamp": "2021-03-22 13:50:56.318987",
                    "receiverStatus": "ERROR",
                    "receiverThreadState": "",
                    "replicationLag": null,
                    "source": "test-machine02:3306"
                },
                "status": "ERROR",
                "transactionSetConsistencyStatus": null
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "fenced": true,
                "instanceErrors": [
                    "ERROR: Replication I/O thread (receiver) has stopped with an error."
                ],
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Slave has read all relay log; waiting for more updates",
                    "expectedSource": "test-machine02:3306",
                    "receiverLastError": "error reconnecting to master 'mysql_innodb_rs_3@test-machine02:3306' - retry-time: 60 retries: 3 message: Can't connect to MySQL server on 'test-machine02' (111)",
                    "receiverLastErrorNumber": 2003,
                    "receiverLastErrorTimestamp": "2021-03-22 13:50:56.318986",
                    "receiverStatus": "ERROR",
                    "receiverThreadState": "",
                    "replicationLag": null,
                    "source": "test-machine02:3306"
                },
                "status": "ERROR",
                "transactionSetConsistencyStatus": null
            },
            "test-machine02:3306": {
                "address": "test-machine02:3306",
                "connectError": "Could not open connection to 'test-machine02:3306': Can't connect to MySQL server on 'test-machine02' (111)",
                "fenced": null,
                "instanceRole": "PRIMARY",
                "mode": null,
                "status": "UNREACHABLE"
            },
            "test-machine02:3307": {
                "address": "test-machine02:3307",
                "fenced": true,
                "instanceErrors": [
                    "ERROR: Replication I/O thread (receiver) has stopped with an error."
                ],
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Slave has read all relay log; waiting for more updates",
                    "expectedSource": "test-machine02:3306",
                    "receiverLastError": "error reconnecting to master 'mysql_innodb_rs_4@test-machine02:3306' - retry-time: 60 retries: 3 message: Can't connect to MySQL server on 'test-machine02' (111)",
                    "receiverLastErrorNumber": 2003,
                    "receiverLastErrorTimestamp": "2021-03-22 13:50:56.314898",
                    "receiverStatus": "ERROR",
                    "receiverThreadState": "",
                    "replicationLag": null,
                    "source": "test-machine02:3306"
                },
                "status": "ERROR",
                "transactionSetConsistencyStatus": null
            }
        },
        "type": "ASYNC"
    }
}
 MySQL  test-machine01:3306 ssl  JS >

Step 5: Dryrun for Failover: Run function forcePrimaryInstance with dryrun option to ‘test-machine01:3306’ to check for any potential error. You can use command \help Replicaset.forcePrimaryInstance to get full options.

MySQL  test-machine01:3306 ssl  JS > \help Replicaset.forcePrimaryInstance
NAME
      forcePrimaryInstance - Performs a failover in a replicaset with an
                             unavailable PRIMARY.

SYNTAX
      <ReplicaSet>.forcePrimaryInstance(instance, options)

rs.forcePrimaryInstance('test-machine01:3306',{dryRun: true})


 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > rs.forcePrimaryInstance('test-machine01:3306',{dryRun: true})
* Connecting to replicaset instances
** Connecting to test-machine01:3306
** Connecting to test-machine01:3307
** Connecting to test-machine02:3307

* Waiting for all received transactions to be applied
** Waiting for received transactions to be applied at test-machine01:3307
** Waiting for received transactions to be applied at test-machine01:3306
** Waiting for received transactions to be applied at test-machine02:3307
test-machine01:3306 will be promoted to PRIMARY of the replicaset and the former PRIMARY will be invalidated.

* Checking status of last known PRIMARY
NOTE: test-machine02:3306 is UNREACHABLE
* Checking status of promoted instance
NOTE: test-machine01:3306 has status ERROR
* Checking transaction set status
* Promoting test-machine01:3306 to a PRIMARY...

* Updating metadata...

test-machine01:3306 was force-promoted to PRIMARY.
NOTE: Former PRIMARY test-machine02:3306 is now invalidated and must be removed from the replicaset.
* Updating source of remaining SECONDARY instances
** Changing replication source of test-machine01:3307 to test-machine01:3306
** Changing replication source of test-machine02:3307 to test-machine01:3306

Failover finished successfully.

dryRun finished.

 MySQL  test-machine01:3306 ssl  JS >

Step 6. Actual Failover: Use below command to perform actual failover. Once process is finished you will notice “primary”: “test-machine01:3306” and “test-machine02:3306” “status”: “INVALIDATED”

 MySQL  test-machine01:3306 ssl  JS > rs.forcePrimaryInstance('test-machine01:3306')
* Connecting to replicaset instances
** Connecting to test-machine01:3306
** Connecting to test-machine01:3307
** Connecting to test-machine02:3307

* Waiting for all received transactions to be applied
** Waiting for received transactions to be applied at test-machine01:3307
** Waiting for received transactions to be applied at test-machine01:3306
** Waiting for received transactions to be applied at test-machine02:3307
test-machine01:3306 will be promoted to PRIMARY of the replicaset and the former PRIMARY will be invalidated.

* Checking status of last known PRIMARY
NOTE: test-machine02:3306 is UNREACHABLE
* Checking status of promoted instance
NOTE: test-machine01:3306 has status ERROR
* Checking transaction set status
* Promoting test-machine01:3306 to a PRIMARY...

* Updating metadata...

test-machine01:3306 was force-promoted to PRIMARY.
NOTE: Former PRIMARY test-machine02:3306 is now invalidated and must be removed from the replicaset.
* Updating source of remaining SECONDARY instances
** Changing replication source of test-machine01:3307 to test-machine01:3306
** Changing replication source of test-machine02:3307 to test-machine01:3306

Failover finished successfully.

 MySQL  test-machine01:3306 ssl  JS >



 MySQL  test-machine01:3306 ssl  JS >  rs.status()
{
    "replicaSet": {
        "name": "TestReplicaSet",
        "primary": "test-machine01:3306",
        "status": "AVAILABLE_PARTIAL",
        "statusText": "The PRIMARY instance is available, but one or more SECONDARY instances are not.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "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"
            },
            "test-machine02:3306": {
                "address": "test-machine02:3306",
                "connectError": "Could not open connection to 'test-machine02:3306': Can't connect to MySQL server on 'test-machine02' (111)",
                "fenced": null,
                "instanceRole": null,
                "mode": null,
                "status": "INVALIDATED"
            },
            "test-machine02:3307": {
                "address": "test-machine02:3307",
                "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"
    }
}
 MySQL  test-machine01:3306 ssl  JS >

Step 7. Lets remove “test-machine02:3306”: Remove mysql instance “test-machine02:3306” from topology using function rs.removeInstance.

MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > rs.removeInstance('test-machine02:3306')
ERROR: Unable to connect to the target instance test-machine02:3306. Please make sure the instance is available and try again. If the instance is permanently not reachable, use the 'force' option to remove it from the replicaset metadata and skip reconfiguration of that instance.
ReplicaSet.removeInstance: Could not open connection to 'test-machine02:3306': Can't connect to MySQL server on 'test-machine02' (111) (MySQL Error 2003)
 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS >


 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > rs.removeInstance('test-machine02:3306',{force:true})
NOTE: Unable to connect to the target instance test-machine02:3306. The instance will only be removed from the metadata, but its replication configuration cannot be updated. Please, take any necessary actions to make sure that the instance will not replicate from the replicaset if brought back online.
NOTE: test-machine02:3306 is invalidated, replication sync will be skipped.
Metadata for instance 'test-machine02:3306' was deleted, but instance configuration could not be updated.

 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS >  rs.status()
{
    "replicaSet": {
        "name": "TestReplicaSet",
        "primary": "test-machine01:3306",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "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"
            },
            "test-machine02:3307": {
                "address": "test-machine02:3307",
                "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"
    }
}
 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS >

Reference : MySQL InnoDB ReplicaSet : https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-replicaset.html

Reference : MySQL Database Architectures : https://www.youtube.com/watch?v=NYrcHMdwrpo

Part I : How to Configure InnoDB ReplicaSet Click here to read more.

Part III : How To Bootstrap MySQL Router For InnoDB ReplicaSet Click here to read more

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.