DEV Community

Ryan Giggs
Ryan Giggs

Posted on

MySQL HeatWave: Replicating to DB System (Inbound Replication)

#mysqlheatwave #databasereplication #oracle #cloudmigration

Inbound replication is a powerful feature of MySQL HeatWave that enables you to replicate data from external MySQL sources into your HeatWave DB system. This capability supports various migration scenarios, disaster recovery strategies, and hybrid cloud architectures. Understanding inbound replication is essential for database administrators planning migrations or implementing multi-cloud strategies.

This guide covers inbound replication concepts, supported scenarios, limitations, prerequisites, configuration steps, and best practices for MySQL HeatWave.

What is Inbound Replication?

Inbound replication uses a replication channel configured in MySQL HeatWave Service to copy transactions from another location to a DB system. The channel connects the source (a MySQL instance or another DB system) to the replica (a DB system), and copies data from the source to the replica.

Key Characteristics

Asynchronous Replication: Replication is asynchronous by default; replicas do not need to be connected permanently to receive updates from a source. The replica can pick up new updates whenever it is online and reconnects.

Single Source Only: A replica DB system can connect to only one MySQL source. Multi-source replication is not supported.

Source Independence: The source server does not control the DB system replica and does not need to have permission to write to it. The replica uses the configured replication channel to connect to the source, providing a set of replication user credentials.

Important Note: Inbound replication is not a managed functionality. You are responsible for configuring and maintaining the channel, and for ensuring that the traffic between source and replica is properly configured.

Supported Replication Scenarios

MySQL HeatWave inbound replication supports three primary scenarios:

1. On-Premises MySQL Instance to DB System

Replicate an on-premises MySQL instance to a DB system on OCI. This scenario is ideal for:

  • Cloud migration projects
  • Hybrid cloud architectures
  • Disaster recovery to cloud
  • Gradual migration strategies

2. MySQL Instance on Cloud Compute to DB System

Replicate a MySQL instance installed on OCI compute instance or other cloud infrastructure (AWS, Azure, GCP) to a DB system on OCI.

Use Cases:

  • Cross-cloud replication
  • Migrating from self-managed MySQL to fully managed HeatWave
  • Consolidating databases from multiple clouds
  • Testing and validation environments

3. DB System to DB System

Replicate from one DB system to another DB system, where both systems are on OCI.

Use Cases:

  • Cross-region replication
  • Data distribution across multiple regions
  • Creating reporting replicas
  • Geographic redundancy

Understanding Replication Architecture

Source Database

The source database can be:

  • MySQL server running on customer data center: On-premises infrastructure
  • MySQL instance on OCI compute instance: Self-managed MySQL on cloud infrastructure
  • MySQL HeatWave DB System: Another fully managed HeatWave instance
  • MySQL on other clouds: AWS RDS, Azure Database for MySQL, GCP Cloud SQL

Requirements:

  • MySQL version 5.7 or higher
  • Binary logging enabled
  • Row-based replication format
  • GTID mode enabled (recommended)

Replica Database

The replica database is a MySQL HeatWave DB System connected to a private subnet of an OCI VCN.

Characteristics:

  • Fully managed by Oracle
  • Resides in private subnet
  • Applies transactions from source
  • Can be standalone or high availability DB system

Replication Flow

  1. Source server commits transactions to binary log
  2. Replication channel retrieves committed transactions
  3. Replica receives transactions over the connection
  4. Replica applies transactions to database
  5. Process repeats asynchronously

Inbound Replication Limitations

MySQL HeatWave inbound replication does not support some configurations available for standard MySQL replication:

1. Row-Based Replication Only

Only row-based replication is supported. This binary log format is the default in MySQL version 5.7 and higher. Statement-based replication and mixed replication are not supported.

Why Row-Based:

  • Provides consistent replication
  • Prevents execution variance between source and replica
  • Default for modern MySQL versions
  • Better for complex SQL statements

2. Asynchronous Replication Only

Only asynchronous replication is supported. Semi-synchronous replication is not supported.

Implications:

  • Replica does not need constant connection
  • Source doesn't wait for replica acknowledgment
  • Slight lag possible between source and replica
  • Trade-off: performance vs. synchronization

