MySQL InnoDB tablespace encryption example detailed explanation

MySQL InnoDB tablespace encryption example detailed explanation

Preface

Starting from MySQL 5.7.11, MySQL supports data encryption for InnoDB tables stored in separate tablespaces. This feature provides encryption at rest for physical tablespace data files. This encryption is performed at the data page level within the engine. The data page is encrypted when it is written to the file system. The encryption uses the AES algorithm, and the decryption is performed when it is read from the file into the memory.

1 Configure the encryption plugin

1.1 Modify the configuration file

Add the following content to the mysql configuration file [mysqld] x item

plugin_dir=/usr/local/mysql5.7/lib/mysql/plugin # Plugin path, modify according to actual situation early-plugin-load="keyring_file.so" # Encryption plugin keyring_file_data=/data/mysql3306/keyring/keyring # The path does not exist and needs to be created innodb_file_per_table=1 # Only works on independent tablespaces

1.2 Create the paths required for encryption and configure permissions

Note that the keyring configured in keyring_file_data will be automatically created at startup. In this step, just create it in the corresponding directory.

mkdir -p /data/mysql3306/keyring/
chown -R mysql:mysql /data/mysql3306/keyring/
chmod 750 /data/mysql3306/keyring

1.3 Restart MySQL

Just restart mysql. After restarting, you will find that the keyring file is generated in the /data/mysql3306/keyring directory.

Note that after restarting, you should also check if there is any relevant error information in the MySQL error log. If there is no error, continue.

1.4 Check plugin status

After starting, you can check whether the plug-in is effective

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_Type,PLUGIN_Library FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring_file';
+--------------+---------------+-------------+-----------------+
| PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_Type | PLUGIN_Library |
+--------------+---------------+-------------+-----------------+
| keyring_file | ACTIVE | KEYRING | keyring_file.so |
+--------------+---------------+-------------+-----------------+
1 row in set (0.01 sec)

Or use the show plugins command to view

2 Test the encrypted tablespace

2.1 Create a new encrypted table

Create a new table and add ENCRYPTION='Y' to encrypt the tablespace

mysql> create table test1(
id int primary key auto_increment, 
name varchar(20),
key name(name))
 ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)

At this time, the keyring file will also change

2.2 New data

Add test data to the newly added test table and view

mysql> insert into test1(id,name) values(1,'anm'),(2,'keyring');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test1;
+----+---------+
| id | name |
+----+---------+
| 1 | anm |
| 2 | keyring |
+----+---------+
2 rows in set (0.00 sec)

2.3 Modify whether to encrypt

Test to cancel tablespace encryption

mysql> show create table test1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ENCRYPTION='Y' |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table test1 ENCRYPTION='N';
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> show create table test1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ENCRYPTION='N' |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test1;
+----+---------+
| id | name |
+----+---------+
| 1 | anm |
| 2 | keyring |
+----+---------+
2 rows in set (0.00 sec)

Reconfigure for encryption

mysql> alter table test1 ENCRYPTION='Y';
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test1;
+----+---------+
| id | name |
+----+---------+
| 1 | anm |
| 2 | keyring |
+----+---------+
2 rows in set (0.00 sec)

Therefore, the tablespace encryption method can be adjusted online without affecting data query.

In addition, keyring_file_data can also be adjusted dynamically, which is relatively simple and will not be demonstrated here.

2.4 Statistics tablespace encryption table

If you want to know which tables' tablespaces are encrypted, you can check in the data dictionary table.

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='testdb2' and CREATE_OPTIONS='ENCRYPTION="Y"';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| testdb2 | test1 | ENCRYPTION="Y" |
+--------------+------------+----------------+
1 row in set (0.00 sec)

3. Exception handling

What happens if the keyring file is corrupted or accidentally deleted?

3.1 Back up the keyring file

To be on the safe side, back up the keyring file first

[root@mha1 keyring]# cp -p keyring keyring.bak
[root@mha1 keyring]# ll -h 
total 8.0K
-rw-r----- 1 mysql mysql 155 Aug 16 09:10 keyring
-rw-r----- 1 mysql mysql 155 Aug 16 09:10 keyring.bak

3.2 Deleting the keyring

Delete the keyring file directly

[root@mha1 keyring]# rm -f keyring
[root@mha1 keyring]# ll -h 
total 4.0K
-rw-r----- 1 mysql mysql 155 Aug 16 09:10 keyring.bak

