Note:

• This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
• It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Set up Cross-Region Oracle Heatwave MySQL Disaster Recovery Copy in OCI

Introduction

Oracle Heatwave MySQL disaster recovery copy is a feature in Oracle Cloud Infrastructure (OCI) where you can set up cross-region replication for the production databases in OCI. There are two ways to set up Oracle HeatWave MySQL disaster recovery copy.

• Using OCI Console level full manual backup, copying the backup to cross-region and set up an in-bound replication channel.

• Using MySQL Shell utilities, backup and restore the production database to disaster recovery region database and set up an in-bound replication channel.

This tutorial outlines how to set up Oracle Heatwave MySQL disaster recovery copy for the production database using the OCI Console level full backup, copying the backup to another region, building the disaster recovery database from the primary backup and setting up the in-bound replication channel from primary to disaster recovery database. In this tutorial, you will set up disaster recovery for Oracle Heatwave MySQL database services running on v8.0.35 and above.

The following image shows the architecture we will use in this tutorial.

Objectives

• Set up Oracle Heatwave MySQL disaster recovery copy for production databases using OCI Console level backups.

Prerequisites

• Set up remote VCN peering between source and target regions.

• Add port 3306 and 33060 to the security lists of the subnets in both source and target VCNs.

• Create a custom configuration in order to increase the default value of binlog_expire_logs_seconds to minimum 3 days and apply the same to the primary database system.

Task 1: Prepare and Take a Full Manual Backup in OCI Console

During non-business hours, take a manual full backup of production Oracle Heatwave MySQL database service at OCI Console level.

1. Click More actions drop-down menu and select Create manual backup.

   

2. Enter the required information and click Create manual backups.

   

Task 2: Copy Backup to Disaster Recovery Region

1. Click Copy to another region to copy the full manual backup to the disaster recovery region.

   

2. Enter the required information and click Copy.

   

   Verify the backup in disaster recovery region once copy is completed.

   

Task 3: Restore the Backup from Disaster Recovery Region into a New Database System

1. Click Restore to a new DB system to restore the primary database backup to a new database system in the disaster recovery region.

   

2. Select the Compartment, VCN and Subnets in OCI Console and the respective DB Shape and Storage required.

3. Select the MySQL database version to be the same as primary database and click Restore.

   

   Once the database in disaster recovery region is provisioned, you can connect to the disaster recovery database to validate the schemas and objects.

Task 4: Create a Database User for Replication

Run the following query to create a replication user in primary database using the disaster recovery region provisioned database endpoint IP.

CREATE USER 'repl1'@'x.x.x.x' IDENTIFIED BY '********' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl1'@'x.x.x.x';

Task 5: Create a Replication Channel

1. Click Create channel to create an inbound replication channel in the disaster recovery database system in OCI Console. Enter the necessary source database user credentials and other replication channel details.

   

   

   

2. Click Create channel.

   

Task 6: Validate the Replication Channel

Once the channel is active in OCI Console, connect to the disaster recovery database and check for Replica Status and Sync between the primary and disaster recovery database.

Show Replica Status \G;

Note: You must check for I/O running, SQL running status and seconds behind master in the replica status output.

Related Links

• Setting Up MySQL Disaster Recovery Copy in OCI

• Setup Disaster Recovery for OCI MySQL Database Service

Acknowledgments

• Author - Ajay Rao (Lift Implementation Specialist - Custom, 3rd party, VM Database & Apps)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

---

Title and Copyright Information

<

Set up Cross-Region Oracle Heatwave MySQL Disaster Recovery Copy in OCI

G29045-01

Copyright ©2025, Oracle and/or its affiliates.