How to manually decrypt an encrypted binary log file

The encrypted binary log file format introduced in MySQL version 8.0.14 was designed to allow a “manual” decryption of the file data when the value of the key that encrypted its file password is known.

Each encrypted binary (or relay) log file is composed by an encrypted binary log file header and the encrypted binary log content (the file data).

As shown in this blog post, encrypted binary log files have a “magic header” (file’s first four bytes) of 0xFD62696E to differentiate them from plain binary log files (that has 0xFE62696E as magic header).

Besides the magic header, the encrypted binary log file header contains all the information the server needs to fetch the the correct binary log encryption key from keyring and to decrypt file data:

  • The binary log encryption version: The version specifies the encrypted binary log header size, encryption keys sizes and ciphers used to protect the file password and the file data;
  • The binary log encryption key ID: The name of the key that protects the file password. This will be used to fetch the key from the keyring to decrypt the file password;
  • The encrypted file password;
  • The IV for encrypting/decrypting the file password.

Let’s take a look at an example:

The file data (the encrypted binary log file content) has exactly the same content as a plain binary log file (including the magic header), but it is encrypted according to binary log encryption version.

So, when the keyring key value for the key that protects a given encrypted binary log file is known, we can actually decrypt it by just using common Linux programs and openssl command line program. The script below has all the steps implemented.

In the script, lines 55 to 93 are fetching header content (magic, version, key ID, encrypted file password and IV). Lines 95 to 100 are decrypting the file password and lines 102 to 109 are retrieving the file key and IV to decrypt the file data.

Then, lines 111 to 115 are decrypting the file data into a new file. When using the correct keyring key value, this results in a plain binary log file (readable by mysqlbinlog client program too). To check if the file was decrypted correctly, lines 117 to 121 fetch the decrypted file magic and compares it with a plain binary log file magic.

Testing the script

Below is the result of a test using the script trying to display all the binary log file events positions from a given sample file. It was known that the keyring key value that protected the file password was filled only with zeroes.

 54,810 total views,  34 views today

About João Gramacho

João Gramacho is a Principal Software Developer for the MySQL Replication Core team at Oracle. Before joining Oracle, he worked for more than ten years with IT infrastructure support (servers hardware, operating systems, networks, management and security) and did a PhD in high-performance computing.

4 thoughts on “How to manually decrypt an encrypted binary log file

  1. Hey,

    Nice blog post !
    However, I could not figure out how to retrieve a self generated binlog encryption key, even knowing its key.

    Example :
    select * from keyring_keys;
    | MySQLReplicationKey_deac7747-9141-11e9-a566-000c29b7be28_1 | | |
    | MySQLReplicationKey_deac7747-9141-11e9-a566-000c29b7be28 | | |
    | MyShortKey | root@localhost | |
    3 rows in set (0,00 sec)

    How could I get the key for id ‘MySQLReplicationKey_deac7747-9141-11e9-a566-000c29b7be28_1’ ? Is there any way to achive that ?

    1. Hi!

      From a MySQL session, you cannot. This manual page ( contains a paragraph mentioning: “A key stored in the keyring by a given user can be manipulated later only by the same user. That is, the value of the CURRENT_USER() function at the time of key manipulation must have the same value as when the key was stored in the keyring. (This constraint rules out the use of the keyring UDFs for manipulation of instance-wide keys, such as those created by InnoDB to support tablespace encryption.)”

      This means, from security point of view, that no MySQL user can manipulate system keys (like those used by InnoDB tablespace encryption or binary log encryption).

      But there are ways of retrieving it from keyring (not in a MySQL session). In this ( blog post, Jesper Krogh shows a Python script able to fetch a given key from a file of ‘keyring_file’ plug-in. Quoting a paragraph of his post:

      “The keyring must be from the keyring_file plugin and using format version 2.0 (the format current as of MySQL 8.0.14). If you use a different keyring plugin, you can use the keyring migration feature ( to create a copy of the keyring using keyring_file. (But, please note that keyring_file is not a secure keyring format.)”

      The python script shows the code to fetch keys from the keyring.

Comments are closed.