[GUIDE]: Encrypting existing MySQL RDS with reduced downtime

Smitha Hiriyanna January 10, 2019 0 Comments

AWS RDS instances and snapshots at rest can be encrypted by enabling the encryption option in AWS. Data that is encrypted at rest includes the underlying storage for DB instances, its automated backups, Read Replicas, and snapshots.

Amazon RDS encrypted DB instances use the industry standard AES-256 encryption algorithm to encrypt the  data on the server that hosts Amazon RDS DB instances. After the data is encrypted, Amazon RDS handles authentication of access and decryption of the data transparently with a minimal impact on performance.

Encryption can be enabled for the newly created RDS instances while launching the instance itself by choosing Enable encryption  option.  However, the existing RDS cannot be encrypted on the fly. The option to migrate the existing unencrypted RDS to encrypted is to:

  • Create a snapshot of DB instance
  • Create an encrypted copy of that snapshot.
  • Restore a DB instance from the encrypted snapshot

The process mentioned above would take more time and yields more downtime which is not acceptable for the production databases.  To reduce the downtime to migrate the unencrypted MySQL RDS to encrypted ,  master-slave replication can be used for MySQL RDS along with read replica feature from AWS RDS

Master-Slave Replication Configuration
  • Create a read replica for the unencrypted MySQL RDS and ensure it is in sync with master. This replica will be used to capture the master bin log details which is later used in the master-slave configuration
  • Increase the binlog retention period to a higher value in master
    mysql> call mysql.rds_set_configuration(‘binlog retention hours’,<value>);
  • Create replication user in master
    mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘<password>’;
    mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
  • Stop the replication in Read Replica
    mysql> CALL mysql.rds_stop_replication;
  • On read replica , annotate the binlog status (Master_Log_File and Read_Master_Log_Pos)
    mysql> show slave status\G
  • Backup the read replica using RDS snapshot method
  • Encrypt the snapshot using AWS Copy Snapshot method – Default key or KMS option can be chosen for encrypting the snapshot based on the requirements
  • Delete the read replica RDS instance
  • Restore the DB instance using the encrypted snapshot

    While restoring the DB instance, ensure you set the Multi-AZ and backup retention period as per the requirement.  Also, create a new parameter group for the restored instance and set READ_ONLY to ON
  • Using the earlier captured values of Master_Log_File and Read_Master_Log_Pos , set the encrypted RDS as slave for the master RDS
    mysql>    call mysql.rds_set_external_master(‘<master_rds_endpoint>’,<port>,’repl’,'<repl_user_password>’, <master_log_file>’, <master_log_pos>,0);
  • Start the replication on encrypted RDS and make sure it is in sync with the master i.e., unencrypted RDS
    mysql>    call mysql.rds_start_replication;

On the encrypted RDS DB instance, run the show slave status\G  command to determine when the replica is up-to-date with the replication master. The results of the SHOW SLAVE STATUS command include the Seconds_Behind_Master field. When the Seconds_Behind_Master field returns 0, then the replica is up-to-date with the master.

Redirect Live Application to the encrypted RDS Instance

After the encrypted RDS instance is up-to-date with the replication master, live application can be updated to use the encrypted RDS instance.

  • Verify that the Seconds_Behind_Master field in the below command results is 0, which indicates that the replica is up-to-date with the replication master.
    mysql> show slave status\G
  • Close all connections to the master(unencrypted RDS) when their transactions complete.
  • Stop the replication on encrypted RDS and ensuring that there is no lag.
    mysql> show slave status\G
    mysql> CALL mysql.rds_stop_replication;
  • Update the application to use the encrypted RDS DB instance. This update typically involves changing the connection settings to identify the host name and port of the encrypted RDS DB instance, the user account and password to connect with, and the database to use.

Note:- If the same endpoint to be retained as unencrypted RDS to avoid any changes in the application configurations,  rename the unencrypted RDS to temporary DB identifier and then rename the encrypted RDS to the original endpoint.

  • Set the encrypted RDS to read-write mode by setting READ_ONLY to OFF in parameter group
  • Reset the replication configuration in encrypted RDS so that this instance is no longer identified as a replica.
    mysql> CALL mysql.rds_reset_external_master;
Summary
  • Encryption can be enabled for an Amazon RDS DB instance when it is created, not after the DB instance is created.  For the existing RDS, it can be enabled using the copy encrypted snapshot of an unencrypted RDS.
  • DB instances that are encrypted can’t be modified to disable encryption.

The encryption migration for MySQL RDS can be performed with the reduced downtime using master-slave replication as mentioned in the steps described in this post

North America

+1-(917)-793-2500

Europe

+49-(621)-400-676-00

Asia-Pacific

+91-(80)-466-58999

Email

connect@smartshifttech.com
AboutSmitha Hiriyanna

Leave a Reply

Your email address will not be published. Required fields are marked *