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:
|
1 |
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
#!/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:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
# 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.
|
1
2
3
4
5
6
|
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.
|
1
2
3
4
5
6
7
|
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.
|
1
2
3
4
5
6
7
8
9
10
11
|
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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.
|
1
2
3
4
5
|
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.
|
1
2
3
4
5
6
7
8
9
10
|
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!