Oracle Cloud Infrastructure Documentation

Switchover

You can manually promote one of the secondary MySQL instances as the primary instance. This is called as switchover.

When you perform a switchover, the preferred placement changes to the newly selected placement of the primary instance, that is, the current placement of the primary instance is same as the preferred placement. The IP address of the DB system endpoint does not change. Switchover results in a short period of downtime while the primary instance is redirected to the newly promoted instance. You must reopen the database connections.

Following is the switchover process:

  • Running transactions are allowed to complete. The switchover process waits until all running transactions finish and commit.
  • New transactions are accepted and can perform reads and writes but, when the MySQL instance is promoted to be the new primary instance, and the connection to the previous primary instance is broken, all uncommitted transactions are rolled back.
  • The DB system endpoint is assigned to the newly promoted primary instance.
  • Existing connections to the previous primary instance are closed. The client application must reopen connections.
Note

After a switchover, the current binary log file name and position of the new primary may be different from the old primary. As the binary logs of each instance are managed independently, each transaction recorded in the binary logs may be written to a different binary log file and position in different instances.

Switchover Scenarios

You can use switchover in the following scenarios:

  • Application testing: Ensuring your application works correctly with the newly promoted primary instance.
  • Proximity of availability domain: While fault domains are contained in the same data center, availability domains are spread across different data centers. Even though these availability domains are connected with a low-latency network, it may be necessary to test that latency by moving primary instance from availability domain to availability domain to confirm the best location for your primary instance. For example, switching to an instance in the same availability domain as the application which connects to its.

HeatWave Support

When you perform a switchover, the HeatWave cluster attached to the earlier primary instance is deleted and a new HeatWave cluster is attached to the new primary instance.

When you perform a switchover, a long running HeatWave query can get aborted. In this case, you need to reconnect and rerun the query after the HeatWave cluster is ready.

Using the Console

Use the Console to promote a secondary MySQL instance as the primary instance.

This task requires the following:
  • A running DB system with high availability enabled.
Do the following to switch from the current primary instance to one of the secondary instances:
  1. Open the navigation menu. Under MySQL HeatWave, click DB Systems.
  2. Choose the compartment from the List Scope.
  3. In the list of DB systems, find the DB system you want to switch, and do one of the following:
    • Choose Switchover from the Actions menu on the same line as the DB system.
    • Click the name of the DB system to open the DB System Details page. Select Switchover from the More Actions menu.
  4. In the Switchover dialog box, select the availability or fault domain, depending on your setup, which contains the instance you want to switch to.
  5. Click Switchover.
The status of the DB system changes to Updating, and the selected instance becomes the primary.

Using the CLI

Use the command-line interface to promote a secondary MySQL instance to the primary instance.

This task requires the following:
  • The OCID of a running DB system with high availability enabled.
  • A policy that permits you to update DB system.
  • A properly configured CLI installation. See Command Line Interface.
Do the following to switch from the current primary instance to one of the secondary instances:
  1. Open the command-line interface and list all the availability domains in the current region:
    oci iam availability-domain list
  2. Switchover to the desired MySQL instance:
    • For multi-availability domain: 
      oci mysql db-system update --db-system-id <DBSystemOCID> --availability-domain <SelectedAvailabilityDomain>
    • For single-availability domain:
      oci mysql db-system update --db-system-id <DBSystemOCID> --fault-domain <SelectedFaultDomain>

    Where

    • db-system-id: Specify the OCID of the DB system.
    • availability-domain: Specify the name of the availability domain in which the secondary MySQL instance, which you want to promote to the primary, is present.
    • fault-domain: Specify the name of the fault domain in which the secondary MySQL instance, which you want to promote to the primary, is present.
The status of the DB system changes to Updating, and the instance in the selected availability or fault domain becomes the primary instance and the read/write endpoint.