From MySQL to Amazon Aurora: Different Methods of Data Migration

Amazon Aurora, a simple and cost-effective relational database engine, is used to set up, operate, and scale MySQL deployments. It possesses the speed and reliability of high-end commercial databases, provides faster recovery from instance failure, has a consitently lower impact on Primary replicas, and is compatible with InnoDB engine and Aurora I/O mechanisms (16K for read and 4K for write — and all can be batched if smaller).

In this blog, let's discuss launching Amazon Aurora DB Cluster and various ways of migrating data from MySQL to Amazon Aurora DB Cluster.

Prerequisites

• Create an Amazon Aurora account.

Use Case

Launching an Amazon Aurora DB Cluster and analyzing various ways of migrating data from existing an MySQL database to an Amazon Aurora DB Cluster.

Launching Amazon Aurora DB Cluster

To launch Amazon Aurora DB cluster, perform the following steps:

• Sign into the Amazon RDS instance.
• On the Select Engine page, select Amazon Aurora as your DB engine.
• On the Specify DB Details page, specify the DB details such as Instance Specifications and Settings.
• On the Configure Advanced Settings page, provide network and security details such as VPS, subnet group, publicly accessible, availability zone, and VPC security group.
• Click Launch DB Instance to launch the instance. Upon successfully launching the DB instance, the page looks similar to this one:

Migrating Data From MySQL to an Amazon Aurora DB Cluster

A few ways of migrating data from MySQL to Amazon Aurora DB cluster are:

• Using the Talend Extract-Transform-Load (ETL) tool: Integrate Talend with Aurora and migrate data into the Aurora DB cluster.
• Using MySQL dump: Create a MySQL data dump using the mysqldump utility and import it into the Aurora DB cluster.
• Using an Amazon RDS MySQL DB snapshot: Create a DB snapshot of an Amazon RDS MySQL DB instance and migrate it to the Aurora DB cluster.
• Using Amazon AWS Database Migration Service (AWS DMS): Connect AWS DMS with MySQL and migrate data from MySQL to the Aurora DB cluster.

Let's discuss all the above ways of data migration to help you choose the most optimized way of migration based on your specific need.

Using ETL + Talend

Migrating data from MySQL to the Amazon Aurora DB cluster using the ETL tool is the easiest way to migrate. A few ETL tools used for migration are Pentaho, Kettle, Informatica, and Talend. Talend is best-suited when migrating aggregated data from MySQL to Aurora. Using aggregation functions, the data can be migrated from MySQL to Aurora. After integrating MySQL and Aurora, drag and drop the required components to perform any function.

Using MySQL Dump

This is the best-suited method to migrate data from MySQL to the Aurora DB cluster if the data size exceeds 6 TB. The mysqldump utility is used to create a MySQL data dump file. The dump file is then imported into the Aurora DB cluster to migrate the data to the Aurora DB cluster.

Using Amazon RDS MySQL DB Snapshot

This is the best-suited method to migrate data from MySQL to the Aurora DB cluster if the data size is less than 6 TB. It's easy to migrate data from different regions such as ap-northeast-1, ap-northeast-2, ap-south-1, and so on by taking a DB snapshot. The DB snapshot of an Amazon RDS MySQL DB instance is taken and the data is migrated into the Aurora DB cluster. As Aurora DB supports only InnoDB engine, any MyISAM engine tables already present will be converted into InnoDB during migration.

Using AWS Database Migration Service (AWS DMS)

AWS Database Migration Service, a web service, is used to easily and securely migrate data between heterogeneous or homogeneous databases such as on-premises databases, RDS database, SQL, NoSQL, and text-based targets in zero downtime. It is also used for continuous data replication with high availability. It is a low-cost service and allows you to pay only for the resources used and other additional log storage. AWS DMS is connected with MySQL to load data from MySQL to the Aurora DB cluster.

It takes four to five minutes to migrate 1 GB file size from MySQL to Aurora DB cluster using AWS DMS at zero downtime. The migration time differs based on AWS instance type.

Data Migration Comparison Chart

The time taken to migrate data from MySQL to the Aurora DB cluster is graphically represented as a comparison chart as follows: