Migrating Your On-Premises MySQL Database to AWS Aurora: A Step-by-Step Guide

Migrating your on-premises MySQL database to AWS Aurora offers numerous benefits, including improved performance, scalability, availability, and cost-effectiveness. This blog post provides a detailed, step-by-step guide to help you successfully migrate your MySQL database to Aurora.

Before You Begin:

• AWS Account: Ensure you have an active AWS account with appropriate permissions (IAM roles) to create and manage Aurora clusters, S3 buckets, and other related services.
• MySQL Compatibility: Verify your on-premises MySQL version is compatible with the Aurora MySQL version you plan to use. Refer to the AWS documentation for compatible versions. Upgrading your on-premises MySQL before migration might be necessary.
• Downtime Assessment: Understand the downtime required for the migration. Choose a migration method that aligns with your business needs (e.g., minimal downtime using AWS DMS).
• Backup: Always create a complete backup of your on-premises MySQL database before starting the migration. This backup serves as a safety net in case anything goes wrong during the process.
• Testing: Thoroughly test your application against the migrated database in a staging environment before switching over your production environment.

Migration Methods:

There are several methods to migrate your MySQL database to Aurora. This guide will focus on the logical dump and restore method and the AWS Database Migration Service (DMS) method.

• Logical Dump and Restore (For smaller databases and acceptable downtime): This method involves creating a logical dump of your on-premises database using mysqldump, transferring the dump file to AWS, and restoring it into your Aurora cluster.
• AWS Database Migration Service (DMS) (For minimal downtime and ongoing replication): DMS provides a robust and flexible service to migrate databases with minimal downtime. It supports ongoing replication, allowing you to keep your Aurora cluster synchronized with your on-premises database until you are ready to switch over.

Let's dive into each method in detail.

Method 1: Logical Dump and Restore

This method is suitable for smaller databases where a certain amount of downtime is acceptable.

Step 1: Create a Logical Dump of Your On-Premises Database

On your on-premises MySQL server, use the mysqldump utility to create a logical dump of your database.

      mysqldump -u <username> -p <database_name> --single-transaction --routines --triggers --events > database_dump.sql
            

• <username>: Your MySQL username with appropriate privileges.
• <database_name>: The name of the database you want to migrate.
• --single-transaction: Ensures data consistency by creating a consistent snapshot of your database.
• --routines: Dumps stored procedures and functions.
• --triggers: Dumps triggers.
• --events: Dumps scheduled events.
• database_dump.sql: The name of the output file containing the database dump.

Step 2: Create an S3 Bucket

In the AWS Management Console, navigate to the S3 service and create a new bucket to store your database dump file.

• Choose a region closest to your Aurora cluster for faster data transfer.
• Ensure your IAM user or role has permission to upload and download objects from the S3 bucket.

Step 3: Upload the Dump File to S3

Use the AWS CLI or the AWS Management Console to upload the database_dump.sql file to the S3 bucket you created.

Using AWS CLI:

      aws s3 cp database_dump.sql s3://<your_s3_bucket_name>/
            

• <your_s3_bucket_name>: The name of your S3 bucket.

Step 4: Create an Aurora MySQL Cluster

In the AWS Management Console, navigate to the RDS service and create a new Aurora MySQL cluster.

• Choose Database Option: Select "Amazon Aurora" and choose "MySQL-compatible."
• Engine Version: Select a MySQL-compatible Aurora engine version compatible with your on-premises MySQL version.
• Templates: Select "Production" or "Development/Testing" based on your environment.
• Settings: Configure the DB cluster identifier, master username, and master password. Choose a strong password.
• Instance Configuration: Choose the DB instance class (e.g., db.r5.large) based on your workload requirements.
• Connectivity: Ensure your Aurora cluster is accessible from your on-premises environment. You might need to configure security groups and network ACLs.
• Additional Configuration: Enable backups, encryption, and other features as needed.

Step 5: Restore the Database Dump to Aurora

Connect to your Aurora MySQL cluster using a MySQL client (e.g., MySQL Workbench, mysql command-line tool). You'll need the endpoint of your Aurora cluster (found in the RDS console) and the master username and password you configured earlier.

      mysql -h <your_aurora_cluster_endpoint> -u <master_username> -p
            

Once connected, execute the following commands to create the database and restore the dump file:

      CREATE DATABASE <database_name>;
USE <database_name>;
SOURCE /path/to/database_dump.sql; -- This only works when dump is local. See next method for restoring from S3
            

Important: Restoring directly using the SOURCE command works when the dump file is on the same server. Since you uploaded it to S3, you can't directly SOURCE the file from S3. You will need to:

1. Download the dump from S3 to the Aurora EC2 instance (if you have one, or launch one temporarily)
2. Connect to the Aurora Database from that EC2 Instance.
3. Then, you can use the SOURCE command

An easier approach is to use the mysql command-line tool to execute the SQL commands directly from your local machine, like this:

      mysql -h <your_aurora_cluster_endpoint> -u <master_username> -p <database_name> < database_dump.sql
            

