Oracle Technical Track

Encrypting an existing MySQL/MariaDB RDS instance

4 min read

Jun 26, 2018 12:00:00 AM

Often it is necessary to convert an unencrypted RDS instance into an encrypted one. And it is usually expected that this process is done with minimum or no downtime. Unfortunately, one can only enable encryption when the instance is created. However, there is still hope, as there are a couple of workarounds to encrypt your existing data. In this article, I will discuss two different solutions to achieve this result. Solution 1: Create a snapshot and copy the snapshot to a new encrypted snapshot:

Create a manual snapshot of the unencrypted RDS instance
Go to Snapshots from the left panel and choose the snapshot just created
From the Actions, choose Copy snapshot option and enable encryption
Select the new encrypted snapshot
Go to Actions and select Restore snapshot

Of course, we need to stop writes on the instance while the above steps are executed. However, since we are using RDS snapshots to create the new instance, this can still be a feasible option for a production instance. To help you decide on the best solution for you, I am sharing the statistics of a test case I performed. Case: On a t2.medium RDS instance of 100 GB allocated storage, the used data size is of 96GB, running in single AZ deployment. Here is the time consumed at different stages of the complete process: Creating a snapshot: 47 mins 16 secs Copying snapshot: 14 mins 15 secs Restoring snapshot: 2 mins 38 secs The total time consumed, which can be considered as the downtime in this process, was approximately 64 mins in my test case. Please note that this time will always vary and it will depend on many factors like the storage size, workload of your instance, the number of transactions going on your instance at that time, instance class, etc. Also, note that new volumes created from existing EBS snapshots load lazily in the background. This means that after a volume is created from a snapshot, there is no need to wait for all the data to transfer from Amazon S3 to your EBS volume before your attached instance can start accessing the volume and all its data. This is the reason why restore operation takes such less amount of time. If your instance accesses data that hasn't yet been loaded, the volume immediately downloads the requested data from Amazon S3 and continues loading the rest of the data in the background. Storage blocks on volumes that were restored from snapshots must be initialized, i.e pulled down from Amazon S3 and written to the volume, before you can access the block. This preliminary action takes time and can cause a significant increase in the latency of an I/O operation the first time each block is accessed. Performance is restored after the data is accessed once. If looking for a reduced downtime option, keep reading. Solution 2: Set up external replication from unencrypted to encrypted RDS instance: The below-mentioned steps are performed on an unencrypted RDS MySQL 5.7.17 to convert it into an encrypted one. It’s recommended that before implementing this procedure in a production environment, you test it in your development instance and create your action plan accordingly. Here is the overview of the steps:

Extend the Binary Log Retention Period on the unencrypted RDS instance
Create a Read Replica from the unencrypted RDS instance
Stop Replication on the Read Replica and note down the Relay_Master_Log_File & Exec_Master_Log_Pos from SHOW SLAVE STATUS
Create a manual snapshot from that Read Replica (This snapshot will be unencrypted)
Copy that snapshot and enable encryption
Restore that snapshot (this will create an encrypted RDS instance)
Start an external replication from the binary log file and position mentioned in Step-3

Below are the details of each step: Step-1: Extend the binary log retention period on the unencrypted RDS instance mysql> CALL mysql.rds_set_configuration('binlog retention hours', 144); RDS normally purges a binary log as soon as possible, but the binary log might still be required for the external replication. Please specify the number of hours to retain binary log files as per your requirement. Created replication user on the unencrypted RDS instance. mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'REPL_USER'@'%' IDENTIFIED BY 'REPL_PASSWORD'; NOTE: REPLICATION SLAVE privilege is restricted in the RDS MySQL 5.5 Step-2: Create a Read Replica of the unencrypted RDS instance Step-3: Once the Read Replica RDS instance becomes available, stop the replication using below command: mysql> CALL mysql.rds_stop_replication; After stopping the replication, note the Relay_Master_Log_File & Exec_Master_Log_Pos from the output of SHOW SLAVE STATUS\G Step-4: Create a manual snapshot from that Read Replica Select the Read Replica RDS instance and from the Instance Actions take a snapshot (this snapshot will be the unencrypted snapshot). Step-5: Copy that snapshot and enable the encryption Select the created snapshot and from the snapshot actions menu, copy the snapshot. Enable encryption and provide a name for the snapshot. Step-6: Restore that snapshot to the RDS instance Choose this encrypted snapshot and from snapshot actions select Restore Snapshot. This will create the new encrypted RDS instance from the snapshot. Step-7: Set up external replication between unencrypted RDS instance and encrypted RDS instance Once the encrypted RDS instance becomes available, set up its external replication with the unencrypted RDS instance. Start the external replication from the binary log file and position noted in Step 3. mysql> CALL mysql.rds_set_external_master ('RDS_ENDPOINT', 3306, 'repl', 'repl', 'BINARY_LOG_FILE', 'BINARY_LOG_POS', 0); Please replace the RDS_ENDPOINT with the endpoint of unencrypted RDS instance. Also replace the BINARY_LOG_FILE and BINARY_LOG_POS with the details noted down in Step 3. Start the replication using the below command: mysql> CALL mysql.rds_start_replication; A few things to make sure when setting up the external replication between RDS instances:

The binary log retention period is extended on the master RDS instance, as in RDS, binary logs will be flushed as soon as the replica executes events.
On the master RDS instance, a replication user is created with the required privileges.
The access of the replica RDS instance is allowed into the security group of the master RDS instance.

Please monitor your slave status and once the slave gets in sync with the master, your instance is ready to be used. You can switch the traffic to the encrypted RDS instance. The cutover time will be the downtime in this case. Conclusion While encrypting an existing RDS instance, if you are using the first solution, a read-replica creation is not required, but on the flipside, the downtime required will be considerably more. So if downtime is a considerable factor for you, then evaluate the feasibility of creating a read-replica at your end and go for the second solution, as the cutover time of moving the pointer from unencrypted to encrypted instance would be the only downtime in this solution. I hope this helps!

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Encrypting an existing MySQL/MariaDB RDS instance

Share this

Share this

More resources

EBS DBA: WF Mailer Override Address and Cloning: How to

Cosmos DB consistency models – SQL on the edge episode 16

EBS: R12 Default OACORE Memory Settings Are Not Enough

Ready to unlock value from your data?