In today's data-driven landscape, protecting your MySQL databases is paramount. Binary log encryption(called binlog encryption) in MySQL 8.0 offers a powerful solution to safeguard sensitive information. This feature is available from the MySQL version 8.0.14 or above.

Our previous blogs discussed about table space encryption in MySQL and Percona servers. In Mydbops, we are giving high importance about achieving security compliances.

The binary log records changes made to the databases so that it can be used to replicate the same to the slaves and also for the point in time recovery (PITR). So, it means that if someone has access to the binary logs, they can reproduce our entire database in many forms. As a DBA, we need to make sure that the binary log files are protected from users who are having access to the file system and also, log files need to be encrypted to follow the security compliance requirements by some clients.

These new features came as a rescue to satisfy those requirements. Now we will have a look at how to use and maintain it.

Note: To explore this feature, I have installed MySQL 8.0.18 in our testing environment.

Below are the topics that we are going to discuss in this blog:

1. Enabling Encryption
2. Rotating Key Manually
3. High Level Architecture
4. To access the encrypted binary log
5. To decrypt the encrypted binary log

Enabling Encryption

Let’s start this by enabling the binlog encryption. Let us list down the current binary logs and their status.

 
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       474 | No        |
+---------------+-----------+-----------+
1 row in set (0.02 sec)
	

MySQL Server has one binary log and it is not encrypted it is not encrypted by default.

To proceed further, make sure you are having SUPER or BINLOG_ENCRYPTION_ADMIN privilege for your user to enable the encryption online.

 
mysql> show global variables like 'binlog_encryption';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_encryption | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)
	

 
mysql> set global binlog_encryption=on;
 Query OK, 0 rows affected (0.18 sec)

