Migrating from Self-Hosted MySQL to AWS Aurora Serverless: A Zero-Downtime Technical Playbook

Pre-Migration Assessment and Planning

Before embarking on a migration from self-hosted MySQL to AWS Aurora Serverless, a thorough assessment of the existing environment and a detailed plan are paramount. This isn’t merely about lifting and shifting; it’s about optimizing for a managed, elastic database service. Key considerations include understanding your current MySQL version, storage engine (primarily InnoDB for Aurora compatibility), character sets, collations, and any specific configurations or extensions that might not translate directly. Aurora Serverless v1 has specific limitations regarding maximum connections and query timeouts that must be evaluated against your application’s load profile. Aurora Serverless v2 offers greater flexibility but still requires understanding its scaling characteristics.

Identify critical tables, query patterns, and peak load times. Tools like Performance Schema in MySQL can provide insights into query execution times and resource utilization. AWS Schema Conversion Tool (SCT) is indispensable for identifying potential incompatibilities and estimating the effort required for schema and code conversion. For a zero-downtime migration, the strategy will revolve around minimizing the delta between the source and target databases during the cutover phase.

Setting Up AWS Aurora Serverless

Provisioning an Aurora Serverless cluster involves defining its capacity range (Aurora Capacity Units – ACUs), enabling necessary features like backups and multi-AZ deployment for high availability, and configuring security groups to allow access from your application servers. For Aurora Serverless v1, the minimum and maximum ACUs are crucial for cost and performance tuning. For v2, the scaling is more granular and dynamic.

Example AWS CLI command to create an Aurora Serverless v2 cluster (MySQL 8.0 compatible):

aws rds create-db-cluster \
    --db-cluster-identifier my-aurora-serverless-cluster \
    --engine aurora-mysql \
    --engine-version 8.0.mysql_aurora.3.02.0 \
    --master-username admin \
    --master-user-password YOUR_SECURE_PASSWORD \
    --serverless-v2-scaling-configuration MinCapacity=1,MaxCapacity=16 \
    --storage-type aurora-iopt1 \
    --region us-east-1 \
    --tags Key=Project,Value=MyApp Key=Environment,Value=Production

Next, create a DB instance within the cluster. For Aurora Serverless v2, you don’t explicitly create instances in the same way as provisioned clusters; the service manages the underlying compute. For v1, you would specify instance class and count.

Ensure your application’s VPC and subnets are correctly configured, and that the Aurora cluster’s security group allows inbound traffic on port 3306 from your application’s security group or IP range.

Data Replication Strategy: AWS Database Migration Service (DMS)

AWS DMS is the cornerstone of a zero-downtime migration. It supports both full load and ongoing replication (Change Data Capture – CDC). The process involves setting up a DMS Replication Instance, Source Endpoint (your self-hosted MySQL), and Target Endpoint (your Aurora Serverless cluster).

Prerequisites for Self-Hosted MySQL Source:

• Binary logging enabled: log_bin = ON
• Binlog format set to ROW: binlog_format = ROW
• Binlog row image set to FULL: binlog_row_image = FULL
• `binlog_expire_logs_seconds` set to a value sufficient to retain logs during the migration (e.g., 86400 for 1 day).
• A dedicated MySQL user with necessary privileges for replication: REPLICATION SLAVE, REPLICATION CLIENT, SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS.

Example MySQL configuration snippet (in my.cnf or my.ini):

[mysqld]
log_bin = ON
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 86400
server-id = 1 # Ensure a unique server ID

Restart your MySQL server after applying these changes.

DMS Replication Instance Setup:

aws dms create-replication-instance \
    --replication-instance-id my-dms-replication-instance \
    --replication-instance-class dms.t3.medium \
    --allocated-storage 100 \
    --engine-version 3.4.5 \
    --vpc-security-group-ids sg-xxxxxxxxxxxxxxxxx \
    --replication-subnet-group-id my-dms-subnet-group \
    --region us-east-1

DMS Endpoint Configuration:

