Rotating binary log master key online

Starting on version 8.0.16, MySQL server introduces a new command that allows

for the binary log master key rotation, online!

When binary log encryption is enabled, the binary log master key can be rotated online by using the following new command:

ALTER INSTANCE ROTATE BINLOG MASTER KEY;

This new command can be used to rotate the binary log master key periodically or whenever you suspect that a key might have been compromised.

Executing the command generates a new binary log master key, used to

encrypt file passwords for the new binary and relay log files created after this command and to re-encrypt file passwords for existent encrypted binary and relay log files by overwriting the encrypted file header . The command also takes care of cleaning up the keyring, removing previously generated binary log encryption keys that aren’t used by the server, anymore. The command is not written to the binary log, so it is not replicated to a slave.

Inspecting the binary log master key

MySQL has no built in interface to show the binary log encryption keys in binary or relay log files. But the script inspect_encryption_key.sh can show the binary log encryption key ids in keyring from binary or relay log files. See below.

#!/usr/bin/env bash
 
set -e
set -o nounset
 
#
# Functions
#
 
function usage
{
  echo "Usage: $( basename ${0} ) "
  echo "Where:"
  echo "      <BINARY LOG FILES>:"
  echo "          The binary or relay log files to inspect the encryption key id in keyring."
  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 files to inspect."
 
echo "Log_name; Encrypted; Encryption key id in keyring"
 
while [ ${#} -ge 1 ]
do
 
  # ${BINLOG_FILE} is the encrypted binary log file
  BINLOG_FILE="${1}"
  shift
 
  [ ! -e "${BINLOG_FILE}" ] && error "Binary log file '${BINLOG_FILE}' not found."
 
  #
  # Decryption logic
  #
 
  MAGIC=$( hexdump -v -e '/1 "%02X"' ${BINLOG_FILE} -n 4 )
  if [ "${MAGIC}" == "FE62696E" ]
  then
    echo "${BINLOG_FILE}; No;"
    continue
  fi
 
  [ "${MAGIC}" != "FD62696E" ] && error "Found invalid magic '0x${MAGIC}' for 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 )
 
  echo "${BINLOG_FILE}; Yes; ${V1}"
done

Executing this script against a mix of encrypted and non-encrypted

binary logs, yields the following output:

# Using a wrong log name
/var/mysqld.1/data$ ~/script/inspect_encryption_key.sh wrong-log-bin.name
Log_name; Encrypted; Encryption key id in keyring
Error: Binary log file 'wrong-log-bin.name' not found.
 
# Now using the correct log names
/var/mysqld.1/data$ ~/script/inspect_encryption_key.sh master-bin.0* | column -t -s";"
Log_name            Encrypted   Encryption key id in keyring
master-bin.000001   No
master-bin.000002   Yes         MySQLReplicationKey_980bdde6-4ed1-11e9-b61c-4c34889dcb73_1
master-bin.000003   No
master-bin.000004   Yes         MySQLReplicationKey_980bdde6-4ed1-11e9-b61c-4c34889dcb73_1

Some Use Cases for Rotating Binary Log Keys

Now that we have a script that can help us look into the binary

log files and extract the key id, lets see what happens to the

files whenever we rotate the master key.

Example 1: binary log files master key rotation

Given a master that has some non-encrypted and encrypted binary logs as below.

var/mysqld.1/data$ ~/script/inspect_encryption_key.sh master-bin.0* | column -t -s";"
Log_name            Encrypted   Encryption key id in keyring
master-bin.000001   No
master-bin.000002   Yes         MySQLReplicationKey_980bdde6-4ed1-11e9-b61c-4c34889dcb73_1
master-bin.000003   No
master-bin.000004   Yes         MySQLReplicationKey_980bdde6-4ed1-11e9-b61c-4c34889dcb73_1

Executing ALTER INSTANCE ROTATE BINLOG MASTER KEY changes:

1) the key used to encrypt file passwords for new master-bin.000005

2) re-encrypts file passwords of previous existent encrypted master-bin.000002

and master-bin.000004 with the new binary log master key.

var/mysqld.1/data$ ~/script/inspect_encryption_key.sh master-bin.0* | column -t -s";"
Log_name            Encrypted   Encryption key id in keyring
master-bin.000001   No
master-bin.000002   Yes         MySQLReplicationKey_980bdde6-4ed1-11e9-b61c-4c34889dcb73_2
master-bin.000003   No
master-bin.000004   Yes         MySQLReplicationKey_980bdde6-4ed1-11e9-b61c-4c34889dcb73_2
master-bin.000005   Yes         MySQLReplicationKey_980bdde6-4ed1-11e9-b61c-4c34889dcb73_2

Example 2: relay and binary log files master key rotation

Given a slave that has some non-encrypted and encrypted relay and binary logs as below.

var/mysqld.2/data$ ~/script/inspect_encryption_key.sh slave-relay-bin.0* | column -t -s";"
Log_name                 Encrypted   Encryption key id in keyring
slave-relay-bin.000001   No
slave-relay-bin.000002   Yes         MySQLReplicationKey_2359klse-gfjk-ferj-tejr-fkjdfdkf3048_1
slave-relay-bin.000003   Yes         MySQLReplicationKey_2359klse-gfjk-ferj-tejr-fkjdfdkf3048_1
 
var/mysqld.2/data$ ~/script/inspect_encryption_key.sh slave-bin.0* | column -t -s";"
Log_name           Encrypted   Encryption key id in keyring
slave-bin.000001   No
slave-bin.000002   Yes         MySQLReplicationKey_2359klse-gfjk-ferj-tejr-fkjdfdkf3048_1
slave-bin.000003   Yes         MySQLReplicationKey_2359klse-gfjk-ferj-tejr-fkjdfdkf3048_1

Executing ALTER INSTANCE ROTATE BINLOG MASTER KEY changes:

1) the key used to encrypt file passwords for new slave-relay-bin.000004 and

