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, 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 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. The stable value 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'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

To restore a new DB instance from the encrypted snapshot, complete the following steps:

1. Restore a new DB instance from the copied snapshot that has encryption turned on. In this example, the new encrypted DB instance is called NEW-RR-EN.
2. 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: 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. 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 replication user name and password123 with your password.
3. 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, 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, 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, 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 no new changes are made 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 stop replication:

   MySQL > call mysql.rds_stop_replication;

   Important: 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, 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 encrypt Amazon RDS snapshots using a KMS key?

Troubleshooting replication on the MySQL website

Configuring binary log file position replication with an external source instance