# Source Endpoint (Self-hosted MySQL)
aws dms create-endpoint \
    --endpoint-identifier my-mysql-source-endpoint \
    --endpoint-type source \
    --engine-name mysql \
    --endpoint-uri "mysql://user:password@your_mysql_host:3306/your_database" \
    --server-name your_mysql_host \
    --port 3306 \
    --username your_replication_user \
    --password YOUR_REPLICATION_PASSWORD \
    --extra-connection-attributes "parallelLoadThreads=2,maxFileSize=1024" \
    --region us-east-1

# Target Endpoint (Aurora Serverless)
aws dms create-endpoint \
    --endpoint-identifier my-aurora-target-endpoint \
    --endpoint-type target \
    --engine-name aurora-mysql \
    --endpoint-uri "mysql://admin:YOUR_SECURE_PASSWORD@my-aurora-serverless-cluster.cluster-xxxxxxxxxxxx.us-east-1.rds.amazonaws.com:3306" \
    --server-name my-aurora-serverless-cluster.cluster-xxxxxxxxxxxx.us-east-1.rds.amazonaws.com \
    --port 3306 \
    --username admin \
    --password YOUR_SECURE_PASSWORD \
    --extra-connection-attributes "parallelLoadThreads=2,maxFileSize=1024" \
    --region us-east-1

Note: For the target endpoint, use the Aurora cluster endpoint. Ensure your DMS replication instance can reach both the source and target endpoints. This often involves configuring VPC peering, VPN, or using public IPs with appropriate security group rules.

Creating and Running the DMS Migration Task

A DMS Migration Task orchestrates the data transfer. For a zero-downtime migration, we’ll use a task that performs a full load followed by ongoing replication (CDC).

Task Settings:

• Migration Type: full-load-and-cdc
• Target Table Preparation Mode: DO_NOTHING (if schema is already migrated) or TRUNCATE_BEFORE_LOAD (if you want DMS to clear tables before loading). For zero-downtime, it’s often best to pre-create the schema on Aurora using SCT or manual scripts and use DO_NOTHING.
• Enable Logging: Crucial for troubleshooting.
• LOB Settings: Configure how Large Objects (BLOBs, TEXT, etc.) are handled. LIMITED_LOB_MODE is often sufficient and faster, but ensure the limit is adequate.
• Validation: Enable data validation to ensure consistency.

Example DMS Task creation using AWS CLI:

aws dms create-replication-task \
    --replication-task-identifier my-migration-task \
    --replication-instance-arn arn:aws:dms:us-east-1:123456789012:replication-instance:my-dms-replication-instance \
    --source-endpoint-arn arn:aws:dms:us-east-1:123456789012:endpoint:my-mysql-source-endpoint \
    --target-endpoint-arn arn:aws:dms:us-east-1:123456789012:endpoint:my-aurora-target-endpoint \
    --migration-type FULL_LOAD_AND_CDC \
    --table-mappings '{"rules":[{"rule-type":"selection","rule-id":"1","rule-name":"1","object-locator":{"schema-name":"your_database","table-name":"%"},"rule-action":"include","filters":[]}]}' \
    --replication-task-settings '{"FullLoadSettings":{"MaxFullLoadSubTasks":4,"CommitRateOverride":10000},"ChangeProcessingSettings":{"EnableLagIndexing":true,"FullLoadIgnoreConflicts":false,"ApplyErrorPolicies":{"TransactionConsistencyTimeout":600,"EventDeadLetterQueueArn":"","ErrorIgnoreMaxCount":0,"ErrorIgnorePercent":0,"ErrorMaskErrorsToIgnore":""}},"Logging":{"EnableLogging":true,"IncludeControlFlowLogs":true,"IncludeTaskLogs":true,"Include ЛеонидLogs":true}}' \
    --region us-east-1

The --table-mappings JSON specifies which schemas and tables to migrate. The example above includes all tables from `your_database`. You can refine this with specific table inclusions/exclusions.

Start the task:

