How to Execute a Switchover in InnoDB Cluster

Introduction

The process of executing a switchover operation is important for an InnoDB cluster. It encompasses not merely routine maintenance but extends to recovery from system failures. The core of this operation involves promoting a replica node to the PRIMARY role methodically and securely.

Objective

This blog post is a comprehensive guide for database engineers and system administrators. It delineates the steps in executing a switchover by employing MySQL Shell within an InnoDB Cluster environment. The cluster under consideration comprises the following nodes:

vinnie-mysql-8-01 – 10.110.3.43
vinnie-mysql-8-02 – 10.110.3.79
vinnie-mysql-8-03 – 10.110.3.197

Pre-conditions

Before commencing the switchover process, it is imperative to establish certain pre-conditions to ascertain that the environment is primed for a successful operation:

Cluster Health: Ensuring operational status and synchronization across all nodes (vinnie-mysql-8-01, vinnie-mysql-8-02, vinnie-mysql-8-03).
Data Backup: Availability of comprehensive, validated backups for each node.
Documentation and Knowledge: Proper documentation of current configurations, MySQL versions, and detailed understanding of the network architecture peculiarities.
Monitoring Tools: Deployment of tools configured to monitor the cluster’s status, performance metrics, and log files.
Maintenance Window: Schedule an appropriate maintenance window to carry out the switchover.

Switchover Procedure

The execution of a switchover using MySQL Shell encompasses the following procedural steps:

Preparation:

Cease Write Operations: Ascertain that no applications are executing write operations on the current PRIMARY. During switch over, the node transitions to read mode, potentially causing write operations to fail. For instance:

FATAL: mysql_stmt_execute() returned error 1290 (The MySQL server is running with the --super-read-only option so it cannot execute this statement) for query 'INSERT INTO sbtest20 (id, k, c, pad) VALUES (?, ?, ?, ?)'

FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:488: SQL error, errno = 1290, state = 'HY000': The MySQL server is running with the --super-read-only option so it cannot execute this statement

FATAL: mysql_stmt_execute() returned error 1290 (The MySQL server is running with the --super-read-only option so it cannot execute this statement) for query 'INSERT INTO sbtest20 (id, k, c, pad) VALUES (?, ?, ?, ?)'

FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:488: SQL error, errno = 1290, state = 'HY000': The MySQL server is running with the --super-read-only option so it cannot execute this statement

Target Server Identification: Determine the target server for promotion, such as vinnie-mysql-8-02.
PRIMARY Identification and Connection:

shell.connect('vinnie@localhost:3306') var cluster = dba.getCluster()

1
2

shell.connect('vinnie@localhost:3306')
var cluster = dba.getCluster()
Cluster Status Review:

MySQL localhost:3306 ssl JS > cluster.status()

1

MySQL localhost:3306 ssl JS > cluster.status()

Execution of Switch Over

Initiate the switchover process:

MySQL localhost:3306 ssl JS > cluster.setPrimaryInstance('vinnie-mysql-8-02:3306');

1	MySQL localhost:3306 ssl JS > cluster.setPrimaryInstance('vinnie-mysql-8-02:3306');

System feedback will confirm the new PRIMARY and delineate the status of the other instances:

Setting instance 'vinnie-mysql-8-02:3306' as the primary instance of cluster 'mycluster'... 

Instance 'vinnie-mysql-8-03:3306' remains SECONDARY. 
Instance 'vinnie-mysql-8-02:3306' remains PRIMARY. 
Instance 'vinnie-mysql-8-01:3306' remains SECONDARY. 
  
The instance 'vinnie-mysql-8-02:3306' was successfully elected as primary.

Setting instance 'vinnie-mysql-8-02:3306' as the primary instance of cluster 'mycluster'...

Instance 'vinnie-mysql-8-03:3306' remains SECONDARY.

Instance 'vinnie-mysql-8-02:3306' remains PRIMARY.

Instance 'vinnie-mysql-8-01:3306' remains SECONDARY.

The instance 'vinnie-mysql-8-02:3306' was successfully elected as primary.

Post-Switch Over

Verification of New PRIMARY:

var cluster = dba.getCluster() 
cluster.status()

1 2	var cluster = dba.getCluster() cluster.status()

MySQL  localhost:3306 ssl  JS > cluster.status() 
{ 
    "clusterName": "mycluster", 
    "defaultReplicaSet": { 
        "name": "default", 
        "primary": "vinnie-mysql-8-02:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": { 
            "vinnie-mysql-8-01:3306": { 
                "address": "vinnie-mysql-8-01:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.34" 
            }, 
            "vinnie-mysql-8-02:3306": { 
                "address": "vinnie-mysql-8-02:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.34" 
            }, 
            "vinnie-mysql-8-03:3306": { 
                "address": "vinnie-mysql-8-03:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.34" 
            } 
        }, 
        "topologyMode": "Single-Primary" 
    }, 
    "groupInformationSourceMember": "vinnie-mysql-8-02:3306" 
}

