DEV Community

Cover image for MySQL HeatWave: Replicating to MySQL Server (Outbound Replication)
Ryan Giggs
Ryan Giggs

Posted on

MySQL HeatWave: Replicating to MySQL Server (Outbound Replication)

#mysqlheatwave #hybridcloud #databasereplication

Outbound replication enables you to copy data from a MySQL HeatWave DB system to external MySQL instances, supporting data distribution, disaster recovery, and hybrid cloud architectures. Unlike inbound replication where HeatWave acts as the replica, outbound replication positions HeatWave as the source, allowing you to replicate data to on-premises servers, cloud compute instances, or other DB systems.

This guide covers outbound replication scenarios, configuration, prerequisites, and best practices.

What is Outbound Replication?

Outbound replication uses a replication channel to copy transactions from a DB system to another location asynchronously. A channel connects the source DB system (HeatWave) to a replica database (external MySQL instance or another DB system), copying data changes from source to replica.

Key Characteristics:

  • Source: MySQL HeatWave DB system
  • Replica: External MySQL instance or another DB system
  • Asynchronous: Replica doesn't need constant connection
  • Channel Configuration: Always configured on the replica
  • Scale-Out: Enables read scaling by distributing load

Important: You can configure only a DB system as the source; you cannot configure a read replica as the source.

Supported Outbound Replication Scenarios

MySQL HeatWave supports three primary outbound replication scenarios. In all scenarios, the replica must be the same version as, or a higher version of MySQL than, the DB system source.

1. DB System to On-Premises MySQL Instance

Replicate from a DB system to an on-premises MySQL server.

Use Cases:

  • Data repatriation from cloud
  • Hybrid cloud architectures
  • Local reporting and analytics
  • Disaster recovery to on-premises
  • Compliance requirements for local data

2. DB System to MySQL on Cloud Compute

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

Use Cases:

  • Custom MySQL configurations not available in managed service
  • Testing new MySQL versions
  • Migration staging environments
  • Cross-cloud data distribution

3. DB System to DB System

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

Use Cases:

  • Cross-region replication
  • Data distribution across geographies
  • Reporting replicas
  • Development/test environment refresh

Note: Prior to MySQL 8.3.0-u2, you cannot perform outbound replication from a DB system with MySQL HeatWave Lakehouse enabled. As of MySQL 8.3.0-u2+, outbound replication is supported with Lakehouse, but Lakehouse tables can only replicate to replica DB systems, not external replicas.

Outbound vs. Inbound Replication

Aspect Outbound Replication Inbound Replication
Source HeatWave DB System External MySQL or DB System
Replica External MySQL or DB System HeatWave DB System
Channel Config On replica In HeatWave Service
Use Case Data out of cloud Data into cloud
Management Manual on replica Managed in OCI Console

Prerequisites for Outbound Replication

Source DB System Requirements

1. Active DB System

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

2. Binary Logging Enabled

Binary logging is enabled by default on all DB systems. Verify:

-- On source DB system
SHOW VARIABLES LIKE 'log_bin';
Enter fullscreen mode Exit fullscreen mode

3. Row-Based Replication

Row-based binary logging must be enabled (default):

SHOW VARIABLES LIKE 'binlog_format';
-- Should return: ROW
Enter fullscreen mode Exit fullscreen mode

4. GTID Mode Enabled (Recommended)

GTID mode is disabled by default on DB systems but strongly recommended for outbound replication.

Why GTID Matters:

  • Simplifies replication setup with auto-positioning
  • Easier failover and recovery
  • Better tracking of transactions
  • Reduces configuration complexity

Note: GTID mode is a compatibility option you must consider when creating your DB system.

5. Replication User Account

Create a dedicated replication user on the source DB system with required privileges.

Create replication user:

-- Connect to source DB system
CREATE USER 'repl_user'@'%' 
  IDENTIFIED BY 'SecurePassword123!' 
  REQUIRE SSL;

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

