MySQL whole table encryption solution keyring_file detailed explanation

illustrate

MySql Community Edition supports table-based data encryption solutions starting from 5.7.11. The module is called keyring_file and supports encryption of the entire table. This encryption method is actually based on file encryption. Once mysqld reads the key and starts, it will decrypt the data of the entire table. In the mysql service, the data read is decrypted, which means that the client is not aware of it. This key is stored locally, and the MySQL service has the permission to read and write this key.

In general, this solution is not very safe because the database files are encrypted, but as long as there is an account for the MySQL service, the access data will be decrypted and the encryption will be automatically broken. Moreover, the decryption key is also stored locally, so the intruder can take it away with him. This solution can only ensure that the intruder cannot read the content after dragging away the database file.

Three additional modules for MySQL Enterprise Edition

If it is the enterprise version of MySQL, there are three other encryption schemes.

1.keyring_encrypted_file

It is similar to the community version I mentioned before, except that it has an extra key. This key is used to encrypt and decrypt the database key. Security wise they are pretty much the same.

2.keyring_okv

Compared with local storage of keys, this module uses KMIP to access keys, which is relatively safer.

3.keyring_aws

Integrate AWS key management service to manage encryption and decryption keys. Further improve the security of key management.

Encryption types supported by the four encryption modules

To sum up, all four solutions are file encryption and memory decryption solutions, the difference lies in the encryption and decryption key storage scheme. It is recommended to use keyring_okv and keyring_aws, and ensure the security of the MySQL account and strictly distinguish account permissions.

The other two are less safe.

Implementation steps

OK, now let me briefly talk about the simplest keyring_file deployment solution. I would like to point out in advance that Windows does not seem to be able to use this solution because for some reason the encryption key cannot always be generated.

1. Use the latest version of MySQL 5.7.21

Use tools such as yum apt to install the latest version of mysql or download the source code and compile and install it yourself

sudo apt install mysql-5.7

2. Enable the encryption module

INSTALL PLUGIN keyring_file soname 'keyring_file.so';

mysql> INSTALL PLUGIN keyring_file soname 'keyring_file.so';
Query OK, 0 rows affected (0.10 sec)

3. Set the encryption key storage path

set global keyring_file_data='/root/mysql-keyring/keyring';

mysql> set global keyring_file_data='/var/lib/mysql-keyring/keyring';
Query OK, 0 rows affected (0.00 sec)

4. Enable the setting permanently

The above two steps are temporary and will fail if the service is restarted. We write the configuration into the configuration file to ensure that it will take effect after restarting the service.

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/root/mysql-keyring/keyring

5. Check the key storage path

show global variables like '%keyring_file_data%';

mysql> show global variables like '%keyring_file_data%';
+-------------------+--------------------------------+
| Variable_name | Value |
+-------------------+--------------------------------+
| keyring_file_data | /var/lib/mysql-keyring/keyring |
+-------------------+--------------------------------+
1 row in set (0.00 sec)

6. View enabled modules

Check whether the keyring_file module has been loaded.
show plugins;

mysql> show plugins;
+----------------------------+----------+--------------------+-----------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+-----------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

. . . . . . (Omit N items)

| keyring_file | ACTIVE | KEYRING | keyring_file.so | GPL |
+----------------------------+----------+--------------------+-----------------+---------+
45 rows in set (0.00 sec)

7. Encrypt existing tables

alter table table encryption='Y';

mysql> create table cc (id int);
Query OK, 0 rows affected (0.01 sec)

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

8. Cancel encryption

alter table table encryption='N';

mysql> alter table cc encryption='N';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Official documentation:

https://dev.mysql.com/doc/refman/5.7/en/keyring-installation.html

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.