MySQL 表空间加密插件 Keyring

​ MySQL支持对InnoDB单表空间、通用表空间、系统表空间和Redo、Undo文件进行静态加密。从8.0.16开始支持对schema和通用表空间设置加密默认值，这就允许对在这些schema和表空间中的表是否加密进行统一控制；静态加密功能依赖于keyring组件或插件，MySQL社区版提供的keyring file插件会将keyring数据存储在服务器主机的本地文件系统中。

• 安装：

mkdir /usr/local/mysql/keyring
chown -R mysql.mysql keyring

配置文件中增加
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/usr/local/mysql/keyring/keyring
重启数据库

mysql> select * from information_Schema.plugins where plugin_name like '%keyring%'\G
*************************** 1. row ***************************
           PLUGIN_NAME: keyring_file
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: KEYRING
   PLUGIN_TYPE_VERSION: 1.1
        PLUGIN_LIBRARY: keyring_file.so
PLUGIN_LIBRARY_VERSION: 1.10
         PLUGIN_AUTHOR: Oracle Corporation
    PLUGIN_DESCRIPTION: store/fetch authentication data to/from a flat file
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
           
mysql> show global variables like '%keyring%';
+--------------------+----------------------------------+
| Variable_name      | Value                            |
+--------------------+----------------------------------+
| keyring_file_data  | /usr/local/mysql/keyring/keyring |
| keyring_operations | ON                               |
+--------------------+----------------------------------+
2 rows in set (0.02 sec)
复制

• 加密操作

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

mysql> alter table t2 encryption='Y'; 
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> alter  database test DEFAULT ENCRYPTION = 'Y';
Query OK, 1 row affected (0.03 sec)


mysql> ALTER TABLESPACE mysql ENCRYPTION = 'Y';   
Query OK, 0 rows affected (2.80 sec)


复制

• 取消加密

mysql> alter table t2 encryption='N';
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

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


mysql> alter  database test DEFAULT ENCRYPTION = 'N';
Query OK, 1 row affected (0.03 sec)


mysql> ALTER TABLESPACE mysql ENCRYPTION = 'N';
Query OK, 0 rows affected (2.37 sec)

复制

• 查看元数据

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

查看存在的key
mysql> SELECT * FROM performance_schema.keyring_keys;
+--------------------------------------------------+-----------+----------------+
| KEY_ID                                           | KEY_OWNER | BACKEND_KEY_ID |
+--------------------------------------------------+-----------+----------------+
| INNODBKey-8c537ce5-4a53-12eb-907d-000c298c47fa-1 |           |                |
+--------------------------------------------------+-----------+----------------+
1 row in set (0.00 sec)
复制

• 查看加密对象

#加密表空间
mysql> SELECT SPACE, NAME, SPACE_TYPE, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES        WHERE ENCRYPTION='Y';
+------------+---------+------------+------------+
| SPACE      | NAME    | SPACE_TYPE | ENCRYPTION |
+------------+---------+------------+------------+
| 4294967294 | mysql   | General    | Y          |
|        145 | test/t7 | Single     | Y          |
+------------+---------+------------+------------+
2 rows in set (0.00 sec)

#查看加密表
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES  WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';      
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| test         | t7         | ENCRYPTION='Y' |
+--------------+------------+----------------+
1 row in set (0.04 sec)

#查看加密database
mysql> SELECT SCHEMA_NAME, DEFAULT_ENCRYPTION FROM INFORMATION_SCHEMA.SCHEMATA   WHERE DEFAULT_ENCRYPTION='YES';
+-------------+--------------------+
| SCHEMA_NAME | DEFAULT_ENCRYPTION |
+-------------+--------------------+
| test        | YES                |
+-------------+--------------------+
1 row in set (0.00 sec)
复制

• 从MySQL8.0.23版本开始，支持对DoubleWrite文件页的加密，这一特性无需单独配置。MySQL会自动加密属于加密表的双写文件页；

• 支持通过配置innodb_redo_log_encrypt选项对redo日志进行加密，默认禁用。启用innodb_redo_log_encrypt后原redo日志中未加密页面保持未加密状态，新产生的redo日志页面以加密形式写入；反之亦然。加密metadata存放在ib_logfile0文件header中；

• 支持通过配置innodb_undo_log_encrypt选项对undo日志进行加密，默认禁用。启用innodb_undo_log_encrypt后原undo日志中未加密页面保持未加密状态，新产生的undo日志页面以加密形式写入；反之亦然。加密metadata存放在undo日志文件header中；

• 加密秘钥轮换

  加密秘钥应该定期轮换，轮换操作是原子的实例级别的操作。每次轮换主加密密钥时，MySQL 实例中的所有表空间密钥都会重新加密并保存回各自的表空间表头。如果轮换操作被服务器故障中断，重启后将会做前滚操作。

  轮换操作只会更新主秘钥并重新加密表空间秘钥，并不会重新解密并加密表空间数据；

  轮换操作需要Super权限 或 ENCRYPTION_KEY_ADMIN权限；语句如下

  ALTER INSTANCE ROTATE INNODB MASTER KEY;

  成功的 ALTER INSTANCE ROTATE INNODB MASTER KEY 语句将写入二进制日志以在副本上进行复制。

• 请确保对主秘钥进行备份（在创建和轮换后），否则可能无法恢复加密表空间中的数据。

• 通过Performance Schema监控加密进度

#1.打开 stage/innodb/alter tablespace (encryption) instrument:

mysql> system clear
mysql> USE performance_schema;
Database changed
mysql> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter tablespace (encryption)';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

#2.启用the stage event consumer tables，包括events_stages_current, events_stages_history, and events_stages_history_long.

mysql> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

#3.执行一个加密操作
mysql> ALTER TABLESPACE mysql ENCRYPTION = 'Y'; 
Query OK, 0 rows affected (2.80 sec)

#4.通过查询 Performance_Schema events_stages_current 表来检查加密操作的进度。 WORK_ESTIMATED 报告表空间中的总页数。 WORK_COMPLETED 报告处理的页数。
mysql> select * from events_stages_current;
Empty set (0.00 sec)

#5.如果加密操作已完成，events_stages_current 表将返回一个空集。 在这种情况下，您可以检查 events_stages_history 表以查看已完成操作的事件数据。
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history;
+--------------------------------------------+----------------+----------------+
| EVENT_NAME                                 | WORK_COMPLETED | WORK_ESTIMATED |
+--------------------------------------------+----------------+----------------+
| stage/innodb/alter tablespace (encryption) |           2559 |           2559 |
| stage/innodb/alter tablespace (encryption) |           2559 |           2559 |
| stage/innodb/alter tablespace (encryption) |           2559 |           2559 |
| stage/innodb/alter tablespace (encryption) |           2559 |           2559 |
| stage/innodb/alter tablespace (encryption) |           2559 |           2559 |
| stage/innodb/alter tablespace (encryption) |           2559 |           2559 |
| stage/innodb/alter tablespace (encryption) |           2559 |           2559 |
+--------------------------------------------+----------------+----------------+
7 rows in set (0.00 sec)

复制