MySQL Shell 9.4

9.8 InnoDB ClusterSet Controlled Switchover

A controlled switchover makes a selected replica cluster into the primary cluster for the InnoDB ClusterSet deployment. During a controlled switchover process, data consistency is assured. The process verifies that the selected replica cluster is synchronized with the primary cluster (which might mean a short wait if there is replication lag), then makes that cluster into the primary of the InnoDB ClusterSet. The original primary cluster is demoted to a working read-only replica cluster. You can then take the original primary offline if necessary, repair any issues, and bring it back into operation in the InnoDB ClusterSet deployment.

Follow the controlled switchover procedure if the primary cluster in an InnoDB ClusterSet deployment is functioning acceptably, but you need to carry out maintenance or fix some minor issues to improve the primary cluster's function. A primary cluster that is functioning acceptably has the global status OK when you check it using AdminAPI's clusterSet.status() command in MySQL Shell.

If the primary cluster is not functioning acceptably (with the global status NOT_OK) in the InnoDB ClusterSet deployment, first try to repair any issues using AdminAPI through MySQL Shell. For example, if the primary cluster has lost quorum, it can be restored using a cluster.forceQuorumUsingPartitionOf command. For instructions to do this, see Section 9.10, “InnoDB ClusterSet Repair and Rejoin”.

If you cannot fix the issue by working with the primary cluster (for example, because you cannot contact it), you need to perform an emergency failover. An emergency failover is designed for disaster recovery when the primary cluster is suddenly unavailable. That procedure carries the risk of losing transactions and creating a split-brain situation for the InnoDB ClusterSet. If you do need to carry out an emergency failover, follow the procedure in Section 9.9, “InnoDB ClusterSet Emergency Failover” to ensure that the risk is managed.

The diagram shows the effects of a controlled switchover in an example InnoDB ClusterSet deployment. The primary cluster in the Rome datacenter requires maintenance, so a controlled switchover has been carried out to make the replica cluster in the Brussels datacenter into the primary of the InnoDB ClusterSet deployment, and demote the Rome cluster to a replica. The ClusterSet replication channel on the Rome cluster has been activated by the controlled switchover process, and it is replicating transactions from the Brussels cluster. Now that the Rome cluster is a replica cluster, the member servers or the complete cluster can safely be taken offline if required to carry out the maintenance work.

Figure 9.2 InnoDB ClusterSet Switchover

The InnoDB Cluster in the Rome datacenter is now a replica cluster, and the InnoDB Cluster in the Brussels datacenter is now the primary cluster. The asynchronous replication channel is now sending transactions from the Brussels cluster to the Rome cluster. The MySQL Router instances that targeted the primary or the Brussels cluster are sending traffic to the Brussels cluster. The instance that specifically targeted the Rome cluster can continue to send traffic to it because it is only sending read traffic.

The MySQL Router instances in the example InnoDB Cluster deployment that were set to follow the primary have routed read and write traffic to the Brussels cluster which is now the primary. The MySQL Router instance that was routing read traffic to the Brussels cluster by name when it was a replica cluster, continues to route traffic to it, and is not affected by the fact that the cluster is now the primary rather than a replica cluster. Similarly, the MySQL Router instance that was routing read traffic to the Rome cluster by name can continue to do this, because the replica cluster still accepts read traffic.

To carry out a controlled switchover for the primary InnoDB Cluster, follow this procedure:

  1. Using MySQL Shell, connect to any member server in the primary cluster or in one of the replica clusters, using an InnoDB Cluster administrator account (created with cluster.setupAdminAccount()). You may also use the InnoDB Cluster server configuration account, which also has the required permissions. Get the ClusterSet object using dba.getClusterSet() or cluster.getClusterSet() command. It is important to use an InnoDB Cluster administrator account or server configuration account so that the default user account stored in the ClusterSet object has the correct permissions. For example: 

    mysql-js> \connect admin2@127.0.0.1:3310
