MYSQL 8.0 社区版的透明数据加密

和MySQL 5.0不同，MySQL 8.0 不再使用keyring插件，而是使用keyring component。这包括：

• component_keyring_file
• component_keyring_encrypted_file
• component_keyring_oci

MySQL企业版支持所有3个，MySQL社区版只支持第一个，即component_keyring_file。本文测试的也是这个。

MySQL 5.0只支持表加密，而8.0还支持表空间加密。

keying component的安装参见这里。

首先，component 文件必须位于 plugin_dir系统变量所指定的目录。

查看变量：

mysql> show variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)

可以看到，社区版的component_keyring_file已在其中：

$ ls -1 /usr/lib64/mysql/plugin/*keyring*
/usr/lib64/mysql/plugin/component_keyring_file.so
/usr/lib64/mysql/plugin/keyring_udf.so

接下来，keyring component的安装分为2步：

1. 编写manifest文件，告知服务器要加载哪个密钥环组件。
2. 为该密钥环组件编写一个配置文件。

manifest可以是全局的或本地的，但文件名必须是mysqld.my。

全局manifest 必须位于mysqld所在目录。

sudo -s

cd $(dirname `which mysqld`)

cat <<EOF > mysqld.my
{
  "components": "file://component_keyring_file"
}
EOF

对此文件，权限是有要求的：

Server access to a manifest file should be read only. For example, a mysqld.my server manifest file may be owned by root and be read/write to root, but should be read only to the account used to run the MySQL server (通常是mysql).

检查，发现满足要求：

# ls -l mysqld.my
-rw-r--r--. 1 root root 52 Jul 31 03:34 mysqld.my

然后，进行到下一步，为该密钥环组件编写一个配置文件。操作参考这里。

密钥环组件配置文件可以是全局的或本地的，本例使用前者。配置文件的文件名前缀为组件名称，后缀为cnf，对于本例，其文件名为component_keyring_file.cnf。文件位置为component_keyring_file 库文件所在目录，即plugin_dir系统变量指定的位置。

sudo -s

cd /usr/lib64/mysql/plugin/

cat <<EOF > component_keyring_file.cnf
{
  "path": "/usr/local/mysql/keyring/component_keyring_file",
  "read_only": false
}
EOF

此配置文件的权限如下：

# ls -l component_keyring_file.cnf
-rw-r--r--. 1 root root 86 Jul 31 03:44 component_keyring_file.cnf

/usr/local/mysql/keyring/component_keyring_file是官方示例，但目录/usr/local/mysql目前还不存在。这个实验所耗费的时间大部分也是由此引起的。后面再详述。

先建立目录和文件，并赋权：

sudo -s

mkdir -p /usr/local/mysql/keyring
cd /usr/local/mysql/keyring
touch component_keyring_file
cd /usr/local
chown -R mysql:mysql mysql
chmod -R 751 mysql
chmod 640 mysql/keyring/component_keyring_file

目前keyring文件的权限为：

# ls -l mysql/keyring/component_keyring_file
-rw-r-----. 1 mysql mysql 0 Jul 31 06:38 mysql/keyring/component_keyring_file

如果此时试着加密表，则会失败：

mysql> alter table employees encryption = 'Y';
ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.

这个错误说的是读不到秘钥，但根本原因却是加密时自动生成的秘钥写入文件component_keyring_file失败。根本的原因在于SELinux。那么现在有三种解决方法：

1. 使用setenforce 0将SELinux模式由enforcing改为permissive
2. 将秘钥文件换到SELinux已经有权限的目录下，例如mysqld的数据目录，使用cat /etc/my.cnf|grep datadir查看
3. 为当前秘钥所在的目录赋权

法1做演示可以，生产环境不建议；法2好些，但也不建议，因为数据和秘钥应分开放，如图锁和钥匙一样。
本例使用的是法3，具体参考MySQL数据库与SELinux文件权限设置
重启mysql：

$ sudo systemctl restart mysqld

登入数据库查看，完美，和文档描述一致：

mysql> SELECT * FROM performance_schema.keyring_component_status;
+---------------------+-------------------------------------------------+
| STATUS_KEY          | STATUS_VALUE                                    |
+---------------------+-------------------------------------------------+
| Component_name      | component_keyring_file                          |
| Author              | Oracle Corporation                              |
| License             | GPL                                             |
| Implementation_name | component_keyring_file                          |
| Version             | 1.0                                             |
| Component_status    | Active                                          |
| Data_file           | /usr/local/mysql/keyring/component_keyring_file |
| Read_only           | No                                              |
+---------------------+-------------------------------------------------+
8 rows in set (0.01 sec)

mysql> SELECT * FROM performance_schema.keyring_keys;
Empty set (0.00 sec)

-- 无需keyring plugin, 因为在8.0只需要keyring component
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';
Empty set (0.00 sec)

如果你的权限设置正确，那么此时，就可以加密表空间和表了。并且在首次加密时顺带生成了秘钥：

mysql> CREATE TABLESPACE `ts_emps` ADD DATAFILE 'ts_emps.ibd' ENCRYPTION = 'Y' Engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

或者，如果已有表空间，可以直接改为加密状态：

在这里插入代码片

然后，可以看到秘钥，然后再试一下单表加密：

mysql> SELECT * FROM performance_schema.keyring_keys;
+--------------------------------------------------+-----------+----------------+
| KEY_ID                                           | KEY_OWNER | BACKEND_KEY_ID |
+--------------------------------------------------+-----------+----------------+
| INNODBKey-9f93195b-4f06-11ef-918b-02001714986a-1 |           |                |
+--------------------------------------------------+-----------+----------------+
1 row in set (0.00 sec)

mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

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

mysql> SELECT * FROM performance_schema.keyring_keys;
+--------------------------------------------------+-----------+----------------+
| KEY_ID                                           | KEY_OWNER | BACKEND_KEY_ID |
+--------------------------------------------------+-----------+----------------+
| INNODBKey-9f93195b-4f06-11ef-918b-02001714986a-1 |           |                |
+--------------------------------------------------+-----------+----------------+
1 row in set (0.00 sec)

参考这里，安装了keyring通用函数，但这不是必需的：

INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_generate RETURNS INTEGER
  SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_fetch RETURNS STRING
  SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER
  SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_type_fetch RETURNS STRING
  SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_store RETURNS INTEGER
  SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_remove RETURNS INTEGER
  SONAME 'keyring_udf.so';

数据库中可以看到这个插件：

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| keyring_udf | ACTIVE        |
+-------------+---------------+
1 row in set (0.01 sec)

然后可以用keyring自定义函数生成秘钥，之前的加密并未使用此处生成的秘钥：

mysql> SELECT keyring_key_generate('MyKey', 'AES', 128);
+-------------------------------------------+
| keyring_key_generate('MyKey', 'AES', 128) |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM performance_schema.keyring_keys;
+--------------------------------------------------+----------------+----------------+
| KEY_ID                                           | KEY_OWNER      | BACKEND_KEY_ID |
+--------------------------------------------------+----------------+----------------+
| MyKey                                            | root@localhost |                |
| INNODBKey-9f93195b-4f06-11ef-918b-02001714986a-1 |                |                |
+--------------------------------------------------+----------------+----------------+
2 rows in set (0.00 sec)

最后，看一下秘钥文件中的内容：

$ sudo cat /usr/local/mysql/keyring/component_keyring_file
{"version":"1.0","elements":[{"user":"","data_id":"INNODBKey-9f93195b-4f06-11ef-918b-02001714986a-1","data_type":"AES","data":"EECBBFF2369418CCB2F8A5D82AA751DA3069DD58FBA1B233C396562FB4B189CE","extension":[]},{"user":"root@localhost","data_id":"MyKey","data_type":"AES","data":"E768E1CDCDAC14F5692AEF45039A198DA3E190D8FD48BC2AB3832DC1004460ECABE57C9090D536DD24F4C60EE66CBD83772411AA0A1C929B6DFA3F9D04C6BBBC01166188E0C10AB756C2051CEDA157300F2B7953D81BA75700A2167CEA6A18C69782227B9D9572C273B941F2B086BDD8F0139495A30AF69F85F22EEC0FAFDE52","extension":[]}]}

错误

如果没有权限写秘钥文件，则会遇到以下错误：

mysql> SELECT keyring_key_generate('MyKey', 'AES', 128);
ERROR 3188 (HY000): Function 'keyring_key_generate' failed because underlying keyring service returned an error. Please check if a keyring is installed and that provided arguments are valid for the keyring you are using.

2024-07-30T09:11:35.270688Z 8 [Note] [MY-013725] [Server] Component component_keyring_file reported: 'Error generating data for Data ID: 'MyKey', Auth ID: 'root@localhost'. Either data already exists with same identifier or keyring backend encountered an error.'

参考

• https://dev.mysql.com/doc/refman/8.0/en/keyring-component-installation.html
• https://dev.mysql.com/doc/refman/8.0/en/keyring-file-component.html
• https://blogs.oracle.com/mysql/post/keyring-components
• https://blogs.oracle.com/mysql/post/component-keyring-file
• https://dev.mysql.com/doc/refman/8.4/en/keyring.html
• https://stackoverflow.com/questions/37992813/logging-verbosity-mysql-5-7
• https://dev.mysql.com/doc/refman/8.4/en/security.html