3. Single Source Replication Only

Only replication from a single source is supported. Multi-source replication (one replica connecting to multiple sources) is not supported.

4. MySQL Schema Changes Not Replicated

Changes to the mysql schema are not replicated and cause replication to stop.

What This Means:

  • User account changes on source don't replicate
  • Privilege modifications don't replicate
  • System table changes don't replicate
  • Must manage users separately on replica

Best Practice: Create users and manage privileges on both source and replica independently.

5. GTID Tags on Source Not Supported (Pre-May 2024 Systems)

DB systems (MySQL 8.3.0 or higher) created prior to May 2024 that do not have the TRANSACTION_GTID_TAG privilege need to be upgraded in order to replicate transactions with GTID tags.

Current Systems: MySQL 8.3.0+ created after May 2024 support GTID tags.

6. Applier Privilege Restrictions

Only statements which the applier username has privilege to execute can be replicated. Replication fails if the applier username has insufficient privilege to execute any statement read from the binary logs of the source server.

Solution: Grant appropriate privileges to applier user on replica.

7. HeatWave Cluster Considerations

When the ALTER TABLE SECONDARY_LOAD and ALTER TABLE SECONDARY_UNLOAD statements are replicated to a target DB system, these statements do not load or unload data in the HeatWave cluster attached to the target DB system, if any.

Note: In versions before 8.4.0-u2, replication channels can break under certain HeatWave Lakehouse configurations.

8. High Availability DB System Behavior

When a high availability DB system is upgraded, the inbound replication channel is suspended. The channel is resumed when the upgrade process is complete.

For HA systems: Transactions on the source DB System are replicated first to the primary instance, and then asynchronously to the secondary instances.

Replication Prerequisites

Before setting up inbound replication, ensure the following prerequisites are met:

Source Server Requirements

1. GTID Mode Enabled (Recommended)

The source must use GTIDs, with the system variables gtid_mode=ON and enforce_gtid_consistency=ON.

Benefits of GTID:

  • Auto-positioning simplifies configuration
  • Easier to maintain replication
  • Better tracking of transactions
  • Simplified failover procedures

Enable GTID on Source:

SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = OFF_PERMISSIVE;
SET GLOBAL gtid_mode = ON_PERMISSIVE;
SET GLOBAL gtid_mode = ON;
Enter fullscreen mode Exit fullscreen mode

2. Binary Logging Enabled

The source must have binary logging enabled:

SET GLOBAL log_bin = ON;
Enter fullscreen mode Exit fullscreen mode

Verify binary logging:

SHOW VARIABLES LIKE 'log_bin';
Enter fullscreen mode Exit fullscreen mode

3. Row-Based Binary Logging

The source must use row-based binary logging:

SET GLOBAL binlog_format = 'ROW';
Enter fullscreen mode Exit fullscreen mode

Verify format:

SHOW VARIABLES LIKE 'binlog_format';
Enter fullscreen mode Exit fullscreen mode

4. Extended Binary Log Retention

The source's binary log has an extended retention period, so that the target database can apply all transactions before the log is flushed.

For AWS RDS sources:

CALL mysql.rds_set_configuration('binlog retention hours', 24);
Enter fullscreen mode Exit fullscreen mode

5. Replication User with Required Privileges

Create a dedicated replication user on the source server.

Create replication user:

CREATE USER 'repl_user'@'%' 
  IDENTIFIED BY 'StrongPassword123!' 
  REQUIRE SSL;

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Why REQUIRE SSL:

  • Encrypts replication traffic
  • Protects credentials in transit
  • Security best practice
  • Often required for compliance

6. Network Connectivity

Allow the replica to connect to the source:

  • Firewall rules permit MySQL port (3306)
  • Source is reachable from replica network
  • SSL/TLS configured if required

Replica DB System Requirements

1. Active DB System

An active MySQL HeatWave DB system must be available as the replication target.

2. Network Path to Source

Replica must be able to reach source through:

  • Site-to-site VPN
  • FastConnect (for on-premises)
  • Direct network connectivity (for cloud-to-cloud)
  • PrivateLink (for AWS)

