MySQL 企业版 TDE加密后 测试和问题汇总

一、测试keyring file

1.1 当keyring file文件丢失或者被篡改

结论：不影响当前正在运行的数据库，但是在重启服务后会启动失败出现报错。

tail -n 100 /var/log/mysql/error.log

报错信息如下：

2025-03-12T08:04:54.668847Z 1 [ERROR] [MY-012657] [InnoDB] Encryption can't find master key, please check the keyring is loaded.
2025-03-12T08:04:54.668873Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./aptool/country.ibd can't be decrypted, please confirm that keyring is loaded.
2025-03-12T08:04:54.669509Z 1 [ERROR] [MY-012657] [InnoDB] Encryption can't find master key, please check the keyring is loaded.
2025-03-12T08:04:54.669529Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./aptool/filedownload.ibd can't be decrypted, please confirm that keyring is loaded.
2025-03-12T08:04:54.669998Z 1 [ERROR] [MY-012657] [InnoDB] Encryption can't find master key, please check the keyring is loaded.
2025-03-12T08:04:54.670012Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./aptool/fileupload.ibd can't be decrypted, please confirm that keyring is loaded.
2025-03-12T08:04:54.670605Z 1 [ERROR] [MY-012657] [InnoDB] Encryption can't find master key, please check the keyring is loaded.
2025-03-12T08:04:54.670618Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./aptool/imaging.ibd can't be decrypted, please confirm that keyring is loaded.
2025-03-12T08:04:54.671389Z 1 [ERROR] [MY-012657] [InnoDB] Encryption can't find master key, please check the keyring is loaded.
2025-03-12T08:04:54.671402Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./aptool/vendor_mgt.ibd can't be decrypted, please confirm that keyring is loaded.
2025-03-12T08:04:54.702825Z 0 [ERROR] [MY-013159] [Server] Plugin audit_log reported: 'No keyring installed.'
2025-03-12T08:04:54.702848Z 0 [Warning] [MY-013434] [Server] Plugin audit_log reported: 'Invalid audit log file content: '/var/log/mysql/audit.20250311T063025.log.20250311T062553-1.enc''
2025-03-12T08:04:54.702863Z 0 [ERROR] [MY-013159] [Server] Plugin audit_log reported: 'No keyring installed.'
2025-03-12T08:04:54.702868Z 0 [Warning] [MY-013434] [Server] Plugin audit_log reported: 'Invalid audit log file content: '/var/log/mysql/audit.20250311T063911.log.20250311T063025-1.enc''
2025-03-12T08:04:54.702879Z 0 [ERROR] [MY-013159] [Server] Plugin audit_log reported: 'No keyring installed.'
2025-03-12T08:04:54.702883Z 0 [Warning] [MY-013434] [Server] Plugin audit_log reported: 'Invalid audit log file content: '/var/log/mysql/audit.20250311T065904.log.20250311T063911-1.enc''
2025-03-12T08:04:54.702894Z 0 [ERROR] [MY-013159] [Server] Plugin audit_log reported: 'No keyring installed.'
2025-03-12T08:04:54.702898Z 0 [Warning] [MY-013434] [Server] Plugin audit_log reported: 'Invalid audit log file content: '/var/log/mysql/audit.20250311T071525.log.20250311T063911-1.enc''
2025-03-12T08:04:54.702908Z 0 [ERROR] [MY-013159] [Server] Plugin audit_log reported: 'No keyring installed.'
2025-03-12T08:04:54.702912Z 0 [Warning] [MY-013434] [Server] Plugin audit_log reported: 'Invalid audit log file content: '/var/log/mysql/audit.20250312T080452.log.20250311T063911-1.enc''
2025-03-12T08:04:54.702928Z 0 [ERROR] [MY-013159] [Server] Plugin audit_log reported: 'No keyring installed.'
2025-03-12T08:04:54.702942Z 0 [ERROR] [MY-010202] [Server] Plugin 'audit_log' init function returned error.
2025-03-12T08:04:54.702947Z 0 [ERROR] [MY-010734] [Server] Plugin 'audit_log' registration as a AUDIT failed.
2025-03-12T08:04:54.703521Z 0 [ERROR] [MY-010169] [Server] Failed to initialize dynamic plugins.
2025-03-12T08:04:54.703535Z 0 [ERROR] [MY-010119] [Server] Aborting

将备份的keyring file文件还原，重新启动服务后，数据库启动正常，原来加密的表正常。说明定期备份keyring file文件很重要。

三、测试mysqldump备份文件

模拟文件被泄漏。对启用TDE加密的数据库(某些表加密)执行mysqldump逻辑备份。将mysqldump文件拷贝到其他mysql服务器上执行导入测试

3.1 在未启用加密插件的MySQL上导入备份

在未启用加密插件的mysql示例上(我测试社区版)，导入备份文件报错且数据库和表都未创建。

mysql > ERROR 3185 (HY000) at line 27: Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.Operation failed with exitcode 1

通过notepad打开mysqldump文件，我们可以看到创建表的SQL语句含有ENCRYPTION='Y'加密操作，没有启用TDE的情况下加上这句肯定会报错，我们将文件里面的ENCRYPTION='Y'都使用空替换，重新保存mysqldump文件。

重新导入新的mysqldump文件，经过测试成功，数据库和表都成功创建。

总结：这说明mysqldump文件是明文内容，没有加密，我们去掉创建表时的加密语句后仍然可以在没有TDE的情况下导入到其他MySQL实例上读取数据。

3.2 在已启用加密插件的mysql实例上导入备份

在已启用加密插件的mysql实例上，导入备份文件无报错，同时导入的表也是加密状态。

总结：mysqldump备份文件可以还原到开启加密插件的mysql实例上，并自动生成master key。