How can I encrypt an unencrypted Amazon RDS DB instance for MySQL or MariaDB with minimal downtime?

Last updated: 2020-08-19

I tried to create an encrypted read replica of my unencrypted Amazon Relational Database Service (Amazon RDS) instance for MySQL or MariaDB. However, I received the following error:

"You cannot create an encrypted Read Replica from an unencrypted DB instance. (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterCombination; Request ID:"

How can I encrypt an unencrypted DB instance with minimal downtime?

Short description

Amazon RDS has the following limitations for encrypted DB instances:

  • You can't modify an existing unencrypted Amazon RDS DB instance to encrypt the instance.
  • You can't create an encrypted read replica from an unencrypted instance.

Do the following:

1.    Encrypt an unencrypted snapshot that you take from an unencrypted read replica of the instance.

2.    Restore a new RDS DB instance from the encrypted snapshot to deploy a new encrypted DB instance.

3.    Use MySQL replication to synchronize changes from the source to the new encrypted DB instance.

4.    Verify that the new, encrypted DB instance is in sync with the source DB instance.

5.    Switch your connections and redirect your traffic to the new DB instance.

This process results in minimal downtime.

Resolution

1.    Create a temporary read replica for the source unencrypted Amazon RDS DB instance. In this example, the source unencrypted DB instance is called SOURCE-UE and the temporary read replica is called TEMP-RR.

2.    Connect to TEMP-RR, and monitor the replica lag until Seconds_Behind_Master is stable at value 0. This indicates that TEMP-RR is in sync with SOURCE-EU:

mysql> SHOW SLAVE STATUS \G
Seconds_Behind_Master: 0

3.    Stop the replication process on TEMP-RR.

MySQL > call mysql.rds_stop_replication;
+---------------------------+
| Message                   |
+---------------------------+
| Slave is down or disabled |
+---------------------------+

4.    Note the values for Relay_Master_Log_File and Exec_Master_Log_Pos from TEMP-RR:

mysql> SHOW SLAVE STATUS \G
Relay_Master_Log_File: mysql-bin-changelog.000012
Exec_Master_Log_Pos: 123
5.    In SOURCE-EU, set the binlog retention hours parameter to preserve binary logs for the time that is required to complete the operation. In the following example, binlog retention hours is set to 24 hours:
mysql> call mysql.rds_set_configuration('binlog retention hours', 24);

6.    Take a snapshot of TEMP-RR. Optionally, you can delete TEMP-RR after taking the snapshot.

7.    Copy the snapshot of TEMP-RR, and set Enable Encryption to Yes.

8.    Restore a new DB instance from the copied snapshot that has encryption enabled. In this example, the new encrypted DB instance is called NEW-RR-EN.

9.    Modify the inbound rules in SOURCE-EU's security group to allow traffic from NEW-RR-EN. If you use the same security group on both DB instances, you can use the same security group ID reference as SOURCE-EU. From the Amazon RDS console, choose Databases. Select the DB instance, and then choose the Connectivity & Security tab. Choose the Security group, and then select the Inbound tab. Choose Edit, enter your security group ID, and then choose Save.
Note: Be sure that the outbound traffic to SOURCE-EU is allowed from NEW-RR-EN.

10.    Log in to SOURCE-EU, set up a replication user, and grant the necessary privileges to the user:

mysql> create user repl_user@'%' identified by ‘password123’;
mysql> grant replication slave, replication client on *.* to repl_user@'%';
mysql> show grants for repl_user@'%';

Note: Replace repl_user with your own replication user name and password123 with your own password.

11.    Connect to NEW-RR-EN, and establish a replication connection to SOURCE-EU:

mysql> CALL mysql.rds_set_external_master ( 'rds-endpoint' , 3306 , 'repl_user' , 'password123’ , 'mysql-bin.000012' , 123 , 0 );

Note: The rds-endpoint is the endpoint for SOURCE-EU. If the source unencrypted DB instance (SOURCE-UE) is publicly accessible (the Publicly Accessible property of the db instance is set to Yes), then you need to provide a private IP address instead of the rds-endpoint.

The user name (repl_user) and password (password123) are the user name and password that you created in step 10. Use the captured values of Relay_Master_Log_File and Exec_Master_Log_Pos from step 4 to set up replication with mysql.rds_set_external_master procedure.

12.    From NEW-RR-EN, start replication:

mysql > CALL mysql.rds_start_replication;

13.    From NEW-RR-EN, confirm that the replication is in sync between SOURCE-EU and NEW-RR-EN.

mysql> SHOW SLAVE STATUS \G
Seconds Behind master: 0

14.    After Seconds_Behind_Master is stable at value 0, stop the traffic and close the connections on SOURCE-EU. Downtime then begins.

Note: You must stop all application servers and clients that connect to SOURCE-EU to ensure that no new changes are made to SOURCE-EU. Optionally, you can temporarily lock the security group that SOURCE-EU uses. This prevents inbound traffic from any application or client, except from NEW-RR-EN and the host that the user is performing these actions from.

15.    As the database master user, connect to NEW-RR-EN and stop replication:

MySQL > call mysql.rds_stop_replication;

Important: After you run this command, the NEW-RR-EN doesn't replicate data from SOURCE-EU.

16.    Promote NEW-RR-EN to a standalone server by stopping the replication relationship between SOURCE-EU and NEW-RR-EN:

MySQL > call mysql.rds_reset_external_master;

17.    Point all applications, clients, and database connections to NEW-RR-EN by specifying NEW-RR-EN's DNS endpoint in all connection strings. Or, you can rename SOURCE-EU, and then modify NEW-RR-EN to use the same name that SOURCE-EU uses.

18.    Confirm that the security group rules on NEW-RR-EN allow inbound traffic from the appropriate applications and clients.

19.    After you point the applications and clients to NEW-RR-EN and test the environment, you can delete SOURCE-EU.

Tip: It's a best practice to test this operation on a restored instance before applying this operation in a production environment.