3. Matching Configuration

The source and DB system must run with the same lower_case_table_names system variable value.

Connectivity Scenarios

Connecting On-Premises MySQL to HeatWave

For on-premises MySQL servers replicating to HeatWave, establish network connectivity using:

Option 1: FastConnect

Description: Dedicated private connection between on-premises and OCI.

Characteristics:

  • Up to 10 Gbps bandwidth
  • Predictable network performance
  • Lower latency than VPN
  • Higher cost but better for production

Use Case: Production replication with high data volume

Option 2: Site-to-Site VPN

Description: IPSec VPN tunnel over the internet.

Characteristics:

  • Encrypted connectivity
  • Cost-effective solution
  • Up to 250 Mbps per tunnel
  • Suitable for moderate bandwidth needs

Use Case: Dev/test environments, moderate data volumes

Connecting Cloud MySQL to HeatWave

For MySQL instances on other clouds:

AWS: Use PrivateLink or VPN
Azure: Use OCI-Azure interconnect or VPN
GCP: Use Partner Interconnect or VPN

Benefits of Creating a Dedicated Replication User

Creating a dedicated user for replication provides several security and operational benefits:

1. Grant Minimum Privileges for Increased Security

The replication user only needs REPLICATION SLAVE privilege, following the principle of least privilege.

Example:

CREATE USER 'repl_user'@'replica_subnet' 
  IDENTIFIED BY 'SecurePassword!';

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'replica_subnet';
Enter fullscreen mode Exit fullscreen mode

2. Restrict Client Host to IP Address or Subnet

Limit connections to specific IP or subnet of the replica database.

Example with specific IP:

CREATE USER 'repl_user'@'10.0.1.100' 
  IDENTIFIED BY 'SecurePassword!';
Enter fullscreen mode Exit fullscreen mode

Example with subnet:

CREATE USER 'repl_user'@'10.0.1.%' 
  IDENTIFIED BY 'SecurePassword!';
Enter fullscreen mode Exit fullscreen mode

3. Enforce an Encrypted Connection

Use REQUIRE SSL to ensure all replication traffic is encrypted.

Example:

CREATE USER 'repl_user'@'replica_subnet' 
  IDENTIFIED BY 'SecurePassword!' 
  REQUIRE SSL;
Enter fullscreen mode Exit fullscreen mode

Verify SSL requirement:

SELECT user, host, ssl_type FROM mysql.user WHERE user = 'repl_user';
Enter fullscreen mode Exit fullscreen mode

Loading Initial Data

Before starting replication, you typically need to load existing data from the source to the replica. This process involves two steps:

Step 1: Export Data from Source

Export the data from the MySQL server source database using MySQL Shell Instance Dump utility.

Using MySQL Shell Instance Dump:

mysqlsh user@source_host:3306 -- util dump-instance /path/to/dump \
  --excludeSchemas=mysql,sys,performance_schema,information_schema \
  --ocimds=true
Enter fullscreen mode Exit fullscreen mode

What This Does:

  • Exports all schemas including user accounts and privileges
  • Excludes system schemas (mysql, sys, performance_schema, information_schema)
  • Creates a consistent snapshot
  • Captures GTID position

Why Exclude System Schemas:

  • System schemas managed separately
  • Prevents conflicts with managed service
  • User accounts handled differently in HeatWave
  • Best practice for cloud migrations

Step 2: Import Data into DB System

Import data into the DB system replica database during DB system creation or to existing DB system.

Import During DB System Creation:

  1. When creating DB system, specify import from Object Storage
  2. Provide Object Storage bucket details
  3. DB system loads data during provisioning

Import to Existing DB System:

mysqlsh admin@replica_host:3306 -- util load-dump /path/to/dump \
  --progressFile=/path/to/progress.json
Enter fullscreen mode Exit fullscreen mode

Monitor Import Progress:

SELECT * FROM performance_schema.clone_status;
Enter fullscreen mode Exit fullscreen mode

Creating an Inbound Replication Channel

Once prerequisites are met and initial data loaded, create the replication channel.

