RDS & Aurora MySQL Migrations | Deep Dive.

Here is twtech deep-dive guide for RDS & Aurora MySQL migrations.

Scope:

•        migration patterns,
•        DMS internals,
•        MySQL-specific replication considerations,
•        Cutover approaches,
•        Best-practice architectures.

Breakdown:

•        Understanding RDS MySQL vs Aurora MySQL,
•        Migration Approaches,
•        How AWS DMS Works with MySQL,
•        Preparing Source MySQL for Migration,
•        Schema Migration Process,
•        DMS Task Best Practices (MySQL),
•        Aurora MySQL-Specific Considerations,
•        Cutover Strategies,
•        Validation & QA,
•        Architecture Diagrams.

Intro:

•        Migrating workloads to Amazon RDS for MySQL or Amazon Aurora MySQL-Compatible Edition has its own unique behaviors, especially around binary logs (binlogs), replication formats, triggers, stored procedures, and schema compatibility.

1. Understanding RDS MySQL vs Aurora MySQL

Amazon RDS for MySQL

•         Managed MySQL on EC2-based storage.
•         Storage is EBS-backed.
•         Instance performance is bounded by EBS throughput.
•         Supports native MySQL functionality + many common plugins.

Amazon Aurora MySQL

•         MySQL-compatible cluster engine using highly distributed storage.
•         5x throughput of MySQL due to optimized log-structured storage.
•         Auto-scaling storage (up to 128 TiB).
•         Sub-10 second failovers.
•         Some MySQL features unsupported (e.g., SUPER user, certain plugins).

Migration Tip:
Aurora requires binlog_row_image=FULL for DMS CDC. Validate before migration.

2. Migration Approaches

A. Homogeneous Migration

Source: MySQL → Target: RDS MySQL or Aurora MySQL
Use when:

•         Same engine family
•         Few schema changes
•         Moderate downtime permitted

Tools:

•         mysqldump / mysqlpump
•         Percona XtraBackup
•         AWS DMS for minimal downtime

B. Zero/Minimal-Downtime Migration

Use:

•         AWS DMS (Full Load + CDC)
•         Native MySQL replication
•         Percona hot-copy options
•         Aurora fast cloning (for test iterations)

This is the most common migration path.

3. How AWS DMS Works with MySQL

A. Full Load

• Bulk data copy using multiple threads.

B. Change Data Capture (CDC)

Reads MySQL binlogs from:

•         On-prem MySQL
•         EC2 MySQL
•         RDS MySQL binlog
•         Aurora MySQL binlog

DMS requires:

binlog_format = ROW

binlog_row_image = FULL

expire_logs_days = <large enough>

C. Apply Phase

DMS replays INSERT/UPDATE/DELETE on the target.

4. Preparing Source MySQL for Migration

A. Enable Binary Logging

# In my.cnf:

server_id           = 100

log_bin             = mysql-bin

binlog_format       = ROW

binlog_row_image    = FULL

binlog_checksum     = NONE

expire_logs_days    = 7

B. Create a DMS User

# pgsql

CREATE USER 'dms_user'@'%' IDENTIFIED BY 'password';

GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT, SHOW VIEW ON *.* TO 'dms_user'@'%';

5. Schema Migration Process

Unlike PostgreSQL MySQL does NOT require AWS SCT for compatibility, but it's useful for:

•         Stored procedures
•         Trigger compatibility
•         Function differences
•         Collation mismatches (utf8 vs utf8mb4)
•         Partitioning differences

Recommended

Use:

mysqldump --no-data --routines --triggers > schema.sql

Then import into RDS or Aurora.

6. DMS Task Best Practices (MySQL)

Full Load

•         Enable parallel table loading
•         Enable BatchApply for faster inserts

Change Data Capture (CDC)

•         Ensure binlog retention covers peak load time
•         Avoid high-transaction tables without primary keys
•         Turn off foreign keys on target during load:

SET FOREIGN_KEY_CHECKS = 0;

LOB Columns

Choose appropriately:

•         Limited LOB mode (better performance)
•         Full LOB mode (slower but safer)

7. Aurora MySQL-Specific Considerations

A. Cluster Endpoints

•         Cluster endpoint: read/write
•         Reader endpoint: read scaling
•         Custom endpoints: traffic distribution

B. Storage Benefits

•         Auto-expand
•         High write throughput
•         Six-way replication across three AZs

C. Failover

• Aurora has near-instant recovery due to log-structured storage.

D. Migrating from RDS MySQL → Aurora MySQL

•         Aurora MySQL supports drop-in migration
•         Use built-in snapshot migration
•         OR DMS if continuous replication needed

8. Cutover Strategies

Option A: Simple Migration

1.      Full load
2.      Scheduled downtime
3.      Stop app writes
4.     CDC catch-up
5.     Cutover
6.     Validate

Downtime: minutes

Option B: Near-Zero Downtime Migration (Most Common)

1.     Full load
2.      Continuous CDC
3.      Perform live read-only cutover test
4.      Stop writes briefly
5.      Final catch-up
6.      Flip app writers
7.      Re-enable triggers & FKs

Downtime: <30 seconds

9. Validation and QA

Recommended Tools

•         DMS validation
•         Table-level checksum (pt-table-checksum)
•         Row count comparison
•         Application-level validation tests

10.  Architecture Diagrams