Data security has always been important for any organization handling sensitive information. Data-at-rest encryption is one of the key things used to protect stored data, ensuring that unauthorized users cannot access or read the data even if they gain access to the physical storage medium.
In this post, we’ll dive into how MySQL implements data-at-rest encryption, specifically highlighting the Keyring Plugin used for secure encryption key management. We’ll also explore why it’s vital to keep sensitive data encrypted by walking through practical, hands-on examples.
So what is Data-at-rest? It is data stored on persistent media, such as hard drives, and it is opposed to data over network transmission, called Data-in-transit. So encryption at rest means that data is encrypted while being stored, so that unauthorized users who got access to the storage device will not be able to read the data.
This post will provide practical examples and step-by-step guidelines for setting up encryption, managing encryption keys, and verifying encrypted data.
Test environment/data
My test environment is prepared in Docker, with two hosts: a source and a replica with MySQL Server Community Edition v.8.0.40 (the latest version).
$ docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 0fbe03459348 mysql:8.0.40-debian "docker-entrypoint.s…" 15 seconds ago Up 3 seconds (health: starting) 33060/tcp, 0.0.0.0:3308->3306/tcp replica b391109eafcc mysql:8.0.40-debian "docker-entrypoint.s…" 15 seconds ago Up 14 seconds (healthy) 33060/tcp, 0.0.0.0:3307->3306/tcp source
Encryption is enabled by default on both hosts, see Using the component_keyring_file File-Based Keyring Component.
--default-table-encryption=ON --binlog_encryption=ON --innodb_redo_log_encrypt=ON --innodb_undo_log_encrypt=ON
Let’s verify that:
source> SELECT @@hostname, @@global.binlog_encryption, @@global.innodb_redo_log_encrypt, @@global.innodb_undo_log_encrypt, @@global.default_table_encryption\G *************************** 1. row *************************** @@hostname: source @@global.binlog_encryption: 1 @@global.innodb_redo_log_encrypt: 1 @@global.innodb_undo_log_encrypt: 1 @@global.default_table_encryption: 1 1 row in set (0.00 sec)
replica> SELECT @@hostname, @@global.binlog_encryption, @@global.innodb_redo_log_encrypt, @@global.innodb_undo_log_encrypt, @@global.default_table_encryption\G *************************** 1. row *************************** @@hostname: replica @@global.binlog_encryption: 1 @@global.innodb_redo_log_encrypt: 1 @@global.innodb_undo_log_encrypt: 1 @@global.default_table_encryption: 1 1 row in set (0.00 sec)
I’ve also prepared test data containing sensitive information across two tables, one with encryption enabled and the other without it.
-- Create and use the test database USE testdb; -- Create tables with sensitive data DROP TABLE IF EXISTS tbl_encrypted; CREATE TABLE IF NOT EXISTS tbl_encrypted ( id INT PRIMARY KEY, email VARCHAR(100), gov_id VARCHAR(20), credit_card VARCHAR(20) ) ENGINE=InnoDB, ENCRYPTION='Y'; CREATE TABLE tbl_not_encrypted LIKE tbl_encrypted; ALTER TABLE tbl_not_encrypted ENCRYPTION='N'; -- Insert some rows with fake sensitive data INSERT INTO tbl_encrypted (id, email, gov_id, credit_card) VALUES (1, 'johndoe\@example.com', 'ID123456789', '4111111111111111'), (2, 'janedoe\@example.com', 'ID987654321', '5500000000000004'), (3, 'bobsmith\@example.com', 'ID111222333', '340000000000009'), (4, 'alicejones\@example.com', 'ID444555666', '30000000000004'); INSERT INTO tbl_not_encrypted SELECT * FROM tbl_encrypted;
Read tablespace
Imagine you’ve performed binary backup and decided to store the files in some external location away from the database host. Now, let’s compare two tablespaces and make an attempt to read information from them using the following command:
strings /var/lib/mysql/testdb/tbl_encrypted.ibd | head -c 128
Encrypted
root@replica:/# strings /var/lib/mysql/testdb/tbl_encrypted.ibd | head -c 128
2b2b54e7-96f2-11ef-8a6c-0242ac14010c
nm8o
L5Z`
TZOh
xoU{
/l+#<8
F.'
~2Q[
J?$f
^cnE|x
dh[?
TDY2
8T"s
L)n5
JK~lF0
g(r,\
=g8&
2h L
Not Encrypted
root@replica:/# strings /var/lib/mysql/testdb/tbl_not_encrypted.ibd
Einfimum
supremum
cd[o.
>*qS
nOnf}t
*T/#
"Z<D
Sn\1
H^O=F'
J@EVW
W='O
*]I=Q
vAU'
5p10
/^L
KYqa
0c<`
nM{n
,8kX'
/J;LA
?\h' 5
>p~(
infimum
supremum
johndoe\@example.comID1234567894111111111111111
janedoe\@example.comID9876543215500000000000004
*bobsmith\@example.comID111222333340000000000009
7alicejones\@example.comID44455566630000000000004
Summary of Reading Encrypted vs. Non-Encrypted Tablespaces
In contrast to the encrypted tablespace, reading from the non-encrypted one reveals plaintext data, including identifiable information such as emails and IDs. This demonstrates the risks of storing sensitive information without encryption, as it remains accessible to anyone with access to the storage location.
What about Binary Logs?
Now, let’s discuss binary logs. These logs can also be stored externally, offering a valuable means for analyzing and auditing database changes, as well as supporting point-in-time recovery. Here is the list of binary logs from our replica:
source> show binary logs\G *************************** 1. row *************************** Log_name: source-bin.000001 File_size: 692 Encrypted: Yes *************************** 2. row *************************** Log_name: source-bin.000002 File_size: 2942090 Encrypted: Yes *************************** 3. row *************************** Log_name: source-bin.000003 File_size: 709 Encrypted: Yes 3 rows in set (0.00 sec)
As you can see, all three binary logs have been encrypted.
The binary log source-bin.000002 contains more than 14K events.
source> \P grep -E "[0-9]+ rows" PAGER set to 'grep -E "[0-9]+ rows"' source> SHOW BINLOG EVENTS IN 'source-bin.000002'; 14192 rows in set (0.04 sec)
Let’s attempt to read one of them using the mysqlbinlog utility.
root@source:/# mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/source-bin.000002 # The proper term is pseudo_replica_mode, but we use this compatibility alias # to make the statement usable on server versions 8.0.24 and older. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ERROR: Reading encrypted log files directly is not supported. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
This demonstrates that reading an encrypted binary log isn’t straightforward.
Why encrypting binary logs on all hosts is important?
Let’s disable binary logs encryption on our replica.
replica> set global binlog_encryption=0; Query OK, 0 rows affected (0.06 sec) replica> select @@global.binlog_encryption; +----------------------------+ | @@global.binlog_encryption | +----------------------------+ | 0 | +----------------------------+ 1 row in set (0.00 sec) replica> flush logs; Query OK, 0 rows affected (0.03 sec)
And insert more data on our source.
source> INSERT INTO testdb.tbl_encrypted (id, email, gov_id, credit_card) VALUES -> (5, 'michaelwhite\@example.com', 'ID555666777', '6011111111111117'), -> (6, 'sarahbrown\@example.com', 'ID888999000', '3530111333300000'); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0
Let’s check binary logs on our replica and read the events from the non-encrypted one.
replica> SHOW BINARY LOGS\G *************************** 1. row *************************** Log_name: replica-bin.000001 File_size: 692 Encrypted: Yes *************************** 2. row *************************** Log_name: replica-bin.000002 File_size: 2940072 Encrypted: Yes *************************** 3. row *************************** Log_name: replica-bin.000003 File_size: 2942244 Encrypted: Yes *************************** 4. row *************************** Log_name: replica-bin.000004 File_size: 286 Encrypted: No *************************** 5. row *************************** Log_name: replica-bin.000005 File_size: 654 Encrypted: No 5 rows in set (0.01 sec)
Let’s read events:
replica> SHOW BINLOG EVENTS IN 'replica-bin.000005'\G ... *************************** 5. row *************************** Log_name: replica-bin.000005 Pos: 395 Event_type: Table_map Server_id: 1 End_log_pos: 470 Info: table_id: 92 (testdb.tbl_encrypted) *************************** 6. row *************************** Log_name: replica-bin.000005 Pos: 470 Event_type: Write_rows Server_id: 1 End_log_pos: 623 Info: table_id: 92 flags: STMT_END_F ...
Now, let’s attempt to read the binary log file directly:
root@replica:/# mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/replica-bin.000005 | grep -A5 INSERT ### INSERT INTO `testdb`.`tbl_encrypted` ### SET ### @1=5 ### @2='michaelwhite\@example.com' ### @3='ID555666777' ### @4='6011111111111117' ### INSERT INTO `testdb`.`tbl_encrypted` ### SET ### @1=6 ### @2='sarahbrown\@example.com' ### @3='ID888999000' ### @4='3530111333300000'
As shown, the binary log on the replica is readable.
Summary
Unencrypted tablespaces and binary logs leave sensitive data vulnerable, as they can expose details like emails, IDs to anyone with access to storage files. Encrypting both tablespaces and logs is essential to protect them against unauthorized access, ensuring that data remains secure and compliant with privacy regulations, even when backups or logs are stored externally. Prioritizing encryption helps safeguard critical information and strengthens your organization’s data security posture.
P.S.
We’ll also be hosting a free webinar on MySQL encryption at 4 PM GMT November, 27, please join us to learn more!
0 Comments