Using OCI Console

  1. Navigate to DB System details page
  2. Under Resources, click Channels
  3. Click Create channel
  4. Configure channel settings:
    • Display name: Descriptive name for channel
    • Description: Purpose and details
    • Source connection: Source hostname, port, username, password
    • SSL mode: Required (recommended)
    • Replication positioning: Use GTID auto-positioning (recommended)
  5. Click Create channel

Using OCI CLI

Generate template:

oci mysql channel create-from-mysql --generate-full-command-json-input > channel-config.json
Enter fullscreen mode Exit fullscreen mode

Edit configuration file:

{
  "compartmentId": "ocid1.compartment.oc1...",
  "displayName": "production-replication-channel",
  "description": "Replication from on-prem to OCI",
  "isEnabled": true,
  "sourceHostname": "source.example.com",
  "sourcePort": 3306,
  "sourceUsername": "repl_user",
  "sourcePassword": "SecurePassword!",
  "sourceSslMode": "REQUIRED",
  "targetDbSystemId": "ocid1.mysqldbsystem.oc1...",
  "waitForState": ["SUCCEEDED"]
}
Enter fullscreen mode Exit fullscreen mode

Create channel:

oci mysql channel create-from-mysql --from-json file://channel-config.json
Enter fullscreen mode Exit fullscreen mode

Replication Positioning Options

Option 1: GTID Auto-Positioning (Recommended)

Requires GTID enabled on source with gtid_mode=ON.

Advantages:

  • Automatic transaction tracking
  • No manual binary log position needed
  • Easier maintenance
  • Simplified failover

Configuration: Select "Use GTID auto-positioning" when creating channel

Option 2: Binary Log Positioning

Use when GTID is not enabled on source.

Requirements:

  • Binary log file name
  • Binary log offset position

Obtain binary log position on source:

SHOW MASTER STATUS;
Enter fullscreen mode Exit fullscreen mode

Capture output:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      154 |              |                  |
+------------------+----------+--------------+------------------+
Enter fullscreen mode Exit fullscreen mode

Use File and Position values when creating channel.

Managing Replication Channels

Starting a Channel

Console: Navigate to channel details → Click Start

CLI:

oci mysql channel update \
  --channel-id <CHANNEL_OCID> \
  --is-enabled true
Enter fullscreen mode Exit fullscreen mode

Stopping a Channel

Console: Navigate to channel details → Click Stop

CLI:

oci mysql channel update \
  --channel-id <CHANNEL_OCID> \
  --is-enabled false
Enter fullscreen mode Exit fullscreen mode

Resetting a Channel

Reset clears replication position and requires reconfiguration.

Console: Navigate to channel details → Actions → Reset

CLI:

oci mysql channel reset \
  --channel-id <CHANNEL_OCID> \
  --from-json '{"resetType": "POSITION"}'
Enter fullscreen mode Exit fullscreen mode

Deleting a Channel

Console: Navigate to channel details → Delete

CLI:

oci mysql channel delete \
  --channel-id <CHANNEL_OCID> \
  --force
Enter fullscreen mode Exit fullscreen mode

Monitoring Replication

Checking Replication Status

Using MySQL Shell on replica:

SHOW REPLICA STATUS\G
Enter fullscreen mode Exit fullscreen mode

Key metrics to monitor:

  • Replica_IO_Running: Should be "Yes"
  • Replica_SQL_Running: Should be "Yes"
  • Seconds_Behind_Source: Replication lag in seconds
  • Last_Error: Any error messages

Using Database Management

OCI Database Management provides comprehensive replication monitoring:

  • Active connections
  • Statement count
  • Fetch state
  • Apply state
  • Time behind source
  • GTID executed

Access: Navigate to DB System → Database Management → Replication tab

Common Replication States

ACTIVE: Channel is running normally
NEEDS_ATTENTION: Channel requires intervention (check error logs)
INACTIVE: Channel is stopped
CREATING: Channel is being created
DELETING: Channel is being deleted

Best Practices for Inbound Replication

1. Always Enable GTID

Use GTID mode for simplified management and better tracking:

SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;
Enter fullscreen mode Exit fullscreen mode

2. Use SSL/TLS Encryption

Always encrypt replication traffic:

