As I discussed in some of my recent talks at conferences (at the DOAG for example), MySQL 8 came out with new features which bring lots of improvements in terms of security.
“At-Rest” encryption has been existing from some releases by now:
– InnoDB Tablespace Encryption: by 5.7.11
– Redo and Undo Log Data Encryption: by 8.0.1
Now starting from version 8.0.14, you can also encrypt binary and relay log files. In this blog post we will see how to configure that and we will do some tests.
Case 1: Binary log files are not encrypted
Binary log files encryption is disables by default:
mysql> show variables like 'binlog_encryption'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | binlog_encryption | OFF | +-------------------+-------+ 1 row in set (0.02 sec)
With this configuration, we could extract some sensitive information with some simple OS commands without connecting to the database, which means that if an OS user account is compromised we could have some important security issues.
First of all I create a database and a table and insert in it some sensitive information:
mysql> create database cards; Query OK, 1 row affected (0.01 sec) mysql> use cards; Database changed mysql> CREATE TABLE cards.banking_card (id int (128), day int(2), month int(2), year int(4), type varchar(128), code varchar(128)); Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO cards.banking_card VALUES (1, 8, 3, 1984, 'secret code', '01-234-5678'); Query OK, 1 row affected (0.01 sec)
I check which is the binary log file which is currently in use:
mysql> SHOW BINARY LOGS; +--------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +--------------------+-----------+-----------+ | mysqld8-bin.000001 | 1384 | No | | mysqld8-bin.000002 | 178 | No | | mysqld8-bin.000003 | 974 | No | +--------------------+-----------+-----------+ mysql> SHOW BINLOG EVENTS IN 'mysqld8-bin.000003'; +--------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +--------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | mysqld8-bin.000003 | 4 | Format_desc | 8 | 124 | Server ver: 8.0.15, Binlog ver: 4 | | mysqld8-bin.000003 | 124 | Previous_gtids | 8 | 155 | | | mysqld8-bin.000003 | 155 | Anonymous_Gtid | 8 | 232 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysqld8-bin.000003 | 232 | Query | 8 | 341 | create database cards /* xid=17 */ | | mysqld8-bin.000003 | 341 | Anonymous_Gtid | 8 | 420 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysqld8-bin.000003 | 420 | Query | 8 | 635 | use `cards`; CREATE TABLE cards.banking_card (id int (128), day int(2), month int(2), year int(4), type varchar(128), code varchar(128)) /* xid=23 */ | | mysqld8-bin.000003 | 635 | Anonymous_Gtid | 8 | 714 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysqld8-bin.000003 | 714 | Query | 8 | 790 | BEGIN | | mysqld8-bin.000003 | 790 | Table_map | 8 | 865 | table_id: 66 (cards.banking_card) | | mysqld8-bin.000003 | 865 | Write_rows | 8 | 943 | table_id: 66 flags: STMT_END_F | | mysqld8-bin.000003 | 943 | Xid | 8 | 974 | COMMIT /* xid=24 */ | +--------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ 11 rows in set (0.00 sec)
With these 2 following commands for example (od to dump files in octal and other formats and xxd to make an hexdump) I can easily read the content of my binary log file:
# od -c mysqld8-bin.000003 0001440 a 003 B 001 0001460 005 c a r d s 016 b a n k i n g 0001500 _ c a r d ? 006 003 003 003 003 017 017 004 0001520 002 002 ? 001 001 002 003 374 377 005 224 022 0001540 332 375 ~ n 036 b N 257 003 0001560 B 001 002 006 377 0001600 001 b 003 300 a 0001620 v s e c r e t c o d e v 0001640 0 1 - 2 3 4 - 5 6 7 8 9 N _ 312 375 # xxd mysqld8-bin.000003 00001f0: ff00 1300 6361 7264 7300 4352 4541 5445 ....cards.CREATE 0000200: 2054 4142 4c45 2063 6172 6473 2e63 6172 TABLE cards.ban 0000210: 645f 656e 6372 7970 7465 6420 2869 6420 king_card (id 0000220: 696e 7420 2831 3238 292c 2064 6179 2069 int (128), day i 0000230: 6e74 2832 292c 206d 6f6e 7468 2069 6e74 nt(2), month int 0000240: 2832 292c 2079 6561 7220 696e 7428 3429 (2), year int(4) 0000250: 2c20 7479 7065 2076 6172 6368 6172 2831 , type varchar(1 0000260: 3238 292c 2063 6f64 6520 7661 7263 6861 28), code varcha 0000270: 7228 3132 3829 29f5 771f aafd 7e6e 5c22 r(128)).w...~n" 0000280: 0800 0000 4f00 0000 ca02 0000 0000 0000 ....O........... 0000290: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 00002a0: 0000 0000 0000 0002 0200 0000 0000 0000 ................ 00002b0: 0300 0000 0000 0000 e692 3509 6582 05fc ..........5.e... 00002c0: 5301 8f38 0100 9993 56a3 fd7e 6e5c 0208 S..8....V..~n.. 00002d0: 0000 004c 0000 0016 0300 0008 000b 0000 ...L............ 00002e0: 0000 0000 0005 0000 1d00 0000 0000 0001 ................ 00002f0: 2000 a045 0000 0000 0603 7374 6404 ff00 ..E......std... 0000300: ff00 ff00 12ff 0063 6172 6473 0042 4547 .......cards.BEG 0000310: 494e 0567 d2c2 fd7e 6e5c 1308 0000 004b IN.g...~n.....K 0000320: 0000 0061 0300 0000 0042 0000 0000 0001 ...a.....B...... 0000330: 0005 6361 7264 7300 0e63 6172 645f 656e ..cards..banking 0000340: 6372 7970 7465 6400 0603 0303 030f 0f04 _card........... 0000350: 0002 0002 3f01 0100 0203 fcff 0005 9412 ....?........... 0000360: dafd 7e6e 5c1e 0800 0000 4e00 0000 af03 ..~n.....N..... 0000370: 0000 0000 4200 0000 0000 0100 0200 06ff ....B........... 0000380: 0001 0000 0008 0000 0003 0000 00c0 0700 ................ 0000390: 000b 0073 6563 7265 7420 636f 6465 0b00 ...secret code.. 00003a0: 3031 2d32 3334 2d35 3637 3839 4e5f cafd 01-234-56789N_.. 00003b0: 7e6e 5c10 0800 0000 1f00 0000 ce03 0000 ~n............. 00003c0: 0000 1800 0000 0000 0000 f9d0 d057 .............W
Yes, that’s not good news.
Case 2: Binary log files are encrypted
Now let’s try to activate encryption through the following steps:
1) Activate a keyring plugin for the master key management (for the Community Edition, it’s called keyring_file and it stores keyring data in a local file):
# mysqld_multi stop 8 mysqld_multi log file version 2.16; run: Thu Feb 21 10:54:19 2019 Stopping MySQL servers # mysqld_multi --defaults-file=/u01/app/mysql/admin/mysqld8/etc/my.cnf start 8 > /dev/null 2>&1 # ps -eaf|grep mysqld mysql 3362 1 13 10:58 pts/0 00:00:00 /u01/app/mysql/product/mysql-8.0.15/bin/mysqld --port=33008 --socket=/u01/app/mysql/admin/mysqld8/socket/mysqld8.sock --pid-file=/u01/app/mysql/admin/mysqld8/socket/mysqld8.pid --log-error=/u01/app/mysql/admin/mysqld8/log/mysqld8.err --datadir=/u02/mysqldata/mysqld8/ --basedir=/u01/app/mysql/product/mysql-8.0.15/ --slow_query_log=0 --slow_query_log_file=/u01/app/mysql/admin/mysqld8/log/mysqld8-slow-query.log --log-bin=/u01/app/mysql/admin/mysqld8/binlog/mysqld8-bin --innodb_flush_log_at_trx_commit=1 --sync_binlog=1 --local-infile=0 --general_log=0 --general_log_file=/u01/app/mysql/admin/mysqld8/log/mysqld8.log --lc_messages_dir=/u01/app/mysql/product/mysql-8.0.15/share/ --lc_messages=en_US --server-id=8 --log_timestamps=SYSTEM --early-plugin-load=keyring_file.so
The /u01/app/mysql/admin/mysqld8/etc/my.cnf file is defined as follows:
[mysqld8] port = 33008 mysqladmin = /u01/app/mysql/product/mysql-8.0.15/bin/mysqladmin ... server-id = 8 early-plugin-load = keyring_file.so
2) Turn on the binlog_encryption variable:
mysql> SET PERSIST binlog_encryption=ON; Query OK, 0 rows affected (0.03 sec)
At this point, encryption is enabled:
mysql> flush logs; Query OK, 0 rows affected (0.05 sec) mysql> SHOW BINARY LOGS; +--------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +--------------------+-----------+-----------+ | mysqld8-bin.000001 | 1384 | No | | mysqld8-bin.000002 | 178 | No | | mysqld8-bin.000003 | 1023 | No | | mysqld8-bin.000004 | 716 | Yes | | mysqld8-bin.000005 | 667 | Yes | +--------------------+-----------+-----------+
I insert again some data into my table:
mysql> use cards; Database changed mysql> INSERT INTO cards.banking_card VALUES (2, 5, 9, 1986, 'secret code', '01-234-5678'); Query OK, 1 row affected (0.02 sec)
I can try to extract some information from the binary log files on disk:
# xxd mysqld8-bin.000005 ... 0000250: 2796 8d0c 9171 7109 df65 2434 9d0e 4f40 '....qq..e$4..O@ 0000260: e024 07e8 9db7 ae84 f0d5 5728 90d4 905f .$........W(..._ 0000270: 9cc4 6c33 d4e1 5839 aa1f 97bb af04 b24d ..l3..X9.......M 0000280: e36d dd05 3d0c f9d8 fbee 2379 2b85 2744 .m..=.....#y+.'D 0000290: efe4 29cb 3eff 03b8 b934 ec6b 4e9c 9189 ..).>....4.kN... 00002a0: d14b 402c 3d80 effe c34d 0d27 3be7 b427 .K@,=....M.';..' 00002b0: 5389 3208 b199 7da6 acf6 d98a 7ac3 299c S.2...}.....z.). 00002c0: 3de0 5e12 3ed6 5849 f907 3d2c da66 f1a1 =.^.>.XI..=,.f.. 00002d0: 7556 c62b b88f a3da 1a47 230b aae8 c63c uV.+.....G#....< 00002e0: 6751 4f31 2d14 66e9 5a17 a980 4d37 2067 gQO1-.f.Z...M7 g 00002f0: 034c e0d7 b8ad 8cb4 b6d0 16e9 f6a5 3f90 .L............?. 0000300: 95aa 008e 79e1 7fda d74e ada2 f602 cc3b ....y....N.....; 0000310: 1b61 c657 b656 3840 712d 2bb3 61b9 3c44 .a.W.V8@q-+.a.. 0000390: 2a6b e68f e14c 6b3d b6ac e4cf 4f75 a828 *k...Lk=....Ou.( 00003a0: 0e21 24ad 27c7 e970 37a2 c883 46b0 ff26 .!$.'..p7...F..& 00003b0: 7c2a cf9f 9845 e4ca c067 f763 cd80 b1b3 |*...E...g.c.... 00003c0: 74b8 6066 b1c0 634e fabc 9312 d0c4 ed8d t.`f..cN........ 00003d0: 880d 41b7 a1d4 3c59 bea3 63e7 ab61 11b7 ..A...<Y..c..a.. 00003e0: 9f40 4555 f469 38b8 1add 1336 f03d [email protected].=
Well done, no clear-text data is readable anymore now!
Just a little information. When encryption is turned on, to display the content of the binary log files with mysqlbinlog, we have to use the –read-from-remote-server (-R) option, otherwise mysqlbinlog has no access to them:
# cd /u01/app/mysql/admin/mysqld8/binlog # mysqlbinlog mysqld8-bin.000005 /*!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*/; # mysqlbinlog -R --protocol TCP --host 192.168.25.2 --port 33008 --user root -p mysqld8-bin.000005 Enter password: /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; #at 4 #190221 12:01:55 server id 8 end_log_pos 124 CRC32 0x92413637 Start: binlog v 4, server v 8.0.15 created 190221 12:01:55 BINLOG ' I4VuXA8IAAAAeAAAAHwAAAAAAAQAOC4wLjE1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgE3NkGS '/*!*/; #at 124 #190221 12:01:55 server id 8 end_log_pos 155 CRC32 0x7d47d67e Previous-GTIDs #[empty] #at 155 ...
Stay tuned with MySQL 8! 😉
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/10/ELU_web-min-1-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)