本文转载自微信公众号《数据与云》，作者杨明涵。转载本文请联系数据和云公众号。前言MySQL支持对InnoDB单表空间、通用表空间、系统表空间、Redo和Undo文件进行静态加密。从8.0.16开始，支持为Schema和普通表空间设置加密默认值，可以统一控制这些Schemas和表空间中的表是否加密；静态加密功能依赖于Keyring组件或插件，MySQL社区版提供的Keyring文件插件会将Keyring数据存储在服务器主机的本地文件系统中。01安装mkdir/usr/local/mysql/keyringchown-Rmysql.mysqlkeyring配置文件中添加[mysqld]early-plugin-load=keyring_file.sokeyring_file_data=/usr/local/mysql/keyring/keyring重新启动数据库mysql>select*frominformation_Schema.pluginswhereplugin_namelike'%keyring%'\G******************************1.row***************************PLUGIN_NAME:keyring_filePLUGIN_VERSION:1.0PLUGIN_STATUS:ACTIVEPLUGIN_TYPE:KEYRINGPLUGIN_TYPE_VERSION:1.1PLUGIN_LIBRARY:keyring_file.soPLUGIN_LIBRARY_VERSION:1.10PLUGIN_AUTHOR:OracleCorporationPLUGIN_DESCRIPTION:store/fetchauthenticationdatato/fromaflatfilePLUGIN_LICENSE:GPLLOAD_OPTION:ONmysql>showglobalvariableslike'%keyring%';+--------------------+------------------------------+|Variable_name|Value|+--------------------+----------------------------------+|keyring_file_data|/usr/local/mysql/keyring/keyring||keyring_operations|ON|+-------------------+--------------------------------+2rowsinset(0.02sec)02加密操作mysql>altertablet7encryption='Y';QueryOK,2rowsaffected(0.10sec)Records:2Duplicates:0Warnings:0mysql>altertablet2encryption='Y';QueryOK,1rowaffected(0.11sec)Records:1Duplicates:0Warnings:0mysql>alterdatabasetestDEFAULTENCRYPTION='Y';QueryOK,1rowaffected(0.03sec)mysql>ALTERTABLESPACEmysqlENCRYPTION='Y';QueryOK,0rowsaffected(2.80sec)03取消加密mysql>altertablet2encryption='N';QueryOK,1rowaffected(0.11sec)Records:1Duplicates:0Warnings:0mysql>altertiontablet='Nencrypt';QueryOK,2rowsaffected(0.08sec)Records:2Duplicates:0Warnings:0mysql>alterdatabasetestDEFAULTENCRYPTION='N';QueryOK,1rowaffected(0.03sec)mysql>ALTERTABLESPACEmysqlENCRYPTION='N';QueryOK,0rowsaffected(2.37sec)04查看元数据插件mysql>SELECTPLUGIN_NAME,PLUGIN_STATUSFROMINFORMATION_SCHEMA.PLUGINSWHEREPLUGIN_NAMELIKE'keyring%';+------------+--------------+|PLUGIN_NAME|PLUGIN_STATUS|+------------+----------------+|keyring_file|ACTIVE|+--------------+----------------+1rowinset(0.01sec)查看已有keymysql>SELECT*FROMperformance_schema.keyring_keys;+--------------------------------------------------+------------+--------------+|KEY_ID|KEY_OWNER|BACKEND_KEY_ID|+------------------------------------------------+------------+----------------+|INNODBKey-8c537ce5-4a53-12eb-907d-000c298c47fa-1|||+-----------------------------------------------+----------+----------------+1rowinset(0.00sec)05查看加密对象加密表空间mysql>SELECTSPACE,NAME,SPACE_TYPE,ENCRYPTIONFROMINFORMATION_SCHEMA.INNODB_TABLESPACESWHEREENCRYPTION='Y';+------------+--------+------------+-----------+|SPACE|NAME|SPACE_TYPE|ENCRYPTION|+------------+--------+------------+------------+|4294967294|mysql|General|Y||145|test/t7|Single|Y|+------------+---------+------------+------------+2rowsinset(0.00sec)查看加密表mysql>SELECTTABLE_SCHEMA,TABLE_NAME,CREATE_OPTIONSFROMINFORMATION_SCHEMA.TABLESWHERECREATE_OPTIONSLIKE'%加密%';+------------+------------+----------------+|表格_SCHEMA|TABLE_NAME|CREATE_OPTIONS|+------------+------------+-----------------+|test|t7|ENCRYPTION='Y'|+------------+------------+----------------+1rowinset(0.04sec)查看加密数据库mysql>SELECTSCHEMA_NAME,DEFAULT_ENCRYPTIONFROMINFORMATION_SCHEMA.SCHEMATAWHEREDEFAULT_ENCRYPTION='YES';+------------+---------------------+|SCHEMA_NAME|DEFAULT_ENCRYPTION|+------------+--------------------+|test|YES|+------------+----------------+1rowinset(0.00sec)从MySQL8开始.0.23，支持DoubleWrite文件页加密。此功能不需要单独配置。MySQL会自动加密属于加密表的双写文件页。支持通过配置innodb_redo_log_encrypt选项来加密Redo日志，默认禁用。启用innodb_redo_log_encrypt后，原始Redolog中未加密的页面保持未加密状态，新生成的Redolog页面以加密形式写入；反之亦然。加密的元数据存储在ib_logfile0文件的标头中。通过配置innodb_undo_log_encrypt选项支持对Undo日志进行加密，默认禁用。启用innodb_undo_log_encrypt后，原Undolog中未加密的页面保持未加密状态，新生成的undolog页面以加密形式写入；反之亦然。加密的元数据存储在撤消日志文件的标头中。06加密密钥轮换加密密钥应定期轮换，轮换操作是原子实例级操作。每次轮换主加密密钥时，MySQL实例中的所有表空间密钥都会重新加密并保存回各自的表空间标头。如果轮换操作因服务器故障而中断，它将在重新启动后前滚。轮换操作只更新主密钥并重新加密表空间密钥，但不会重新解密和重新加密表空间数据。轮转操作需要Super权限或ENCRYPTION_KEY_ADMIN权限；语句如下：ALTERINSTANCEROTATEINNODBMASTERKEY;成功的ALTERINSTANCEROTATEINNODBMASTERKEY语句将写入二进制日志，以便在副本上进行复制。确保备份主密钥（在创建和轮换之后），否则可能无法恢复加密表空间中的数据。07通过PerformanceSchemaOpenstage/innodb/altertablespace(encryption)instrument:mysql>systemclearmysql>USEperformance_schema;Databasechangedmysql>UPDATEsetup_instrumentsSETENABLED='YES'WHERENAMELIKE'stage/innodb/altertablespace(encryption)';QueryOK(0.01rowsaffected)Rowsmatched监控加密进度:1Changed:0Warnings:0启动阶段事件消费者表，包括events_stages_current,events_stages_history,andevents_stages_history_long.mysql>UPDATEsetup_consumersSETENABLED='YES'WHERENAMELIKE'%stages%';QueryOK,3rowsaffected(0.01sec)Rowsmatched:3Changed:0Executingsanencryptionoperationmysql>ALTERTABLESPACEmysqlENCRYPTION='Y';QueryOK,0rowsaffected(2.80sec)通过查询Performance_Schemaevents_stages_current表来检查加密操作的进度。WORK_ESTIMATED报告表空间中的总页数。WORK_COMPLETED报告已处理的页数。mysql>select*fromevents_stages_current;Emptyset(0.00sec)如果加密操作完成，events_stages_current表将返回一个空集。在这种情况下，您可以检查events_stages_history表以查看已完成操作的事件数据。mysql>SELECTEVENT_NAME,WORK_COMPLETED,WORK_ESTIMATEDFROMevents_stages_history;+-------------------------------------------+------------+----------------+|EVENT_NAME|WORK_COMPLETED|WORK_ESTIMATED|+-----------------------------------------+-----------------+----------------+|stage/innodb/altertablespace（加密）|2559|2559||stage/innodb/altertablespace（加密）|2559|2559||stage/innodb/altertablespace(加密)|2559|2559||stage/innodb/altertablespace(加密)|2559|2559||stage/innodb/altertablespace(加密)|2559|2559||stage/innodb/altertablespace(加密)|2559|2559||stage/innodb/altertablespace(加密)|2559|2559|+----------------------------------------+----------------+--------------+7rowsinset(0.00sec)作者简介云和恩墨服务总监杨明涵。拥有MySQL、TDSQL、TiDB、openGauss等认证。长期从事MySQL、PG、Redis、MongoDB数据库技术服务。目前负责云和恩墨西区开源数据库的交付、运维工作；热衷于开源数据库产品的研究。
