From this point forward, all new binary logs are encrypted. To remove old, unencrypted logs, use RESET MASTER or PURGE BINARY LOGS.
Key Rotation
If your key management plugin supports rotation (check here), the server can use new key versions for new binary logs. However, unlike InnoDB, binary logs do not have a background re-encryption mechanism. Existing binary log files remain encrypted with the key version used at the time of their creation.
Reading Encrypted Binary Logs
Because encrypted logs cannot be read directly by standard text editors, you must use the mariadb-binlog utility.
Method 1: Remote Decryption (Recommended)
The most secure method is to have the server stream the decrypted events. This avoids the need to distribute encryption keys to administrative workstations.
Method 2: Local Decryption
You can decrypt logs locally if the mariadb-binlog utility has access to the same key management plugin and key material used by the server.
Disabling Encryption
Disabling encryption stops the server from encrypting future logs. It does not decrypt existing files.
Important: Keep your key management plugin loaded as long as you have encrypted binary logs on disk that you may need to read. If the plugin is removed, the server and mariadb-binlog will be unable to decrypt those older files.
️Understanding Binlog Encryption
Behind the Scenes
When starting with binary log encryption, MariaDB Server logs a Format_descriptor_log_event and a START_ENCRYPTION_EVENT, then encrypts all subsequent events for the binary log.
Each event header and footer are created and processed to produce encrypted blocks. These encrypted blocks are produced before transactions are committed and before the events are flushed to the binary log. As such, they exist in an encrypted state in memory buffers and in the IO_CACHE files for user connections.
Effects of Data-at-Rest Encryption on Replication
When using encrypted binary logs with replication, you can have different encryption keys on the master and the replica. The master decrypts encrypted binary log events as it reads them from disk, and before its binary log dump thread sends them to the replica, so the replica actually receives the unencrypted binary log events.
To ensure that binary log events are encrypted as they are transmitted between the master and replica, use TLS with the replication connection.
Effects of Data-at-Rest Encryption on mariadb-binlog
Learn about InnoDB encryption for data at rest. This section details how to encrypt InnoDB tablespaces, ensuring strong data security and compliance for your mission-critical applications.
Key Management and Encryption Plugins
Explore key management and encryption plugins for MariaDB Server. This section details how to manage encryption keys and leverage plugins for robust data-at-rest protection.
Secure MariaDB Server data at rest with encryption. This section details how to protect your sensitive information stored on disk, ensuring data confidentiality and compliance.
Amazon Web Services (AWS) Key Management Service (KMS) Encryption Plugin Advanced Usage
Step-by-step tutorial for setting up the AWS KMS plugin, covering the creation of a Customer Master Key (CMK) in AWS, configuring IAM roles for EC2, and installing the plugin from source.
Ultimately, keeping all the credentials required to read the key on a single host means that a user who has gained access to the host has enough information to read the encrypted files in the datadir, read the encrypted keys from the datadir, interact with AWS KMS to decrypt the encrypted keys, and then used the decrypted keys to decrypt the encrypted data.
Theoretically, a superuser can read the memory of the MariaDB server process to read the decrypted keys or restart MariaDB with password authentication disabled in order to dump data, or add new users to MariaDB in order to allow a user to connect and dump the data. Resolving these issues is beyond the scope of this document. A user who gains root access to your operating system or root access to your MariaDB server will have the ability to decrypt your data. Plan accordingly.
Managing AWS Credentials
Putting the AWS credentials in a file inside the MariaDB home directory is not ideal. By default, any user with the FILE privilege can read any files that the MariaDB server has permission to read, which would include the credentials file. To protect against this, you should set secure_file_priv to restrict the location the server will allow a user to read from when executing LOAD DATA INFILE or the LOAD_FILE() function.
But putting them in other locations requires passing additional data to the server, which in the case of CentOS 7 requires customizing the systemd startup procedure. This is most easily done by creating a "drop-in" file in /etc/systemd/system/mariadb.service.d/. The file should end in ".conf" but can otherwise be named whatever you like. After making any changes to systemd files, execute systemctl daemon-reload and then start (or restart) the service as usual.
You can place the credentials file in a location of your choosing and then refer to that file by setting the AWS_CREDENTIAL_PROFILES_FILE environment variable in the drop-in file:
The credentials file will need to be readable by the "mysql" user, but it does not need to be readable by any other user.
AWS credentials can also be put directly into a "drop-in" systemd file that will be read when starting the MariaDB service:
However, any OS user can read this information from systemd, which could be considered a security risk. Another solution is to put the credentials in a separate file that is only readable by root and then refer to that file using an EnvironmentFile directive in a drop-in systemd file.
That has the advantage the credentials can only be read directly by root. systemd adds those variables to the environment of the MariaDB server when starting it, and MariaDB can use the credentials to interact with AWS. Note, though, that any process running as the "mysql" user can still read the credentials from the proc filesystem on Linux.
AWS KMS Key Policy
AWS KMS allows flexible, user-editable key policy. This offers fine-grained control over which users can operate on keys. The possibilities range from simply restricting which IP addresses are allowed to perform operations on the key, to requiring a MFA (Multi-Factor Authentication) device to use the key, to enforcing separation of responsibilities by creating an additional user with limited privileges to enable and disable the key. All 3 of these options will be outlined in this section.
For more details about customizing the Key Policy for your master keys, please consult the documentation.
Source IP Restrictions
A simple, common-sense restriction to put in place is to restrict the range of IP addresses that are allowed to use your master key. This way, even if someone obtains API credentials, they'll be unable to use them to decrypt your encryptions keys from a different host.
To restrict API access from only a specific IP address or range of IP addresses, you'll need to manually edit the key policy.
Load the IAM console at.
Click "Encryption Keys" in the left-hand sidebar.
Click the name of your encryption key to view its details.
Click the link labeled "Switch to policy view", to the right of the heading of the "Key Policy" section.
... replacing 10.1.2.3/32 above with an IP address or range of IP addresses in CIDR format. For example, a single address would be 192.168.12.34/32, while a range of addresses might be 192.168.0.0/24.
Click "Save Changes".
Click "Proceed" if prompted with a warning about using the default view in the future.
Access to the API will now be restricted to requests coming from the IP address or range of IP addresses specified in the policy.
Using a Multi-Factor Authentication (MFA) Device
One approach is to modify the key policy for the master key so that MFA (Multi-Factor Authentication) is required in order to use the key. This is achieved with a wrapper that handles prompting the user for an MFA token, acquires temporary, limited-privilege credentials from the AWS Security Token Service (STS), and puts those credentials into the environment of the MariaDB server process. The credentials can expire after as little as 15 minutes.
To require an MFA token for users of the key, you'll need to manually edit the key policy.
Load the IAM console at.
Click "Encryption Keys" in the left-hand sidebar.
Click the name of your encryption key to view its details.
Click the link labeled "Switch to policy view", to the right of the heading of the "Key Policy" section.
Click "Save Changes".
Click "Proceed" if prompted with a warning about using the default view in the future.
Now, add an MFA device for your user. You'll need to have a hardware MFA device or an application such as Google Authenticator installed on your smartphone.
Click "Users" in the left-hand sidebar.
Click the name of your user.
Click the "Security Credentials" tab.
In the "Sign-In Credentials" section, click the "Manage MFA Device" button.
Now, set up the wrapper program.
Copy the iam-kms-wrapper file to /usr/local/bin/, and ensure that it is executable.
Create a drop-in systemd config file in /etc/systemd/system/mariadb.service.d/:
Execute systemctl daemon-reload.
Create a file at /etc/my.cnf.d/iam-kms-wrapper.config with these contents, using the ARN for your MFA device as the value for kms_mfa_id:
When you start the MariaDB service now, the wrapper will temporarily create a socket file at the location given by the kms_mfa_socket option. The wrapper will read the MFA code from the socket and will use it to authenticate to KMS. To give the MFA code, simply write the digits to the socket file using echo: echo 111676 > /tmp/kms_mfa_socket.
The systemctl command will block until MariaDB starts, so you will need to write the code to the socket file via a separate terminal.
Note that the temporary credentials put into the environment of the MariaDB process will expire after a period of time defined by the request to the AWS Security Token Service (STS). In the example below, they will expire after 900 seconds. After that time, MariaDB may be unable to generate new encrypted data keys, which means that, for example, an attempt to create a table with a previously-unused key ID would fail.
Wrapper Program Example
Here's an example wrapper program written in go. Build this into an executable named iam-kms-wrapper and use it as instructed above. This could of course be written in any language for which an appropriate AWS SDK exists, but go has the benefit of compiling to a static binary, which means you do not have to worry about interpreter versions or installing complex dependencies on the host that runs your MariaDB server.
Disabling Keys When not Needed
Another possibility is to use the API to disable access to the master key and enable it only when a trusted administrator knows that the MariaDB service needs to be started. A specialized tool on a separate host could be used to enable the key for a very short period of time while the service starts and then quickly disable the key.
To do this, you can create an extra IAM User that can only use the kms:EnableKey and kms:DisableKey API endpoints for your key. This user will not be able to encrypt or decrypt any data using the key.
First, create a new user.
Load the IAM console at.
Click "Users" in the left-hand sidebar.
Click "Create New Users".
Enter a new user name. (The examples will use "MDBEncAdmin".)
Click "Close".
Click "Close" again if prompted.
Click the name of your new user to open the details view.
Copy the "User ARN" value for your user (for example "arn:aws:iam::551888181234:user/MDBEncAdmin"). You will need this for the next step.
Now, give the new user permission to perform API operations on your key.
Click "Encryption Keys" in the left-hand sidebar.
Click the name of your key to open the details view.
Click "Switch to policy view" if it is not already open. (The "policy view" is a large text field that contains JSON describing the key policy.)
...so that your Key Policy looks like this:
Click "Save Changes".
You've now added a new IAM user and you've given that user privileges to enable and disable your key. This user will be able to perform those operations using the AWS CLI or via a script of your own design using the AWS API. For example, using the :
In order for MariaDB to start, this new user will have to enable the master key, then the DBA can start MariaDB, and this user can once again disable the master key after the service has started. Note that in this workflow, MariaDB will be unable to create new encryption keys, such as would be done when a user creates a table that refers to a non-existent key ID. The AWS KMS plugin will encounter an error if it tries to generate a new encryption key while the master key is disabled. In that scenario, the key administrator would have to enable the key before the operation could succeed. Here's what you should expect to see in the journal if MariaDB tries to interact with a disabled master key:
Adding MFA
It's also possible to add MFA to this technique so that the user that enables & disables the master key has to authenticate using an MFA device. Adapt the instructions in the MFA section above to add MFA to the policy section for the user with EnableKey and DisableKeys privileges, add an MFA device for that user, use Security Token Service (STS) to get temporary security credentials, and then use those credentials to make the API calls. Here's an example Python script that follows that workflow:
Logging and Auditing
CloudTrail
Amazon's CloudTrail service creates JSON-formatted text log files for every API interaction. Enabling CloudTrail requires S3, which incurs additional fees.
First, enable CloudTrail and add a trail.
Load the CloudTrail console at.
If you've never used CloudTrail before, click "Get Started Now".
Enter a value for "trail name". This example uses "mariadb-encryption-key".
Create a new S3 bucket, using a globally unique name, or use an existing S3 bucket, according to your needs.
If you navigate to the S3 bucket you created, you should find log files that contain JSON-formatted descriptions of your API interactions.
CloudWatch
Amazon's CloudWatch service allows you to create alarms and event rules that monitor log information.
First, send your CloudTrail logs to CloudWatch.
Load the CloudTrail console at.
Click "Trails" in the left-hand navigation sidebar.
Click the name of your trail to open the Configuration view.
In the "CloudWatch Logs" section, click "Configure".
Now, set up an SNS topic to facilitate email notifications.
Open.
Make sure the region in the console (look in the upper-right corner) is the same as the region where you created your key!
Click "Get Started" is prompted.
Click "Events" in the left-hand sidebar.
Now, configure CloudWatch and create an Event Rule.
Open.
Make sure the region in the console (look in the upper-right corner) is the same as the region where you created your key and your SNS topic!
Click "Events" in the left-hand sidebar.
Click "Create rule".
You should now get emails any time someone executes API calls on the KMS service in the region where you've created the CloudWatch Event rule. That means you should get email any time the key is enabled or disabled, and any time the AWS KMS plugin generates new keys or decrypts the keys stored on disk on the MariaDB server.
You may also wish to create an event rule (or an additional event) that matches only when an unauthorized user tries to access the key. You might accomplish that by manually editing the Event selector of the rule to look something like this:
The emails are formatted as JSON. Further customization of the CloudWatch email workflow is beyond the scope of this document.
There are many other workflows available using CloudWatch, including workflows with alarms and dashboards. Those are beyond the scope of this document.
This page is licensed: CC BY-SA / Gnu FDL
Locate the section that contains "Sid": "Allow use of the key".
Add this text below the "Sid" line:
Locate the section that contains "Sid": "Allow use of the key".
Add this text below the "Sid" line:
Complete the steps to activate your MFA device.
Copy the ARN for your MFA device. You will need to use this when configuring the wrapper program.
Click "Show User Security Credentials".
Copy the credentials and put them in a credentials file with this structure:
Create a new item in the
Statement
array with this structure:
Click "Turn On".
Click "Continue".
Click "Allow".
Click "Create new topic".
Enter a Topic name of your choosing.
Enter a Display name of your choosing.
Click "Create topic".
Click the ARN of your new SNS topic.
Click "Create Subscription".
Select "Email" from the Protocol dropdown.
Enter the desired notification email address in the Endpoint field.
Wait for the confirmation email to show up and follow the instructions.
Choose "AWS API call" from the "Select event source" dropdown.
Choose "KMS" from the "Service name" dropdown.
Decide which operations should trigger the event. (You can eep "Any operation" selected for simplicity.)
Click "Add target".
Select "SNS target" from the dropdown.
Select the SNS topic you created in the previous steps.
[ERROR] AWS KMS plugin : GenerateDataKeyWithoutPlaintext failed : DisabledException - Unable to parse ExceptionName: DisabledException Message: arn:aws:kms:us-east-1:551888181234:key/abcdf8f6-084b-4cff-99ca-abcdef6c7907c is disabled.
#!/usr/bin/env python
import boto3
import sys
# Command-line argument processing should be more robust than this...
action= sys.argv[1]
mfa_token= sys.argv[2]
# These should perhaps go into a config file instead of here
mfa_serial= 'arn:aws:iam::551888181234:mfa/MDBEncAdmin'
key_id= 'arn:aws:kms:us-east-1:551888181234:key/abcdf8f6-084b-4cff-99ca-abcdef6c7907c'
# Make the connection to the Security Token Service to get temporary credentials
token_client= boto3.client('sts')
token_response= token_client.get_session_token(
DurationSeconds= 900,
SerialNumber= mfa_serial,
TokenCode= mfa_token
)
cred= token_response['Credentials']
# Start new session using temporary, MFA-authenticated credentials
kms_session= boto3.session.Session(
aws_access_key_id= cred['AccessKeyId'],
aws_secret_access_key= cred['SecretAccessKey'],
aws_session_token= cred['SessionToken'],
region_name= key_id.split(':')[3]
)
# Start KMS client and execute operation against key
kms_client= kms_session.client('kms')
if action == 'enable' or action == 'e':
action_f= kms_client.enable_key
elif action == 'disable' or action == 'd':
action_f= kms_client.disable_key
else:
raise Exception('Action must be either "disable" or "enable"')
action_f(KeyId=key_id)
Learn about Aria encryption in MariaDB Server for data at rest. This section details how to encrypt Aria tablespaces, providing enhanced security for your stored data.
Aria: Encryption Keys
Details how Aria manages encryption keys (using ID 1 for user tables and ID 2 for temporary tables) and notes limitations regarding key rotation and per-table key assignment.
As with other storage engines that support data-at-rest encryption, Aria relies on an Encryption Key Management plugin to handle its encryption keys. Where the support is available, Aria can use multiple keys.
Encryption Keys
MariaDB keeps track of each encryption key internally using a 32-bit integer, which serves as the key identifier. Unlike InnoDB, Aria does not support the ENCRYPTION_KEY_ID table option (for more information, see MDEV-18049), which allows the user to specify the encryption key to use. Instead, Aria defaults to specific encryption keys provided by the Encryption Key Management plugin.
When working with user-created tables, Aria encrypts them to disk using the ID 1 key.
When working with internal temporary tables written to disk, Aria encrypts them to disk using the ID 2 key, unless there is no ID 2 key, then it falls back on the ID 1 key.
Key Rotation
Some allow you to automatically rotate and version your encryption keys. If a plugin support key rotation, and if it rotates the encryption keys, then InnoDB's can re-encrypt InnoDB pages that use the old key version with the new key version. However, Aria does not have a similar mechanism, which means that the tables remain encrypted with the older key version. For more information, see .
In order for key rotation to work, both the backend key management service (KMS) and the corresponding have to support key rotation. See to determine which plugins currently support key rotation.
This page is licensed: CC BY-SA / Gnu FDL
Aria: Encryption Overview
Introduction to encrypting Aria tables, covering the necessary system variables (aria_encrypt_tables, encrypt_tmp_disk_tables) and how to verify encryption status by inspecting data files.
MariaDB can encrypt data in tables that use the . This includes both user-created tables and internal on-disk temporary tables that use the Aria storage engine. This ensures that your Aria data is only accessible through MariaDB.
For encryption with the InnoDB and XtraDB storage engines, see .
Basic Configuration
In order to enable encryption for tables using the , there are a couple server system variables that you need to set and configure. Most users will want to set
Aria: Disabling Encryption
Instructions for safely disabling encryption on Aria tables, emphasizing the need to rebuild tables to an unencrypted state before removing key management plugins.
Overview
This guide provides instructions for safely disabling encryption for the Aria storage engine, including user-created tables and internal on-disk temporary tables.
To fully disable encryption, you must set the relevant system variables and then rebuild each table to an unencrypted state.
1
Uninstall Key Management Plugins
Final step of removing key management plugins from the configuration once all data and logs have been confirmed as unencrypted.
Once all data and logs have been decrypted, you can safely remove key management plugins, if desired. For example, if using the file key management plugin:
Restart the server after editing the configuration.
Ensure that all tables no longer report encryption in CREATE_OPTIONS.
Confirm that redo logs and binary logs are unencrypted by checking server variables and log headers.
First, prevent MariaDB from encrypting any new data by updating the global system variables.
User-Created Tables
Set aria_encrypt_tables to OFF. This ensures that any new Aria tables created or existing tables rebuilt from this point forward will be unencrypted.
SET GLOBAL aria_encrypt_tables = OFF;
To make this change persistent across server restarts, add the following to your MariaDB configuration file (for instance, my.cnf):
[mysqld]
aria_encrypt_tables = OFF
Internal On-Disk Temporary Tables
MariaDB creates internal temporary tables using the Aria engine when aria_used_for_temp_tables is set to ON. To ensure these are no longer encrypted, set:
2
Identify encrypted Aria tables.
Aria does not use background encryption threads (unlike InnoDB). Therefore, tables already on disk will remain encrypted until you manually rebuild them.
Run the following query to identify which tables need to be rebuilt:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE ENGINE = 'Aria'
AND ROW_FORMAT = 'PAGE'
AND TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'sys')
AND (CREATE_OPTIONS LIKE '%`encrypted`=yes%' OR CREATE_OPTIONS LIKE '%`encrypted`=1%');
3
Rebuild tables to decrypt.
To decrypt the tables identified in the previous step, you must rebuild them using an ALTER TABLE statement. This causes MariaDB to rewrite the data (.MAD) and index (.MAI) files in an unencrypted format.
Option A: Manual Rebuild (Single Table)
Use the following statement to rebuild a specific table:
ALTER TABLE db_name.table_name ENGINE=Aria, ALGORITHM=COPY;
Option B: Generate Rebuild Statements (Bulk)
You can generate the DDL for all encrypted Aria tables at once using this query:
4
Verify and clean up.
Verify: Run the query from Step 2 again to ensure no encrypted Aria tables remain.
Remove Plugins (optional): Once all Aria tables (and any InnoDB tables or binary logs) are decrypted, you can safely remove the Encryption Key Management plugin from your configuration file and restart the server.
This page is licensed: CC BY-SA / Gnu FDL
Important Safety Precautions
Do not remove your Encryption Key Management plugin yet. MariaDB must be able to read the existing encrypted data to rewrite it in an unencrypted state.
Maintain configurations. Keep your key management settings in place until the very last step of the process.
Aria: Enabling Encryption
Step-by-step guide to enabling encryption for user-created and internal temporary Aria tables, including the requirement to manually rebuild existing tables using ALTER TABLE.
To enable data-at-rest encryption for tables using the Aria storage engine, configure the server to use an Encryption Key Management plugin. Once this is done, you can enable encryption by setting the relevant system variables.
Encrypting User-Created Tables
For user-created tables, enable encryption by setting the aria_encrypt_tables system variable to ON, then restart the server:
[mariadb]
aria_encrypt_tables = ON
Alternatively, set the variable with an SQL statement. This doesn't require a server restart, but the setting is lost on server restart:
Once this is set, Aria enables encryption on all newly created tables.
Encrypting Existing Tables
In cases where you have existing Aria tables that you would like to encrypt, the process is a little more complicated. Unlike InnoDB, Aria does not utilize to automatically perform encryption changes (see about that). Therefore, to encrypt existing tables, you need to identify each table that needs to be encrypted, and then you need to manually rebuild each table.
First, set the aria_encrypt_tables system variable to encrypt new tables.
Identify Aria tables that have the ROW_FORMAT table option set to PAGE.
For each table in the result set, issue an ALTER TABLE statement to rebuild the table.
This statement causes Aria to rebuild the table using the ROW_FORMAT table option. Since you enabled encryption, Aria also encrypts the table in the process.
Encrypting Internal Temporary Tables on Disk
During the execution of queries, MariaDB routinely creates internal temporary tables. These internal temporary tables initially use the storage engine, which is entirely stored in memory. When the table size exceeds the allocation defined by the system variable, MariaDB writes the data to disk using another storage engine. If you have the set to ON, MariaDB uses Aria in writing the internal temporary tables to disk.
Encryption for internal temporary tables is handled separately from encryption for user-created tables. To enable encryption for these tables, set the system variable to ON. Once set, all internal temporary tables that are written to disk using Aria are automatically encrypted.
Manually Encrypting Tables
Currently, Aria does not support manually encrypting tables through the and table options. For more information, see .
In cases where you want to encrypt tables manually or set the specific encryption key, use .
This page is licensed: CC BY-SA / Gnu FDL
InnoDB: Disabling Encryption
Instructions for safely disabling encryption on InnoDB tables, emphasizing the critical need to decrypt all tablespaces and redo logs using background threads or ALTER TABLE.
Overview
Decryption for InnoDB is more complex than Aria because it involves background threads and the Redo Log.
This guide covers the process for safely decrypting InnoDB tablespaces, the system tablespace, and the Redo Log.
1
InnoDB: Background Encryption Threads
Details the operation of background threads (configured via innodb_encryption_threads) which handle key rotation, and the encryption/decryption of tablespaces when global settings.
InnoDB performs some encryption and decryption operations with background encryption threads. The system variable controls the number of threads that the storage engine uses for encryption-related background operations, including encrypting and decrypting pages after key rotations or configuration changes, and data to permanently delete it.
Background Operations
InnoDB performs the following encryption and decryption operations using background encryption threads:
Encryption only works if theROW_FORMATtable option set to PAGE.
Aria does not support encryption of tables where the ROW_FORMAT table option is set to FIXED or DYNAMIC.
Aria does not support the ENCRYPTED table option (see MDEV-18049 about that).
Encryption for Aria can only be enabled globally using the aria_encrypt_tables system variable.
When changing the innodb_encrypt_tables system variable to FORCE, InnoDB's background encryption threads encrypt the system tablespace and any file-per-table tablespaces that have the ENCRYPTED table option set to DEFAULT.
When changing the innodb_encrypt_tables system variable to OFF, InnoDB's background encryption threads decrypt the system tablespace and any file-per-table tablespacs that have the ENCRYPTED table option set to DEFAULT.
The innodb_encryption_rotation_iops system variable can be used to configure how many I/O operations you want to allow for the operations performed by InnoDB's background encryption threads.
Whenever you change the value on the innodb_encrypt_tables system variable, InnoDB's background encryption threads perform the necessary encryption or decryption operations. Because of this, you must have a non-zero value set for the innodb_encryption_threads system variable. InnoDB also considers these operations to be key rotations internally. Because of this, you must have a non-zero value set for the innodb_encryption_rotate_key_age system variable. For more information, see disabling key rotations.
Non-Background Operations
InnoDB performs the following encryption and decryption operations without using background encryption threads:
When a file-per-table tablespaces and using ALTER TABLE to manually set the ENCRYPTED table option to YES, InnoDB does not use background threads to encrypt the tablespaces.
Similarly, when using file-per-table tablespaces and using ALTER TABLE to manually set the ENCRYPTED table option to NO, InnoDB does not use background threads to decrypt the tablespaces.
In these cases, InnoDB performs the encryption or decryption operation using the server thread for the client
connection that executes the statement. This means that you can update encryption on file-per-table tablespaces with an ALTER TABLE statement, even when the innodb_encryption_threads and/or the innodb_rotate_key_age system variables are set to 0.
InnoDB does not permit manual encryption changes to tables in the system tablespace using ALTER TABLE. Encryption of the system tablespace can only be configured by setting the value of the innodb_encrypt_tables system variable. This means that when you want to encrypt or decrypt the system tablespace, you must also set a non-zero value for the innodb_encryption_threads system variable, and you must also set the innodb_system_rotate_key_age system variable to 1 to ensure that the system tablespace is properly encrypted or decrypted by the background threads. See MDEV-14398 for more information.
For example, to see which InnoDB tablespaces are currently being decrypted or encrypted on by background encryption, you can check which InnoDB tablespaces have the ROTATING_OR_FLUSHING column set to 1:
And to see how many InnoDB tablespaces are currently being decrypted or encrypted by background encryption threads, you can call the COUNT() aggregate function.
And to see how many InnoDB tablespaces are currently being decrypted or encrypted by background encryption threads, while comparing that to the total number of InnoDB tablespaces and the total number of encrypted InnoDB tablespaces, you can join the table with the INNODB_SYS_TABLESPACES table in the information_schema database:
SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name,
'` ENGINE=Aria, ALGORITHM=COPY;') AS ddl
FROM information_schema.tables
WHERE ENGINE='Aria'
AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
AND (CREATE_OPTIONS LIKE '%`encrypted`=yes%' OR CREATE_OPTIONS LIKE '%`encrypted`=1%');
SET GLOBAL aria_encrypt_tables=ON
SET GLOBAL aria_encrypt_tables=ON
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE ENGINE='Aria'
AND ROW_FORMAT='PAGE'
AND TABLE_SCHEMA != 'information_schema';
ALTER TABLE aria_table ENGINE=Aria ROW_FORMAT=PAGE;
SELECT SPACE, NAME
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ROTATING_OR_FLUSHING = 1;
SELECT COUNT(*) AS 'encrypting'
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ROTATING_OR_FLUSHING = 1;
/* information_schema.INNODB_TABLESPACES_ENCRYPTION does not always have rows for all tablespaces,
so let's join it with information_schema.INNODB_SYS_TABLESPACES */
WITH tablespace_ids AS (
SELECT SPACE
FROM information_schema.INNODB_SYS_TABLESPACES ist
UNION
/* information_schema.INNODB_SYS_TABLESPACES doesn't have a row for the system tablespace (MDEV-20802) */
SELECT 0 AS SPACE
)
SELECT NOW() AS 'time',
'tablespaces', COUNT(*) AS 'tablespaces',
'encrypted', SUM(IF(ite.ENCRYPTION_SCHEME IS NOT NULL, ite.ENCRYPTION_SCHEME, 0)) AS 'encrypted',
'encrypting', SUM(IF(ite.ROTATING_OR_FLUSHING IS NOT NULL, ite.ROTATING_OR_FLUSHING, 0)) AS 'encrypting'
FROM tablespace_ids
LEFT JOIN information_schema.INNODB_TABLESPACES_ENCRYPTION ite
ON tablespace_ids.SPACE = ite.SPACE
Disable automatic encryption.
To stop the server from encrypting new tables and to begin the background decryption process for "automatically" encrypted tables (those where ENCRYPTED=DEFAULT), update the global system variables.
-- Disable encryption for new tables and the system tablespace
SET GLOBAL innodb_encrypt_tables = OFF;
-- Enable encryption threads to perform the decryption work
SET GLOBAL innodb_encryption_threads = 4;
-- Force rotation to unencrypted state by setting age to 1
SET GLOBAL innodb_encryption_rotate_key_age = 1;
[mariadb]
innodb_encrypt_tables = OFF
innodb_encryption_threads = 4
innodb_encryption_rotate_key_age = 1
2
Decrypt manually encrypted tables.
Tables created with the explicit option ENCRYPTED=YES are not always automatically decrypted by background threads. You must manually issue an ALTER TABLE statement for these.
Identify Manually Encrypted Tables
Run this query to find tables that require manual decryption:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE ENGINE='InnoDB'
AND (CREATE_OPTIONS LIKE '%ENCRYPTED=YES%' OR CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%');
Perform Decryption
For each table identified, run:
3
Decrypt the redo log.
The Redo Log must be decrypted separately. This requires a server restart.
The Redo Log is decrypted by ensuring the server can read the existing logs at startup and then configuring it to write new logs in plaintext.
Ensure keys are active: Before attempting to disable Redo Log encryption, verify that your key management plugin is fully functional. MariaDB must be able to decrypt the current ib_logfile members during the startup/recovery phase.
Update Configuration: Change the system variable to OFF in your server option file (for instance, my.cnf):
Restart the server – perform a clean restart:
During this restart, MariaDB uses the existing keys to read the encrypted Redo Logs, completes any necessary crash recovery, and then begins writing new Redo Log events in plaintext.
Verification: Confirm the status by running:
The value should now be OFF.
4
Monitor and verify decryption status.
Before removing your encryption keys, you must verify that no tablespaces remain encrypted.
Check Background Progress
Monitor the INNODB_TABLESPACES_ENCRYPTION table. Decryption is complete when the count reaches 0.
SELECT COUNT(*) AS "Encrypted_Tablespaces"
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ENCRYPTION_SCHEME != 0 OR ROTATING_OR_FLUSHING != 0;
Verify Individual Tables
Ensure that no tables show encryption in their creation options:
5
Clean up.
Once the count of encrypted tablespaces is 0 and the Redo Log has been rotated (after restart), you can safely:
Remove the Encryption Key Management plugin settings from your configuration file.
Restart the MariaDB Server one final time.
This page is licensed: CC BY-SA / Gnu FDL
Important Safety Precautions
Do Not Remove Plugins Early: Keep your Key Management plugin configured and loaded until the very end. If you remove the keys before decryption is complete, the encrypted data will become permanently inaccessible.
Order Matters: You must decrypt all tablespaces and the Redo Log before disabling the encryption plugin.
InnoDB: Encryption Keys
How InnoDB manages encryption keys using 32-bit integer IDs, including the default key ID (innodb_default_encryption_key_id), assigning specific keys to tables, and the process of key rotation.
InnoDB uses plugins to support the use of multiple .
Encryption Keys
Each encryption key has a 32-bit integer that serves as a key identifier.
The default key is set using the system variable.
InnoDB: Encryption Overview
Introduction to InnoDB's encryption architecture, explaining how data is encrypted/decrypted during disk I/O, the role of the buffer pool (where data is unencrypted), and how to verify encryption stat
MariaDB supports data-at-rest encryption for tables using the storage engines. When enabled, the server encrypts data when it writes it to and decrypts data when it reads it from the file system. You can to automatically have all new InnoDB tables automatically encrypted, or specify encrypt per table.
For encrypting data with the Aria storage engine, see .
InnoDB Encryption and Decryption Behavior
When data-at-rest encryption is enabled for InnoDB, encryption and decryption occur at specific points during disk I/O operations.
Encryption Key Management
Overview of key management in MariaDB, discussing the need for plugins to manage encryption keys, support for multiple keys (ID 1 for system, ID 2 for temp), and key rotation capabilities.
MariaDB's requires the use of a key management and encryption plugin. These plugins are responsible both for the management of encryption keys and for the actual encryption and decryption of data.
MariaDB supports the use of multiple encryption keys. Each encryption key uses a 32-bit integer as a key identifier. If the specific plugin supports key rotation, then encryption keys can also be rotated, which creates a new version of the encryption key.
When InnoDB pages are written to disk, they are automatically encrypted.
When is InnoDB data decrypted?
InnoDB pages are decrypted before they are read from disk and stored in the InnoDB buffer pool. A page remains decrypted in memory while it is in the buffer pool. As a result, decrypted memory pages may include data from rows, columns, or even tables that the current query does not directly access.
Basic Configuration
Using data-at-rest encryption requires that you first configure an Encryption Key Management plugin, such as the file_key_management or aws_key_management plugins.
MariaDB uses this plugin to store, retrieve and manage the various keys it uses when encrypting data to and decrypting data from the file system.
For more information on system variables for encryption and other features, see the InnoDB system variables page.
Creating Encrypted Tables
To create encrypted tables, specify the table options ENCRYPTED=YES and ENCRYPTION_KEY_ID= with a corresponding key id;
Finding Encrypted Tables
When using data-at-rest encryption with the InnoDB storage engine, it is not necessary that you encrypt every table in your database. You can check which tables are encrypted and which are not by querying the INNODB_TABLESPACES_ENCRYPTION table in the Information Schema. This table provides information on which tablespaces are encrypted, which encryption key each tablespace is encrypted with, and whether the background encryption threads are currently working on the tablespace. Since the system tablespace can also contain tables, it can be helpful to join the INNODB_TABLESPACES_ENCRYPTION table with the INNODB_SYS_TABLES table to find out the encryption status of each specific table, rather than each tablespace. For example:
Redo Logs
Using data-at-rest encryption with InnoDB, the innodb_encrypt_tables system variable only encrypts the InnoDB tablespaces. In order to also encrypt the InnoDB Redo Logs, you also need to set the innodb_encrypt_log system variable.
Where the encryption key management plugin supports key rotation, the InnoDB Redo Log can also rotate encryption keys. In previous releases, the Redo Log can only use the first encryption key.
InnoDB encrypts the temporary tablespace using the encryption key with the ID 1.
InnoDB encrypts the Redo Log using the encryption key with the ID 1.
Keys with Manually Encrypted Tablespaces
With tables that use manually enabled encryption, one way to set the specific encryption key for the table is to use the ENCRYPTION_KEY_ID table option. For example:
With tables that use automatically enabled encryption, one way to set the specific encryption key for the table is to use the innodb_default_encryption_key_id system variable. For example:
InnoDB tables that are part of the system tablespace can only be encrypted using the encryption key set by the innodb_default_encryption_key_id system variable.
Some key management and encryption plugins allow you to automatically rotate and version your encryption keys. If a plugin support key rotation, and if it rotates the encryption keys, then InnoDB's background encryption threads can re-encrypt InnoDB pages that use the old key version with the new key version.
You can set the maximum age for an encryption key using the innodb_encryption_rotate_key_age system variable. When this variable is set to a non-zero value, background encryption threads constantly check pages to determine if any page is encrypted with a key version that's too old. When the key version is too old, any page encrypted with the older version of the key is automatically re-encrypted in the background to use a more current version of the key. Bear in mind, this constant checking can sometimes result in high CPU usage.
Key rotation for the InnoDB Redo Log is only supported in and later. For more information, see MDEV-12041.
In the event that you encounter issues with background key encryption, you can disable it by setting the innodb_encryption_rotate_key_age system variable to 0. You may find this useful when the constant key version checks lead to excessive CPU usage. It's also useful in cases where your encryption key management plugin does not support key rotation, (such as with the file_key_management plugin). For more information, see MDEV-14180.
There are, however, issues that can arise when the background key rotation is disabled.
Pending Encryption Operations
When updating the value on the innodb_encrypt_tables system variable, InnoDB internally treats the subsequent background operations to encrypt and decrypt tablespaces as background key rotations. See MDEV-14398 for more information.
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=YES ENCRYPTION_KEY_ID=100;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=YES;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
);
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTION_KEY_ID=100;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
-> FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
-> WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
How MariaDB manages encryption keys depends on which encryption key management solution you choose. Currently, MariaDB has three options:
File Key Management Plugin
The File Key Management plugin that ships with MariaDB is a basic key management and encryption plugin that reads keys from a plain-text file. It can also serve as an example and as a starting point when developing a key management plugin.
The AWS Key Management plugin is a key management and encryption plugin that uses the Amazon Web Services (AWS) Key Management Service (KMS). The AWS Key Management plugin depends on the AWS SDK for C++, which uses the Apache License, Version 2.0. The license is not compatible with MariaDB Server's , so we are not able to distribute packages that contain the AWS Key Management plugin. Therefore, the only way to currently obtain the plugin is to install it from the source.
Key management and encryption plugins support using multiple encryption keys. Each encryption key can be defined with a different 32-bit integer as a key identifier.
The support for multiple keys opens up some potential use cases. For example, let's say that a hypothetical key management and encryption plugin is configured to provide two encryption keys. One encryption key might be intended for "low security" tables. It could use short keys, which might not be rotated, and data could be encrypted with a fast encryption algorithm. Another encryption key might be intended for "high security" tables. It could use long keys, which are rotated often, and data could be encrypted with a slower but more secure encryption algorithm. The user would specify the identifier of the key that they want to use for different tables, only using high-level security where it's needed.
There are two encryption key identifiers that have special meanings in MariaDB. An encryption key 1 is intended for encrypting system data, such as InnoDB redo logs, binary logs, and so on. It must always exist when data-at-rest encryption is enabled. An encryption key 2 is intended for encrypting temporary data, such as temporary files and temporary tables. It is optional. If it doesn't exist, then MariaDB uses an encryption key 1 for these purposes instead.
Encryption key rotation is optional in MariaDB Server. Key rotation is only supported if the backend key management service (KMS) supports key rotation and if the corresponding key management and encryption plugin for MariaDB also supports key rotation. When a key management and encryption plugin supports key rotation, users can opt to rotate one or more encryption keys, which creates a new version of each rotated encryption key.
Key rotation allows users to improve data security in the following ways:
If the server is configured to automatically re-encrypt table data with the newer version of the encryption key after the key is rotated, then that prevents an encryption key from being used for long periods of time.
If the server is configured to simultaneously encrypt table data with multiple versions of the encryption key after the key is rotated, then that prevents all data from being leaked if a single encryption key version is compromised.
Details the File Key Management plugin, which reads encryption keys from a plain-text (or encrypted) file, serving as a simple solution or reference implementation for data-at-rest encryption.
MariaDB supports the use of multiple encryption keys. Each encryption key uses a 32-bit integer as a key identifier. If the specific plugin supports key rotation, then encryption keys can also be rotated, which creates a new version of the encryption key.
The File Key Management plugin that ships with MariaDB is a key management and encryption plugin that reads encryption keys from a plaintext file.
Overview
The File Key Management plugin is the for users who want to use . Some of the plugin's primary features are:
It reads encryption keys from a plain-text key file.
As an extra protection mechanism, the plain-text key file can be encrypted.
It supports multiple encryption keys.
It supports key rotation with MariaDB Enterprise Server from MariaDB Enterprise Server 11.8.
It can also serve as an example and as a starting point when developing a key management and encryption plugin with the encryption plugin API.
Installing the File Key Management Plugin's Package
The File Key Management plugin is included in MariaDB packages as the file_key_management.so or file_key_management.dll shared library. The shared library is in the main server package, so no additional package installations are necessary. The plug-in must be installed into MariaDB however as follows.
Installing the Plugin
Although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. The plugin can be installed by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an . For example:
Uninstalling the Plugin
Before you uninstall the plugin, you should ensure that is completely disabled, and that MariaDB no longer needs the plugin to decrypt tables or other files.
You can uninstall the plugin dynamically by executing or . For example:
If you installed the plugin by providing the or the options in a relevant server in an , then those options should be removed to prevent the plugin from being loaded the next time the server is restarted.
Creating the Key File
In order to encrypt your tables with encryption keys using the File Key Management plugin, create the file containing the encryption keys. File name and location don't matter; see in the following what configuration is needed.
For each encryption key, the file contains these options, separated by a semicolon:
The key identifier (format: 32-bit integer)
The key version (optional, and only available as of Enterprise Server 11.8)
The key itself (format: hex-encoded)
Entries look like this:
You can also optionally encrypt the key file to make it less accessible from the file system. That is explained further in the section below.
The File Key Management plugin uses to encrypt data. It supports 128-bit, 192-bit, and 256-bit encryption keys, just like the plugin does.
Generate random encryption keys using the command. To create a random 256-bit (32-byte) encryption key, run the following command:
The key file needs to have a key identifier for each encryption key added to the beginning of each line. Key identifiers do not have to be contiguous. For example, to append three new encryption keys to a new key file, issue these commands:
The resulting key file looks like this:
The key identifiers give you a way to reference the encryption keys from MariaDB. In the example above, you could reference these encryption keys using the key identifiers 1, 2 or 100 with the table option or with system variables such as . You do not necessarily need multiple encryption keys; an encryption key with the key identifier 1 is the only mandatory one.
If the key file is left unencrypted, the File Key Management plugin only requires the system variable to be configured.
This system variable can be specified as a command-line argument to mariadbd , or it can be specified in a server of an , like this:
Encrypting the Key File
If you use an unencrypted key file, keys are stored in plain text on your system, posing a security risk. It's recommended to encrypt the key file, with these hints in mind:
MariaDB only supports the mode of .
The encryption key size can be 128-bits, 192-bits, or 256-bits.
The encryption key is created from the hash of the encryption password.
Generate a random encryption password using the command. To create a random 256 character encryption password, execute the following:
Encrypt the key file using the command. To encrypt the key file with the encryption password created in the previous step, execute for example one of the following commands:
The resulting keys.enc file is the encrypted version of keys.txt file. Delete the unencrypted key file.
Having the key file encrypted requires both the and the system variables to be configured.
The file_key_management_filekey variable can be provided in two forms:
As a plain-text encryption password. This is not recommended, since the plain-text encryption password would be visible in the output of the statement.
Prefixed with FILE:, it can be a path to a file that contains the plain-text encryption password.
These variables can be specified as command-line arguments to mariadbd, or they can be specified in a relevant server in an :
Choosing an Encryption Algorithm
The File Key Management plugin currently supports two encryption algorithms for encrypting data: AES_CBC and AES_CTR. Both of these algorithms use in different modes. AES uses 128-bit blocks, and supports 128-bit, 192-bit, and 256-bit keys. The modes are:
The AES_CBC mode uses AES in the mode.
The AES_CTR mode uses AES in two slightly different modes in different contexts. When encrypting tablespace pages (such as pages in InnoDB, XtraDB, and Aria tables), it uses AES in the mode. When encrypting temporary files (where the cipher text is allowed to be larger than the plain text), it uses AES in the authenticated .
The recommended algorithm is AES_CTR, but this algorithm is only available when MariaDB is built with . If the server is built with then this algorithm is not available. See for more information about which libraries are used on which platforms.
Configuring the Encryption Algorithm
The encryption algorithm can be configured by setting the system variable.
This system variable can be set to one of the following values:
System Variable Value
Description
This system variable can be specified as command-line arguments to mariadbd, or it can be specified in a relevant server in an . For example:
Note that the option prefix is specified. This option prefix is used in case the plugin hasn't been installed yet.
Note that this variable does not affect the algorithm that MariaDB uses to decrypt the key file. This variable only affects the encryption algorithm that MariaDB uses to encrypt and decrypt data. The only algorithm that MariaDB currently supports to encrypt the key file is mode of .
Using the File Key Management Plugin
Once the File Key Management Plugin is enabled, you can use it by creating an encrypted table:
Now, table t will be encrypted using the encryption key from the key file. For more information on how to use encryption, see .
The provides information about files stored in tablespaces, such as those used by the InnoDB storage engine.
Using Multiple Encryption Keys
The File Key Management Plugin supports . Each encryption key can be defined with a different 32-bit integer as a key identifier.
When , the key that is used to encrypt tables . When , the key that is used to encrypt tables .
Key Rotation
The plugin supports key rotation and allows an optional key version in the key file. The keys can be rotated using the FLUSH FILE_KEY_MANAGEMENT_KEYS statement, without needing to restart the server. The plugin remains compatible with old key file format, and when version is not specified, it defaults to version 1.
Generation of New Key Versions
How would new key versions be generated?
The plugin doesn't generate any encryption keys itself, and it doesn't have a backend KMS to generate encryption keys for it either.
Any encryption keys need to be generated by the user with external tools, such as OpenSSL. For example:
Versions
Version
Status
Introduced
System Variables
file_key_management_encryption_algorithm
This system variable is used to determine which algorithm the plugin will use to encrypt data.
The recommended algorithm is AES_CTR, but this algorithm is only available when MariaDB is built with . If the server is built with then this algorithm is not available. See for more information about which libraries are used on which platforms.
Specifies the digest function to use in key derivation of the key used for decrypting the key file.
Command line: --file-key-management-digest=value
Scope: Global
file_key_management_filekey
Used to determine the encryption password that is used to decrypt the key file defined by .
If this system variable's value is prefixed with FILE:, then it is interpreted as a path to a file that contains the plain-text encryption password.
If this system variable's value is not prefixed with FILE:, then it is interpreted as the plain-text encryption password. However, this is not recommended.
file_key_management_filename
Used to determine the path to the file that contains the encryption keys. If is set, this file can be encrypted with mode of .
Controls how the server should treat the plugin when the server starts up.
Valid values are:
OFF - Disables the plugin without removing it from the table.
This page is licensed: CC BY-SA / Gnu FDL
Hashicorp Key Management Plugin
Guide to using the HashiCorp Key Management plugin, which integrates MariaDB with HashiCorp Vault for centralized, secure key storage and lifecycle management.
The Hashicorp Key Management Plugin is used to implement encryption using keys stored in the Hashicorp Vault KMS. For more information, see , and for how to install Vault, see , as well as .
The current version of this plugin implements the following features:
Authentication is done using Hashicorp Vault's token authentication method.
If additional client authentication is required, then the path to the CA authentication bundle file may be passed
as a plugin parameter;
It supports two different algorithms for encrypting data.
The encryption password has a maximum length of 256 characters.
Keys need to be manually saved to the key file. See this section.
The format of the key file is simplistic. It stores encryption keys in a plain-text file.
The File Key Management plugin does not support key rotation.
The encryption password has a max length of 256 characters.
The only algorithm that MariaDB currently supports when decrypting the key file is Cipher Block Chaining (CBC) mode of Advanced Encryption Standard (AES). The encryption key size can be 128-bits, 192-bits, or 256-bits. The encryption key is calculated by taking a SHA-1 hash of the encryption password. Instead of SHA-1, SHA-2 can be used as well.
Command line: --file-key-management-filekey=value
Scope: Global
Dynamic: No
Data type: string
Default value: (empty)
Dynamic: No
Data type: string
Default value: (empty)
Dynamic: No
Data type: numeric
Default value: 0
Valid values: integers ≥ 0 (reasonable value: 600000 for sha256, less for sha512)
Available from MariaDB 12.0.1
ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.
FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
Data is encrypted using AES either in the Counter (CTR) mode or in the authenticated Galois/Counter Mode (GCM) mode, depending on context. This is only supported in some builds. See the previous section for more information.
1.0
Stable
From MariaDB 10.1.18
To avoid startup failures, the loose option prefix can be specified. No error is produced in case the plugin hasn't been installed yet.
This step is optional, but highly recommended.
Use the -iter (iterations) option with -pbkdf2 to specify more iterations than the OpenSSL default of 10k.
To use -pbkdf2 effectively, specify the iteration count on the MariaDB Server side. Without this, key decryption will fail. Use the option --file_key_management_use_pbkdf2=number_of_iterations to set it in MariaDB Server.
FILE: must be in uppercase in MariaDB. If you use lowercase instead (like OpenSSL does), errors occur (for instance, mariadbd: Cannot decrypt ../key.enc. Wrong key?).
To avoid startup failures, the loose option prefix can be specified. No error is produced in case the plugin hasn't been installed yet.
The following Information Schema table is available from MariaDB 11.8.
The creation of the keys and their management is carried out using the Hashicorp Vault KMS and its tools.
The plugin uses libcurl (https) as an interface to the HashiCorp Vault server.
JSON parsing is performed through the JSON service (through the include/mysql/service_json.h).
HashiCorp Vault 1.2.4 was used for development and testing.
As of MariaDB 10.6.24, the plugin is configured to use cached keys for all communication errors, not just for timeouts. This ensures continuous operation when the Vault server is temporarily unreachable.
As of MariaDB 10.6.24, the default setting for cache usage on error is ON.
Since we require support for key versioning, the key-value storage must be configured in Hashicorp Vault as a key-value storage that uses the interface of the second version. For example, you can create it as follows:
Key names must correspond to their numerical identifiers. Key identifiers themselves, their possible values, and rules of use are described in more detail in the MariaDB main documentation.
From the point of view of the key-value storage (in terms of Hashicorp Vault), the key is a secret containing one key-value pair with the name "data" and a value representing a binary string
containing the key value, for example:
Key values are strings containing binary data. MariaDB currently uses the AES algorithm with 256-bit keys as the default encryption method. In this case, the keys that will be stored in the Hashicorp Vault should be 32-byte strings. Most likely, you will use some utilities for creating and administering keys designed to work with Hashicorp Vault. But in the simplest case, keys can be created from the command line through the vault utility, for example, as follows:
If you use default encryption (AES), you should ensure that the key length is 32 bytes, as it may fail to use InnoDB as a data storage.
The plugin currently does not unseal Hashicorp Vault on its own; you must do this in advance and on your own.
To use Hashicorp Vault KMS, the plugin must be preloaded and activated on the server. Most of its parameters should not be changed during plugin operation and therefore must be preconfigured as part of the server configuration through configuration file or command line options:
Options
The plugin supports the following parameters, which must be set in advance and cannot be changed during server operation:
hashicorp-key-management-vault-url
Description: HTTP[s] URL that is used to connect to the Hashicorp Vault server. It must include the name of the scheme (https:// for a secure connection) and, according to the API rules for storage of the key-value type in Hashicorp Vault, after the server address, the path must begin with the "/v1/" string (as prefix), for example: https://127.0.0.1:8200/v1/my_secrets. By default, the path is not set; therefore, you must replace it with the correct path to your secrets.
Description: An Authentication token that is passed to the Hashicorp Vault in the request header. By default, this parameter contains an empty string, so you must specify the correct value for it, otherwise the Hashicorp Vault server will refuse authorization. Alternatively, you can define an environment variable VAULT_TOKEN and store the token there.
Description: Path to the Certificate Authority (CA) bundle (is a file that contains root and intermediate certificates). By default, this parameter contains an empty string, which means no CA bundle.
Description: Set the duration (in seconds) for the Hashicorp Vault server connection timeout. The default value is 15 seconds. The allowed range is from 1 to 86400 seconds. The user can also specify a zero value, which means the default timeout value set by the libcurl library (currently 300 seconds).
Description: This parameter instructs the plugin to use the key values or version numbers taken from the cache in the event of a timeout when accessing the vault server. By default, this option is disabled. Please note that key values or version numbers will be read from the cache when the timeout expires, only after the number of attempts to read them from the storage server that specified by the --[loose-]hashicorp-key-management-max-retries parameter has been exhausted.
Description: The time (in milliseconds) after which the value of the key stored in the cache becomes invalid and an attempt to read this data causes a new request to be sent to the vault server. By default, cache entries become invalid after 60,000 milliseconds (after one minute). If the value of this parameter is zero, then the keys will always be considered invalid, but they still can be used if the vault server is unavailable and the corresponding cache operating mode (--[loose-]hashicorp-key-management-use-cache-on-timeout="on") is enabled.
As of MariaDB 10.6.24, the default value is 1 year (specified in milliseconds).
Description: The time (in milliseconds) after which the information about the latest version number of the key (which is stored in the cache) becomes invalid and an attempt to read this information causes a new request to be sent to the vault server. If the value of this parameter is zero, then information about the latest key version numbers is always considered invalid, unless there is no communication with the vault server, and use of the cache is allowed when the server is unavailable. By default, this parameter is zero, that is, the latest version numbers for the keys stored in the cache are considered always invalid, except when the vault server is unavailable and use
of the cache is allowed on server failures.
Description: This parameter enables ("on", this is the default value) or disables ("off") checking the kv storage version during plugin initialization. The plugin requires storage version 2 or later in order for it to work properly.
When this option is enabled, the configured Vault token must also have read access to thesys/mounts/my_vault/tuneendpoint, allowing the plugin to determine the kv storage version. See Required Vault Token Permissions for details.
Required unless hashicorp-key-management-check-kv-version is set to off
Note: During plugin initialization, the kv storage version is verified using the sys/mounts/my_vault/tune path. Most configurations require this permission because hashicorp-key-management-check-kv-version is set to on by default.
Example Minimal Vault Policy for Read-Only Access
The following example provides a minimal Vault policy that grants the required privileges:
Replace my_vault with the path segment of your hashicorp-key-management-vault-url configuration.
This ensures that the plugin does not require additional permissions to access encryption keys or verify the kv storage version.
Key Rotation and Cache Flushing
The HashiCorp Key Management plugin supports key versioning provided by the HashiCorp Vault Server. In previous versions, rotating keys required a server restart to clear the internal cache. As of MariaDB 12.3, you can flush the plugin cache manually while the server is running.
Flushing the Cache
To rotate keys, you must flush the cached keys using the FLUSH command. This clears the local cache, forcing the server to re-fetch the latest key versions from the HashiCorp Vault server upon the next access.
Executing this command requires the RELOAD privilege.
Verifying Key Versions
To view the current Key IDs and Key Versions stored in the latest version cache, you can query the Information Schema table or use the SHOW command.
As of MariaDB 12.3, you can manually rotate keys and flush the cache without restarting the server. See Key Rotation and Cache Flushing for details.
The configured Vault token requires specific read-only permissions. See Required Vault Token Permissions for details to prevent authorization failures during plugin initialization.
CREATE TABLE t (i INT) ENGINE=InnoDB ENCRYPTED=YES
-- Generate a new 256-bit key
openssl rand -hex 32
~$ vault secrets enable -path /test -version=2 kv
~$ vault kv get /test/1
====== Metadata ======
Key Value
--- -----
created_time 2019-12-14T14:19:19.42432951Z
deletion_time n/a
destroyed false
version 1
==== Data ====
Key Value
--- -----
data 0123456789ABCDEF0123456789ABCDEF
~$ vault kv put /test/1 data="0123456789ABCDEF0123456789ABCDEF"
Introduction to the AWS Key Management plugin, which uses Amazon KMS to generate and store master keys, decrypting them at startup to enable data-at-rest encryption with key rotation support.
Due to license incompatibilities between the MariaDB server source code and the Amazon AWS C++ SDK, we can only distribute the plugin in source code form, and not as ready-to-use binaries. See Installing the Plugin's Package for details.
MariaDB supports the use of multiple encryption keys. Each encryption key uses a 32-bit integer as a key identifier. If the specific plugin supports key rotation, then encryption keys can also be rotated, which creates a new version of the encryption key.
The AWS Key Management plugin uses the to generate and store AES keys on disk, in encrypted form, using the Customer Master Key (CMK) kept in AWS KMS. When MariaDB Server starts, the plugin will decrypt the encrypted keys, using the AWS KMS "Decrypt" API function. MariaDB data will then be encrypted and decrypted using the AES key. It supports multiple encryption keys. It supports key rotation.
Tutorials
Tutorials related to the AWS Key Management plugin can be found at the following pages:
Preparation
Before you use the plugin, you need to create a Customer Master Key (CMK). Create a key using the AWS Console as described in the .
The easiest way to give the AWS key management plugin access to the key is to create an IAM Role with access to the key, and to apply that IAM Role to an EC2 instance where MariaDB Server runs.
Make sure that MariaDB Server runs under the correct AWS identity that has access to the above key. For example, you can store the AWS credentials in an AWS credentials file for the user who runs mysqld. More information about the credentials file can be found in .
Installing the Plugin's Package
The AWS Key Management plugin depends on the , which uses the . This license is not compatible with MariaDB Server's , so we are not able to distribute packages that contain the AWS Key Management plugin. Therefore, the only way to currently obtain the plugin is to install it from source.
Installing from Source
When , the AWS Key Management plugin is built by default, on systems that support it.
Compilation is controlled by the following cmake arguments:
-DPLUGIN_AWS_KEY_MANAGEMENT=DYNAMIC to build a loadable plugin library
-DAWS_SDK_EXTERNAL_PROJECT=ON to download the AWS C++ SDK code
-DNOT_FOR_DISTRIBUTION=ON to confirm that you know to not distribute the resulting binaries
The plugin uses , which introduces the following restrictions:
The plugin can only be built on Windows, Linux, and macOS.
The plugin requires that one of the following compilers is used: gcc 4.8 or later, clang 3.3 or later, Visual Studio 2013 or later.
On Unix, the libcurl development package (e.g.
You do not need to download/install the AWS C++ SDK yourself; the correct version of the SDK GitHub repository will be cloned into the build directory at build time, and only the libraries for AWS components actually needed by the key management plugin will be built, which takes much less time than building the full AWS C++ SDK.
Building on Linux
With all prerequisites installed, the actual build process pretty much comes down to:
Cmake will print the following warning as part of its output. Please take it seriously and do not distribute the aws_key_management.so file to any third parties: You have linked MariaDB with Apache 2.0 libraries! You may not distribute the resulting binary. If you do, you will put yourself into a legal problem with the Free Software Foundation.
After make succeeded you can copy the created aws_key_management.so plugin library file to the plugin directory of your actual MariaDB Server machines installation, e.g. /usr/lib64/mysql/plugin on RedHat/Fedora based systems or /usr/lib/mysql/plugin on Debian-based systems.
Installing the Plugin
Even after the package that contains the plugin's shared library is installed on the operating system, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.
The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing or . For example:
The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an . For example:
Uninstalling the Plugin
Before you uninstall the plugin, you should ensure that is completely disabled, and that MariaDB no longer needs the plugin to decrypt tables or other files.
You can uninstall the plugin dynamically by executing or . For example:
If you installed the plugin by providing the or the options in a relevant server in an , then those options should be removed to prevent the plugin from being loaded the next time the server is restarted.
Configuring the AWS Key Management Plugin
To enable the AWS Key Management plugin, you also need to set the plugin's system variables. The system variable is the primary one to set. These system variables can be specified as command-line arguments to or they can be specified in a relevant server in an . For example:
Once you've updated the configuration file, restart the MariaDB server to apply the changes and make the key management and encryption plugin available for use.
Using the AWS Key Management Plugin
Once the AWS Key Management Plugin is enabled, you can use it by creating an encrypted table:
Now, table t will be encrypted using the encryption key generated by AWS.
For more information on how to use encryption, see .
Using Multiple Encryption Keys
The AWS Key Management Plugin supports . Each encryption key can be defined with a different 32-bit integer as a key identifier. If a previously unused identifier is used, then the plugin will automatically generate a new key.
When , the key that is used to encrypt tables .
When , the key that is used to encrypt tables .
Key Rotation
The AWS Key Management plugin does support . To rotate a key, set the system variable. For example, to rotate key with ID 2:
Or to rotate all keys, set the value to -1:
Versions
Version
Status
Introduced
System Variables
aws_key_management_key_spec
Description: Encryption algorithm used to create new keys
Commandline: --aws-key-management-key-spec=value
Scope: Global
aws_key_management_log_level
Description: Dump log of the AWS SDK to MariaDB error log. Permitted values, in increasing verbosity, are Off (default), Fatal, Error, Warn, Info, Debug, and Trace.
Commandline: --aws-key-management-log-level=value
Scope: Global
aws_key_management_master_key_id
Description: AWS KMS Customer Master Key ID (ARN or alias prefixed by alias/) for the master encryption key. Used to create new data keys. If not set, no new data keys will be created.
Description: Set this variable to a data key ID to perform rotation of the key to the master key given in aws_key_management_master_key_id. Specify -1 to rotate all keys.
Description: Controls how the server should treat the plugin when the server starts up.
Valid values are:
OFF - Disables the plugin without removing it from the table.
This page is licensed: CC BY-SA / Gnu FDL
InnoDB: Enabling Encryption
Step-by-step guide to enabling encryption for InnoDB, covering the configuration of innodb_encrypt_tables for automatic encryption and the use of ENCRYPTED=YES table options for per-table encryption.
In order to enable data-at-rest encryption for tables using the InnoDB storage engines, you first need to configure the server to use an Encryption Key Management plugin. Once this is done, you can enable encryption by setting the innodb_encrypt_tables system variable to encrypt the InnoDB system and file tablespaces and setting the innodb_encrypt_log system variable to encrypt the InnoDB Redo Log.
Setting these system variables enables the encryption feature for InnoDB tables on your server. To use the feature, you need to use the ENCRYPTION_KEY_ID table option to set what encryption key you want to use and set the ENCRYPTED table option to enable encryption.
When encrypting any InnoDB tables, the best practice is also enable encryption for the Redo Log. If you have encrypted InnoDB tables and have not encrypted the Redo Log, data written to an encrypted table may be found unencrypted in the Redo Log.
Enabling Encryption for Automatically Encrypted Tablespaces
The system variable controls the configuration of automatic encryption of InnoDB tables. It has the following possible values:
Option
Description
When is set to ON, InnoDB tables are automatically encrypted by default. For example, the following statements create an encrypted table and confirm that it is encrypted:
When is set to ON, an unencrypted InnoDB table can be created by setting the table option to NO for the table. For example, the following statements create an unencrypted table and confirm that it is not encrypted:
When is set to FORCE, InnoDB tables are automatically encrypted by default, and unencrypted InnoDB tables can not be created. In this scenario, if you set the table option to NO for a table, then you will encounter an error. For example:
When is set to ON or FORCE, then you must ensure that is set to a non-zero value, so that InnoDB can perform any necessary encryption operations in the background. See for more information about that. must also be set to a non-zero value for the initial encryption operations to happen in the background. See for more information about that.
Enabling Encryption for Manually Encrypted Tablespaces
If you do not want to automatically encrypt every InnoDB table, then it is possible to manually enable encryption for just the subset of InnoDB tables that you would like to encrypt. MariaDB provides the and table options that can be used to manually enable encryption for specific InnoDB tables. These table options can be used with and statements. These table options can only be used with InnoDB tables that have their own , meaning that tables that were created with set.
Table Option
Value
Description
You can manually enable or disable encryption for a table by using the table option. If you only need to protect a subset of InnoDB tables with encryption, then it can be a good idea to manually encrypt each table that needs the extra protection, rather than encrypting all InnoDB tables globally with . This allows you to balance security with speed, as it means the encryption and decryption performance overhead only applies to those tables that require the additional security.
If a manually encrypted InnoDB table contains a , then the internal table for the full-text index will not also be manually encrypted. To encrypt internal tables for InnoDB full-text indexes, you must by setting to ON or FORCE.
You can also manually specify a for a table by using the table option. This allows you to use different encryption keys for different tables. For example, you might create a table using a statement like this:
If the table option is not specified, then the table will be encrypted with the key identified by the system variable. For example, you might create a table using a statement like this:
In the event that you have an existing table and you want to manually enable encryption for that table, then you can do the same with an statement. For example:
InnoDB does not permit manual encryption changes to tables in the tablespace using . Encryption of the tablespace can only be configured by setting the value of the system variable. This means that when you want to encrypt or decrypt the tablespace, you must also set a non-zero value for the system variable, and you must also set the system variable to 1 to ensure that the system tablespace is properly encrypted or decrypted by the background threads. See for more information.
Enabling Encryption for Temporary Tablespaces
The system variable controls the configuration of encryption for the . It has the following possible values:
Option
Description
This system variable can be specified as a command-line argument to or it can be specified in a relevant server in an . For example:
Enabling Encryption for the Redo Log
InnoDB uses the in crash recovery. By default, these events are written to file in an unencrypted state. In configuring MariaDB for data-at-rest encryption, ensure that you also enable encryption for the Redo Log.
To encrypt the Redo Log, first the server process. Then, set the to ON in a relevant server in an . For example:
Then, start MariaDB. When the server starts back up, it checks to recover InnoDB in the event of a crash. Once it is back online, it begins writing encrypted data to the Redo Log.
Key rotation for the Redo Log is supported. See for more information.
See Also
This page is licensed: CC BY-SA / Gnu FDL
Data-at-Rest Encryption (TDE) Fundamentals
MariaDB Data-at-Rest Encryption, also known as Transparent Data Encryption (TDE), protects your data by encrypting files directly on the storage media. This ensures that if physical disks or backup files are stolen, the data remains unreadable without the corresponding encryption keys.
Security Context
OFF
Disables table encryption.
ON
Enables table encryption, but allows unencrypted tables to be created.
FORCE
Enables table encryption, and doesn't allow unencrypted tables to be created.
ENCRYPTED
YES / NO / DEFAULT
Defines whether or not to encrypt the table. DEFAULT means that the decision is based on the global innodb_encrypt_tables setting.
ENCRYPTION_KEY_ID
32-bit integer
Defines the identifier for the encryption key to use
SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
);
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=NO;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 0 | 100 |
+----------+-------------------+----------------+
SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables='FORCE';
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=NO;
ERROR 1005 (HY000): Can't create table `db1`.`tab1` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------+
| Warning | 140 | InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 |
| Warning | 140 | InnoDB: ENCRYPTED=NO cannot be used with innodb_encrypt_tables=FORCE |
| Error | 1005 | Can't create table `db1`.`tab1` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+----------------------------------------------------------------------+
4 rows in set (0.00 sec)
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=YES ENCRYPTION_KEY_ID=100;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=YES;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=NO;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 0 | 100 |
+----------+-------------------+----------------+
ALTER TABLE tab1
ENCRYPTED=YES ENCRYPTION_KEY_ID=100;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
[mariadb]
innodb_encrypt_temporary_tables=ON
[mariadb]
innodb_encrypt_log = ON
libcurl3-dev
on Debian Jessie),
uuid
development package, and
openssl
need to be installed.
You may need to use a newer version of cmake than is provided by default in your OS.
Experimental
Dynamic: No
Data Type: enumerated
Default Value: AES_128
Valid Values: AES_128, AES_256
Dynamic: No
Data Type: enumerated
Default Value: Off
Valid Values: Off, Fatal, Warn, Info, Debug and Trace
Dynamic: No
Data Type: string
Default Value:
Dynamic: No
Data Type: boolean
Default Value: OFF
Valid Values: OFF, ON
Dynamic: No
Data Type: string
Default Value: 'us-east-1'
Dynamic: No
Data Type: integer
Default Value: 0
Dynamic: Yes
Data Type: integer
Default Value:
ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.
FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT
# clone the MariaDB Server source code repository
git clone https://github.com/MariaDB/server.git
cd server
# prepare the build
mkdir _build
cd _build
cmake .. -DNOT_FOR_DISTRIBUTION=ON \
-DPLUGIN_AWS_KEY_MANAGEMENT=DYNAMIC \
-DAWS_SDK_EXTERNAL_PROJECT=1
# build the plugin only
cd plugin/aws_key_management
make
Physical Theft: Unauthorized access to hard drives or storage arrays.
Backup Exposure: If database backups are intercepted or accessed by unauthorized parties.
Service Provider Access: Preventing infrastructure administrators in cloud or managed environments from viewing sensitive data.
When Encryption is No Help
Authorized Access: Users with valid SQL credentials will still see decrypted data; TDE does not replace a robust User Account Management strategy.
Application Vulnerabilities: TDE does not prevent SQL injection or application-level data breaches.
Data-in-Transit: This feature only secures stored data. Use TLS/SSL to secure data moving across the network.
Architecture and Lifecycle
MariaDB performs encryption at the I/O layer. This process is "transparent" because applications and queries do not need to be modified to interact with encrypted data.
Once a Key Management plugin is configured, encryption occurs automatically whenever MariaDB writes pages to disk, and decryption occurs when data is read back into memory.
Write Path: When MariaDB flushes data from the buffer pool to the disk, the data is encrypted.
Read Path: When MariaDB reads data from the disk into memory, it is decrypted.
Memory: Data stored in RAM (such as the buffer pool) remains in a decrypted state while in use.
Storage Engine Support
MariaDB provides flexible control over what is encrypted, though support and requirements vary by component:
InnoDB: Fully supported. You can encrypt all tablespaces, individual tables, and the InnoDB redo log. For details, see InnoDB Encryption.
Aria: Supported only for tables created with ROW_FORMAT=PAGE (the default). For details, see Aria Encryption.
Binary Logs: MariaDB can encrypt binary logs and relay logs to protect the replication pipeline. See Managing Binary Log Encryption.
Temporary Files: Internal on-disk temporary files can be encrypted by setting encrypt_tmp_files=ON.
Limitations
The following elements are not encrypted by the MariaDB server:
Metadata: Information in .frm files and the system data dictionary.
Specific Logs: The MariaDB Error Log, General Query Log, and Slow Query Log.
Aria Control Log: While Aria tables can be encrypted, the Aria storage engine log is not currently encrypted.
Utilities: Tools like mariadb-binlog require the --read-from-remote-server flag to read encrypted content.
Key Management
Encryption requires a key management and encryption plugin. These plugins are responsible for managing the 32-bit integer key identifiers and performing the cryptographic operations. You must install and configure a Key Management Plugin before enabling encryption options for storage engines.
Enabling Data-at-Rest Encryption
Enabling data-at-rest encryption encompasses these steps:
Enabling encryption for Aria tables, InnoDB tables, or both.
Verifying encryption is turned on.
1
Create the key file.
In this step, we create a key file that fulfills basic requirements. It contains only one unencrypted key, which is good enough to perform the rest of the steps. It is highly recommended, though, to use multiple keys, and to encrypt the key files. See Creating the Key File.
Run these commands to create an encryption folder, and a 32 byte (256 bit) long key file within that folder.
mkdir -p /etc/mysql/encryption
echo $(echo -n "1;1;" ; openssl rand -hex 32) | sudo tee -a /etc/mysql/encryption/keyfile.txt
Run these commands to create an encryption folder, and a 32 byte (256 bit) long key file within that folder.
2
Install and enable a key management plugin.
Add the following to your configuration file (for instance, my.cnf), then restart the server so the changes take effect:
For details about file name, file key, and encryption algorithm, see .
3
Enable encryption.
You can encrypt a number of database objects by setting the respective variables:
InnoDB user tables –
InnoDB redo log –
4
Verify encryption is turned on.
Make sure that the global encryption you configured is turned on, by issuing this statement:
If encryption of all database objects is successfully enabled, this is the output of the query:
Disabling Data-at-Rest Encryption
If you determine that encryption is no longer necessary, you can revert the system to an unencrypted state.
Prerequisites
Encryption Status: MariaDB Enterprise Server must currently have data-at-rest encryption enabled and active.
Key Management Access: You must have the original key management plugin active and the correct keys loaded to facilitate the decryption of the data.
Sufficient Disk Space: Ensure adequate free space is available to accommodate the rewritten, unencrypted data files.
1
Decrypt tables.
Disable global encryption at the storage engine level by issuing these statements. Note that some variables cannot be turned off at runtime – they have to be removed from the server configuration (for instance, my.cnf), and require a server restart:
SET GLOBAL innodb_encrypt_tables = OFF; -- InnoDB tables
SET GLOBAL aria_encrypt_tables = OFF; -- Aria tables
SET GLOBAL encrypt_tmp_disk_tables = OFF; -- Aria temporary tables
Any per-table encryption for InnoDB tables explicitly created with ENCRYPTED=YES must be manually decrypted using ALTER TABLE table_name ENCRYPTED=NO.
2
Disable log and temp encryption.
Update your configuration file (my.cnf) to stop encrypting Aria and InnoDB tables, as well as new logs and temporary files:
3
Monitor decryption progress.
You must wait for the background threads to finish decrypting data pages before removing the keys. Monitor the status for InnoDB tables via the Information Schema (there's no built-in way to monitor the status for Aria tables):
4
Remove the key management plugin.
Only after all tables and logs are confirmed as unencrypted should you uninstall the encryption plugin and remove its configuration from your my.cnf file. For instance, if you're using the , uninstall it:
Remove its configuration, then restart the server:
5
Verify encryption is turned off.
Use to verify encryption is turned off.
TDE in Action: Real-World Behavior
Transparent Data Encryption (TDE) is designed to protect data "at rest" while remaining completely invisible to standard SQL operations. With encryption turned on, MariaDB applies encryption to all new tables automatically.
Automatic Encryption (InnoDB & Aria)
For the subsequent instructions, we use these tables. With encrytion turned on, note that they're encrypted without using any special syntax:
Verifying Encryption Status
Because InnoDB and Aria handle data differently, you use two different methods to verify their encryption status.
For InnoDB
Check the information_schema to see the tablespace encryption scheme. If it's set to 1, the table is encrypted.
For Aria
There's no built-in way to check the encryption status of Aria tables, but you can check the contents of their .MAD files (the Aria data files) with commands like cat:
If you see unintelligible output rather than something resembling data, the Aria table is encrypted.
Transparent Data Access
Whether the table is InnoDB or Aria, the encryption is transparent. Standard SQL statements work without modification, as the engine decrypts the data in memory (the Buffer Pool for InnoDB or Page Cache for Aria).
Manual Control: Disabling Encryption
If you need to exempt a specific table from the global encryption policy, use the ENCRYPTED attribute.
Decrypting the Tables
Decrypting tables is done with an ALTER TABLE statement. For InnoDB tables, you can do this while global InnoDB table encryption is turned on.
Reencrypting the Tables
-- Create an InnoDB table
CREATE TABLE sensitive_accounts_innodb (
id INT PRIMARY KEY,
account_name VARCHAR(100)
) ENGINE=InnoDB;
-- Create an Aria table
-- Note that the ROW_FORMAT must be PAGE; otherwise, encryption isn't possible
CREATE TABLE sensitive_accounts_aria (
id INT PRIMARY KEY,
account_name VARCHAR(100)
) ENGINE=Aria ROW_FORMAT=PAGE;
SELECT NAME, ENCRYPTION_SCHEME
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME LIKE '%sensitive_accounts_innodb%';
sudo cat /var/lib/mysql/test/sensitive_accounts_aria.MAD | more
-- Standard inserts work for both
INSERT INTO sensitive_accounts_innodb VALUES (1, 'Customer InnoDB');
INSERT INTO sensitive_accounts_aria VALUES (1, 'Customer Aria');
-- Standard selects return plain text
SELECT * FROM sensitive_accounts_innodb;
SELECT * FROM sensitive_accounts_aria;
-- Disable encryption for the InnoDB table
ALTER TABLE sensitive_accounts_innodb ENCRYPTED=NO;
SELECT NAME, ENCRYPTION_SCHEME FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME LIKE '%sensitive_accounts_innodb%';
+--------------------------------+-------------------+
| NAME | ENCRYPTION_SCHEME |
+--------------------------------+-------------------+
| test/sensitive_accounts_innodb | 0 |
+--------------------------------+-------------------+
-- Disable encryption for the Aria table
-- This is only possible after disabling global Aria encryption
ALTER TABLE sensitive_accounts_aria ENGINE=Aria, ALGORITHM=COPY;
ALTER TABLE sensitive_accounts_innodb ENCRYPTED=YES;
SELECT NAME, ENCRYPTION_SCHEME FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME LIKE '%sensitive_accounts_innodb%';
+--------------------------------+-------------------+
| NAME | ENCRYPTION_SCHEME |
+--------------------------------+-------------------+
| test/sensitive_accounts_innodb | 1 |
+--------------------------------+-------------------+
Enterprise Server provides encryption for Galera Cluster via GCache.
How GCache encryption is enabled and disabled is detailed .
For the rest of this page, we assume you're using the . If you're using a different one, adjust the configuration and SQL statements accordingly.
The path used in these instructions (/etc/mysql/encryption/) is common for most Linux systems. Adjust if your operating system uses a different path.
MariaDB server must be restarted several times. On most Linux systems, the command for that is sudo systemctl restart mariadb. Consult your operating system documentation to find out what your system uses.
If you're getting errors, particularly if MariaDB fails to start, for example due to a configuration issue, inspect the . On many modern Linux versions, this can be done using journalctl. Example:
This is only possible for InnoDB tables.
For Aria tables, you can only do this when global Aria table encryption is turned off.
InnoDB: Encryption Troubleshooting
Solutions for common issues such as Error 1005 (Wrong create options) when configuring encryption, and handling cases where encryption key IDs are set for unencrypted tables.
Wrong Create Options
With InnoDB tables using encryption, there are several cases where a CREATE TABLE or ALTER TABLE statement can throw Error 1005, due to the InnoDB error 140, Wrong create options:
When this occurs, you can usually get more information about the cause of the error by following it with a SHOW WARNINGS statement.
This error is known to occur in the following cases:
Encrypting a table by setting the table option to YES when the is set to OFF.In this case, would return the following:
Encrypting a table by setting the table option to YES, and the system variable or the table option refers to a non-existent key identifier. In this case, would return the following:
In some versions, this could happen while creating a table with the table option set to DEFAULT while the system variable is set to OFF, and the system variable or the table option are not set to 1. In this case, would return the following:
Creating a table with the table option set to DEFAULT while the system variable is set to OFF, and the system variable or the table option are not set to 1 no longer fail, and it no longer throws a warning.
For more information, see .
Setting Encryption Key ID For an Unencrypted Table
If you set the table option for a table that is unencrypted because the system variable is set to OFF and the table option set to DEFAULT, then this encryption key ID will be saved in the table's .frm file, but the encryption key will not be saved to the table's .ibd file.
As a side effect, with the current encryption design, if the system variable is later set to ON, and InnoDB goes to encrypt the table, then the will not read this encryption key ID from the .frm file. Instead, the threads may encrypt the table with the encryption key with ID 1, which is internally considered the default encryption key when no key is specified. For example:
A similar problem is that, if you set the table option for a table that is unencrypted because the table option is set to NO, then this encryption key ID will be saved in the table's .frm file, but the encryption key will not be saved to the table's .ibd file.
Recent versions of MariaDB will throw warnings in the case where the table option is set to NO, but they will allow the operation to succeed. For example:
However, in this case, if you change the table option to YES or DEFAULT with , then it will actually use the proper key. For example:
For more information, see , , and .
Tablespaces Created on MySQL 5.1.47 or Earlier
MariaDB's data-at-rest encryption implementation re-used previously unused fields in InnoDB's buffer pool pages to identify the encryption key version and the post-encryption checksum. Prior to MySQL 5.1.48, these unused fields were not initialized in memory due to performance concerns. These fields still had zero values most of the time, but since they were not explicitly initialized, that means that these fields could have occasionally had non-zero values that could have been written into InnoDB's tablespace files. If MariaDB were to encounter an unencrypted page from a tablespace file that was created on an early version of MySQL that also had non-zero values in these fields, then it would mistakenly think that the page was encrypted.
The fix for changed the way that MariaDB distinguishes between encrypted and unencrypted pages, so that it is less likely to mistake an unencrypted page for an encrypted page.
If is set to full_crc32 or strict_full_crc32, and if the table does not use , then data files are guaranteed to be zero-initialized.
For more information, see .
Spatial Indexes
Support for encrypting . To enable, set the to full_crc32 or to strict_full_crc32. Note that MariaDB only encrypts spatial indexes when the table option is not set to .
For more information, see .
Managing CPU Usage During InnoDB Encryption Operations
When encryption threads are enabled for InnoDB tables, the system initiates background threads to encrypt existing tablespace pages. During this initial process, you may see significant spikes in CPU and I/O usage. This can occur due to:
Periodic key rotation checks that trigger re‑encryption work.
Large datasets being encrypted, especially if many tables are enabled at once.
Clustered environments such as MariaDB Galera Cluster, where each node performs encryption operations independently.
CPU usage can increase under several conditions. Common contributing factors are included below.
Causes of CPU Spikes
CPU usage increases during table encryption due to the following factors:
CPU usage spikes after enabling encryption: This is expected because InnoDB must begin encrypting existing tablespace pages in the background once encryption is turned on. The initial workload is heavy.
Increased disk I/O activity: The process involves reading unencrypted pages, encrypting them, and writing them back as encrypted. This read–encrypt–write cycle generates both CPU load (for encryption) and disk I/O load (for page movement).
Background threads encrypting tablespace pages: InnoDB background threads actively encrypt tablespace pages that were encrypted before encryption was enabled. If too many threads are active or configured aggressively, they compete for CPU resources, causing spikes.
This behavior is normal during the one‑time setup phase when InnoDB begins encrypting existing tablespace pages. However, the impact can be reduced through:
Tuning system variables such as innodb_encryption_threads, innodb_encryption_rotation_iops, and innodb_encryption_rotate_key_age.
Monitoring background encryption activity to check progress and identify bottlenecks.
Configuration Options
Monitor Background Encryption Activity
MariaDB performs encryption in background threads. This helps determine if CPU usage is due to ongoing table encryption or key rotation.
Before changing configuration, verify whether background encryption is currently running by executing the following command:
You can review the encryption activity output.
Additionally, you can check the essential encryption-related status variables, as detailed in .
Disable Key Rotation Checks
The innodb_encryption_rotate_key_age system variable manages how often InnoDB checks tablespace pages to determine whether they need to be re‑encrypted with a new key.
Setting this variable to 0 disables automatic key rotation checks if you do not require periodic re-encryption of old pages:
Once disabled, key rotation checks can significantly reduce CPU usage. See .
Limit Background Encryption Threads
The innodb_encryption_threads system variable controls the number of background encryption threads.
Lowering this value can prevent CPU contention at the cost of slower encryption progress.
You can maintain the value based on available CPU capacity and workload requirements. See .
Reduce Encryption Rotation IOPS
The innodb_encryption_rotation_iops system variable defines the maximum number of I/O operations per second that InnoDB background threads can use for page encryption and key rotation tasks.
Effect of Lowering the Value:
Reduces CPU usage: fewer encryption operations are performed per second, lowering the load on CPU resources.
Lowers disk pressure: decreases the number of read/write operations, reducing contention on storage I/O.
Setting a lower value (e.g., 25 or 50) limits the threads, making them read and write data more slowly, reducing both I/O and the associated CPU overhead. See .
Operational Best Practices
In addition to configuration changes, you can also manage encryption more effectively by applying the following operational practices:
Encrypt table in batches: Instead of enabling encryption for all tables at once, apply it in stages. This allow background encryption to complete for one set of tables and then proceed with more. With this approach, peak CPU and I/O load can be reduced.
Galera Cluster setup considerations:
Encryption operations are performed independently on each node
See Also
This page is licensed: CC BY-SA / Gnu FDL
Amazon Web Services (AWS) Key Management Service (KMS) Encryption Plugin Setup Guide
Advanced configuration guide for the AWS KMS plugin, detailing how to secure key access using IAM policies, restrict usage by IP address, and implement Multi-Factor Authentication (MFA).
Overview
MariaDB contains a robust, full instance, at-rest encryption. This feature uses a flexible plugin interface to allow actual encryption to be done using a key management approach that meets the customer's needs. MariaDB Server includes a plugin that uses the Amazon Web Services (AWS) Key Management Service (KMS) to facilitate separation of responsibilities and remote logging & auditing of key access requests.
Rather than storing the encryption key in a local file, this plugin keeps the master key in AWS KMS. When you first start MariaDB, the AWS KMS plugin will connect to the AWS Key Management Service and ask it to generate a new key. MariaDB will store that key on-disk in an encrypted form. The key stored on-disk cannot be used to decrypt the data; rather, on each startup, MariaDB connects to AWS KMS and has the service decrypt the locally-stored key(s). The decrypted key is stored in-memory as long as the MariaDB server process is running, and that in-memory decrypted key is used to encrypt the local data.
InnoDB temporary files – encrypt_temporary_tables
MariaDB creates temporary files on disk, for instance, files used for file sorts. It is recommended to encrypt those, too.
To configure global encryption for all of those objects, add this to your configuration file (for instance, my.cnf), then restart the server:
Alternatively, set it by running the following statements. Remember, though, that the settings do not persist across server restarts. Also note that some of the variables cannot be set at runtime – they have to be set in a configuration file:
mkdir -p /etc/mysql/encryption
echo $(echo -n "1;" ; openssl rand -hex 32) | sudo tee -a /etc/mysql/encryption/keyfile.txt
[mariadb]
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/encryption/keyfile.txt
# The following line is optional but highly recommended.
# Uncomment it to enable usage of an encrypted key file.
# file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
file_key_management_encryption_algorithm = AES_CTR
SELECT
'InnoDB Tables' AS Component, @@innodb_encrypt_tables AS Active
UNION SELECT 'InnoDB Logs', @@innodb_encrypt_log
UNION SELECT 'InnoDB Temp', @@innodb_encrypt_temporary_tables
UNION SELECT 'Aria System', @@aria_encrypt_tables
UNION SELECT 'Aria Temp', @@encrypt_tmp_disk_tables;
[mariadb]
innodb_encrypt_tables = OFF
innodb_encrypt_log = OFF
innodb_encrypt_temporary_tables = OFF
aria_encrypt_tables = OFF
encrypt_tmp_disk_tables = OFF
SELECT NAME, ENCRYPTION_SCHEME, ROTATING_OR_FLUSHING
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION
WHERE ENCRYPTION_SCHEME != 0;
UNINSTALL SONAME 'file_key_management';
[mariadb]
# Remove or comment out the following line
plugin_load_add = file_key_management
[mariadb]
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
encrypt_temporary_tables = ON
aria_encrypt_tables = ON
encrypt_tmp_disk_tables = ON
SET GLOBAL innodb_encrypt_tables = ON; -- for InnoDB tables
SET GLOBAL aria_encrypt_tables = ON; -- for Aria tables
SET GLOBAL encrypt_tmp_disk_tables = ON; -- for Aria temporary tables
Particularly InnoDB has more encryption options. You can fine-tune the encryption, for instance, configure encryption threads. Those details are covered .
Performance degradation during initial encryption: The initial stage of encrypting large tablespaces is the most resource-intensive phase. Once completed, ongoing CPU usage stabilizes, but during the initial encryption, performance can degrade significantly.
Phased rollout of encryption, enabling it for a subset of tables at a time rather than all tables at once. See the Operational Best Practices section.
Use an odd number of nodes (3, 5, 7) to maintain quorum and prevent split-brain scenarios. See for details.
SET GLOBAL innodb_encrypt_tables=OFF;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTION_KEY_ID=100;
SET GLOBAL innodb_encrypt_tables=ON;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 1 |
+----------+-------------------+----------------+
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=NO ENCRYPTION_KEY_ID=100;
Query OK, 0 rows affected, 1 warning (0.01 sec)
SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------+
| Warning | 140 | InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)
SET GLOBAL innodb_encrypt_tables=ON;
ALTER TABLE tab1 ENCRYPTED=DEFAULT;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME = 'db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
This guide is based on CentOS 7, using systemd with SELinux enabled. Some steps will differ if you use other operating systems or configurations.
Installing the Plugin's Package
The AWS Key Management plugin depends on the AWS SDK for C++, which uses the Apache License, Version 2.0. This license is not compatible with MariaDB Server's GPL 2.0 license, so we are not able to distribute packages that contain the AWS Key Management plugin. Therefore, the only way to currently obtain the plugin is to install it from source.
Compilation is controlled by the -DPLUGIN_AWS_KEY_MANAGEMENT=DYNAMIC -DAWS_SDK_EXTERNAL_PROJECT=1 arguments.
The plugin uses AWS C++ SDK, which introduces the following restrictions:
The plugin can only be built on Windows, Linux, and macOS.
The plugin requires that one of the following compilers is used: gcc 4.8 or later, clang 3.3 or later, or Visual Studio 2013 or later.
On Unix, the libcurl development package (e.g., libcurl3-dev on Debian Jessie), the uuid development package, and openssl need to be installed.
You may need to use a newer version of cmake than is provided by default in your OS.
Installing the Plugin
Even after the package that contains the plugin's shared library is installed on the operating system, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.
The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing INSTALL SONAME or INSTALL PLUGIN. For example:
The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the --plugin-load or the --plugin-load-add options. This can be specified as a command-line argument to mysqld, or it can be specified in a relevant server option group in an option file. For example:
Sign up for Amazon Web Services
If you already have an AWS account, you can skip this section.
Load.
Click "Create a Free Account" and complete the steps.
You'll need to enter credit card information. Charges related only to your use of the AWS KMS service should be limited to about $1/month for the single master key we will create. If you use other services, additional charges may apply. Consult AWS Cloud Pricing Principles for more information about the pricing of AWS services.
You'll need to complete the AWS identity verification process.
Create an IAM User and/or Role
After creating an account or logging in to an existing account, follow these steps to create an IAM User or Role with restricted privileges that will use (but not administer) your master encryption key.
If you intend to run MariaDB Server on an EC2 instance, you should create a Role (or modify an existing Role already attached to your instance). If you intend to run MariaDB Server outside of AWS, you may want to create a User.
Creating an IAM Role
Load the Identity and Access Management Console at.
Click "Roles" in the left-hand sidebar
Click "Create new role"
Select "AWS Service Role"
Click the "Select" button next to "Amazon EC2 / Allows EC2 instances to call AWS services on your behalf."
Do not select any policies on the "Attach Policy" screen. Click "Next Step"
Click "Next Step"
Give your Role a "Role name"
Click "Create role"
Creating an IAM User
Load the Identity and Access Management Console at.
Click "Users" in the left-hand sidebar.
Click the "Create New Users" button
Enter a single User Name of your choosing. We'll use "MDBEnc" for this demonstration. Keep the "Generate an access key for each user" box checked.
Click "Create".
Click "Show User Security Credentials".
Copy the Access Key ID and Secret Access Key. Optionally, you can click "Download Credentials". We will need these in order for local programs to interact with AWS using its API.
Create a file on your computer to hold the credentials for this user. We'll use this file later. It should have this structure:
Click "Close". If prompted because you did not Download Credentials, ensure that you've saved them somewhere, and click "Close".
Create a Master Encryption Key
Now, we'll create a master encryption key. This key can never be retrieved by any application or user. This key is used remotely to encrypt (and decrypt) the actual encryption keys that will be used by MariaDB. If this key is deleted or you lose access to it, you will be unable to use the contents of your MariaDB data directory.
Click "Encryption Keys" in the left-hand sidebar.
Click the "Get Started Now" button.
Use the "Filter" dropdown to choose the region where you'd like to create your master key.
Click the "Create Key" button.
Enter an Alias and Description of your choosing.
Click "Next Step".
Do not check the box to make your IAM Role or IAM User a Key Administrator.
Click "Next Step" again.
Check the boxes to give your IAM Role and/or IAM User permissions to use this key.
Click "Next Step".
Click "Finish".
You should now see your key listed in the console:
You'll use the "Alias" you provided when you configure MariaDB later.
We now have a Customer Master Key and an IAM user that has privileges to access it using access credentials. This is enough to begin using the AWS KMS plugin.
Configure AWS Credentials
There are a number of ways to give the IAM credentials to the AWS KMS plugin. The plugin supports reading credentials from all standard locations used across the various AWS API clients.
The easiest approach is to run MariaDB Server in an EC2 instance that has an IAM Role with User access to the CMK you wish to use. You can give key access privileges to a Role already attached to your EC2 instance, or you can create a new IAM Role and attach it to an already-running EC2 instance. If you've done that, no further credentials management is required, and you do not need to create a credentials file.
If you're not running MariaDB Server on an EC2 instance, you can also place the credentials in the MariaDB data directory. The AWS API client looks for a credentials file in the .aws subdirectory of the home directory of the user running the client process. In the case of MariaDB, its home directory is its datadir.
Create a credentials file that MariaDB can read. Use the region you selected when creating the key. Master keys cannot be used across regions. For example:
Change the permissions of the file so that it is owned by, and can only be read by, the mysql user:
Configure MariaDB
Create a new option file to tell MariaDB to enable encryption functionality and to use the AWS KMS plugin. Create a new file under /etc/my.cnf.d/ (or wherever your OS may have you create such files) with contents like this:
Append the "Alias" value you copied above to alias/ to use as the value for the aws-key-management-master-key-id option.
Note that you must include aws-key-management-region in your .cnf file if you are not using the us-east-1 region.
Now, you have told MariaDB to use the AWS KMS plugin, and you've put credentials for the plugin in a location where the plugin will find them. The /etc/my.cnf.d/enable_encryption.preset file contains a set of options that enable all available encryption functionality.
When you start MariaDB, the AWS KMS plugin will connect to the AWS Key Management Service and ask it to generate a new key. MariaDB will store that key on-disk in an encrypted form. The key stored on-disk cannot be used to decrypt the data; rather, on each startup, MariaDB must connect to AWS KMS and have the service decrypt the locally-stored key. The decrypted version is stored in-memory as long as the MariaDB server process is running, and that in-memory decrypted key is used to encrypt the local data.
SELinux and Outbound Connections from MariaDB
Because MariaDB needs to connect to the AWS KMS service, you must ensure that the host has outbound network connectivity over port 443 to AWS, and you must ensure that local policies allow the MariaDB server process to make those outbound connections. By default, SELinux restricts MariaDB from making such connections.
The most simple way to cause SELinux to allow outbound HTTPS connections from MariaDB is to enable to mysql_connect_any boolean, like this:
There are more complex alternatives that have a more granular effect, but those are beyond the scope of this document.
Start MariaDB
Start MariaDB using the systemctl tool:
If you do not use systemd, you may have to start MariaDB using some other mechanism.
You should see journal output similar to this:
Note the several lines of output that refer explicitly to the "AWS KMS plugin". You can see that the plugin generates a "datakey", loads that data key, and then later generates and loads a second data key. The 2nd data key is used to encrypt temporary files and temporary tables.
You can see the encrypted keys stored on-disk in the datadir:
Note that those keys are not useful alone. They are encrypted. When MariaDB starts up, the AWS KMS plugin decrypts those keys by interacting with AWS KMS.
For maximum security, you should start from an empty datadir and run mariadb-install-db after configuring encryption. Then you should re-import your data so that it is fully encrypted. Use sudo to run mariadb-install-db so that it finds your credentials file:
Create Encrypted Tables
With innodb-encrypt-tables=ON, new InnoDB tables will be encrypted by default, using the key ID set in innodb_default_encryption_key_id (default 1). With innodb-encrypt-tables=FORCE enabled, it is not possible to manually bypass encryption when creating a table.
You can cause the AWS KMS plugin to create new encryption keys at-will by specifying a new ENCRYPTION_KEY_ID when creating a table:
Read more about encrypting data in the section of the MariaDB Documentation.
AWS KMS Plugin Option Reference
aws_key_management_master_key_id: AWS KMS Customer Master Key ID (ARN or alias prefixed by alias/) for master encryption key. Used to create new data keys. If not set, no new data keys will be created.
aws_key_management_rotate_key: Set this variable to a data key ID to perform rotation of the key to the master key given in aws_key_management_master_key_id. Specify -1 to rotate all keys.
aws_key_management_key_spec: Encryption algorithm used to create new keys. Allowed values are AES_128 (default) or AES_256.
aws_key_management_log_level: Logging for AWS API. Allowed values, in increasing verbosity, are "Off" (default), "Fatal", "Error", "Warn", "Info", "Debug", and "Trace".
# journalctl --no-pager -o cat -u mariadb.service
[Note] /usr/sbin/mysqld (mysqld 10.1.9-MariaDB-enterprise-log) starting as process 19831 ...
[Note] AWS KMS plugin: generated encrypted datakey for key id=1, version=1
[Note] AWS KMS plugin: loaded key 1, version 1, key length 128 bit
[Note] InnoDB: Using mutexes to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: Memory barrier is not used
[Note] InnoDB: Compressed tables use zlib 1.2.7
[Note] InnoDB: Using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 2.0G
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: Highest supported file format is Barracuda.
[Note] InnoDB: 128 rollback segment(s) are active.
[Note] InnoDB: Waiting for purge to start
[Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.26-74.0 started; log sequence number 1616819
[Note] InnoDB: Dumping buffer pool(s) not yet started
[Note] Plugin 'FEEDBACK' is disabled.
[Note] AWS KMS plugin: generated encrypted datakey for key id=2, version=1
[Note] AWS KMS plugin: loaded key 2, version 1, key length 128 bit
[Note] Using encryption key id 2 for temporary files
[Note] Server socket created on IP: '::'.
[Note] Reading of all Master_info entries succeded
[Note] Added new Master_info '' to hash table
[Note] /usr/sbin/mysqld: ready for connections.
# ls -l /var/lib/mysql/aws*
-rw-rw----. 1 mysql mysql 188 Feb 25 18:55 /var/lib/mysql/aws-kms-key.1.1
-rw-rw----. 1 mysql mysql 188 Feb 25 18:55 /var/lib/mysql/aws-kms-key.2.1
# sudo -u mysql mariadb-install-db
Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
2016-02-25 23:16:06 139731553998976 [Note] /usr/sbin/mysqld (mysqld 10.1.11-MariaDB-enterprise-log) starting as process 39551 ...
2016-02-25 23:16:07 139731553998976 [Note] AWS KMS plugin: generated encrypted datakey for key id=1, version=1
2016-02-25 23:16:07 139731553998976 [Note] AWS KMS plugin: loaded key 1, version 1, key length 128 bit
...
MariaDB [test]> CREATE TABLE t1 (id serial, v VARCHAR(32)) ENCRYPTION_KEY_ID=3;
Query OK, 0 rows affected (0.91 sec)
[Note] AWS KMS plugin: generated encrypted datakey for key id=3, version=1
[Note] AWS KMS plugin: loaded key 3, version 1, key length 128 bit
# ls -l /var/lib/mysql/aws*
-rw-rw----. 1 mysql mysql 188 Feb 25 18:55 /var/lib/mysql/aws-kms-key.1.1
-rw-rw----. 1 mysql mysql 188 Feb 25 18:55 /var/lib/mysql/aws-kms-key.2.1
-rw-rw----. 1 mysql mysql 188 Feb 25 19:10 /var/lib/mysql/aws-kms-key.3.1