File-per-Table Tablespace Encryption

InnoDB data-at-rest encryption allows file-per-table tablespace encryption by providing the innodb_file_per_table system variable in the mysql.cnf file. Once enabled, you can provide encryption at rest for tables created in file-per-table tablespaces,

[mysqld]
innodb_file_per_table=ON

and restart the mysql service:

$ sudo service mysql restart

You can enable encryption for a new file-per-table tablespace by specifying an encryption='y' clause along with the create table statement:

mysql> create table testTB (c1 INT) encryption='y';
mysql> alter table testTB encryption='y';
Query OK, 1 row affected (0.33 sec)
Records: 1 Duplicates: 0 Warnings: 0

The alter table statement shows how the system variable also enables encryption for existing InnoDB file-per-table tablespaces.

Encryption-at-Rest Verification

Next, you should query the information_schema.tables column to identify newly created or altered encrypted file_per_table tablespaces; similarly, verify that the plugin status is set to ACTIVE to determine successful plugin configuration (Listing 1).

mysql> select table_schema, table_name, create_options from information_schema.tables where create_options like '%encryption%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| test         | testTB     | ENCRYPTION="Y" |
+--------------+------------+----------------+
 **
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'keyring%';
+--------------+---------------+
| plugin_name  | plugin_status |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

Finally, confirm data encryption at rest with the strings command to view the encrypted output:

$ strings /var/lib/mysql/testDB/testTB.ibd | head -n 20

In this section, you learned how to configure MySQL to provide encryption at rest to ensure physical data protection. Next up, I show you how to set up the encryption-in-transit configuration for a MySQL client and server to secure data on the network.

Encryption in Transit

Data in transit or in motion is exposed to potential risk if sniffed or intercepted by a man in the middle (MITM) attack. Encryption provides effective measures against MITM to secure unprotected, in-transit data. MySQL and MariaDB databases provide encrypted communication between client and server with the SSL/TLS protocol. In this section, I walk you through the MySQL configuration of SSL to ensure secure communication between the client and server.

MySQL version 5.7.28 and above provides the handy mysql_ssl_rsa_setup tool that automatically creates required files to set up the default encrypted communication. To begin, you should check either the default SSL status connection on the MySQL server instance or the value of the Ssl_cipher variable for the current session (Listing 2). The output indicates an unencrypted connection.

mysql> show global variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | DISABLED        |
| have_ssl      | DISABLED        |
| ssl_ca        |                 |
| ssl_capath    |                 |
| ssl_cert      |                 |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       |                 |
+---------------+-----------------+
9 rows in set (0.53 sec)
 **
mysql> show session status like 'Ssl_cipher';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| Ssl_cipher    |                 |
+---------------+-----------------+
1 row in set (0.50 sec)