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

Last updated: 2019-12-11

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

There are some limitations for encrypted Amazon RDS DB Instances—you can't modify an existing unencrypted Amazon RDS DB instance to make the instance encrypted, and you can't create an encrypted read replica from an unencrypted instance.

However, you can use the Amazon RDS snapshot feature to encrypt an unencrypted snapshot that's taken from a temporary, unencrypted Amazon RDS read replica of the instance that you want to encrypt. Restore a new RDS DB instance from the encrypted snapshot to deploy a new encrypted DB instance. Finally, use MySQL replication to synchronize changes from the source to the new encrypted DB instance.

After the new encrypted DB instance is in sync with the source unencrypted instance, switch your connections to the new DB instance. Because you must stop traffic to the source DB instance, first verify that the new DB instance is in sync with the source, and then redirect the traffic to the new DB instance. This process results in minimal downtime, and the data is replicated to an encrypted storage DB instance.

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 confirm that the Seconds_Behind_Master is 0, which 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 Master_Log_File and Exec_Master_Log_Pos from TEMP-RR:

mysql> SHOW SLAVE STATUS \G
Relay_Master_Log_File: mysql-bin-changelog.000012
Read_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 change 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 the same security group is used 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.

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 'repl_user';
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.

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'  , 'password'  , 'mysql-bin.000012'  , 123  , 0 );

Note: The rds-endpoint is the endpoint for SOURCE-EU. The user name (repl_user) and password are the user name and password that were created in step 10. And the MASTER_LOG_FILE and MASTER_LOG_POS are the values noted in step 4.

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.

14.    After the Seconds_Behind_Master is 0, close connections and stop writing to SOURCE-EU. This begins the downtime to SOURCE-EU.

15.    Stop all application servers and clients that connect to SOURCE-EU to verify that no new changes are made to SOURCE-EU. Optionally, you can temporarily lock the security group that is used by SOURCE-EU. 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.

16.    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.

17.    Promote NEW-RR-EN to stand-alone server by stopping the replication relationship between SOURCE-EU and NEW-RR-EN:

MySQL > call mysql.rds_reset_external_master;

18.    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 was used by SOURCE-EU.

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

20.    After the applications and clients are pointed to NEW-RR-EN and the environment is tested, 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.


Did this article help you?

Anything we could improve?


Need more help?