Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.9Mb
PDF (A4) - 44.9Mb
PDF (RPM) - 40.6Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.6Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 205.8Kb
Man Pages (Zip) - 308.3Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Encrypting Binary Log Files and Relay Log Files

17.3.10 Encrypting Binary Log Files and Relay Log Files

From MySQL 8.0.14, binary log files and relay log files can be encrypted, helping to protect these files and the potentially sensitive data contained in them from being misused by outside attackers, and also from unauthorized viewing by users of the operating system where they are stored. The encryption algorithm used for the files, the AES (Advanced Encryption Standard) cipher algorithm, is built in to MySQL Server and cannot be configured. Binary log encryption is supported for OpenSSL and wolfSSL builds of MySQL Server.

You enable encryption on a MySQL server by setting the binlog_encryption system variable to ON. OFF is the default. The system variable sets encryption on for binary log files and relay log files.

When you first start the server with encryption enabled, a new binary log encryption key is generated before the binary log and relay logs are initialized. This key is used to encrypt a file password for each binary log file (if the server has binary logging enabled) and relay log file (if the server has replication channels), and further keys generated from the file passwords are used to encrypt the data in the files. Relay log files are encrypted for all channels, including Group Replication applier channels and new channels that are created after encryption is activated. The binary log index file and relay log index file are never encrypted.

If you activate encryption while the server is running, a new binary log encryption key is generated at that time. The exception is if encryption was active previously on the server and was then disabled, in which case the binary log encryption key that was in use before is used again. The binary log file and relay log files are rotated immediately, and file passwords for the new files and all subsequent binary log files and relay log files are encrypted using this binary log encryption key. Existing binary log files and relay log files still present on the server are not encrypted, but you can purge them if they are no longer needed.

If you deactivate encryption by changing the binlog_encryption system variable to OFF, the binary log file and relay log files are rotated immediately and all subsequent logging is unencrypted. Previously encrypted files are not automatically decrypted, but the server is still able to read them. SUPER privileges or the BINLOG_ENCRYPTION_ADMIN privilege are required to activate or deactivate encryption while the server is running. Group Replication applier channels are not included in the relay log rotation request, so unencrypted logging for these channels does not start until their logs are rotated in normal use.

Encrypted and unencrypted binary log files can be distinguished using the magic number at the start of the file header for encrypted log files (0xFD62696E), which differs from that used for unencrypted log files (0xFE62696E). The SHOW BINARY LOGS statement shows whether each binary log file is encrypted or unencrypted.

Note that when encryption is active for a MySQL server instance, only the data at rest that is written to the binary log files and relay log files is encrypted. The data in motion in the replication event stream, which is sent to MySQL clients including mysqlbinlog, is always in unencrypted format, so it must be protected in transit by the use of connection encryption (see Section 6.4, “Using Encrypted Connections”). The data in use that is held in the binary log transaction and statement caches during a transaction, and any data that exceeds the space available in those caches and is therefore stored in a temporary file on disk, is also in unencrypted format. The temporary files and caches are deleted when the thread that handles the transaction ends. The server status variables Binlog_cache_disk_use and Binlog_stmt_cache_disk_use show whether any data has been stored in temporary files, and you can increase the size of the binary log cache or binary log statement cache if you want to minimize the possibility of having unencrypted temporary files for the duration of a transaction.

Binary log encryption keys

The binary log encryption keys used to encrypt the file passwords for the log files are 256-bit keys that are generated specifically for each MySQL server instance using MySQL Server's built-in keyring service (see Section 6.5.4, “The MySQL Keyring”). The keyring service handles the creation, retrieval, and deletion of the binary log encryption keys. A server instance only creates and removes keys generated for itself, but it can read keys generated for other instances if they are stored in the keyring, as in the case of a server instance that has been cloned by file copying.

Important

The binary log encryption keys for a MySQL server instance must be included in your backup and recovery procedures, because if the keys required to decrypt the file passwords for current and retained binary log files or relay log files are lost, it might not be possible to start the server.

The format of binary log encryption keys in the keyring is as follows:

MySQLReplicationKey_{UUID}_{SEQ_NO}

For example:

MySQLReplicationKey_00508583-b5ce-11e8-a6a5-0010e0734796_1

{UUID} is the true UUID generated by the MySQL server (the value of the server_uuid system variable). {SEQ_NO} is the sequence number for the binary log encryption key, which is incremented by 1 for each new key that is generated on the server.

The binary log encryption key that is currently in use on the server is called the binary log master key. The sequence number for the current binary log master key is stored in the keyring. The binary log master key is used to encrypt each new log file's file password, which is a randomly generated 32-byte file password specific to the log file that is used to encrypt the file data. The file password is encrypted using AES-CBC (AES Cipher Block Chaining mode) with the 256-bit binary log encryption key and a random initialization vector (IV), and is stored in the log file's file header. The file data is encrypted using AES-CTR (AES Counter mode) with a 256-bit key generated from the file password and a nonce also generated from the file password. It is technically possible to decrypt an encrypted file offline, if the binary log encryption key used to encrypt the file password is known, by using tools available in the OpenSSL cryptography toolkit.

The binlog_rotate_encryption_master_key_at_startup system variable controls whether the binary log master key is automatically rotated when the server is restarted. If this system variable is set to ON, a new binary log encryption key is generated and used as the new binary log master key whenever the server is restarted. If it is set to OFF, which is the default, the existing binary log master key is used again after the restart.

If you use file copying to clone a MySQL server instance that has encryption active so its binary log files and relay log files are encrypted, ensure that the keyring is also copied, so that the clone server can read the binary log encryption keys from the source server. When encryption is activated on the clone server (either at startup or subsequently), the clone server recognizes that the binary log encryption keys used with the copied files include the generated UUID of the source server. It automatically generates a new binary log encryption key using its own generated UUID, and uses this to encrypt the file passwords for subsequent binary log files and relay log files. The copied files continue to be read using the source server's keys.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.