Replica MySQL Server Requirements

1. MySQL Version Compatibility

The replica must be the same version as, or a higher version than, the DB system source. For example, you can replicate from MySQL 8.0 to MySQL 8.4, but not from MySQL 8.4 to MySQL 8.0.

2. Compatible Configuration Options

The replica must have matching configuration for certain system variables:

lowercase_table_names: The source and replica must run with the same lower_case_table_names system variable value (0, 1, or 2).

Enable Partial Revoke (if used on source): If your source DB system uses partial revokes, the replica must also have this enabled:

-- On replica
SET GLOBAL partial_revokes = ON;
Enter fullscreen mode Exit fullscreen mode

GTID Mode (recommended): If source uses GTID, replica should have GTID enabled:

-- On replica
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

Timezone Information Loaded: If your source DB system uses named timezones, load timezone information on the replica:

# On replica server
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
Enter fullscreen mode Exit fullscreen mode

3. Bi-Directional Communication Enabled

The replica must be able to connect to the source DB system:

  • Network path exists (VPN, FastConnect, direct connectivity)
  • Firewall rules allow MySQL port (3306)
  • Security lists/NSGs permit traffic

4. Row-Based Binary Logging

The replica should also use row-based replication:

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

Network Connectivity Options

Connecting External MySQL to HeatWave Source

For external MySQL servers replicating FROM HeatWave, establish connectivity:

Option 1: Network Load Balancer

Set up a Network Load Balancer to provide external access to the DB system:

Steps:

  1. Create Network Security Group allowing access from replica IP
  2. Configure LB Listener (add NSG)
  3. Configure LB Backend
  4. Specify Health Check Policy
  5. Configure backend to access MySQL source

Security: Enforce SSL/TLS for all connections.

Option 2: Site-to-Site VPN

For on-premises replicas:

  • IPSec VPN tunnel over internet
  • Encrypted connectivity
  • Up to 250 Mbps per tunnel

Option 3: FastConnect

For production on-premises replicas:

  • Dedicated private connection
  • Up to 10 Gbps bandwidth
  • Predictable performance
  • Lower latency

Loading Initial Data from DB System to MySQL Server

Before starting replication, export existing data from the source to the replica.

Step 1: Export Data from Source DB System

Use MySQL Shell Instance Dump utility to export data:

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

What This Exports:

  • All user schemas and data
  • User accounts and privileges
  • GTID executed position
  • Excludes system schemas

Why Exclude System Schemas:

  • System schemas managed differently
  • Prevents replication conflicts
  • User accounts can be recreated separately
  • Best practice for replication setup

Important: If using GTID-based replication, capture the value of the source server's system variable gtid_executed from the dump metadata.

Step 2: Import Data into Replica MySQL Server

Import the dump into your replica MySQL instance:

mysqlsh admin@<REPLICA_IP>:3306 -- util load-dump /path/to/dump \
  --includeSchemas=<schema_list> \
  --progressFile=/path/to/progress.json
Enter fullscreen mode Exit fullscreen mode

Import User Accounts:

mysqlsh admin@<REPLICA_IP>:3306 -- util load-dump /path/to/dump \
  --includeUsers=true \
  --excludeUsers=admin,root
Enter fullscreen mode Exit fullscreen mode

Step 3: Update gtid_purged Settings

If using GTID-based replication, set gtid_purged on the replica:

-- On replica
RESET MASTER;
SET GLOBAL gtid_purged='<gtid_executed_value>';
Enter fullscreen mode Exit fullscreen mode

Example:

SET GLOBAL gtid_purged='3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5';
Enter fullscreen mode Exit fullscreen mode

Configuring Outbound Replication Channel

The replication channel is always configured on the replica (not in HeatWave Service for outbound replication).

Configuration Parameters

Source Connection Settings

source_host: IP address or hostname of the source DB system

SOURCE_HOST='10.0.1.100'
Enter fullscreen mode Exit fullscreen mode