mysql> show global variables like 'binlog_encryption';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_encryption | ON    |
+-------------------+-------+
1 row in set (0.17 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       497 | No        |
| binlog.000002 |       199 | No        |
| binlog.000003 |       667 | Yes       |
+---------------+-----------+-----------+
3 rows in set (0.03 sec)
	

Now, we can observe the latest binary log is encrypted. If the encrypted binary logs were the only one to be present in the server we can safely purge(remove) them.

 
mysql> purge binary logs to 'binlog.000003';
Query OK, 0 rows affected (0.07 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000003 |       667 | Yes       |
+---------------+-----------+-----------+
1 row in set (0.00 sec)
	

Currently, the server has encrypted binary logs. Also, we can observe the increase in the size of the encrypted binary log (empty) when compared with unencrypted ones. It is because of the 512 bytes encrypted header and the header is never replicated.

Rotating Encryption Key

The encryption of binary logs is made and it is time to rotate the encryption key. This must be done periodically to comply with security compliances. We can rotate the encryption key manually.

 
mysql> alter instance rotate binlog master key;
Query OK, 0 rows affected (0.36 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000003 |       711 | Yes       |
| binlog.000004 |       667 | Yes       |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
	

The below process is performed by the server while rotating the key.

1. The binary and relay log files are flushed (rotated).
2. 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.
3. Binary log encryption keys that are no longer in use for any files after the re-encryption process are removed from the keyring.

Also, we are having one more variable binlog_rotate_encryption_master_key_at_startup which ensures the binary log encryption key has to be rotated at server startup.

High-Level Architecture

Binlog Encryption is designed to use two-tier encryption.

1. File Passwords
2. Replication Encryption key

The File Password is used to encrypt/decrypt binary log content and Replication Encryption Key is used to encrypt/decrypt the file password in the encrypted binary log header.

A single replication encryption key may be used to encrypt/decrypt many binary and relay log files passwords, while a file password is intended to encrypt/decrypt a single binary or relay log file.

Multiple file passwords rely on the single encryption key whereas file password is used for the individual log files.

Okay, what happens if the encryption key REK1 is rotated?

The server generates a new replication encryption key REKj and it iterates over all encrypted log files to re-encrypt their password (Iterate from the last file to the first file ) by overwriting the encrypted file header with the new one. So, my new encryption key can decrypt all the available file passwords.

Compare the encrypted log file with unencrypted one

The only difference is the binlog header.

Here is the detailed header of the encrypted binlog:

Magic Number

It is needed to distinguish from an encrypted to an unencrypted binary log files.

An encrypted binlog file has 0xFD62696E . An unencrypted binlog file : 0xFE62696E

Form an encrypted log file:

 
[root@testing mysql]# hexdump binlog.000003 -n 4 -e '/1 "%02X"'
FD62696E
	

Form an unencrypted log file:

 
[root@gr1 mysql]# hexdump binlog.000001 -n 4 -e '/1 "%02X"'
FE62696E
	

Replication logs encryption version

The current value is 1. It might change in the future.

Replication encryption key ID

The key ID of the replication master key that encrypted the file password. It is a combination of MySQLReplicationKey, UUID, and SEQ_NO. It can be identified in the following format.

 
MySQLReplicationKey_{UUID}_{SEQ_NO}
Where MySQLReplicationKey is the prefix
      UUID is the MySQL server’s UUID that generated the key               SEQ_NO is the global replication master key sequence number.
	

Here is an example:

 
                                      |<-----Keyword------|<---------------UUID--------------->|SEQ_NO|                        
Keyring key ID for 'binlog.000003' is 'MySQLReplicationKey_d1deace2-24cc-11ea-a1db-0800270a0142_2'
	

Now Let us try rotating the encryption key.

 
mysql> alter instance rotate binlog master key;
Query OK, 0 rows affected (0.12 sec)
	

After successful rotation, the encryption key is

 
Keyring key ID for 'binlog.000003' is 'MySQLReplicationKey_d1deace2-24cc-11ea-a1db-0800270a0142_3'
	

We can observe that My SEQ_NO is incremented by 1. It will keep going on all the encryption key rotations.

Encrypted File Password

It is used to generate the key for encrypting/decrypting the binary log content.

IV for encrypting file password:

It is used with the replication master key to encrypt the file password.

Padding

Unused header space will be filled with 0.

How to access the encrypted binary log using mysqlbinlog utility ?

Sometimes, It is necessary to decode the binary log for PITR / to get to know the write pattern. Using mysqlbinlog, we can’t directly decode the encrypted binlog as it is not having access to the keyring file.

For example:

 
[root@testing mysql]# mysqlbinlog binlog.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ERROR: Reading encrypted log files directly is not supported.
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@testing mysql]#
	

But it is possible to get it by making a request to the MySQL server

 
[root@testing mysql]# mysqlbinlog -R --host localhost --user root binlog.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#191222 15:28:57 server id 1  end_log_pos 124 CRC32 0xcb35f1c9  Start: binlog v 4, server v 8.0.18 created 191222 15:28:57
BINLOG '
uYv/XQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgHJ8TXL
'/*!*/;
# at 124
#191222 15:28:57 server id 1  end_log_pos 155 CRC32 0xb7909c71  Previous-GTIDs
# [empty]
# at 155
#191223  1:55:30 server id 1  end_log_pos 199 CRC32 0x46148c5f  Rotate to binlog.000004  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
	

How to decrypt an encrypted binary log file?

The manual decryption of encrypted binary log is possible when the value of the key that encrypted its file password is known. We can use this amazing post https://mysql.wisborg.dk/2019/01/28/automatic-decryption-of-mysql-binary-logs-using-python/ to decrypt the binary log. To use this script, we need only have access to the keyring file.

Note: Even if we do not have enabled binary log on the server, we can still use it to encrypt relay log files on the slave servers.

Ready to elevate your MySQL security with binary log encryption? Mydbops offers expert guidance and comprehensive MySQL services to ensure optimal data protection. Contact us today to explore our tailored solutions!

{{cta}}