Downloaded 24 times
Uploaded byMydbops
MySQL Data Encryption at Rest
The document outlines the features and implementation of data encryption at rest in MySQL, focusing on InnoDB data encryption mechanisms introduced in versions 5.7 and 8.0. It details the architecture of encryption keys, prerequisites for setup, and highlights the evolution of encryption capabilities over various MySQL releases, including transparent data encryption (TDE) and support for general tablespace and binary log encryption. Additionally, it addresses encryption prerequisites, observability, and the implications of master key rotation in a replication environment.
More Related Content
Similar to MySQL Data Encryption at Rest
MySQL Data Encryption at Rest
- 1. MySQL Data Encryptionat rest Mahto Monu Database Consultant , Mydbops
- 2. • Interested inOpen Source Database technologies • Oracle certified MySQL DBA • Experienced in both On Premises & Cloud • Database Consultant About Me
- 3. • Services ontop open source databases • Founded in 2016 • 90 Member team • Assisted over 500+ Customers • AWS Partner, ISO and a PCI Certified Organisation About Mydbops
- 4. Database Consulting Services Managed Database Services Focuses on TopOpensource database MySQL,MariaDB, MongoDB and PostgreSQL ON Premises and Cloud Mydbops Services
- 5. • What isData-at-rest Encryption ? • MySQL Data Encryption at rest • Innodb Data-at-rest Encryption Features Agenda
- 6. What is Data-at-restEncryption ?
- 7. What is Data-at-restEncryption ?
- 8. What is Data-at-restEncryption ? • Data at rest means all inactive data stored on disk. • Encryption of the data that is stored in the databases. • Data encryption is done by using Transparent Data Encryption (TDE) without any changes to the existing application code or schema. • Data at rest encryption is vital for regulatory compliance and data protection.
- 9. MySQL Data Encryptionat rest
- 10. MySQL Data Encryptionat rest • What is Data at Rest Encryption in MySQL • How InnoDB data-at-rest encryption works • Encryption prerequisites • Evolution of InnoDB data-at-rest encryption in MySQL • Implementation of data-at-rest encryption
- 11. MySQL Data Encryptionat rest • Encryption in MySQL 5.7 • Encryption in MySQL 8.0 • Encryption Observability • Encryption during server crash recovery
- 12. What is Dataat Rest Encryption in MySQL ? • Data at Rest Encryption in MySQL was introduced in MySQL 5.7.11. • Transparent Data Encryption (TDE) method encrypts the actual InnoDB data and log files. • TDE is managed through the database not at the server level. • With TDE the data and log files are encrypted on disk by the database. • When data request by MySQL/queries, the encrypted pages are read from disk and decrypted to be loaded into InnoDB’s buffer pool for execution.
- 13. What is Dataat Rest Encryption in MySQL ?
- 14. How InnoDB data-at-restencryption works ? • Two tier encryption key architecture, a master encryption key and tablespace keys. • Tablespace keys : An encrypted key which is stored in the tablespace header. • Master Key : Used to encrypt and decrypt the tablespace keys. • Encryption algorithm used is AES (Advance Encryption Standard) only. • Electronic Codebook (ECB) block encryption mode for tablespace key encryption.
- 15. How InnoDB data-at-restencryption works ? • Cipher Block Chaining (CBC) block encryption mode for data encryption • Each individual tablespace has it's own encryption key. • Each tablespace key is encrypted by the master key. • Application or authenticated user wants to access encrypted tablespace data, Innodb uses a master encryption key to decrypt the tablespace key. • The version of a tablespace key never changes but the master encryption key can be changed as required. This action is referred to as master key rotation.
- 16. Encryption prerequisites early_plugin_load =keyring_file.so keyring_file_data = /var/lib/mysql-keyring/keyring • A keyring component or plugin must be installed and configured at startup. • Early loading ensures that the component or plugin is available prior to initialization of the Innodb storage engine. • Parameter for enabling keyring plugin. • Enabling multiple keyring components or plugins is unsupported.
- 17. Encryption prerequisites INSTALL PLUGINkeyring_file SONAME 'keyring_file.so'; SELECT plugin_name, plugin_status FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name LIKE 'keyring%'; show global variables like '%keyring%'; +--------------------+--------------------------------+ | Variable_name | Value | +--------------------+--------------------------------+ | keyring_file_data | /var/lib/mysql-keyring/keyring | | keyring_operations | ON | +--------------------+--------------------------------+ 2 rows in set (0.00 sec) • Enabling keyring plugin globally. • Verifying whether keyring plugin is active or not. • Verifying whether keyring is enabled or not
- 18. 5.7.11 Redo & Undolog 10 April, 2017 General tablespace 22 Oct, 2018 Binary & Relay log 21 Jan, 2019 Default schema encryption 25 April, 2019 Doublewrite file 18 Jan,2021 DARE & File-per- tablespace 5 Feb, 2016 8.0.16 8.0.23 8.0.1 8.0.13 8.0.14 INNODB DATA AT REST ENCRYPTION TIMELINE
- 19. Evolution of encryptionin MySQL • Data at Rest Encryption in MySQL was introduced in Mysql 5.7.11. • The encryption in MySQL 5.7.11 was limited to file-per-table encryption. • In MySQL 8.0.13 the encryption functionality of 5.7.11 was extended to general tablespace. • The information_schema.innodb_tablespace table introduced in MySQL 8.0.13, includes an ENCRYPTION column that can be used to identify encrypted tablespaces.
- 20. Evolution of encryptionin MySQL • From MySQL 8.0.14, binary log files and relay log files can be encrypted. • From MySQL 8.0.16 the default encryption for schemas (default_table_encryption) and MySQL system tablespace encryption was available. • Encryption support for doublewrite files is available as of MySQL 8.0.23.
- 21. Implementation of data-at-restencryption early_plugin_load = keyring_file.so keyring_file_data = /var/lib/mysql-keyring/keyring innodb_file_per_table = ON default_table_encryption= ON Alter database database_name encryption='Y'; Alter database_name.table_name encryption='Y'; • The keyring component or plugin must installed and configured at startup. • Variables to be added in config file. • For enabling default encrytion for newly created schema and tables. • For encrypting the existing tables or databases use below alter queries.
- 22. Encryption in MySQL5.7 MySQL 5.7 File-per-tablespace
- 23. Encryption in MySQL5.7 early_plugin_load = keyring_file.so keyring_file_data = /var/lib/mysql-keyring/keyring innodb_file_per_table = ON • Data at Rest Encryption in MySQL was introduced in Mysql 5.7.11 • The encryption in MySQL 5.7.11 was limited to file-per-table encryption. • For enabling the encryption add below variables and restart the MySQL services.
- 24.
- 25. Encryption in MySQL8.0 early_plugin_load = keyring_file.so keyring_file_data = /var/lib/mysql-keyring/keyring deafult_table_encryption = ON • From MySQL 8.0.13 the encryption functionality of 5.7.11 was extended to general tablespace. • From MySQL 8.0.14, binary log files and relay log files can be encrypted. • From MySQL 8.0.16 the default encryption for schemas (default_table_encryption) and MySQL system tablespace encryption was available. • Encryption support for doublewrite files is available as of MySQL 8.0.23. • For enabling the encryption add below variables and restart the MySQL services or loading plugin globally.
- 26. Encryption Observability select schema_name,default_encryptionfrom information_schema.schemata where default_encryption='YES'; select space, name, space_type, encryption from information_schema.innodb_tablespaces where encryption='Y'; select table_schema, table_name, create_options from information_schema.tables where create_options like '%encryption%'; • List of databases which are encrypted in server. • For identify the encrypted tablespaces. • List of encrypted tables.
- 27. Encryption Observability select table_schema,table_name, create_options from information_schema.tables where create_options not like '%encryption%'and table_schema not in ('information_schema', 'mysql', 'sys', 'performance_schema'); select space_type, encryption, count(space_type) as total_no_tablespace from information_schema.innodb_tablespaces group by space_type; select * from performance_schema.keyring_keys; • Getting the list of non encrypted tables from the server • Total no of tablespace in a particular space_type • List of master key id versions
- 28. Encryption during crashrecovery • If server crash or failure during the encryption operation then operation is rolled forward when server is restarted. • For general tablespaces the encryption operation is resumed in a background thread from the last processed page. • If a server failure occurs during master key rotation, innodb continues the operation on server restart.
- 29. Innodb Data-at-rest EncryptionFeatures
- 30. Innodb Data-at-rest EncryptionFeatures • Default Encryption for Schemas • File-Per-Table Tablespace Encryption • General Tablespace Encryption • Doublewrite File Encryption • MySQL System Tablespace Encryption • Redo Log Encryption
- 31. Innodb Data-at-rest EncryptionFeatures • Undo Log Encryption • Binary and Relay Log Encryption • Master Key Rotation • Encryption and Replication • Encryption and Cloning • Encryption Variables • Encryption Limitation
- 32. Default Encryption forSchemas • From MySQL 8.0.16 the default_table_encryption variables defines the default encryption settings for newly created databases and tables. • The default_table_encryption is dynamic variable. • The default encryption setting for a schema can also be defined using encryption clause while creating or altering a schema.
- 33. Default Encryption forSchemas • If the default encryption clause is not specified while creating a schema, the default_table_encryption setting is applied. • A table created in an encryption-enabled schema is encrypted by default. • The table_encryption_privilege_check control the behaviour of TABLE_ENCRYPTION_ADMIN privilege.
- 34. File-Per-Table Tablespace Encryption CREATETABLE table_name (c1 INT) ENCRYPTION = 'Y'; ALTER TABLE table_name ENCRYPTION = 'Y'; • A file-per-table tablespace inherits the schema's default encryption starting with MySQL 8.0.16 unless encryption is specifically specified in the create table statement. • To encrypting the existing file-per-table tablespace an encryption clause is must.
- 35. General Tablespace Encryption CREATETABLESPACE `ts` ADD DATAFILE 'ts.ibd' ENCRYPTION = 'Y' Engine=InnoDB; ALTER TABLESPACE ts ENCRYPTION = 'Y'; • From MySQL 8.0.13 the general tablespace encryption functionality is available. • The default_table_encryption variable determines the encryption of a newly created general tablespace unless an encryption clause is specified explicitly in the CREATE tablespace statement. • To alter the encryption of an existing general tablespace an encryption clause is must.
- 36. Doublewrite File Encryption •Encryption support for doublewrite files is available as of MySQL 8.0.23. • InnoDB automatically encrypts doublewrite file pages that belong to encrypted tablespaces. • Doublewrite file pages are encrypted using the encryption key of the associated tablespace. The same encrypted page written to a tablespace data file is also written to a doublewrite file. • Doublewrite file pages that belong to an unencrypted tablespace remain unencrypted.
- 37. MySQL System TablespaceEncryption ALTER TABLESPACE mysql ENCRYPTION = 'Y'; ALTER TABLESPACE mysql ENCRYPTION = 'N'; • Encryption support for the mysql system tablespace is available as of MySQL 8.0.16. • MySQL system tablespace contains the mysql system database and data dictionary tables which is unencrypted by default. • To enable / disable encryption for the mysql system tablespace we can use alter tablespace statement. • Enabling or disabling encryption for the mysql system tablespace requires the CREATE TABLESPACE privilege on all tables in the instance.
- 38. Redo Log Encryption •Redo log encryption is disabled by default. • Redo log data encryption is enabled using the innodb_redo_log_encrypt configuration option. • The redo log data encryption occurs when redo log data is written to disk and decryption occurs when redo log data is read from disk. • Once redo log data is read into memory, it is in unencrypted form.
- 39. Redo Log Encryption •Redo log data is encrypted and decrypted using the tablespace encryption key. • When innodb_redo_log_encrypt is enabled, existing unencrypted redo log pages remain unencrypted on disk, while newly written pages are written in encrypted form. • Once redo log encryption is enabled, a normal restart without the keyring component or plugin or without the encryption key is not possible.
- 40. Undo Log Encryption •Undo log data encryption is disabled by default. • Enabled using the innodb_undo_log_encrypt configuration option. • Undo log encryption applies to undo logs that reside in undo tablespaces. • Undo log data is encrypted and decrypted using the tablespace encryption key. • Undo log encryption metadata, including the tablespace encryption key, is stored in the header of the undo log file.
- 41. Redo / UndoLog Encryption Process
- 42. Binary and RelayLog Encryption • Introduced in MySQL 8.0.14. • Keyring plugin is must enabled. • Encryption algorithm used is AES (Advanced Encryption Standard) cipher algorithm is built in to MySQL Server. • The binlog_encryption system variable to enable and disable the encryption. • With encryption enabled a new binary log encryption key is generated before the binary log and relay logs are initialised.
- 43. Binary and RelayLog Encryption • The binary log encryption key currently in use on the server is called the binary log master key. • The binary log index file and relay log index file are never encrypted. • Changing the binlog_encryption variable to ON / OFF, the binary log file and relay log files are rotated immediately. • The BINLOG_ENCRYPTION_ADMIN privilege is required to manage the encryption.
- 44. Binary and RelayLog Encryption • The SHOW BINARY LOGS statement shows whether binary log file is encrypted or not. • When binary log files is encrypted mysqlbinlog cannot read them directly but can read them using the --read-from-remote-server option. • The backup of encrypted binary log files using mysqlbinlog are stored in an unencrypted format. • From MySQL 8.0.14, mysqlbinlog returns a suitable error if you attempt to read an encrypted binary log file which was not in previous versions.
- 45. Master Key Rotation •The master encryption key should be rotated periodically and whenever you suspect that the key has been compromised. • Master key rotation is an atomic, instance-level operation. • Each time the master encryption key is rotated, all tablespace keys in the MySQL instance are re-encrypted and saved back to their respective tablespace headers. • As an atomic operation, re-encryption must succeed for all tablespace keys once a rotation operation is initiated. • If master key rotation is interrupted by a server failure, InnoDB rolls the operation forward on server restart.
- 46. Master Key Rotation Alterinstance rotate innodb master key; • Rotating the master encryption key only changes the master encryption key and re- encrypts tablespace keys. It does not decrypt or re-encrypt associated tablespace data. • Rotating the master encryption key requires the ENCRYPTION_KEY_ADMIN privilege. • To rotate the master encryption key • The alter supports concurrent DML but cannot be executed concurrently with tablespace encryption operations, as locks are taken to prevent potential conflicts.
- 47. Encryption and Replication •Keyring plugin should be enabled on all the nodes in a replication topology. • Tables do not use same keys on source and replica. • Each MySQL node generate different set of keys. • The master key rotate statement is only supported in replication environments where the source and replica run a version of MySQL that supports tablespace encryption.
- 48. Encryption and Replication •The alter statements for master key are written in binary log for replication. If statement fails, it is not logged to the binary log and neither replicated on replicas. • Replication of an alter statement operation fails if the keyring component or plugin is installed on the source but not on the replica.
- 49. Encryption and Cloning cloneinstance from mydbops_clone_user@172.31.62.26:6612 identified by 'Mydbops@123'; ERROR 3862 (HY000): Clone Donor Error: 3872 : Clone needs SSL connection for encrypted table. • When we are cloning an instance from encryption enabled instance then the keyring should be enabled on the new instance. • We can't perform cloning using normal user from encrypted instance as the clone will fail because encrypted data can't be transferred. • We must use SSL user to clone data for encrypted table.
- 50. Encryption Variables Variables ValuesInfo keyring_file_data file_name Introduced in MySQL 5.7.11 keyring_operations ON In MySQL 5.7.21 default_table_encryption ON / OFF Introduced in MySQL 8.0.16 innodb_redo_log_encrypt ON / OFF From MySQL 8.0.1 innodb_undo_log_encrypt ON / OFF From MySQL 8.0.1 binlog_encryption ON / OFF From MySQL 8.0.14
- 51. Encryption Limitation • AdvancedEncryption Standard (AES) is the only supported encryption algorithm. • Only supported for file-per-table tablespaces, general tablespaces, the mysql system tablespace , binary log , doublewrite, redo & undo logs. • Encryption is not supported for other tablespace types including the innodb system tablespace. • Encryption for error log and slow log is not available. • Data stored in memory are not encrypted. • Audit logs can be encrypted only in MySQL Enterprise Edition.
- 52. Encryption Limitation • Moveor copy a table from an encrypted tablespace to an unencrypted tablespace is not allowed but the opposite is permitted. • Tables cannot be moved or copied from encrypted file-per-table, general, or system tablespaces to non-encrypted tablespaces. • By default tablespace encryption only applies to data in the tablespace. Redo log , undo log and binary log data can be encrypted by enabling respective parameters. • We can't change the storage engine of a table that resides in, or previously resided in an encrypted tablespace. • In a replication topology the keyring plugin should be enabled on all nodes.
- 53. References • https://dev.mysql.com/doc/refman/5.7/en/innodb-data-encryption.html • https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html •https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/keyring.html • https://dev.mysql.com/doc/refman/8.0/en/replication-binlog-encryption.html • https://dev.mysql.com/blog-archive/mysql-innodb-transparent-tablespace- encryption/ • https://blog.opstree.com/2019/09/24/mysql-data-at-rest-encryption/ • https://mydbops.wordpress.com/2017/02/16/mysql-tablespace-encryptin/ • https://mydbops.wordpress.com/2020/01/27/securing-mysql-binary-logs-at-rest- in-mysql-8-0/
- 54.
- 55. Reach Us :Info@mydbops.com Thank You