Binary Log Encryption in MySQL

As businesses and organisations increasingly rely on databases to store critical information, the need for secure data storage has never been greater. One important aspect of database security is ensuring that sensitive data is protected, even in the event of a breach. One way to achieve this is through the use of binary log encryption in mysql.

What is Binary Log Encryption?

Binary log encryption is a feature in mysql that encrypts binary logs, which are used to record changes to a database. These logs are used for replication and backup purposes, and without encryption, they could contain sensitive information that could be used maliciously in the event of a breach. By encrypting the binary logs, sensitive information is protected, even if the logs fall into the wrong hands.

How Does Binary Log Encryption Works in MySQL?

Binary log encryption in mysql uses the AES encryption algorithm to secure the logs. The encryption key is stored in a secure location and is used to encrypt the logs before they are written to disk. When the logs are needed for replication or backup purposes, they are decrypted using the same encryption key. This ensures that sensitive information is protected at all times, even when the logs are stored on disk.

Why Use Binary Log Encryption in MySQL?

Binary log encryption provides a number of important benefits for organisations that rely on mysql for their data storage needs. Some of these benefits include,

Increased data security : By encrypting the binary logs, sensitive information is protected, even if the logs fall into the wrong hands.

Compliance with regulations : Many organisations are required to comply with regulations that require the protection of sensitive data, such as HIPAA and PCI-DSS. Binary log encryption helps organisations meet these requirements.

Improved peace of mind : With binary log encryption, organisations can have peace of mind knowing that their sensitive data is protected, even in the event of a breach.

How to Enable Binary Log Encryption in MySQL ?

First of all, you need to have keyring enabled on mysql before using binary log encryption. Trying to enable binary log encryption without a keyring will result in the following error,

Example

mysql > set global binlog_encryption = ON;

ERROR 3794 (HY000): Unable to recover binlog encryption master key, please check if keyring plugin is loaded.

For more information about installing keyring plugin, please check MySQL manual pages.

After enabling this keyring plugin, all you have to enable binlog_encryption option. In order to your mysql server always generating encrypted binary and relay log files you must set the option in any startup configuration file. You can do it by adding a line as below to a configuration file under [mysqld] tag.

binlog_encryption=ON

You can also use SET PERSIST when enabling binary log encryption for the first time in a client session to ensure that your mysql server will always generate encrypted binary and relay log files, event after a server restart.

mysql> set persist binlog_encryption = ON;

Query OK, 0 rows affected (0.01 sec)

It is possible to check whether the binary log encryption feature is enabled or not by using a performance schema table,

mysql> select * from performance_schema.global_variables where variable_name = 'binlog_encryption';

+-------------------+----------------+

| VARIABLE_NAME | VARIABLE_VALUE |

+-------------------+----------------+

| binlog_encryption | ON |

+-------------------+----------------+

1 row in set (0.01 sec)

When listing the existing binary log files, the mysql server will now tell which files are encrypted and which files are not encrypted,

mysql> show binary logs;

+---------------------+-----------+-----------+

| Log_name | File_size | Encrypted |

+---------------------+-----------+-----------+

| lab-test-bin.000001 | 203 | No |

| lab-test-bin.000002 | 563 | Yes |

+---------------------+-----------+-----------+

2 rows in set (0.00 sec)

Two tier encryption

Two-tier encryption is a security method that involves encrypting data at two different levels or stages. The purpose of two-tier encryption is to provide an additional layer of security for sensitive data, making it more difficult for un-authorised users to access or steal the information.

The binary logs encryption was designed to use two tiers,

1. A per file password, used to encrypt/decrypt binary log file content, is stored in the encrypted binary log file header.
2. A binary log encryption key, used to encrypt/decrypt sensitive data (i.e., the file password) in the encrypted binary log file header, is stored in the keyring.
3. While a given binary log encryption key may be used to encrypt/decrypt many binary and relay log files passwords, a file password is intended to encrypt/decrypt a single binary or relay log file.
4. The server automatically generates a binary log encryption key to protect file passwords when the binary log encryption feature is enabled for the first time. The key used to protect new binary and relay log files is called the master key.
5. If the option is disabled and then enabled again, the server will not generate a new master key, as it shall be able to fetch the existing master key from keyring.
6. When generating new encrypted binary or relay log files, the server generates a new file password for the new file, encrypt it using the binary log encryption master key, and store the encrypted file password and the master key identification (not the master key itself) in the encrypted file header.
7. Then, when the server needs to read from a binary or relay log file, the server first reads the encrypted file header, fetches the binary log encryption key required to decrypt the file password, decrypts the file password and then it has access to the a plain binary log data.

Conclusion

Binary log encryption is an important tool for protecting sensitive data in mysql databases. By encrypting the binary logs, sensitive information is protected, even in the event of a breach. With binary log encryption, organisations can have peace of mind knowing that their critical information is secure.

References

Binary Log Encryption in MariaDB - https://mariadb.com/kb/en/encrypting-binary-logs/#effects-of-data-at-rest-encryption-on-mysqlbinlog

Binary Log Encryption in MySQL - https://dev.mysql.com/doc/refman/8.0/en/replication-binlog-encryption.html