aws dms start-replication-task \
    --replication-task-arn arn:aws:dms:us-east-1:123456789012:task:my-migration-task \
    --start-replication-task-type START_REPLICATION \
    --region us-east-1

Monitor the task progress in the AWS Console or via the CLI. The “Full load” phase should complete, followed by the “Ongoing replication” (CDC) phase, where DMS catches up and keeps the target in sync with the source.

Application Schema and Code Adjustments

While Aurora MySQL is highly compatible with MySQL, subtle differences can arise. Use AWS SCT to analyze your schema and stored procedures. Pay close attention to:

• Data Types: Ensure no deprecated or incompatible data types are in use.
• Stored Procedures/Functions/Triggers: SCT can often convert these, but manual review and testing are essential. Aurora has specific limitations on trigger execution order and complexity.
• Character Sets and Collations: Ensure consistency. Aurora defaults to utf8mb4, which is generally recommended.
• SQL Dialect: Minor syntax variations might exist. Test all critical queries.
• Connection Strings: Update application configurations to point to the Aurora Serverless cluster endpoint.
• Parameter Groups: Aurora uses its own parameter groups. Review and adjust settings like max_connections, innodb_buffer_pool_size (though Aurora manages this dynamically), and query cache settings (Aurora generally discourages the query cache).

Perform these schema changes on the Aurora target before starting the DMS full load if you are using DO_NOTHING for target table preparation. If you are using TRUNCATE_BEFORE_LOAD, DMS will handle table creation, but you’ll still need to apply any non-DMS-handled schema modifications.

The Zero-Downtime Cutover Strategy

The cutover is the most critical phase. The goal is to minimize the window where writes to the source database are lost or not replicated.

Steps:

1. Pre-Cutover Validation: Ensure DMS CDC is running with minimal latency. Use DMS monitoring tools and query replication lag.
2. Application Read-Only Mode: Briefly put your application into read-only mode. This prevents new writes to the source database.
3. Final Data Sync: Wait for DMS to fully catch up. Monitor the replication lag until it’s near zero.
4. Stop DMS CDC: Stop the DMS replication task. This ensures no further changes are applied from the source.
5. Application Write Enable (on Target): Update your application’s configuration to point to the Aurora Serverless cluster endpoint. Re-enable writes in your application.
6. Final Verification: Perform a few test writes and reads against the Aurora cluster to confirm functionality.
7. Stop DMS Task: Once confident, stop and delete the DMS task and replication instance to avoid ongoing costs.
8. Monitor Application: Closely monitor application logs and performance metrics for any database-related issues.

The duration of read-only mode should ideally be seconds to minutes, depending on your application’s write volume and DMS replication lag. For applications with very high write throughput, consider a phased cutover or a strategy that involves a brief write pause rather than a full read-only mode.

Post-Migration Optimization and Monitoring

After a successful cutover, focus on optimizing and monitoring your Aurora Serverless environment.

Monitoring:

• CloudWatch Metrics: Monitor Aurora-specific metrics like CPUUtilization, DatabaseConnections, ReadIOPS, WriteIOPS, AuroraThrottledEvents, and ServerlessDatabaseCapacity (for v1/v2 ACU usage).
• Performance Insights: Utilize AWS Performance Insights to identify slow queries and performance bottlenecks.
• Logs: Configure and review Aurora logs (error logs, slow query logs) in CloudWatch Logs.

Optimization:

• Parameter Tuning: Adjust Aurora cluster and database instance parameters based on observed performance. For Serverless v2, focus on the MinCapacity and MaxCapacity settings to balance cost and responsiveness.
• Query Optimization: Refactor slow queries identified via Performance Insights or logs.
• Indexing: Ensure appropriate indexes are in place.
• Connection Pooling: Implement robust connection pooling in your application to manage connections efficiently, especially important for Serverless v1’s connection limits.

Regularly review your Aurora Serverless capacity settings and costs. Aurora Serverless v2 offers more granular scaling, but understanding its scaling triggers and behavior is key to cost-effective operation.