From MySQL to Amazon Aurora: Different Methods of Data Migration
Learn about launching an Amazon Aurora DB Cluster and analyzing various ways of migrating data from an existing MySQL database to an Amazon Aurora DB Cluster.
Join the DZone community and get the full member experience.Join For Free
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.
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
mysqldumputility 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:
Published at DZone with permission of Rathnadevi Manivannan. See the original article here.
Opinions expressed by DZone contributors are their own.