Amazon RDS & Aurora MySQL Migrations | Deep Dive.

Amazon RDS & Aurora MySQL Migrations - Deep Dive.

Scope:

• Intro,
• 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
• Amazon Aurora MySQL-Compatible Edition has its own unique behaviors especially:
•  Around binary logs (binlogs), 
• Replication formats, 
• Triggers, 
• Stored procedures, 
• 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