How can I encrypt an unencrypted Amazon RDS DB instance for MySQL or MariaDB 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.

Because you can't encrypt an existing Amazon RDS instance, you must create a new encrypted instance. Otherwise, when you create an encrypted read replica of an unencrypted Amazon RDS for MySQL instance, you receive 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:)"

To encrypt an unencrypted DB instance with minimal downtime, follow these steps:

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

2.    Restore a new 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.

Resolution

Setting up replication with minimal downtime

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-EU 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, 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 security group to allow traffic from NEW-RR-EN. If you use the same security group on both DB instances, then you can use the same security group ID reference as SOURCE-EU.

Note: Be sure to allow outbound traffic to SOURCE-EU from NEW-RR-EN.

10.    Log in to SOURCE-EU, set up a replication user, and then grant the necessary permissions 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 );

The rds-endpoint is the DB instance endpoint for SOURCE-EU.

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.

Note: If SOURCE-EU is publicly accessible and NEW-RR-EN (new encrypted DB instance) is set to "private", use the private IP address (of SOURCE-EU) instead of rds-endpoint.

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

mysql > CALL mysql.rds_start_replication;

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

mysql> SHOW SLAVE STATUS \G

If your connection between the source DB instance and read replica are successful, your output looks like this:

Slave_IO_State: Waiting for master to send event
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: Stop all application servers and clients that connect to SOURCE-EU to make sure 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 then stop replication:

MySQL > call mysql.rds_stop_replication;

Important: After you run this command, NEW-RR-EN no longer replicates 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 the NEW-RR-EN DNS endpoint in all connection strings. Or, 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, delete SOURCE-EU.

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

Related information

Working with read replicas

How do I encrypt Amazon RDS snapshots?

Troubleshooting replication in the MySQL documentation

Configuring binary log file position replication with an external master instance