MySQL

Transparent Encryption (TDE) for MySQL Tables (Windows)

Page creation date :

Operation confirmation environment

MySQL
  • MySQL 8.0 Community Edition
Windows
  • Windows 11

Required environment

MySQL
  • MySQL 8.0 Community Edition
  • MySQL 8.0 Enterprise Edition
Windows
  • Windows 11
  • Windows 10
Windows Server
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • Windows Server 2012 R2

precondition

  • MySQL database must be installed

About Transparent Encryption

Transparent Data Encryption (Enterprise Transparent Data Encryption) is a technology that encrypts as the name suggests, and it is more secure than not doing this. Normally, when encrypting is performed, it is often a burden on the implementer because it is necessary to perform encryption and decryption processing. The meaning of this "transparent" is that it can be encrypted without the implementer making any changes to the processing. Therefore, it is a very effective security measure because it is encrypted on its own after just a little tweaking of the settings at the beginning.

However, as the phrase "no need to make any changes" suggests, if you actually log in to the database, you can see the data normally, and if you get the database records from the program, you can see the data normally. If the data obtained is encrypted, it will be necessary to make changes to the processing, so it will not match the statement "no changes are required to the processing".

So, what exactly is transparent encryption in MySQL, and "stored database files" or "table files" are encrypted. As I will check later, even if you open the file directly, it is only encrypted so that the contents cannot be seen, so it is necessary to operate it so that it does not log in to the database in actual operation.

Create a table normally and check the contents

First, let's create a table and a record normally. You can create it however you want, but this time I'll run SQL in Workbench to create it. This time, we plan to set up transparent encryption for the table, so we will create a database normally.

CREATE DATABASE `test_database`

I also make a table normally. Whatever the column content is, it can be.

CREATE TABLE `plain_user` (
  `id` int NOT NULL,
  `name` varchar(32) ,
  `age` int ,
  `address` varchar(256) ,
  `phone` varchar(32) ,
  `email` varchar(128) ,
  `remarks` varchar(1024) ,
  `height` decimal(5, 2) ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Keep a record to check the encryption.

INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('1','名前1','11','宮城県仙台市1','000-0000-0001','example1@example.com','備考1','170.00');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('2','名前2','12','宮城県仙台市2','000-0000-0002','example2@example.com','備考2','171.01');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('3','名前3','13','宮城県仙台市3','000-0000-0003','example3@example.com','備考3','172.02');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('4','名前4','14','宮城県仙台市4','000-0000-0004','example4@example.com','備考4','173.03');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('5','名前5','15','宮城県仙台市5','000-0000-0005','example5@example.com','備考5','174.04');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('6','名前6','16','宮城県仙台市6','000-0000-0006','example6@example.com','備考6','175.05');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('7','名前7','17','宮城県仙台市7','000-0000-0007','example7@example.com','備考7','176.06');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('8','名前8','18','宮城県仙台市8','000-0000-0008','example8@example.com','備考8','177.07');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('9','名前9','19','宮城県仙台市9','000-0000-0009','example9@example.com','備考9','178.08');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('10','名前10','20','宮城県仙台市10','000-0000-0010','example10@example.com','備考10','179.09');

When you create a database or table, it is created in the following folders by default:

  • C:\ProgramData\MySQL\MySQL Server 8.0\Data

ProgramData The folder is a hidden folder, so make sure it is visible in the File Explorer settings.

Inside the folder is a folder with the name of the database you created, so open it.

Inside are table-level files.

Open this file in a text editor such as Notepad. Since it is a binary file, I think there is basically text that I don't understand well. If you look at the bottom, you will see the text of the registered record.

If it is not encrypted in this way, you can know the contents of the record just by opening the file directly. You don't have to log in to the database to know what's inside.

Set up transparent encryption

To enable Transparent Encryption, you need to install a plugin. First, check the command to see if the plugin is included.

Select MySQL 8.0 Command Line Client from the Start menu to launch it.

Once you have entered your password and logged in, enter the following command:

SELECT PLUGIN_NAME,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

I haven't installed it yet, so it should be empty.

Now let's configure the plugin. First, create the following folders: In fact, the place or name you create is fine. Specify it as a location to save the key file later.

  • C:\ProgramData\MySQL\mysql-keyring

Then open the following file in a text editor.

  • C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Add the following text to the bottom space and save it. If you can't save with administrator privileges, you can save it to another location and then overwrite the file.

early-plugin-load=keyring_file.dll
keyring_file_data=C:\ProgramData\MySQL\mysql-keyring\keyring

Restart the MySQL service. You can restart it with a command.

After restarting, the file will be automatically created in the specified folder.

You can run the Check Plugin command again to verify that the plugin is installed.

SELECT PLUGIN_NAME,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

Creating an encryption table

Now let's create an encrypted table. Create a table similar to the one we had before, but now add to the options ENCRYPTION='Y' . This encryption setting does not seem to be configurable in the GUI at this time, so it must be set by command.

CREATE TABLE `encrypt_user` (
  `id` int NOT NULL,
  `name` varchar(32) ,
  `age` int ,
  `address` varchar(256) ,
  `phone` varchar(32) ,
  `email` varchar(128) ,
  `remarks` varchar(1024) ,
  `height` decimal(5, 2) ,
PRIMARY KEY (`id`)
) ENCRYPTION='Y' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

By the way, before setting up the plugin, even if you try to create an encryption table, you will receive the following error.

Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
キーリングからマスターキーが見つかりません。キーリングが正常にロードされ、初期化されているかどうかをサーバーログで確認してください。

If you don't have data, you don't know if it's encrypted, so I'll try to put similar data.

INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('1','名前1','11','宮城県仙台市1','000-0000-0001','example1@example.com','備考1','170.00');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('2','名前2','12','宮城県仙台市2','000-0000-0002','example2@example.com','備考2','171.01');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('3','名前3','13','宮城県仙台市3','000-0000-0003','example3@example.com','備考3','172.02');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('4','名前4','14','宮城県仙台市4','000-0000-0004','example4@example.com','備考4','173.03');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('5','名前5','15','宮城県仙台市5','000-0000-0005','example5@example.com','備考5','174.04');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('6','名前6','16','宮城県仙台市6','000-0000-0006','example6@example.com','備考6','175.05');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('7','名前7','17','宮城県仙台市7','000-0000-0007','example7@example.com','備考7','176.06');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('8','名前8','18','宮城県仙台市8','000-0000-0008','example8@example.com','備考8','177.07');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('9','名前9','19','宮城県仙台市9','000-0000-0009','example9@example.com','備考9','178.08');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('10','名前10','20','宮城県仙台市10','000-0000-0010','example10@example.com','備考10','179.09');

encrypt_user A table file has been created, so let's check the contents.

The file has been encrypted and the contents of the record cannot be verified.

For the time being, I no longer have to worry about being able to read the records inside just by logging in to the server with a third party and stealing the table file directly. I think it's an easy security setting because there is no need to change the implementation at all.

However, if you log in to the database, you can see the data normally. If you want to increase the level of security, you need to manage database login accounts and introduce a separate encryption mechanism.

Convert to encrypted tables along the way

If you create an unencrypted table in the first place, you can convert it to an encrypted table later. In that case, you can convert it with the following SQL:

use <データベース名>;
ALTER TABLE <テーブル名> ENCRYPTION='Y';

example

use test_database;
ALTER TABLE plain_user ENCRYPTION='Y';

However, be careful not to rewrite all the table files, as it will take a long time if there are too many records.

Backup and restore MySQL databases for Windows