和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步:
- 编写manifest文件,告知服务器要加载哪个密钥环组件。
- 为该密钥环组件编写一个配置文件。
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。那么现在有三种解决方法:
- 使用
setenforce 0
将SELinux模式由enforcing改为permissive - 将秘钥文件换到SELinux已经有权限的目录下,例如mysqld的数据目录,使用
cat /etc/my.cnf|grep datadir
查看 - 为当前秘钥所在的目录赋权
法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