MySQL localhost:3306 ssl JS > cluster.status()

{

"clusterName": "mycluster",

"defaultReplicaSet": {

"name": "default",

"primary": "vinnie-mysql-8-02:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"vinnie-mysql-8-01:3306": {

"address": "vinnie-mysql-8-01:3306",

"memberRole": "SECONDARY",

"mode": "R/O",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.0.34"

"vinnie-mysql-8-02:3306": {

"address": "vinnie-mysql-8-02:3306",

"memberRole": "PRIMARY",

"mode": "R/W",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.0.34"

"vinnie-mysql-8-03:3306": {

"address": "vinnie-mysql-8-03:3306",

"memberRole": "SECONDARY",

"mode": "R/O",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.0.34"

}

"topologyMode": "Single-Primary"

"groupInformationSourceMember": "vinnie-mysql-8-02:3306"

}

Alternatively:

MySQL localhost:3306 ssl JS > \sql
SHOW STATUS LIKE 'group_replication_primary_member';

1 2	MySQL localhost:3306 ssl JS > \sql SHOW STATUS LIKE 'group_replication_primary_member';

MySQL  localhost:3306 ssl  JS > \sql  SHOW STATUS LIKE 'group_replication_primary_member'; 
+----------------------------------+--------------------------------------+ 
| Variable_name                    | Value                                | 
+----------------------------------+--------------------------------------+ 
| group_replication_primary_member | a5d3a627-9e1e-11ee-ac21-42010adc014f | 
+----------------------------------+--------------------------------------+ 
1 row in set (0.0026 sec)

MySQL localhost:3306 ssl JS > \sql SHOW STATUS LIKE 'group_replication_primary_member';

+----------------------------------+--------------------------------------+

| Variable_name | Value |

+----------------------------------+--------------------------------------+

| group_replication_primary_member | a5d3a627-9e1e-11ee-ac21-42010adc014f |

+----------------------------------+--------------------------------------+

1 row in set (0.0026 sec)

Error Log Review: Meticulously inspect the error logs for the servers, with particular emphasis on the new PRIMARY and other members, to confirm operational stability. For example:

2024-01-22T23:01:12.349328Z 228 [System] [MY-013214] [Repl] Plugin group_replication reported: 'Starting group operation local execution: Primary election change' 
2024-01-22T23:01:12.377228Z 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address vinnie-mysql-8-02:3306 was elected. The new primary will execute all previous group transactions before allowing writes. Enabling conflict detection until the new primary applies all relay logs.' 
2024-01-22T23:01:12.485458Z 229 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.' 
2024-01-22T23:01:12.486126Z 181 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.' 
2024-01-22T23:01:12.486215Z 181 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.' 
2024-01-22T23:01:12.486344Z 181 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_start_failover_channels_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "10" will be run.' 
2024-01-22T23:01:12.786657Z 229 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.' 
2024-01-22T23:01:12.789131Z 0 [System] [MY-013213] [Repl] Plugin group_replication reported: 'Configuration operation 'Primary election change' terminated. Primary server switched to: a5d3a627-9e1e-11ee-ac21-42010adc014f'

2024-01-22T23:01:12.349328Z 228 [System] [MY-013214] [Repl] Plugin group_replication reported: 'Starting group operation local execution: Primary election change'

2024-01-22T23:01:12.377228Z 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address vinnie-mysql-8-02:3306 was elected. The new primary will execute all previous group transactions before allowing writes. Enabling conflict detection until the new primary applies all relay logs.'

2024-01-22T23:01:12.485458Z 229 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'

2024-01-22T23:01:12.486126Z 181 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.'

2024-01-22T23:01:12.486215Z 181 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'

2024-01-22T23:01:12.486344Z 181 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_start_failover_channels_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "10" will be run.'

2024-01-22T23:01:12.786657Z 229 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'

2024-01-22T23:01:12.789131Z 0 [System] [MY-013213] [Repl] Plugin group_replication reported: 'Configuration operation 'Primary election change' terminated. Primary server switched to: a5d3a627-9e1e-11ee-ac21-42010adc014f'

Conclusion

The switchover process within an InnoDB Cluster is critical for maintenance and effective failure recovery.

Your inquiries, as modifications or additional detail requests regarding any sections, are welcome.

Additional Resources

For an extensive understanding and adherence to best practices, refer to the official MySQL documentation on Group Replication in Single-Primary Mode.