Creating a session to 'admin2@127.0.0.1:3310'
Please provide the password for 'admin2@127.0.0.1:3310': ********
Save password for 'admin2@127.0.0.1:3310'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 52
Server version: 8.0.27-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
<ClassicSession:admin2@127.0.0.1:3310>
mysql-js> myclusterset = dba.getClusterSet()
<ClusterSet:testclusterset>

    In this example:

    • admin2@127.0.0.1:3310 is the URI-like connection string for any member server instance that is online in the cluster.

      The URI-like connection string is comprised of the following elements:

    • admin2 is the user name for an InnoDB Cluster administrator account.

    • 127.0.0.1:3310 is the host and port for the member server instance, as displayed by the cluster.status() command.

    • The returned ClusterSet object is assigned to the variable myclusterset.

  2. Check the status of the whole InnoDB ClusterSet deployment using AdminAPI's clusterSet.status() command in MySQL Shell. Use the extended option to view detailed information for all the clusters in the deployment, and check for any issues. For example: 

    mysql-js> myclusterset.status({extended: 1})

    For an explanation of the output, see Section 9.7, “InnoDB ClusterSet Status and Topology”.

  3. Identify a suitable replica cluster that can take over as the primary cluster. A replica cluster's eligibility for a controlled switchover depends on its global status, as reported by the clusterSet.status() command:

    Table 9.1 Permitted Cluster Operations By Status

    InnoDB Cluster Global Status in ClusterSet Routable Controlled Switchover Emergency Failover
    OK Yes Yes Yes
    OK_NOT_REPLICATING Yes, if specified as target cluster by name Yes Yes
    OK_NOT_CONSISTENT Yes, if specified as target cluster by name No Yes
    OK_MISCONFIGURED Yes Yes Yes
    NOT_OK No No No
    INVALIDATED Yes, if specified as target cluster by name and accept_ro routing policy is set No No
    UNKNOWN Connected MySQL Router instances might still be routing traffic to the cluster No No

    A replica cluster with the global status OK_NOT_CONSISTENT has a set of transactions on the cluster (the GTID set) that is inconsistent with the GTID set on the primary cluster. InnoDB ClusterSet does not permit a controlled switchover to a cluster in this state, because clients would access incorrect data. An emergency failover is possible, if the cluster has the most up to date set of transactions among the available options.

  4. Check the routing options that are set for each MySQL Router instance, and the global policy for the InnoDB ClusterSet deployment, by issuing a clusterSet.routerOptions() command in MySQL Shell while connected to any member server in the InnoDB ClusterSet deployment.

    By default, a MySQL Router instance sends traffic to whichever cluster is currently the primary in the InnoDB ClusterSet deployment. If all the MySQL Router instances are set to follow the primary ("target_cluster": "primary"), traffic will be automatically redirected to the new primary cluster within a few seconds of the switchover. If a routing option is not displayed for a MySQL Router instance, as in the example above for Rome2, it means the instance does not have that policy set, and it follows the global policy.

    If any of the instances are set to target the current primary cluster by name ("target_cluster": "name_of_primary_cluster"), they will not redirect traffic to the new primary. In that situation, if it is appropriate for the application, you can use the clusterSet.setRoutingOption() command to change the routing policy for those instances. You could change those instances to follow the primary ("target_cluster": "primary"), in which case that option can be set now. For example: 

    mysql-js> myclusterset.setRoutingOption('Rome1', 'target_cluster', 'primary')
Routing option 'target_cluster' successfully updated in router 'Rome1'.

    In this example, myclusterset is the variable for the ClusterSet object, and Rome1 is the name of the MySQL Router instance.

    Or you could specify the replica cluster that will take over as the primary, in which case set the option ("target_cluster": "name_of_new_primary_cluster") after the switchover has taken place, when you have verified that it has worked.

  5. Issue a clusterSet.setPrimaryCluster() command, naming the replica cluster that will take over as the new primary cluster. Use the ClusterSet object that you retrieved using an InnoDB Cluster administrator account, with the dba.getClusterSet() or cluster.getClusterSet() command. For example: 

    mysql-js> myclusterset.setPrimaryCluster('clustertwo')
Switching the primary cluster of the clusterset to 'clustertwo'
* Verifying clusterset status
** Checking cluster clustertwo
  Cluster 'clustertwo' is available
** Checking cluster clusterone
  Cluster 'clusterone' is available

* Refreshing replication account of demoted cluster
* Synchronizing transaction backlog at 127.0.0.1:4410
** Transactions replicated  ############################################################  100%
* Updating metadata

