Rechercher dans le manuel MySQL
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.
You enable this 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. Binary logging does not need to be enabled on the
server to enable encryption, so you can encrypt the relay log
files on a slave that has no binary log. To use encryption, a
keyring plugin must be installed and configured to supply MySQL
Server's keyring service. For instructions to do this, see
Section 6.4.4, “The MySQL Keyring”. Any supported keyring plugin can be
used to store binary log encryption keys.
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. The binary log encryption key that is currently in use on the server is called the binary log master key. The two tier encryption key architecture means that the binary log master key can be rotated (replaced by a new master key) as required, and only the file password for each file needs to be re-encrypted with the new master key, not the whole file. Relay log files are encrypted for all channels, including 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. The
BINLOG_ENCRYPTION_ADMIN privilege
is required to activate or deactivate encryption while the server
is running.
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.
When binary log files have been encrypted,
mysqlbinlog cannot read them directly, but can
read them from the server using the
--read-from-remote-server
option. From MySQL 8.0.14, mysqlbinlog returns
a suitable error if you attempt to read an encrypted binary log
file directly, but older versions of
mysqlbinlog do not recognise the file as a
binary log file at all. If you back up encrypted binary log files
using mysqlbinlog, note that the copies of the
files that are generated using mysqlbinlog are
stored in an unencrypted format.
Scope of binary log encryption
When binary log encryption is active for a MySQL server instance, the encryption coverage is as follows:
Data at rest that is written to the binary log files and relay log files is encrypted from the point in time where encryption is started, using the two tier encryption architecture described above. Existing binary log files and relay log files that were present on the server when you started encryption are not encrypted. You can purge these files when they are no longer needed.
Data in motion in the replication event stream, which is sent to MySQL clients including mysqlbinlog, is decrypted for transmission, and should therefore be protected in transit by the use of connection encryption (see Section 6.3, “Using Encrypted Connections”).
Data in use that is held in the binary log transaction and statement caches during a transaction is in unencrypted format in the memory buffer that stores the cache. The data is written to a temporary file on disk if it exceeds the space available in the memory buffer. From MySQL 8.0.17, when binary log encryption is active on the server, temporary files used to hold the binary log cache are encrypted using AES-CTR (AES Counter mode) for stream encryption. Because the temporary files are volatile and tied to a single process, they are encrypted using single-tier encryption, using a randomly generated file password and initialization vector that exist only in memory and are never stored on disk or in the keyring. After each transaction is committed, the binary log cache is reset: the memory buffer is cleared, any temporary file used to hold the binary log cache is truncated, and a new file password and initialization vector are randomly generated for use with the next transaction. This reset also takes place when the server is restarted after a normal shutdown or an unexpected halt.
If you use LOAD DATA when
binlog_format=STATEMENT is
set, which is not recommended as the statement is considered
unsafe for statement-based replication, a temporary file
containing the data is created on the replication slave where
the changes are applied. These temporary files are not
encrypted when binary log encryption is active on the server.
Use row-based or mixed binary logging format instead, which do
not create the temporary files.
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 keyring service (see Section 6.4.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.
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.
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.
When binary log encryption is enabled, you can rotate the binary
log master key at any time while the server is running by
issuing ALTER INSTANCE ROTATE BINLOG MASTER
KEY. When the binary log master key is rotated
manually using this statement, the passwords for the new and
subsequent files are encrypted using the new binary log master
key, and also the file passwords for existing encrypted binary
log files and relay log files are re-encrypted using the new
binary log master key, so the encryption is renewed completely.
You can rotate the binary log master key on a regular basis to
comply with your organization's security policy, and also if you
suspect that the current or any of the previous binary log
master keys might have been compromised.
When you rotate the binary log master key manually, MySQL Server takes the following actions in sequence:
A new binary log encryption key is generated with the next available sequence number, stored on the keyring, and used as the new binary log master key.
The binary log and relay log files are rotated on all channels.
The new binary log master key is used to encrypt the file passwords for the new binary log and relay log files, and subsequent files until the key is changed again.
The file passwords for existing encrypted binary log files and relay log files on the server are re-encrypted in turn using the new binary log master key, starting with the most recent files. Any unencrypted files are skipped.
Binary log encryption keys that are no longer in use for any files after the re-encryption process are removed from the keyring.
The BINLOG_ENCRYPTION_ADMIN
privilege is required to issue ALTER
INSTANCE ROTATE BINLOG MASTER KEY, and the statement
cannot be used if the
binlog_encryption system
variable is set to OFF.
As the final step of the binary log master key rotation process,
all binary log encryption keys that no longer apply to any
retained binary log files or relay log files are cleaned up from
the keyring. If a retained binary log file or relay log file
cannot be initialized for re-encryption, the relevant binary log
encryption keys are not deleted in case the files can be
recovered in the future. For example, this might be the case if
a file listed in a binary log index file is currently
unreadable, or if a channel fails to initialize. If the server
UUID changes, for example because a backup created using MySQL
Enterprise Backup is used to set up a new replication slave,
issuing ALTER INSTANCE ROTATE BINLOG MASTER
KEY on the new server does not delete any earlier
binary log encryption keys that include the original server
UUID.
If any of the first four steps of the binary log master key
rotation process cannot be completed correctly, an error message
is issued explaining the situation and the consequences for the
encryption status of the binary log files and relay log files.
Files that were previously encrypted are always left in an
encrypted state, but their file passwords might still be
encrypted using an old binary log master key. If you see these
errors, first retry the process by issuing
ALTER INSTANCE ROTATE BINLOG MASTER
KEY again. Then investigate the status of individual
files to see what is blocking the process, especially if you
suspect that the current or any of the previous binary log
master keys might have been compromised.
If the final step of the binary log master key rotation process
cannot be completed correctly, a warning message is issued
explaining the situation. The warning message identifies whether
the process could not clean up the auxiliary keys in the keyring
for rotating the binary log master key, or could not clean up
unused binary log encryption keys. You can choose to ignore the
message as the keys are auxiliary keys or no longer in use, or
you can issue ALTER INSTANCE ROTATE BINLOG
MASTER KEY again to retry the process.
If the server stops and is restarted with binary log encryption
still set to ON during the binary log master
key rotation process, new binary log files and relay log files
after the restart are encrypted using the new binary log master
key. However, the re-encryption of existing files is not
continued, so files that did not get re-encrypted before the
server stopped are left encrypted using the previous binary log
master key. To complete re-encryption and clean up unused binary
log encryption keys, issue ALTER INSTANCE
ROTATE BINLOG MASTER KEY again after the restart.
ALTER INSTANCE ROTATE BINLOG MASTER
KEY actions are not written to the binary log and are
not executed on replication slaves. Binary log master key
rotation can therefore be carried out in replication
environments including a mix of MySQL versions. To schedule
regular rotation of the binary log master key on all applicable
master and slave servers, you can enable the MySQL Event
Scheduler on each server and issue the
ALTER INSTANCE ROTATE BINLOG MASTER
KEY statement using a CREATE
EVENT statement. If you rotate the binary log master
key because you suspect that the current or any of the previous
binary log master keys might have been compromised, issue the
statement on every applicable master and slave server. Issuing
the statement on individual servers ensures that you can verify
immediate compliance, even in the case of slaves that are
lagging, belong to multiple replication topologies, or are not
currently active in the replication topology but have binary log
and relay log files.
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. When the
binary log master key is rotated at startup, the file passwords
for the new binary log and relay log files are encrypted using
the new key. The file passwords for the existing encrypted
binary log files and relay log files are not re-encrypted, so
they remain encrypted using the old key, which remains available
on the keyring.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-replication-binlog-encryption.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.
