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

Because you can't encrypt an existing RDS DB 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, complete the following 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 then redirect your traffic to the new DB instance.

Resolution

Encrypt an unencrypted snapshot

To encrypt an unencrypted snapshot that you take from an unencrypted read replica, complete the following steps:

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

2. Connect to TEMP-RR. Then, run the following command to monitor the replica lag until Seconds_Behind_Master is stable at value 0:

   mysql> SHOW SLAVE STATUS \G
   Seconds_Behind_Master: 0

   Note: A stable value shows that TEMP-RR is in sync with SOURCE-EU.

3. Run the following command to 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, as shown in the following example:

   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's 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 you take the snapshot.

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

Restore a new DB instance from the encrypted snapshot

Restore a new DB instance from the copied snapshot, and turn on encryption. In this example, the new encrypted DB instance is NEW-RR-EN.

Modify the inbound rules in the 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: Allow outbound traffic to SOURCE-EU from NEW-RR-EN.

Synchronize changes from the source instance to the new encrypted instance

To use MySQL replication to synchronize changes from the source to the new encrypted DB instance, complete the following steps:

1. Log in to SOURCE-EU.
2. Run the following commands to set up a replication user and 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 replication user name and password123 with your password.
3. Run the following command to 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: Replace rds-endpoint with the DB instance endpoint for SOURCE-EU. Replace repl_user and password123 with the user name and password that you created. Use the Relay_Master_Log_File and Exec_Master_Log_Pos values to set up replication with the mysql.rds_set_external_master procedure. If SOURCE-EU is publicly accessible and NEW-RR-EN is set to private, then use the private IP address of SOURCE-EU instead of rds-endpoint.
4. From NEW-RR-EN, run the following command to start replication:

   mysql > CALL mysql.rds_start_replication;

Verify that the encrypted instance is in sync with the source instance

To verify that the new encrypted DB instance is in sync with the source DB instance, complete the following steps:

1. From NEW-RR-EN, run the following command to 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 is successful, then your output looks similar to the following:

   Slave_IO_State: Waiting for master to send eventSeconds Behind master: 0

2. After Seconds_Behind_Master is stable at value 0, stop the traffic and close the connections on SOURCE-EU.
   Note: Stop all application servers and clients that connect to SOURCE-EU to make sure that you don't make changes to SOURCE-EU. Optionally, you can temporarily lock the security group that SOURCE-EU uses. You must prevent inbound traffic from any application or client, except from NEW-RR-EN and the host that the user performs these actions from.

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

To switch your connections and redirect your traffic to the new DB instance, complete the following steps:

1. As the database leader user, connect to NEW-RR-EN, and then run the following command to stop replication:

   MySQL > call mysql.rds_stop_replication;

   Note: After you run this command, NEW-RR-EN no longer replicates data from SOURCE-EU.

2. To promote NEW-RR-EN to a standalone server, run the following command to stop the replication relationship between SOURCE-EU and NEW-RR-EN:

   MySQL > call mysql.rds_reset_external_master;

3. To point all applications, clients, and database connections to NEW-RR-EN, specify 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.

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

5. Delete SOURCE-EU.
   Note: Before you apply this operation in a production environment, it's a best practice to test this operation on a test instance.

Related information

Working with DB instance read replicas

How do I take an encrypted snapshot of an unencrypted Amazon RDS DB instance?

Troubleshooting replication on the MySQL website

Configuring binary log file position replication with an external source instance