source_port: MySQL port (default 3306)

SOURCE_PORT=3306
Enter fullscreen mode Exit fullscreen mode

source_user: Replication user account

SOURCE_USER='repl_user'
Enter fullscreen mode Exit fullscreen mode

source_password: Replication user password

SOURCE_PASSWORD='SecurePassword123!'
Enter fullscreen mode Exit fullscreen mode

source_ssl: Enable SSL/TLS (recommended)

SOURCE_SSL=1
Enter fullscreen mode Exit fullscreen mode

Replication Positioning

GTID Auto-Positioning (recommended):

SOURCE_AUTO_POSITION=1
Enter fullscreen mode Exit fullscreen mode

Manual Binary Log Positioning (if GTID not used):

SOURCE_LOG_FILE='mysql-bin.000003'
SOURCE_LOG_POS=154
Enter fullscreen mode Exit fullscreen mode

Setting Up the Replication Channel

Connect to the replica MySQL server and run:

-- Using GTID auto-positioning (recommended)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='<DB_SYSTEM_IP>',
  SOURCE_PORT=3306,
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='SecurePassword123!',
  SOURCE_SSL=1,
  SOURCE_AUTO_POSITION=1;
Enter fullscreen mode Exit fullscreen mode

Without GTID (manual positioning):

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='<DB_SYSTEM_IP>',
  SOURCE_PORT=3306,
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='SecurePassword123!',
  SOURCE_SSL=1,
  SOURCE_LOG_FILE='mysql-bin.000003',
  SOURCE_LOG_POS=154;
Enter fullscreen mode Exit fullscreen mode

Starting Replication 

START REPLICA;
Enter fullscreen mode Exit fullscreen mode

Or for older MySQL versions:

START SLAVE;
Enter fullscreen mode Exit fullscreen mode

Managing Replication

Checking Replication Status 

SHOW REPLICA STATUS\G
Enter fullscreen mode Exit fullscreen mode

Key Metrics:

  • Replica_IO_Running: Should be "Yes"
  • Replica_SQL_Running: Should be "Yes"
  • Seconds_Behind_Source: Replication lag
  • Last_IO_Error: IO thread errors
  • Last_SQL_Error: SQL thread errors

Stopping Replication 

STOP REPLICA;
Enter fullscreen mode Exit fullscreen mode

Resetting Replication

Warning: This clears all replication position data.

STOP REPLICA;
RESET REPLICA ALL;
Enter fullscreen mode Exit fullscreen mode

Reconfiguring the Channel

To change replication settings:

STOP REPLICA;

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='<NEW_IP>',
  SOURCE_USER='<NEW_USER>',
  -- other parameters
  SOURCE_AUTO_POSITION=1;

START REPLICA;
Enter fullscreen mode Exit fullscreen mode

Monitoring Replication

Using Performance Schema

Query replication metrics:

-- Connection status
SELECT * FROM performance_schema.replication_connection_status\G

-- Applier status
SELECT * FROM performance_schema.replication_applier_status\G

-- Worker threads
SELECT * FROM performance_schema.replication_applier_status_by_worker\G
Enter fullscreen mode Exit fullscreen mode

Setting Up Alerts

Monitor these critical metrics:

  • Replication lag exceeding threshold (e.g., 60 seconds)
  • IO or SQL thread stopped
  • Replication errors
  • Connection failures

Using Database Management

If your replica is also a DB system on OCI, use Database Management to monitor:

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

Common Outbound Replication Issues

Issue 1: Replica Cannot Connect to Source

Error: "Can't connect to MySQL server on ''"

Causes:

  • Network Load Balancer not configured
  • Security lists blocking traffic
  • VPN/FastConnect not established
  • Wrong IP or hostname

Solutions:

  1. Verify Network Load Balancer configuration
  2. Check security lists allow port 3306
  3. Test connectivity: telnet <DB_SYSTEM_IP> 3306
  4. Verify VPN/FastConnect is active

