MySQL HeatWave: Replicating to DB System (Inbound Replication)

Question

Inbound replication enables data replication from external MySQL sources into HeatWave DB systems, supporting migrations, disaster recovery, and hybrid cloud architectures.

What is Inbound Replication?

Uses a replication channel to copy transactions from a MySQL source to a HeatWave DB system replica.

Key Characteristics:

• Asynchronous: Replicas don't need permanent connection; can reconnect to receive updates
• Single Source: One source per replica (multi-source not supported)
• Source Independence: Source doesn't control replica; replica connects using replication credentials
• Not Managed: You configure and maintain the channel and network connectivity

Supported Scenarios

1. On-Premises to DB System: Cloud migrations, hybrid architectures, disaster recovery
2. Cloud Compute to DB System: Cross-cloud replication, migrating self-managed to HeatWave
3. DB System to DB System: Cross-region replication, geographic redundancy

Replication Architecture

Source Database:

• MySQL 5.7+ (on-premises, OCI compute, HeatWave DB System, or other clouds)
• Binary logging enabled
• Row-based replication format
• GTID mode enabled (recommended)

Replica Database:

• MySQL HeatWave DB System in private subnet
• Applies transactions from source asynchronously

Flow: Source commits to binary log → Channel retrieves transactions → Replica receives and applies

Limitations

1. Row-Based Only: Statement-based and mixed replication not supported
2. Asynchronous Only: Semi-synchronous not supported
3. Single Source: Multi-source replication not supported
4. MySQL Schema Not Replicated: User/privilege changes don't replicate; manage separately
5. GTID Tags: Pre-May 2024 systems need upgrade for GTID tag support
6. Applier Privileges: Replication fails if applier lacks privileges for statements
7. HeatWave Cluster: ALTER TABLE SECONDARY_LOAD/UNLOAD don't affect target cluster
8. High Availability: Channel suspends during HA upgrades, resumes after completion

Prerequisites

Source Requirements

1. Enable GTID (Recommended):

SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = OFF_PERMISSIVE;
SET GLOBAL gtid_mode = ON_PERMISSIVE;
SET GLOBAL gtid_mode = ON;

2. Enable Binary Logging:

SET GLOBAL log_bin = ON;
SET GLOBAL binlog_format = 'ROW';

3. Extend Binary Log Retention:

-- For AWS RDS:
CALL mysql.rds_set_configuration('binlog retention hours', 24);

4. Create Replication User:

CREATE USER 'repl_user'@'%' 
  IDENTIFIED BY 'StrongPassword123!' 
  REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

5. Network Connectivity: Allow replica access to source MySQL port (3306) with SSL/TLS

Replica Requirements

1. Active HeatWave DB system
2. Network path to source (VPN, FastConnect, PrivateLink)
3. Matching lower_case_table_names value with source

Connectivity Options

On-Premises to HeatWave:

• FastConnect: Up to 10 Gbps, low latency, production workloads
• Site-to-Site VPN: 250 Mbps per tunnel, cost-effective, dev/test

Cloud to HeatWave:

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

Dedicated Replication User Benefits

1. Minimum Privileges: Grant only REPLICATION SLAVE
2. Restrict Host: Limit to replica IP/subnet
3. Enforce Encryption: Use REQUIRE SSL

Example:

CREATE USER 'repl_user'@'10.0.1.%' 
  IDENTIFIED BY 'SecurePassword!' 
  REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.1.%';

Loading Initial Data

Export from Source:

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

Import to Replica:

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

Creating Replication Channel

Using OCI Console

1. Navigate to DB System → Resources → Channels
2. Click Create channel
3. Configure settings (source hostname, port, credentials, SSL mode)
4. Click Create

Using OCI CLI

oci mysql channel create-from-mysql --generate-full-command-json-input > channel-config.json
# Edit channel-config.json with your settings
oci mysql channel create-from-mysql --from-json file://channel-config.json

Positioning Options

GTID Auto-Positioning (Recommended):

• Automatic transaction tracking
• No manual position needed
• Requires gtid_mode=ON on source

Binary Log Positioning:

-- Get position on source:
SHOW MASTER STATUS;

Use File and Position values when creating channel

Managing Channels

Start: oci mysql channel update --channel-id <ID> --is-enabled true

Stop: oci mysql channel update --channel-id <ID> --is-enabled false

Reset: oci mysql channel reset --channel-id <ID> --from-json '{"resetType": "POSITION"}'

Delete: oci mysql channel delete --channel-id <ID> --force

Monitoring Replication

Check Status:

SHOW REPLICA STATUS\G

Key Metrics:

• Replica_IO_Running: Should be "Yes"
• Replica_SQL_Running: Should be "Yes"
• Seconds_Behind_Source: Replication lag
• Last_Error: Error messages

Channel States:

• ACTIVE: Running normally
• NEEDS_ATTENTION: Requires intervention
• INACTIVE: Stopped

Best Practices

1. Enable GTID: Simplifies management
2. Use SSL/TLS: Encrypt replication traffic
3. Monitor Lag: Alert on >60 seconds lag
4. Test Failover: Regular failover testing
5. Binary Log Retention: 24-48 hours recommended
6. Health Checks: Automate monitoring
7. Gradual Migration: Phased approach for migrations

Troubleshooting

NEEDS_ATTENTION State:

• Check error logs in channel details
• Verify connectivity and credentials
• Confirm SSL configuration and firewall rules

Increasing Lag:

• Increase replica resources
• Check network bandwidth
• Optimize source queries
• Break large transactions

MySQL Schema Errors:

• Manage users independently on replica
• Avoid mysql schema changes during replication

SSL Failures:

• Verify source SSL enabled
• Use "REQUIRED" mode
• Check certificate validity

Key Takeaways

• Asynchronous replication tolerates disconnections
• Use GTID mode for simplified management
• Always encrypt replication traffic
• Monitor lag, errors, and channel health continuously
• Understand limitations: row-based only, single source, mysql schema restrictions
• Test thoroughly before production cutover

Inbound replication provides flexible, reliable data migration and hybrid cloud capabilities for MySQL HeatWave workloads.

Additional Resources

• MySQL HeatWave Inbound Replication Documentation
• Source Configuration Guide
• Creating a Replication Channel
• MySQL HeatWave Service Documentation
• OCI Database Management