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.

#!/usr/bin/env bash

set -e
set -o nounset


#
# Functions
#

function usage
{
  echo "Usage: $( basename ${0} ) <BINARY LOG FILE> [<KEYRING KEY VALUE>]"
  echo "Where:"
  echo "      <BINARY LOG FILE>:"
  echo "          The binary or relay log file to be decrypted."
  echo "      <KEYRING KEY VALUE>:"
  echo "          The keyring key value to decrypt the file."
  echo "          It shall be passed in hexadecimal notation."
  echo "          If not specified, the program will display the key ID that."
  echo "          is required to decrypt the file."
  exit 1
}

function error
{
  echo "Error: ${1}" >> /dev/stderr
  exit 1
}

function error_and_usage
{
  echo "Error: ${1}" >> /dev/stderr
  echo ""
  usage
}


#
# Parameters sanity check
#

[ ${#} -lt 1 ] && error_and_usage "Please specify the binary log file to decrypt."
[ ${#} -gt 2 ] && error_and_usage "Too many parameters."

# ${BINLOG_FILE} is the encrypted binary log file
BINLOG_FILE="${1}"

[ ! -e "${BINLOG_FILE}" ] && error "Binary log file '${BINLOG_FILE}' not found."

KEYRING_KEY_VALUE=
[ ${#} -eq 2 ] && KEYRING_KEY_VALUE="${2}"


#
# Decryption logic
#

MAGIC=$( hexdump -v -e '/1 "%02X"' ${BINLOG_FILE} -n 4 )
[ "${MAGIC}" != "FD62696E" ] && error "Found invalid magic '0x${MAGIC}' for encrypted binlog file."

VERSION=$( hexdump -v -e '/1 "%i"' ${BINLOG_FILE} -s 4 -n 1 )
[ "${VERSION}" != "1" ] && error "Unsupported binary log encrypted version '${VERSION}'."

OFFSET=5

# First header field is a TLV: the keyring key ID
T1=$( hexdump -v -e '/1 "%i"' ${BINLOG_FILE} -s ${OFFSET} -n 1 )
[ ${T1} -ne 1 ] && error "Invalid field type (${T1}). Keyring key ID (1) was expected."
((OFFSET++))
L1=$( hexdump -v -e '/1 "%i"' ${BINLOG_FILE} -s $OFFSET -n 1 )
((OFFSET++))
V1=$( dd if=${BINLOG_FILE} of=/dev/stdout bs=1 skip=$OFFSET count=${L1} 2> /dev/null )
[ "${KEYRING_KEY_VALUE}" == "" ] && echo "Keyring key ID for '${BINLOG_FILE}' is '${V1}'" && exit 0

OFFSET=$(( ${OFFSET} + ${L1} ))

# Second header field is a TV: the encrypted file password
T2=$( hexdump -v -e '/1 "%i"' ${BINLOG_FILE} -s ${OFFSET} -n 1 )
[ ${T2} -ne 2 ] && error "Invalid field type (${T2}). Encrypted file password (2) was expected."
((OFFSET++))
L2=32
V2=$( hexdump -v -e '/1 "%02X"' ${BINLOG_FILE} -s $OFFSET -n ${L2} )
dd if=${BINLOG_FILE} of=encrypted_file_password bs=1 skip=$OFFSET count=${L2} 2> /dev/null

OFFSET=$(( ${OFFSET} + ${L2} ))

# Third header field is a TV: the IV to decrypt the file password
T3=$( hexdump -v -e '/1 "%i"' ${BINLOG_FILE} -s ${OFFSET} -n 1 )
[ ${T3} -ne 3 ] && error "Invalid field type (${T3}). IV to decrypt file password (3) was expected."
((OFFSET++))
L3=16
V3=$( hexdump -v -e '/1 "%02X"' ${BINLOG_FILE} -s $OFFSET -n ${L3} )

OFFSET=$(( ${OFFSET} + ${L3} ))

# Decrypt the file password
openssl enc -d -aes-256-cbc -K "${KEYRING_KEY_VALUE}" -iv "${V3}" -nopad -in encrypted_file_password -out file_password
rm encrypted_file_password
FILE_PASSWORD=$( hexdump -v -e '/1 "%02X"' file_password )

# Generate the file key and IV
openssl enc -aes-256-cbc -md sha512 -kfile file_password -nosalt -P > file_key_and_iv
rm file_password
FILE_KEY=$( grep 'key' file_key_and_iv | cut -d"=" -f2 )
IV=$( grep 'iv' file_key_and_iv | cut -d"=" -f2 )
rm file_key_and_iv
# Remove the "counter" (64 bits) from the IV
IV=${IV:0:16}

# Decrypt the file data (the binary log content)
dd if=${BINLOG_FILE} of="headless-${BINLOG_FILE}" bs=1 skip=512 2> /dev/null
COUNTER=0000000000000000
openssl enc -d -aes-256-ctr -K "${FILE_KEY}" -iv "${IV}${COUNTER}" -nosalt -in "headless-${BINLOG_FILE}" -out "plain-${BINLOG_FILE}"
rm "headless-${BINLOG_FILE}"

# Check decrypted binary log magic
MAGIC=$( hexdump -v -e '/1 "%02X"' "plain-${BINLOG_FILE}" -n 4 )
[ "${MAGIC}" != "FE62696E" ] && error "Found invalid magic '0x${MAGIC}' for decrypted binlog file."

echo "'${BINLOG_FILE}' was successfully decrypted as 'plain-${BINLOG_FILE}'".

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

#!/usr/bin/env bash

set -e

set -o nounset

# Functions

function usage

{

echo "Usage: $( basename ${0} ) <BINARY LOG FILE> [<KEYRING KEY VALUE>]"

echo "Where:"

echo " <BINARY LOG FILE>:"

echo " The binary or relay log file to be decrypted."

echo " <KEYRING KEY VALUE>:"

echo " The keyring key value to decrypt the file."

echo " It shall be passed in hexadecimal notation."

echo " If not specified, the program will display the key ID that."

echo " is required to decrypt the file."

exit 1

}

function error

{

echo "Error: ${1}" >> /dev/stderr

exit 1

}

function error_and_usage

{

echo "Error: ${1}" >> /dev/stderr

echo ""

usage

}

# Parameters sanity check

[ ${#} -lt 1 ] && error_and_usage "Please specify the binary log file to decrypt."

[ ${#} -gt 2 ] && error_and_usage "Too many parameters."

# ${BINLOG_FILE} is the encrypted binary log file

BINLOG_FILE="${1}"

[ ! -e "${BINLOG_FILE}" ] && error "Binary log file '${BINLOG_FILE}' not found."

KEYRING_KEY_VALUE=

[ ${#} -eq 2 ] && KEYRING_KEY_VALUE="${2}"

# Decryption logic

MAGIC=$( hexdump -v -e '/1 "%02X"' ${BINLOG_FILE} -n 4 )

[ "${MAGIC}" != "FD62696E" ] && error "Found invalid magic '0x${MAGIC}' for encrypted binlog file."

VERSION=$( hexdump -v -e '/1 "%i"' ${BINLOG_FILE} -s 4 -n 1 )

[ "${VERSION}" != "1" ] && error "Unsupported binary log encrypted version '${VERSION}'."

OFFSET=5

# First header field is a TLV: the keyring key ID

T1=$( hexdump -v -e '/1 "%i"' ${BINLOG_FILE} -s ${OFFSET} -n 1 )

[ ${T1} -ne 1 ] && error "Invalid field type (${T1}). Keyring key ID (1) was expected."

((OFFSET++))

L1=$( hexdump -v -e '/1 "%i"' ${BINLOG_FILE} -s $OFFSET -n 1 )

((OFFSET++))

V1=$( dd if=${BINLOG_FILE} of=/dev/stdout bs=1 skip=$OFFSET count=${L1} 2> /dev/null )

[ "${KEYRING_KEY_VALUE}" == "" ] && echo "Keyring key ID for '${BINLOG_FILE}' is '${V1}'" && exit 0

OFFSET=$(( ${OFFSET} + ${L1} ))

# Second header field is a TV: the encrypted file password

T2=$( hexdump -v -e '/1 "%i"' ${BINLOG_FILE} -s ${OFFSET} -n 1 )

[ ${T2} -ne 2 ] && error "Invalid field type (${T2}). Encrypted file password (2) was expected."

((OFFSET++))

L2=32

V2=$( hexdump -v -e '/1 "%02X"' ${BINLOG_FILE} -s $OFFSET -n ${L2} )

dd if=${BINLOG_FILE} of=encrypted_file_password bs=1 skip=$OFFSET count=${L2} 2> /dev/null

OFFSET=$(( ${OFFSET} + ${L2} ))

# Third header field is a TV: the IV to decrypt the file password

T3=$( hexdump -v -e '/1 "%i"' ${BINLOG_FILE} -s ${OFFSET} -n 1 )

[ ${T3} -ne 3 ] && error "Invalid field type (${T3}). IV to decrypt file password (3) was expected."

((OFFSET++))

L3=16

V3=$( hexdump -v -e '/1 "%02X"' ${BINLOG_FILE} -s $OFFSET -n ${L3} )

OFFSET=$(( ${OFFSET} + ${L3} ))

# Decrypt the file password

openssl enc -d -aes-256-cbc -K "${KEYRING_KEY_VALUE}" -iv "${V3}" -nopad -in encrypted_file_password -out file_password

rm encrypted_file_password

FILE_PASSWORD=$( hexdump -v -e '/1 "%02X"' file_password )

# Generate the file key and IV

openssl enc -aes-256-cbc -md sha512 -kfile file_password -nosalt -P > file_key_and_iv

rm file_password

FILE_KEY=$( grep 'key' file_key_and_iv | cut -d"=" -f2 )

IV=$( grep 'iv' file_key_and_iv | cut -d"=" -f2 )

rm file_key_and_iv

# Remove the "counter" (64 bits) from the IV

IV=${IV:0:16}

# Decrypt the file data (the binary log content)

dd if=${BINLOG_FILE} of="headless-${BINLOG_FILE}" bs=1 skip=512 2> /dev/null

COUNTER=0000000000000000

openssl enc -d -aes-256-ctr -K "${FILE_KEY}" -iv "${IV}${COUNTER}" -nosalt -in "headless-${BINLOG_FILE}" -out "plain-${BINLOG_FILE}"

rm "headless-${BINLOG_FILE}"

# Check decrypted binary log magic

MAGIC=$( hexdump -v -e '/1 "%02X"' "plain-${BINLOG_FILE}" -n 4 )

[ "${MAGIC}" != "FE62696E" ] && error "Found invalid magic '0x${MAGIC}' for decrypted binlog file."

echo "'${BINLOG_FILE}' was successfully decrypted as 'plain-${BINLOG_FILE}'".

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.

/var/mysqld/data: mysqlbinlog master-bin.000002 | grep "^# at " 
ERROR: Reading encrypted log files directly is not supported.

/var/mysqld/data: # Using a wrong key value
/var/mysqld/data: decrypt_binlog.sh master-bin.000002 0000000000000000000000000000000000000000000000000000000000000001
Error: Found invalid magic '0xDB3B3BFA' for decrypted binlog file.

/var/mysqld/data: # Now using the correct key value
/var/mysqld/data: decrypt_binlog.sh master-bin.000002 0000000000000000000000000000000000000000000000000000000000000000
'master-bin.000002' was successfully decrypted as 'plain-master-bin.000002'.

/var/mysqld/data: mysqlbinlog plain-master-bin.000002 | grep "^# at " 
# at 4
# at 124
# at 155
# at 228
# at 352
# at 427
# at 504
# at 552
# at 602
# at 633
# at 708
# at 785
# at 833
# at 883
# at 914
# at 987

/var/mysqld/data: mysqlbinlog master-bin.000002 | grep "^# at "

ERROR: Reading encrypted log files directly is not supported.

/var/mysqld/data: # Using a wrong key value

/var/mysqld/data: decrypt_binlog.sh master-bin.000002 0000000000000000000000000000000000000000000000000000000000000001

Error: Found invalid magic '0xDB3B3BFA' for decrypted binlog file.

/var/mysqld/data: # Now using the correct key value

/var/mysqld/data: decrypt_binlog.sh master-bin.000002 0000000000000000000000000000000000000000000000000000000000000000

'master-bin.000002' was successfully decrypted as 'plain-master-bin.000002'.

/var/mysqld/data: mysqlbinlog plain-master-bin.000002 | grep "^# at "

# at 4

# at 124

# at 155

# at 228

# at 352

# at 427

# at 504

# at 552

# at 602

# at 633

# at 708

# at 785

# at 833

# at 883

# at 914

# at 987