Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Migrating Data From an Encrypted Amazon MySQL RDS Instance to an Encrypted Amazon Aurora Instance

DZone's Guide to

Migrating Data From an Encrypted Amazon MySQL RDS Instance to an Encrypted Amazon Aurora Instance

While AWS Database Migration Service has limitations for both source and target databases, this solution lets you migrate encrypted instances easily and securely!

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

In this blog post, we'll discuss migrating data from encrypted Amazon MySQL RDS to encrypted Amazon Aurora.

One of my customers wanted to migrate from an encrypted MySQL RDS instance to an encrypted Aurora instance. They have a pretty large database; therefore, using mysqldump or a similar tool was not suitable for them. They also wanted to setup replication between old MySQL RDS and new Aurora instances.

Spoiler: This is possible without any logical dump.

At first, I checked Amazon's documentation on encryption and found nothing about this type of migration. Even more, if I trust the documentation, it looks like they don't support replication or migration between encrypted MySQL RDS and encrypted Aurora. All instructions are for either "MySQL RDS to MySQL RDS" or "Aurora to Aurora" setups. For example, the documentation says here:

You can create Read Replicas of both encrypted and unencrypted DB clusters. The Read Replica must be encrypted if the source DB cluster is encrypted.

When I tried to create an Aurora read replica of my encrypted MySQL RDS instance, however, the Enable Encryption select control was grayed out and I could not change No to Yes.

I had to find a workaround.

Another idea was creating an encrypted MySQL RDS replica and migrating it to Aurora. While creating encrypted MySQL replica is certainly possible (actually all replicas of encrypted instances must be encrypted), it was not possible to migrate it to any other instance using the standard Migrate Latest Snapshot option:

However, the documentation specified that Aurora and MySQL RDS use the same AWS KMS key. As a result, both kinds of encryption should be compatible (if not practically the same). Amazon also has the AWS Database Migration Service, which has this promising section in its FAQ:

Q: Can I replicate data from encrypted data sources?

A: Yes, AWS Database Migration Service can read and write from and to encrypted databases. AWS Database Migration Service connects to your database endpoints on the SQL interface layer. If you use the Transparent Data Encryption features of Oracle or SQL Server, AWS Database Migration Service will be able to extract decrypted data from such sources and replicate it to the target. The same applies to storage-level encryption. As long as AWS Database Migration Service has the correct credentials to the database source, it will be able to connect to the source and propagate data (in decrypted form) to the target. We recommend using encryption-at-rest on the target to maintain the confidentiality of your information. If you use application-level encryption, the data will be transmitted through AWS Database Migration Service as is, in encrypted format, and then inserted into the target database.

I decided to give it a try. And it worked!

The next step was to make this newly migrated Aurora encrypted instance a read replica of the original MySQL RDS instance. This is easy in part with the help of great how-to on migration by Adrian Cantrill. As suggested, you only need to find the master's binary log file, current position and supply them to the stored routine mysql.rds_set_external_master. Then, start replication using the stored routine _set_external_master. Then, start replication using the stored routine mysql.rds_start_replication.

Conclusion

While AWS Database Migration Service has limitations for both source and target databases, this solution allows you to migrate encrypted instances easily and securely!

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

Topics:
data migration ,mysql ,amazon aurora ,tutorial ,database

Published at DZone with permission of Sveta Smirnova, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}