This blog post was co-authored by Peter Sylvester and Valerie Parham-ThompsonIntroduced in version 10.1.3 (and with substantial changes in 10.1.4), the MariaDB data encryption at rest feature allows for transparent encryption at the tablespace level for various storage engines, including InnoDB and Aria.Before now, there have been only two widely accepted encryption methods for MySQL/MariaDB: encryption at the file system level, or encryption at the column level. For comparison, we’ll do a brief overview of how these work, as well as the pros and cons typically associated with each option.File System EncryptionThis is performed by setting a file system to be encrypted at the block level within the operating system itself, and then specifying that the encrypted volume should be the location of the data directory for MySQL/MariaDB. You can also use encrypted volumes to store MariaDB binary logs.ProsOne-time setup and then no additional management required.ConsThere is a large degree of overhead at the CPU level. Every time an InnoDB page/extent is retrieved and stored in the InnoDB buffer pool, the data has to be decrypted. The same issue occurs when dirty pages are flushed back to persistent storage, be it data or a log file.Column EncryptionYou can encrypt data at the column level by using a binary data type (varbinary/BLOB) and then encrypt the data as it goes into or out of the the page at the application or code level. Typically this is done using the AES_ENCRYPT and AES_DECRYPT functions in MySQL/MariaDB.ProsYou’re only encrypting the data that needs to be secured. All other data has no encryption-related overhead.This provides a higher degree of security then file system encryption. If the data is encrypted at the file system or by the data encryption at rest feature, if you can get into the running MariaDB instance you can still see the unencrypted version of the data. With column-level encryption, the data is stored in a secure fashion and you need to supply the encryption key every time it is accessed by the MariaDB instance.ConsThe crypt key needs to be stored somewhere that allows the application to easily provide it when running queries against MariaDB.You may be able to see the crypt key in statement-based binary logging, or in the process list.Data that is encrypted should not be used for reverse lookups. For example, if you are encrypting a column that stores a name, and you need to search that column for a specific name, you have to specify the search using the AES_DECRYPT function, which will force all the table records to be scanned, decrypted, and compared as part of the “where” operation of the query.MariaDB Data Encryption at RestThis solution sits somewhere between the aforementioned file system level and column level encryption, allowing you to encrypt data at the table level. This allows for encryption that is easier to manage and work with, while also allowing for a narrower focus so you are encrypting only the data or logs that you wish to encrypt. Although, it should be noted that like file system encryption, if you can get to the launched MariaDB instance, you can get access to the encrypted data.Now let’s walk through a test of the functionality of the MariaDB data encryption at rest feature.PrepPreparation included cloning a Centos7 base VM in VirtualBox, adjusting the IP and hostname, and installing MariaDB 10.1.11 using their repository with instructions here.Create KeysThe first step was to create keys. The output of the openssl command below (with example output) was used to edit a new file /var/lib/mysql/keys.txt.The command was:openssl enc -aes-256-cbc -P -md sha1
enter aes-256-cbc encryption password:
Verifying – enter aes-256-cbc encryption password:Sample output:…
key=AD2F01FD1D496F6A054E3D19B79815D0F6DE82C49E105D63E1F467912E2F0B95
iv =C6A3F3625D420BD19AF04CEB9DA2D89BSample contents of keys.txt using that output:1;C6A3F3625D420BD19AF04CEB9DA2D89B;AD2F01FD1D496F6A054E3D19B79815D0F6DE82C49E105D63E1F467912E2F0B95(You can take the additional step of encrypting the keys, but that was not done here.)Don’t lose the key file, or you won’t be able to start the server:2016-02-13 20:37:49 140334031026304 [ERROR] mysqld: File ‘/var/lib/mysql/keys.txt’ not found (Errcode: 2 "No such file or directory") 2016-02-13 20:37:49 140334031026304 [ERROR] Plugin ‘file_key_management’ init function returned error. 2016-02-13 20:37:49 140334031026304 [ERROR] Plugin ‘file_key_management’ registration as a ENCRYPTION failed. 2016-02-13 20:37:49 140334031026304 [ERROR] InnoDB: cannot enable encryption, encryption plugin is not available 2016-02-13 20:37:49 140334031026304 [ERROR] Plugin ‘InnoDB’ init function returned error. 2016-02-13 20:37:49 140334031026304 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed. 2016-02-13 20:37:49 140334031026304 [Note] Plugin ‘FEEDBACK’ is disabled. 2016-02-13 20:37:49 140334031026304 [ERROR] Unknown/unsupported storage engine: InnoDB 2016-02-13 20:37:49 140334031026304 [ERROR] AbortingYou can of course remove the relevant configs and restart successfully, but we have found at least two issues when trying to remove this configuration after it has been put in place on the MariaDB instance.If you encrypt the InnoDB log files (redo, not binary logs), then remove the encryption configuration and restart MariaDB, it will not be able to start until you re-enable the data at rest encryption feature.If you enable default encryption by putting innodb-encrypt-tables in the my.cnf, and then create a table, remove the feature, and restart MariaDB, the server will crash irrecoverably when selecting data from the table (bug filed as http://ift.tt/24cX7wN).Install PluginNext step was to install the plugin and use this file. The clearest path to doing this is to add the following two lines in /etc/my.cnf within the [mysqld] section:plugin-load-add=file_key_management.so
file-key-management-filename = /var/lib/mysql/keys.txt</pre>Restart MariaDB, and confirm the plugin is installed. The file_key_management plugin should display as “active.”show all_plugins like ‘%file%’;Testing Encrypted TablesAs the documentation indicates, you can encrypt all tables when they are created (specify innodb-encrypt-tables in the my.cnf) or individual tables (by adding the settings to a create or alter table statement). (See further below for result of using the third option, innodb-encrypt-tables=force.)Here are the results if you encrypt a single table.First, create a table:mysqlslap –concurrency=5 –number-int-cols=2 –number-char-cols=3 –auto-generate-sql –auto-generate-sql-write-number=1000 –no-dropAnd encrypt it:alter table mysqlslap.t1 encrypted=yes encryption_key_id=1;Here’s the table definition after encrypting:show create table mysqlslap.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`intcol1` int(32) DEFAULT NULL,
`intcol2` int(32) DEFAULT NULL,
`charcol1` varchar(128) DEFAULT NULL,
`charcol2` varchar(128) DEFAULT NULL,
`charcol3` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes `encryption_key_id`=1Looking at the .ibd file directly via xxd, you can see some text data before encryption:0084570: 0001 8000 0002 7661 6c65 7269 6570 6172 ……valeriepar
0084580: 6861 6d74 686f 6d70 736f 6e00 0000 0000 hamthompson…..And after encryption:0085470: fdf4 7c27 d9cb 5d33 59b1 824d 4656 b211 ..|’..]3Y..MFV..
0085480: 7243 9ce0 1794 7052 9adf 39a1 b4af c2fd rC….pR..9…..Once that table was encrypted, to test moving encrypted tablespaces, the files were copied from the source to a destination server as follows. The destination server had no encryption plugin, configs, or key installed.The following process is typical for moving tablespaces: create a similar empty table on the destination server, without encryption. (It throws an error on that unencrypted server if you try it with `encrypted`=yes `encryption_key_id`=1.)create database mysqlslap;
use mysqlslap
CREATE TABLE `t1` (
`intcol1` int(32) DEFAULT NULL,
`intcol2` int(32) DEFAULT NULL,
`charcol1` varchar(128) DEFAULT NULL,
`charcol2` varchar(128) DEFAULT NULL,
`charcol3` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;Then start the move process. First, discard the tablespace on the destination server. This leaves you with just the .frm file there.-rw-rw—-. 1 mysql mysql 65 Feb 13 13:15 db.opt
-rw-rw—-. 1 mysql mysql 932 Feb 13 13:15 t1.frmALTER TABLE mysqlslap.t1 DISCARD TABLESPACE;Prepare the table on the source server:flush tables t1 for export;Now you have a .cfg file on the source server:-rw-rw—-. 1 mysql mysql 65 Feb 13 13:13 db.opt
-rw-rw—-. 1 mysql mysql 620 Feb 13 13:16 t1.cfg
-rw-rw—-. 1 mysql mysql 976 Feb 13 13:14 t1.frm
-rw-rw—-. 1 mysql mysql 557056 Feb 13 13:14 t1.ibdSend the .cfg and .ibd files from the source to the destination server:scp /var/lib/mysql/mysqlslap/t1.cfg root@192.168.56.69:/var/lib/mysql/mysqlslap/t1.cfg
scp /var/lib/mysql/mysqlslap/t1.ibd root@192.168.56.69:/var/lib/mysql/mysqlslap/t1.ibdFree to unlock on the source server now:unlock tables;You’ll get an error on import if you don’t make them usable by mysql:chown mysql:mysql /var/lib/mysql/mysqlslap/t1*With the .cfg and .ibd files in place on the destination server, import the tablespace there:alter table t1 import tablespace;As intended, the encryption prevents importing the table:MariaDB [mysqlslap]> alter table t1 import tablespace;
ERROR 1296 (HY000): Got error 192 ‘Table encrypted but decryption failed. This could be because correct encryption management plugin is not loaded, used encryption key is not available or encryption method does not match.’ from InnoDBinnodb-encrypt-tables=forceIf you set innodb-encrypt-tables=force in /etc/my.cnf, attempting to create a table with encryption=no fails:create table t3 ( `intcol1` int(32) DEFAULT NULL, `intcol2` int(32) DEFAULT NULL, `charcol1` varchar(128) DEFAULT NULL, `charcol2` varchar(128) DEFAULT NULL, `charcol3` varchar(128) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encryption_key_id`=1 `encrypted`=no;
ERROR 1005 (HY000): Can’t create table `mysqlslap`.`t3` (errno: 140 "Wrong create options")The error message could be more clear, but the setting would save future create statements from undoing desired encryption set up by a DBA.Encrypted BinlogsBinlogs can also be encrypted.Start by adding this to the my.cnf, and restart server.encrypt_binlogBefore encryption, the binlogs look like this:008dfb0: 494e 5345 5254 2049 4e54 4f20 7431 2056 I NSERT INTO t1 V
008dfc0: 414c 5545 5320 2832 3132 3635 3538 3138 ALUES (212655818
008dfd0: 352c 3737 3332 3733 3731 382c 2759 3838 5,773273718,’Y88
008dfe0: 4e30 3774 6f30 3333 6d32 5845 497a 487a N07to033m2XEIzHz
008dff0: 4d4a 7348 7558 544c 3247 6543 6865 4334 MJsHuXTL2GeCheC4
008e000: 574a 7149 436c 4471 6f6c 3479 634d 7071 WJqIClDqol4ycMpq
008e010: 5a68 374b 3463 5a79 7442 4251 684e 4d42 Zh7K4cZytBBQhNMB
008e020: 6234 4c6e 7161 6457 425a 5366 7649 544c b4LnqadWBZSfvITL
008e030: 7a64 5a77 3536 7571 4835 4771 5466 7477 zdZw56uqH5GqTftw
008e040: 6a36 6a5a 5943 336b 6c4f 4e5a 616c 6d50 j6jZYC3klONZalmP
008e050: 454a 4c4a 5047 4161 4c49 4f27 2c27 6970 EJLJPGAaLIO’,’ipAfter restarting the server with encryption, newly generated binlog files look like this:011b860: 69c5 cc00 5cb0 1581 0217 2d3f 728c 77ff i…\…..-?r.w.
011b870: a6ca e6e3 a041 0f26 ee39 c398 eecd 4df9 …..A.&.9….M.
011b880: 5bef 53e0 bf0a 96bd 7b61 bfcc c074 6151 [.S…..{a…taQ
011b890: 208b 63fc 4efd ee91 b2bc 0a90 1009 76a1 .c.N………v.
011b8a0: bf18 84e3 f444 82a1 e674 b44b 7754 2cc9 …..D…t.KwT,.
011b8b0: b63f 946c 821d 222a ae57 a251 451c 8332 .?.l.."*.W.QE..2
011b8c0: d030 1c5f 3997 db77 96f1 4da5 a03e 55a9 .0._9..w..M..>U.
011b8d0: a882 3980 f81f 9fa9 7b45 27c1 2f51 34ad ..9…..{E’./Q4.
011b8e0: b8bf e5e6 4b1e 6732 11a1 1b00 0000 c049 ….K.g2…….I
011b8f0: b2a9 ad08 ed95 4c5c 5541 05b4 a256 14d3 ……L\UA…V..
011b900: 045b e74f 2526 0000 009f 921c 1482 d621 .[.O%&………!Note that also you can’t use mysqlbinlog on encrypted binlogs:mysqlbinlog /var/lib/mysql/maria101-bin.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160213 10:49:27 server id 1 end_log_pos 249 Start: binlog v 4, server v 10.1.11-MariaDB-log created 160213 10:49:27
BINLOG ‘
h1C/Vg8BAAAA9QAAAPkAAAAAAAQAMTAuMS4xMS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA3QAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQAAGbdjEE=
‘/*!*/;
# at 249
# Encryption scheme: 1, key_version: 1, nonce: 4caf0fe45894f796a234a764
# The rest of the binlog is encrypted!
# at 285
/*!50521 SET skip_replication=1*//*!*/;
#620308 22:02:57 server id 3337593713 end_log_pos 2396907567 Ignorable
# Ignorable event type 118 (Unknown)
# at 324
ERROR: Error in Log_event::read_log_event(): ‘Found invalid event in binary log’, data_len: 42, event_type: 204
ERROR: Could not read entry at offset 366: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;As a test of replication, encrypted binlogs were sent from an encrypted master to an unencrypted slave. The master had encrypted binlogs, but the slave had no encryption plugin, configs, or keys.Nothing special in the replication setup, and replication did not break. No issues were detected in multi-master replication with replication filtering. Also, if the slave is set up for encryption, the encryption key in use on the slave does not need to be identical to that of the key that is in use on the master.Of special note for security, while the master’s binlogs were encrypted, the slave’s relay logs were not. Change statements on an unencrypted slave are easily viewed at the file level or using mysqlbinlog on the relay logs. Watch those user permissions! Relay logs on the slave can be encrypted using the ‘encrypt-binlog’ setting on the slave having the plugin installed.ConclusionsBinlog encryption prevents viewing change statements in raw format or via mysqlbinlog.Replication from an encrypted master to a nonencrypted slave works. Note that the relay logs on the nonencrypted slave make the change statements visible.Encrypting a table prevents copying the tablespace to another server.Once implemented, the steps to unencrypt multiple tables in your schema require careful planning. It is recommended you test this feature carefully before implementing in production.
via Planet MySQL
Data Encryption at Rest