CREATE USER 'repl_user'@'%' 
  IDENTIFIED BY 'SecurePassword!' 
  REQUIRE SSL;
Enter fullscreen mode Exit fullscreen mode

3. Monitor Replication Lag

Set up alerts for excessive replication lag (> 60 seconds):

  • Use OCI Monitoring service
  • Configure alarms for Seconds_Behind_Source
  • Investigate and resolve lag issues promptly

4. Test Failover Procedures

Regularly test failover scenarios:

  • Stop replication intentionally
  • Verify application behavior
  • Practice recovery procedures
  • Document lessons learned

5. Plan for Network Interruptions

Replication is asynchronous and can tolerate brief disconnections:

  • Use appropriate binary log retention
  • Monitor relay log space
  • Configure automatic reconnection

6. Size Binary Log Retention Appropriately

Ensure source retains binary logs long enough:

  • Account for network outages
  • Consider maintenance windows
  • Typical: 24-48 hours retention

For RDS sources:

CALL mysql.rds_set_configuration('binlog retention hours', 48);
Enter fullscreen mode Exit fullscreen mode

7. Regular Health Checks

Implement automated health checks:

  • Verify both IO and SQL threads running
  • Monitor replication lag trends
  • Check for errors in logs
  • Validate data consistency periodically

8. Gradual Migration Strategy

For migrations, use phased approach:

  1. Set up replication
  2. Validate data consistency
  3. Test application with replica
  4. Gradually shift traffic
  5. Complete cutover during maintenance window

Troubleshooting Common Issues

Issue 1: Channel in NEEDS_ATTENTION State

Symptoms: Channel shows "NEEDS_ATTENTION" after creation

Common Causes:

  • Connectivity issues
  • Authentication failures
  • SSL/TLS configuration problems
  • Firewall blocking connections

Solutions:

  1. Check error logs in channel details
  2. Verify network connectivity
  3. Test replication user credentials
  4. Confirm SSL certificates (if using)
  5. Check firewall rules

Issue 2: Replication Lag Increasing

Symptoms: Seconds_Behind_Source continuously growing

Common Causes:

  • High transaction volume on source
  • Insufficient replica resources
  • Network bandwidth limitations
  • Large transactions

Solutions:

  1. Increase replica shape (more CPU/memory)
  2. Check network bandwidth
  3. Optimize slow queries on source
  4. Break large transactions into smaller batches
  5. Consider read replicas for scaling

Issue 3: Replication Stops with MySQL Schema Errors

Symptoms: Replication stops, error mentions mysql schema

Cause: Changes to mysql schema are not replicated

Solutions:

  1. Manage user accounts independently on replica
  2. Skip problematic transaction: 
   SET GLOBAL sql_replica_skip_counter = 1;
   START REPLICA;
Enter fullscreen mode Exit fullscreen mode
  1. Better: Avoid mysql schema changes during replication

Issue 4: SSL Connection Failures

Symptoms: Channel fails to start, SSL errors in logs

Common Causes:

  • Certificate mismatch
  • SSL not configured on source
  • Wrong SSL mode selected

Solutions:

  1. Verify source has SSL enabled
  2. Use "REQUIRED" mode if source supports SSL
  3. Check certificate validity
  4. Update source SSL configuration if needed

Inbound replication in MySQL HeatWave provides a powerful mechanism for migrating data, implementing hybrid cloud architectures, and enabling disaster recovery scenarios. By understanding the supported scenarios, limitations, and best practices, you can effectively leverage inbound replication for your specific use cases.

Key takeaways:

  • Replication is asynchronous: Replica doesn't need constant connection
  • Use GTID mode: Simplifies configuration and management
  • Encrypt replication traffic: Always use SSL/TLS
  • Monitor continuously: Track lag, errors, and channel health
  • Plan for limitations: Row-based only, single source, mysql schema restrictions
  • Test thoroughly: Validate data consistency and failover procedures

Whether migrating from on-premises to cloud, implementing cross-region replication, or building hybrid architectures, MySQL HeatWave inbound replication provides the flexibility and reliability needed for production workloads.

Additional Resources:

Top comments (0)