slave-bin.000004

2) re-encrypts file passwords of previous existent encrypted slave-relay-bin.000002, slave-relay-bin.000003, slave-bin.000002 and slave-bin.000003 with the new binary log master key.

var/mysqld.2/data$ ~/script/inspect_encryption_key.sh slave-relay-bin.0* | column -t -s";"
Log_name                 Encrypted   Encryption key id in keyring
slave-relay-bin.000001   No
slave-relay-bin.000002   Yes         MySQLReplicationKey_2359klse-gfjk-ferj-tejr-fkjdfdkf3048_2
slave-relay-bin.000003   Yes         MySQLReplicationKey_2359klse-gfjk-ferj-tejr-fkjdfdkf3048_2
slave-relay-bin.000004   Yes         MySQLReplicationKey_2359klse-gfjk-ferj-tejr-fkjdfdkf3048_2
 
var/mysqld.2/data$ ~/script/inspect_encryption_key.sh slave-bin.0* | column -t -s";"
Log_name           Encrypted   Encryption key id in keyring
slave-bin.000001   No
slave-bin.000002   Yes         MySQLReplicationKey_2359klse-gfjk-ferj-tejr-fkjdfdkf3048_2
slave-bin.000003   Yes         MySQLReplicationKey_2359klse-gfjk-ferj-tejr-fkjdfdkf3048_2
slave-bin.000004   Yes         MySQLReplicationKey_2359klse-gfjk-ferj-tejr-fkjdfdkf3048_2

Example 3: binary and relay log files master key rotation when provisioning new slave

Given a server that was “copied” to become a slave with UUID2, which has some non-encrypted and encrypted binary logs with the source server UUID1 as below.

var/mysqld.1/data$ ~/script/inspect_encryption_key.sh master-bin.0* | column -t -s";"
Log_name            Encrypted   Encryption key id in keyring
master-bin.000001   No
master-bin.000002   Yes         MySQLReplicationKey_569bdde6-4ed1-11e9-b61c-4c34889dch09_1
master-bin.000003   Yes         MySQLReplicationKey_569bdde6-4ed1-11e9-b61c-4c34889dch09_1

Executing ALTER INSTANCE ROTATE BINLOG MASTER KEY changes:

1) the key used to encrypt file passwords for new slave-relay-bin.000001 and

master-bin.000004

2) re-encrypts file passwords of previous existent encrypted master-bin.000002

and master-bin.000003 with the new binary log master key as below.

var/mysqld.2/data$ ~/script/inspect_encryption_key.sh slave-relay-bin.0* | column -t -s";"
Log_name                 Encrypted   Encryption key id in keyring
slave-relay-bin.000001   Yes         MySQLReplicationKey_545dfkls-fdjy-fkyt-eror-lghgmlgh7485_2
 
var/mysqld.2/data$ ~/script/inspect_encryption_key.sh master-bin.0* | column -t -s";"
Log_name            Encrypted   Encryption key id in keyring
master-bin.000001   No
master-bin.000002   Yes         MySQLReplicationKey_545dfkls-fdjy-fkyt-eror-lghgmlgh7485_2
master-bin.000003   Yes         MySQLReplicationKey_545dfkls-fdjy-fkyt-eror-lghgmlgh7485_2
master-bin.000004   Yes         MySQLReplicationKey_545dfkls-fdjy-fkyt-eror-lghgmlgh7485_2

Summary

This feature introduces a new command, ‘ALTER INSTANCE ROTATE BINLOG MASTER KEY’, that allows the online rotation of the binary log master key. For design details, please see WL#12080 . This command is available from MySQL 8.0.16 release onwards, check our download page .

We look forward to hearing from you, leave us your feedback!

90 total views, 30 views today