MYSQL::Audit Requirement:: Keyring file rotation in MYSQL windows ::Master -SLave

 MYSQL::Audit Requirement:: Keyring file rotation in MYSQL windows ::Master -Slave

The ALTER INSTANCE ROTATE INNODB MASTER KEY statement in MySQL is used to rotate the master key for InnoDB tablespace encryption in a master-slave replication setup. This statement is specific to the InnoDB storage engine and is used when you have enabled encryption for InnoDB tablespaces using the keyring plugin.

Note down the keyring file timestamp before rotation.

When you execute the ALTER INSTANCE ROTATE INNODB MASTER KEY statement on the master server, it performs the following actions:

Generates a new master key: The statement generates a new master key for InnoDB tablespace encryption. The master key is used to encrypt and decrypt the InnoDB tablespaces.

Encrypts new data with the new master key: After generating the new master key, the statement starts encrypting new data that is written to the InnoDB tablespaces using the new key.

Re-encrypts existing data: The statement also initiates a background process that gradually re-encrypts existing data in the InnoDB tablespaces using the new master key. This process ensures that all data is eventually re-encrypted with the new key.

Synchronizes key rotation with replication: The ALTER INSTANCE ROTATE INNODB MASTER KEY statement ensures that the rotation of the master key is synchronized with the replication process in a master-slave setup. It ensures that the new key and the re-encryption process are propagated to the slave servers, allowing them to use the new master key for decryption.

Need to execute this command in master server only.

mysql>ALTER INSTANCE ROTATE INNODB MASTER KEY;

By rotating the InnoDB master key, you enhance the security of your encrypted data by periodically changing the encryption key. This helps protect against potential security threats or key exposure.

Note down the keyring file timestamp after rotation to check latest timestamp come.

It's important to note that this statement should be executed on the master server in a master-slave replication setup, and the key rotation and re-encryption process will be automatically propagated to the slave servers.

Note: These steps can be used in Innodb Cluster group replication also. For 4TB database, it took less than 10 seconds only.