3.3 Check whether the data is normal

Check the data and whether the new encrypted table is successful

mysql> select * from test1;
+----+---------+
| id | name |
+----+---------+
| 1 | anm |
| 2 | keyring |
+----+---------+
2 rows in set (0.00 sec)


mysql> create table test2(id int primary key auto_increment, name varchar(20),key name(name)) ENCRYPTION='Y';
Query OK, 0 rows affected (0.01 sec)

That is to say, at this time, even if the keyrig file is lost, it can still operate normally.

3.4 Restart the database

After restarting the database, you will find that the keyring file is automatically generated again

Now check the encryption table again

mysql> select * from test1;
ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.

Creating an encrypted table

mysql> create table test3(id int primary key auto_increment, name varchar(20),key name(name)) ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)

It is possible to create a new table because this is equivalent to initialization.

Then restore the original keyring and restart the database, and you will find that it succeeds again.

mysql> select * from test1;
+----+---------+
| id | name |
+----+---------+
| 1 | anm |
| 2 | keyring |
+----+---------+
2 rows in set (0.00 sec)

4 Keyring Management

4.1 Regular backup

You can perform backups daily, but the backup path is separate from the daily backup, and you can copy it to the target file when you need to restore it.

4.2 Regular Updates

For security reasons, when a key leak is suspected, it needs to be updated. After the update, the original table can still be used normally because the update option changes the master encryption key and re-encrypts the tablespace keys, but does not re-encrypt or decrypt the tablespace. Updated method:

-- Update the master key
mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;
Query OK, 0 rows affected (0.00 sec)

-- After the update, mysql can still be accessed normally> select * from test1;
+----+---------+
| id | name |
+----+---------+
| 1 | anm |
| 2 | keyring |
+----+---------+
2 rows in set (0.00 sec)

At this point, the simple use of the InnoDB tablespace has been demonstrated. There are many more details, you can check the official documentation for exploration, https://dev.mysql.com/doc/refman/5.7/en/innodb-data-encryption.html.

Summarize

This is the end of this article about MySQL InnoDB tablespace encryption. For more information about MySQL InnoDB tablespace encryption, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Summary of MySQL InnoDB architecture
  • A brief introduction to MySQL InnoDB ReplicaSet
  • Detailed explanation of memory management of MySQL InnoDB storage engine
  • MySQL Innodb key features insert buffer
  • Summary of MySQL InnoDB locks
  • How to distinguish MySQL's innodb_flush_log_at_trx_commit and sync_binlog
  • Detailed Example of MySQL InnoDB Locking Mechanism
  • In-depth explanation of InnoDB locks in MySQL technology
  • Change the MySQL database engine to InnoDB
  • Briefly describe the MySQL InnoDB storage engine
  • MySQL InnoDB transaction lock source code analysis

<<:  Do you know all 24 methods of JavaScript loop traversal?

>>:  Use of Linux read command

Recommend

Vue.js implements timeline function

This article shares the specific code of Vue.js t...

MySQL database table and database partitioning strategy

First, let's talk about why we need to divide...

JavaScript implementation of a simple addition calculator

This article example shares the specific code of ...

CentOS7 uses rpm package to install mysql 5.7.18

illustrate This article was written on 2017-05-20...

Solution to the problem of not finding Tomcat configuration in Intelli Idea

I joined a new company these two days. The compan...

Solution to multiple 302 responses in nginx proxy (nginx Follow 302)

Proxying multiple 302s with proxy_intercept_error...

Detailed explanation of the practical use of HTML table layout

When is the table used? Nowadays, tables are gene...

VMwarea virtual machine installation win7 operating system tutorial diagram

The installation process of VMwarea will not be d...

mysql create database, add users, user authorization practical method

1. Create a MySQL database 1. Create database syn...

jQuery+h5 realizes the nine-square lottery special effect (front-end and back-end code)

Preface: Front-end: jq+h5 to achieve the nine-gri...

html+css+js to realize the function of photo preview and upload picture

Preface: When we are making web pages, we often n...

Understanding and example code of Vue default slot

Table of contents What is a slot Understanding of...

A practical record of restoring a MySQL Slave library

Description of the situation: Today, I logged int...

Deep understanding of JavaScript syntax and code structure

Table of contents Overview Functionality and read...

Future-oriented all-round web design: progressive enhancement

<br />Original: Understanding Progressive En...