Starting in version 8.0.14, MySQL server can encrypt all new binary and relay log files on disk. In order to do so, you just need to enable the new binlog_encryption option (and also ensure that you have a keyring).

Once enabling the option in a client session, the server will rotate both binary and relay logs to start using a new encrypted binary log file format. Only the data at rest is encrypted. The replication stream sent to replicas (and to mysqlbinlog client program) wasn’t changed.

Using binary log encryption

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:

For more information about how to install keyring, please check MySQL manual pages.

Once having an operational keyring in the MySQL server, all you have to do is enable binlog_encryption option. In order to have 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.

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.

It is possible to check whether the binary log encryption feature is enabled by consulting the binlog_encryption global variable value, either directly or using a performance schema table.

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

The file size displayed in SHOW BINARY LOGS output takes into account a 512 bytes header for encrypted files. But, besides when dealing with files sizes, this 512 bytes header of the encrypted files is never accounted (i.e., it is not taken into account for event positions in a file). The 512 bytes header of encrypted files is local and it is never replicated.

Note that doing some math with the examples above we can deduce the encrypted binary log file header size:

encrypted binary log file header size
= File_size - End_log_pos (of last event)
= 667 - 155
= 512

Two tiers encryption

The binary logs encryption feature was designed to use two tiers:

A per file password, used to encrypt/decrypt binary log file content, is stored in the encrypted binary log file header.

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.

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.

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.

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.

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.

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.

Using mysqlbinlog client program

As mysqlbinlog client program does not have access to the keyring, it is not able to dump an encrypted binary or relay log file directly.

Let’s show an example with the two files our server has:

Showing the content of the first (unencrypted) binary log file directly is fine:

But when we try to show the content of the second (encrypted) binary log directly, mysqlbinlog complains:

But you can still get access to the encrypted binary log file by requesting it to the server:

It is possible to know if a given binary or relay log file is encrypted or not by consulting the file “magic header” (file’s first four bytes). While unencrypted files has 0xFE62696E as magic header, encrypted files has 0xFD62696E:

This is how MySQL server and mysqlbinlog differentiate encrypted and plain binary and relay log files. Notice that using older than 8.0.14 binaries to access encrypted binary or relay log files directly will lead the programs to not recognize the encrypted files as of binary log format.

Rotating binary log master key

In MySQL version 8.0.14 it is also possible to rotate the binary log master key by enabling binlog_rotate_encryption_master_key_at_startup option before (re)starting the server.

Setting the option will make the server to generate a new binary log master key to protect new binary and relay log files. Old binary log encryption keys on keyring are kept as they may protect existing binary and relay log files still needed for server operation.

How to decrypt an encrypted binary log file

The encrypted binary log file format was designed to allow a “manual” decryption of the file data when the value of the key that encrypted its file password is known. If you want to know more about how to do it, please take a look at this blog post.

About binary log storage access API

One of the challenges for implementing the binary log encryption functionality into MySQL server was to ensure that adding a new way of storing binary and relay logs on disk would not break anything related to the binary log (i.e.: backup, replication, Group Replication).

MySQL 8.0.13 carried a work that refactored the binary log storage access API. This work implemented a storage layer abstraction interface and made all the server (and mysqlbinlog client program) code related to binary and relay log files accesses to use it.

With this new binary log storage access API, the coding part of the binary log encryption functionality could focust mostly in what really matter (encryption and decryption of binary and relay log files).