AWS Mysql replication Method

This explanation will cover how I managed to replicate RDS Mysql database between two account and reduced downtime.

The background

A company has a brand new AWS account with better offers, thus they required all of resources in the old account to be moved to the new account including RDS database. One of which multiple mysql databases.

The Plan

The migration strategy involves setting up replication between the old and new accounts, then performing a coordinated switchover to minimize downtime.

Migration Steps Breakdown

Phase 1: Setup Replication

1. Enable Binary Logging on the source RDS instance
   • Modify parameter group to enable binlog_format = ROW
   • Set appropriate binlog_retention_hours
2. Create RDS Snapshot of the source database
   • This provides the baseline for the replica
3. Share Snapshot with the target AWS account
   • Use AWS Console or CLI to share the snapshot
4. Copy Snapshot to the new account
   • Accept the shared snapshot and copy it to your region
5. Restore Snapshot to create the initial replica instance

Phase 2: Establish Replication

6. Setup Network Connection
   • Configure VPC Peering or AWS Transit Gateway
   • Update security groups to allow MySQL traffic (port 3306)
7. Configure MySQL Replication
   • Create replication user on source
   • Configure replica to connect to source using binlog position
8. Monitor Replication
   • Verify data is syncing
   • Monitor replication lag

Phase 3: Switchover (Minimal Downtime)

9. Monitor Replication Lag
   • Wait until lag is minimal (< 1 second)
10. Stop Application Writes
    • Put application in maintenance mode
    • Ensure no new writes to old database
11. Update Application Configuration
    • Change RDS endpoint to new account's RDS
    • Update connection strings
12. Start Application
    • Bring application back online
    • Verify connectivity to new database

Phase 4: Validation

13. Monitor Application Health
    • Check application logs
    • Verify all services are functioning
14. Data Validation
    • Run checksums or row counts
    • Verify critical data integrity
15. Backup Period
    • Keep old RDS running for 7 days as backup
16. Decommission
    • Delete old RDS instance
    • Clean up snapshots and resources

Key Considerations

• Downtime Window: Typically 5-15 minutes during switchover
• Network Bandwidth: Ensure sufficient bandwidth for initial sync
• Security: Use SSL/TLS for replication traffic
• Cost: Monitor data transfer costs between accounts
• Testing: Always test the process in a non-production environment first

Migration Steps Explained

1. Setup Replication: Configure binary log replication from source RDS to target RDS
2. Initial Data Sync: Perform initial data dump and restore to target
3. Monitor Replication Lag: Ensure replication is catching up and lag is minimal
4. Prepare Cutover: Plan maintenance window and notify stakeholders
5. Stop Application: Put application in maintenance mode
6. Verify Data Sync: Confirm replication lag is zero and data is consistent
7. Switch Endpoint: Update application configuration to point to new RDS endpoint
8. Start Application: Bring application back online with new endpoint
9. Verify & Monitor: Test functionality and monitor for any issues