Skip to main content

MySQL Encryption

Encryption Types in MySQL

  1. Data-at-Rest Encryption:

    • Tablespace Encryption: Encrypts the entire tablespace, including the InnoDB tables.
    • Column-Level Encryption: Encrypts specific columns in a table.

  2. Data-in-Transit Encryption:

    • SSL/TLS Encryption: Encrypts data transmitted between MySQL clients and servers using SSL/TLS.

====================================================================================

Data-at-Rest Encryption

MyISAM does not support encryption natively.

1. Enable the Keyring Plugin

The keyring plugin is used to store and manage encryption keys securely within MySQL.

Install the Keyring Plugin

  • If you are using MySQL 5.7 or later, the keyring_file plugin is included by default.
  • Add the following lines to the MySQL configuration file
[mysqld]
early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql-keyring/keyring

Create the directory for the keyring file if it doesn't exist

sudo mkdir /var/lib/mysql-keyring
sudo chown mysql:mysql /var/lib/mysql-keyring

Restart the MySQL server to load the plugin

Verify the Keyring Plugin is Enabled:

SHOW PLUGINS;

2. Enable InnoDB Tablespace Encryption

Enable InnoDB File-Per-Table:

Ensure that innodb_file_per_table is enabled, which is the default setting in MySQL 5.6 and later.

[mysqld]
innodb_file_per_table = 1

Enable InnoDB Encryption:

[mysqld]
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4

Restart MySQL.

systemctl restart mysql

3. Encrypt Existing Tables

Encrypt a Specific Table

ALTER TABLE mytable ENCRYPTION='Y';

4. Verify Encryption

Check Encryption Status

You can verify if a table is encrypted by querying the information_schema.tables table:

SELECT table_schema, table_name, create_options
FROM information_schema.tables
WHERE create_options LIKE '%ENCRYPTION="Y"%';

------------------------------------------------------------------------------------------------------------------------------------------------

Binary Log Encryption

You can replay unencrypted binary logs onto encrypted tables.

Enable Binary Log Encryption

Add the following configuration to your my.cnf

[mysqld]
binlog_encryption = ON

Verify Binary Log Encryption

SHOW VARIABLES LIKE 'binlog_encryption';

Replaying encrypted binary logs

Replaying encrypted binary logs involves ensuring that the encrypted logs are decrypted and applied correctly on the MySQL server.

Use mysqlbinlog to Read Encrypted Binary Logs

The mysqlbinlog utility will handle the decryption transparently if the keyring plugin is properly configured.

mysqlbinlog /path/to/binlog.000001 | mysql -u username -p

The process for replaying binary logs whether encrypted or not is largely the same, providing that the keyring plugin is enabled. See here for more info.

 

 

 

 

Back to top