Issue 2: Authentication Failures

Error: "Access denied for user 'repl_user'@''"

Causes:

  • Replication user not created on source
  • Wrong credentials
  • User not allowed from replica IP

Solutions:

  1. Verify user exists on source: SELECT User, Host FROM mysql.user WHERE User = 'repl_user';
  2. Check credentials are correct
  3. Verify user grants: SHOW GRANTS FOR 'repl_user'@'%';

Issue 3: Replication Lag Increasing

Symptoms: Seconds_Behind_Source growing

Causes:

  • High transaction volume on source
  • Replica underpowered
  • Network bandwidth limitations
  • Large transactions

Solutions:

  1. Increase replica resources
  2. Optimize slow queries
  3. Check network bandwidth
  4. Break large transactions into batches

Issue 4: SSL Connection Failures

Error: SSL-related errors in replication status

Causes:

  • SSL not configured on source
  • Certificate issues
  • Wrong SSL mode

Solutions:

  1. Verify source supports SSL
  2. Use SOURCE_SSL=1 for encrypted connection
  3. Check certificate validity if using certificate verification

Best Practices

1. Always Use GTID Mode

Enable GTID on both source and replica for simplified management:

  • Easier failover
  • Automatic position tracking
  • Simplified reconfiguration

2. Enforce 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:

  • Lag > 60 seconds
  • IO or SQL thread stopped
  • Replication errors

4. Use Network Load Balancer

For production external replicas:

  • Provides stable endpoint
  • Enables health checks
  • Supports SSL termination
  • Better security controls

5. Plan for Network Interruptions

  • Use appropriate binary log retention on source
  • Monitor relay log space on replica
  • Test reconnection scenarios

6. Regular Health Checks

Implement automated checks:

#!/bin/bash
# Check replication health
REPLICA_STATUS=$(mysql -e "SHOW REPLICA STATUS\G" | grep "Replica_.*_Running:" | awk '{print $2}')
if [[ "$REPLICA_STATUS" != "Yes" ]]; then
  echo "Replication issue detected"
  # Send alert
fi
Enter fullscreen mode Exit fullscreen mode

7. Test Failover Procedures

Regularly test:

  • Stopping and starting replication
  • Switching to different source
  • Replica promotion scenarios
  • Application behavior during failures

8. Document Configuration

Maintain documentation of:

  • Source and replica connection details
  • Replication user credentials
  • Network paths used
  • Failover procedures

Limitations for Outbound Replication

Lakehouse Table Restrictions

Lakehouse tables can only replicate to replica DB systems. Replicating Lakehouse tables to external replicas (on-premises MySQL) causes replication to break with errors.

Workaround: Exclude Lakehouse tables from replication or only replicate to DB system replicas.

Prior to MySQL 8.3.0-u2

  • Cannot perform outbound replication from a DB system with HeatWave Lakehouse enabled
  • Cannot enable Lakehouse on HA DB system with HeatWave cluster

Read Replicas as Source

You cannot configure a read replica as the source for outbound replication. Only the primary DB system can serve as the replication source.

Outbound replication from MySQL HeatWave enables flexible data distribution strategies, supporting hybrid cloud architectures, disaster recovery, and read scaling scenarios. By understanding configuration requirements, network connectivity options, and best practices, you can implement reliable outbound replication for your specific needs.

Key takeaways:

  • Source is HeatWave: DB system acts as replication source
  • Channel on replica: Always configure replication on the replica side
  • Use GTID: Simplifies setup and management
  • Encrypt traffic: Always use SSL/TLS
  • Network connectivity: Use NLB, VPN, or FastConnect for external replicas
  • Monitor continuously: Track lag, errors, and thread status
  • Version compatibility: Replica must be same or higher MySQL version

Whether replicating to on-premises servers, cloud compute instances, or other DB systems, MySQL HeatWave outbound replication provides the flexibility needed for modern distributed architectures.

Top comments (0)