Encrypt Data At Rest — MySQL 5.7

We can enable mysql encryption per table basis. Steps to follow are described as below

Security

STEP 1: Enable Plugin for Encryption

Open my.cnf configuration file & add below plugin details

vi /etc/my.cnf 
# Added for Encryption 
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring

Restart Database to enable plugin

Service mysqld restart

STEP 2: Verify parameters

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) 
mysql> show global variables like '%keyring%'; 
+--------------------+------------------------+ 
| Variable_name      | Value                  | 
+--------------------+------------------------+ 
| keyring_file_data  | /var/lib/mysql/keyring | 
| keyring_operations | ON                     | 
+--------------------+------------------------+ 
2 rows in set (0.00 sec) 
mysql> show global variables like 'innodb_file_per_table'; 
+-----------------------+-------+ 
| Variable_name         | Value | 
+-----------------------+-------+ 
| innodb_file_per_table | ON    | 
+-----------------------+-------+
1 row in set (0.00 sec)

STEP 3: Setup sample tables — only for demo

mysql> create database encryption_test; 
Query OK, 1 row affected (0.01 sec) 
mysql> use encryption_test; 
Database changed 
mysql> create table unencrypted_data(id int auto_increment primary key,creditcard varchar(255)); 
Query OK, 0 rows affected (0.06 sec) 
mysql> create table encrypted_data(id int auto_increment primary key,creditcard varchar(255)); 
Query OK, 0 rows affected (0.09 sec) 
mysql> insert into unencrypted_data(creditcard) values('This is credit card 1'); 
Query OK, 1 row affected (0.02 sec) 
mysql> insert into unencrypted_data(creditcard) values('This is credit card 2'); 
Query OK, 1 row affected (0.01 sec) 
mysql> insert into unencrypted_data(creditcard) values('This is credit card 3'); 
Query OK, 1 row affected (0.01 sec) 
mysql> insert into encrypted_data(creditcard) values('This is credit card 1'); 
Query OK, 1 row affected (0.00 sec) 
mysql> insert into encrypted_data(creditcard) values('This is credit card 2'); 
Query OK, 1 row affected (0.00 sec) 
mysql> insert into encrypted_data(creditcard) values('This is credit card 3'); 
Query OK, 1 row affected (0.01 sec) 
mysql> select * from unencrypted_data; 
+----+-----------------------+ 
| id | creditcard            | 
+----+-----------------------+ 
|  1 | This is credit card 1 | 
|  2 | This is credit card 2 | 
|  3 | This is credit card 3 | 
+----+-----------------------+ 
3 rows in set (0.00 sec) 
mysql> select * from encrypted_data; 
+----+-----------------------+ 
| id | creditcard            | 
+----+-----------------------+ 
|  1 | This is credit card 1 | 
|  2 | This is credit card 2 | 
|  3 | This is credit card 3 | 
+----+-----------------------+
3 rows in set (0.00 sec)

STEP 4 — Enable Encryption

mysql> alter table encrypted_data encryption='Y'; 
Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0

STEP 5 — Verify data with query

mysql> select * from unencrypted_data; 
+----+-----------------------+ 
| id | creditcard            | 
+----+-----------------------+ 
|  1 | This is credit card 1 | 
|  2 | This is credit card 2 | 
|  3 | This is credit card 3 | 
+----+-----------------------+ 
3 rows in set (0.00 sec) 
mysql> select * from encrypted_data; 
+----+-----------------------+ 
| id | creditcard            | 
+----+-----------------------+ 
|  1 | This is credit card 1 | 
|  2 | This is credit card 2 | 
|  3 | This is credit card 3 | 
+----+-----------------------+
3 rows in set (0.00 sec)

STEP 6 — Verify encryption benefit for “data at rest”

cd /var/lib/mysql/encryption_test/ 
# strings unencrypted_data.ibd 
infimum 
supremum 
This is credit card 1 
This is credit card 2 
This is credit card 3 
# strings encrypted_data.ibd
<Some un readable data>