* Updating topology
** Changing replication source of 127.0.0.1:3330 to 127.0.0.1:4410
* Acquiring locks in replicaset instances
** Pre-synchronizing SECONDARIES
** Acquiring global lock at PRIMARY
** Acquiring global lock at SECONDARIES

* Synchronizing remaining transactions at promoted primary
** Transactions replicated  ############################################################  100%
* Updating replica clusters
Cluster 'clustertwo' was promoted to PRIMARY of the clusterset. The PRIMARY instance is '127.0.0.1:4410'

    For the clusterSet.setPrimaryCluster() command:

    • The clusterName parameter is required and specifies the identifier used for the replica cluster in the InnoDB ClusterSet, as given in the output from the clusterSet.status() command. In the example, clustertwo is the cluster that is to become the new primary.

    • Use the dryRun option if you want to carry out validations and log the changes without actually executing them.

    • Use the timeout option to set the maximum number of seconds to wait for the replica cluster to synchronize with the primary cluster before the switchover takes place. If the timeout expires, the switchover is canceled.

    • Use the invalidateReplicaClusters option to name any replica clusters that are unreachable or unavailable. These will be marked as invalidated during the switchover process. The switchover is canceled if any unreachable or unavailable replica clusters that you do not name are discovered during the process. In this situation you must either repair and rejoin the replica clusters then retry the command, or name them on this option when you retry the command, and fix them later.

    When you issue the clusterSet.setPrimaryCluster() command, MySQL Shell checks that the target replica cluster complies with the requirements to take over as the primary cluster, and returns an error if it does not. If the target replica cluster meets the requirements, MySQL Shell carries out the following tasks:

    • Checks for any unreachable or unavailable replica clusters that have not been specified using invalidateReplicaClusters.

    • Waits for the target replica cluster to synchronize with the current primary cluster by applying any outstanding transactions from the primary. If the timeout set by the timeout option expires before the replica cluster has finished applying transactions, the switchover is canceled.

    • Locks the current primary cluster by issuing a FLUSH TABLES WITH READ LOCK statement and setting the super_read_only system variable on all member servers, to prevent further changes during the switchover. The Group Replication member action mysql_disable_super_read_only_if_primary is disabled so that super_read_only remains set after the failover.

    • Reconciles the differences in view change events between the current primary cluster and the replica clusters so that the GTID sets are identical. These Group Replication internal transactions are identified by the UUID specified by the group_replication_view_change_uuid system variable. MySQL Shell injects empty transactions on all the replica clusters to match the view change events on the primary cluster.

      Note

      This is not required for Clusters running MySQL Server 8.3.0 or higher.

    • Updates the ClusterSet replication channel on all replica clusters to replicate from the target cluster as the new primary cluster.

    • Disables super_read_only on the primary server of the target cluster, and enables the Group Replication member action mysql_disable_super_read_only_if_primary to handle any changes to the primary server in that cluster.

    • Disables the Group Replication member action mysql_disable_super_read_only_if_primary on the primary server of the old primary cluster, so that it remains read-only, and enables the Group Replication member action mysql_start_failover_channels_if_primary on that server to enable asynchronous connection failover for replicas on the ClusterSet replication channel.

    • Sets the target cluster as the primary cluster in the ClusterSet metadata, and changes the old primary cluster into a replica cluster.

  6. Issue a clusterSet.status() command again using the extended option, to verify the status of the InnoDB ClusterSet deployment.

  7. If you have any MySQL Router instances to switch over to targeting the new primary cluster, do that now. For example: 

    mysql-js> myclusterset.setRoutingOption('Rome1', 'target_cluster', 'clustertwo')
Routing option 'target_cluster' successfully updated in router 'Rome1'.

    In this example, myclusterset is the variable for the ClusterSet object, Rome1 is the name of the MySQL Router instance, and clustertwo is the name of the specific cluster to target. When you have finished, issue a clusterSet.routerOptions() command to check that all the MySQL Router instances are now routing correctly.

  8. Now you can work with the old primary cluster to fix issues or carry out maintenance. If you had to invalidate any replica clusters during the switchover process, you can repair these as well and add them back into the InnoDB ClusterSet. Section 9.10, “InnoDB ClusterSet Repair and Rejoin” explains how to repair issues with a cluster, how to rejoin a cluster to the InnoDB ClusterSet, and how to make a cluster into the primary cluster again.

MySQL Documentation Library