Decrypt MySQL binary log

decrypt_binlog.sh

#!/usr/bin/env bash

#
# Reference:
# https://dev.mysql.com/blog-archive/binary-log-encryption-at-rest/
# https://dev.mysql.com/blog-archive/how-to-manually-decrypt-an-encrypted-binary-log-file/
#

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}'".