Overview

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

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

Pre-requisites

Create an Amazon Aurora account using the link: https://aws.amazon.com/

Use Case

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

Launching Amazon Aurora DB Cluster

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

• Sign in to Amazon RDS instance.
• In Select Engine page, select Amazon Aurora as your DB engine.
• In Specify DB Details page, specify the DB details such as Instance Specifications and Settings.
• In Configure Advanced Settings page, provide network and security details such as VPS, subnet group, publicly accessible, availability zone, and VPC security group as shown in the below diagram:

• Click Launch DB Instance to launch the instance.
  On successfully launching the DB instance, the page looks similar to the one as shown below:

Migrating Data from MySQL to Amazon Aurora DB Cluster

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

• Using Talend Extract-Transform-Load (ETL) Tool – Integrate Talend with Aurora and migrate data into the Aurora DB cluster.
• Using MySQL Dump – Create MySQL data dump using mysqldump utility and import it into the Aurora DB cluster.
• Using 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 us discuss about all the above ways of data migration so as to help you choose the most optimized way of migration based on your specific need.

Using ETL – Talend

Migrating data from MySQL to Amazon Aurora DB cluster using ETL tool is the easiest way among all other ways of migration. Few ETL tools used for migration are Pentaho, Kettle, Informatica, Talend, and so on.

In this section, let us discuss about migrating data from MySQL to Amazon Aurora DB cluster using 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 functionality.

Pre-requisites

• MySQL Version 5.6
• Configure the tAmazonAuroraOutput component in Talend 6.1

Migrating to Amazon Aurora DB Cluster Using Talend

To migrate data from MySQL to Aurora using Talend, perform the following steps:

• Open Talend.
• In the Palette, search tMysqlInput component.
• In the tMysqlInput component, provide sample RDS instance details.
• Create a Talend job using tFlowMeter, tMap, tfilterRow, tAggregation, and tAmazonAuroraOutput.
• Run the job to migrate the data from MySQL to Aurora.

tAggregation component used for migration is shown in the below diagram:

tfilterRow component used for filtering data above a specified time period is shown in the below diagram:

Note: MySQL engine type will be converted into InnoDB engine after migration.

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.
mysqldump utility is used to create MySQL data dump file. The dump file is then imported into the Aurora DB cluster so as to migrate the data to the Aurora DB cluster.

Migrating to Amazon Aurora DB Cluster Using MySQL Dump

To migrate data from MySQL to Aurora using MySQL dump, perform the following steps:

• Create a MySQL dump file from MySQL database using the below command:

nohup mysql -u username -p'password' -h hostname --port=3306 dstore > dstore_20170703.sql > dstore.log &

• Import the dump file into Aurora DB Cluster using the below command:

nohup mysql -u username - p'password' -h hostname --port=3306 dstore_crawler  dstore_crawler.log &

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 is easy to migrate data from different regions such as ap-northeast-1, ap-northeast-2, ap-south-1, and so on by just 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.

Pre-requisites

• MySQL Version 5.6
• Aurora DB Version 1.13
• Amazon RDS console

Migrating to Amazon Aurora DB Cluster Using Amazon RDS MySQL DB Snapshot

To migrate data from MySQL to Aurora using Amazon RDS MySQL DB Snapshot, perform the following steps:

• Open Amazon RDS console.
• Click Instances.
• Choose RS Instance.
• Click Instance Actions –> Migrate Latest Snapshot as shown in the below diagram:

• Mention Instance Specifications as Aurora and set DB Instance Identifier as shown in the below diagram:

• Click Migrate to initiate the process of data migration as shown in the below diagram:

On clicking migrate, the process of migrating data from MySQL to Aurora will be initialized as shown in the below diagram:

The migration progress is shown in the below diagram:

The data migrated to Aurora is shown in the below diagram:

Using Amazon AWS Database Migration Service (AWS DMS)

AWS Database Migration Service, a web service, is used to easily and securely migrate data between heterogeneous or homogenous databases such as on-premises databases, RDS database, SQL, NoSQL, text based targets, and so on in zero downtime. It is also used for continuous data replication with high-availability.

This service 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 so as to load data from MySQL to the Aurora DB cluster.

Migrating to Amazon Aurora DB Cluster Using Amazon AWS Database Migration Service

To migrate data from MySQL to Aurora using Amazon AWS DMS, perform the following steps:

• Open Amazon DMS console.
• Click Migrate –> Next to start the migration process as shown in the below diagram:

• In the Create replication instance page, provide instance details and click Next as shown in the below diagram:

• In the Connect source and target database endpoints page, provide source and target database connection details and click Next to create the replication instance as shown in the below diagram:

• In the Create task page, provide source and target database connection details and click Create task to create replication instance as shown in the below diagram:

• Under Guided tab, enter details of Selection rules as shown in the below diagram:

• Under Guided tab, enter details of Transformation rules as shown in the below diagram:

The data migrated from MySQL to the Aurora DB cluster is shown in the below diagram:

Data Migration Comparison Chart

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

Amazon AWS DMS: It took 4-5 mins for migrating 1 GB file from MySQL to Aurora DB cluster using Amazon AWS DMS at zero downtime. The migration time differs based on AWS instance type.

Conclusion

In this blog article, we discussed about the various ways of migrating data from MySQL to Amazon Aurora DB Cluster. The optimized way of doing data migration varies based on data size and requirements.

References

• Creating an Amazon Aurora DB Cluster:
  http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.CreateInstance.html
• Migrating Data to an Amazon Aurora DB Cluster:
  http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Migrate.html
• Migrating Data from a MySQL DB Instance to an Amazon Aurora DB Cluster by Using a DB Snapshot:
  http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Migrate.RDSMySQL.html
• Migrating Data from an External MySQL Database to an Amazon Aurora DB Cluster:
  http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Migrate.MySQL.html#Aurora.Migrate.MySQL.S3
• What is AWS Database Migration Service?
  http://docs.aws.amazon.com/dms/latest/userguide/Welcome.html