This command reads the database_dump.sql file and executes the SQL statements against the specified Aurora database.

Step 6: Verify the Migration

After the restore is complete, connect to your Aurora cluster and verify that the database, tables, data, stored procedures, triggers, and events have been successfully migrated.

Method 2: AWS Database Migration Service (DMS)

This method is preferred for minimal downtime and ongoing replication.

Step 1: Create an AWS DMS Replication Instance

In the AWS Management Console, navigate to the DMS service and create a new replication instance.

• Name: Give your replication instance a descriptive name.
• Instance Class: Choose the instance class based on your database size and replication requirements. dms.t3.medium is a good starting point for smaller databases.
• VPC: Select the VPC where your Aurora cluster and on-premises MySQL server are located.
• Security Group: Configure the security group to allow inbound traffic from your on-premises MySQL server to the replication instance.
• Publicly Accessible: Set to 'No' unless your on-premises MySQL server is publicly accessible.

Step 2: Create Source and Target Endpoints

Create a source endpoint for your on-premises MySQL server and a target endpoint for your Aurora cluster.

Source Endpoint (On-Premises MySQL):

• Endpoint Type: Source endpoint.
• Engine: mysql
• Server Name: The IP address or hostname of your on-premises MySQL server.
• Port: 3306 (default MySQL port).
• Username: Your MySQL username with replication privileges.
• Password: Your MySQL password.
• Database Name: The name of the database you want to migrate.

Target Endpoint (Aurora MySQL):

• Endpoint Type: Target endpoint.
• Engine: aurora
• Server Name: The endpoint of your Aurora MySQL cluster.
• Port: 3306 (default MySQL port).
• Username: Your Aurora master username.
• Password: Your Aurora master password.
• Database Name: The name of the database you want to migrate (or create a new database).

Step 3: Create a Database Migration Task

Create a database migration task to migrate data from your on-premises MySQL server to your Aurora cluster.

• Task Identifier: Give your task a descriptive name.
• Replication Instance: Select the replication instance you created earlier.
• Source Database Endpoint: Select the source endpoint for your on-premises MySQL server.
• Target Database Endpoint: Select the target endpoint for your Aurora cluster.
• Migration Type: Choose one of the following options:
• Table Mappings: Define the tables you want to migrate. You can migrate all tables or select specific tables.
• Transformation Rules: Apply transformation rules if needed (e.g., renaming tables or columns).
• Task Settings: Configure task settings, such as LOB settings, parallel load, and validation. Adjust these settings based on your database size and complexity.

Step 4: Start the Migration Task

Start the database migration task. DMS will begin migrating data from your on-premises MySQL server to your Aurora cluster.

Step 5: Monitor the Migration Task

Monitor the progress of the migration task in the DMS console. You can view the status of the task, the number of tables migrated, and any errors that occur.

Step 6: Cutover and Switchover

If you chose "Migrate existing data and replicate ongoing changes," you can perform a cutover to switch over to your Aurora cluster.

1. Stop Writes to On-Premises Database: Stop all write operations to your on-premises MySQL database.
2. Wait for Replication to Catch Up: Wait for DMS to replicate all remaining changes from your on-premises database to your Aurora cluster. You can monitor the CDCLatencySource and CDCLatencyTarget metrics in CloudWatch to ensure the replication lag is minimal.
3. Stop DMS Replication Task: Once replication has caught up, stop the DMS replication task.
4. Update Application Connection Strings: Update your application connection strings to point to your Aurora cluster.

Step 7: Verify the Migration

After the migration is complete, connect to your Aurora cluster and verify that the database, tables, data, stored procedures, triggers, and events have been successfully migrated. Thoroughly test your application against the migrated database to ensure everything is working as expected.

Step 8: (Optional) Delete DMS Resources

Once you have verified the migration and switched over to your Aurora cluster, you can delete the DMS replication instance and endpoints to avoid incurring further charges.

Important Considerations:

• Network Connectivity: Ensure proper network connectivity between your on-premises MySQL server, the DMS replication instance, and the Aurora cluster. This might involve configuring VPNs, Direct Connect, or other network connectivity solutions.
• Security: Secure your database credentials and AWS resources using IAM roles, security groups, and network ACLs.
• Performance Tuning: Monitor the performance of your Aurora cluster and adjust instance sizes, storage, and other settings as needed. Use tools like Performance Insights to identify and resolve performance bottlenecks.
• Schema Conversion: If your on-premises MySQL schema is significantly different from the Aurora schema, you might need to use the AWS Schema Conversion Tool (SCT) to convert the schema before migrating data.

Conclusion:

Migrating your on-premises MySQL database to AWS Aurora can be a complex process, but with careful planning and execution, it can provide significant benefits. Choose the migration method that best suits your needs and follow the steps outlined in this guide to ensure a successful migration. Remember to thoroughly test your application after the migration to ensure everything is working as expected. Good luck!