本文转载自微信公众号「数据和云」，作者杨明翰 。转载本文请联系数据和云公众号。
前言
MySQL支持对InnoDB单表空间、通用表空间、系统表空间和Redo、Undo文件进行静态加密。从8.0.16开始支持对Schema和通用表空间设置加密默认值，这就允许对在这些Schema和表空间中的表是否加密进行统一控制;静态加密功能依赖于Keyring组件或插件，MySQL社区版提供的Keyring file插件会将Keyring数据存储在服务器主机的本地文件系统中。
01 安装
- mkdir /usr/local/mysql/keyring
- chown -R mysql.mysql keyring
- 配置文件中增加
- [mysqld]early-plugin-load=keyring_file.sokeyring_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)
02 加密操作
- 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)
03 取消加密
- 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)
04 查看元数据
- 查看插件
- 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)
05 查看加密对象
- 加密表空间
- 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中。
06 加密秘钥轮换
加密秘钥应该定期轮换，轮换操作是原子的实例级别的操作。每次轮换主加密密钥时，MySQL 实例中的所有表空间密钥都会重新加密并保存回各自的表空间表头。如果轮换操作被服务器故障中断，重启后将会做前滚操作。
轮换操作只会更新主秘钥并重新加密表空间秘钥，并不会重新解密并加密表空间数据。
轮换操作需要Super权限或 ENCRYPTION_KEY_ADMIN权限;语句如下：
- ALTER INSTANCE ROTATE INNODB MASTER KEY;
成功的 ALTER INSTANCE ROTATE INNODB MASTER KEY 语句将写入二进制日志以在副本上进行复制。
请确保对主秘钥进行备份(在创建和轮换后)，否则可能无法恢复加密表空间中的数据。
07 通过Performance Schema监控加密进度
- 打开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
- 启用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
- 执行一个加密操作
- mysql> ALTER TABLESPACE mysql ENCRYPTION = 'Y';
- Query OK, 0 rows affected (2.80 sec)
- 通过查询Performance_Schema events_stages_current 表来检查加密操作的进度。
WORK_ESTIMATED 报告表空间中的总页数。
WORK_COMPLETED 报告处理的页数。
- mysql> select * from events_stages_current;
- Empty set (0.00 sec)
- 如果加密操作已完成，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)
关于作者
杨明翰，云和恩墨服务总监。拥有MySQL、TDSQL、TiDB、openGauss等认证。长期从事MySQL、PG、Redis、MongoDB的数据库技术服务。现负责云和恩墨西区开源数据库交付运维工作;热衷于开源数据库产品的研究。
