一、系統環境

操作系統：Ubuntu 24.04
數據庫:8.4.4-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)

二、安裝TDE組件

前提：檢查組件文件是否存在

ls /usr/lib/mysql/plugin/component_keyring_encrypted_file.so

1.配置全局清單文件

在basedir目錄中創建全局清單文件，設置為只讀權限，目的是告訴服務器要加載哪個密鑰環組件

vim /usr/sbin/mysqld.my# 內容如下 {"read_local_manifest": false,"components": "file://component_keyring_encrypted_file"
}

2.配置全局配置文件

在plugin_dir目錄創建一個全局配置文件，設置為只讀權限

vim /usr/lib/mysql/plugin/component_keyring_encrypted_file.cnf# 內容如下{"read_local_config": false,"path": "/var/lib/mysql-keyring/component_keyring_encrypted_file","password": "AFJQvNQo8GM1!","read_only": false
}

3.創建密鑰環文件

# 創建密鑰環文件 確保密鑰文件目錄權限嚴格touch /var/lib/mysql-keyring/component_keyring_encrypted_file
chown mysql:mysql /var/lib/mysql-keyring/component_keyring_encrypted_file
chmod 700 /var/lib/mysql-keyring/component_keyring_encrypted_file

4.修改apparmor配置

修改apparmor? 用于將配置文件加載到內核中? 否則系統不會自動加載清單文件，查詢會返回"Empty set"

vim /etc/apparmor.d/usr.sbin.mysqld# 添加下面內容到最后面# Allow keyring manifest read file/usr/sbin/mysqld.my r,

完成后重啟apparmor

systemctl reload apparmor.service

5.修改配置文件

修改my,cnf添加默認表加密的配置參數

vim /etc/mysql/mysql.conf.d/mysqld.cnf# 添加下面的內容# table_encryption
default_table_encryption = ON

6.重啟數據庫

重啟數據庫后TDE組件和參數會馬上生效

7.檢查TDE狀態

驗證組件Component_status，查詢keyring_component_status

# 查看組件安裝情況 Component_statusSELECT * FROM performance_schema.keyring_component_status;
+---------------------+---------------------------------------------------------+
| STATUS_KEY          | STATUS_VALUE                                            |
+---------------------+---------------------------------------------------------+
| Component_name      | component_keyring_encrypted_file                        |
| Author              | Oracle Corporation                                      |
| License             | PROPRIETARY                                             |
| Implementation_name | component_keyring_encrypted_file                        |
| Version             | 1.0                                                     |
| Component_status    | Active                                                  |
| Data_file           | /var/lib/mysql-keyring/component_keyring_encrypted_file |
| Read_only           | No                                                      |
+---------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

三、表加密和解密

1.表加密和解密

1.對于已經創建好的表，通過執行SQL可以進行表進行加密和解密

-- 表加密ALTER TABLE database.tablename ENCRYPTION='Y';-- 表解密ALTER TABLE database.tablename ENCRYPTION='N';

2.批量生成未加密表執行加密的SQL語句

-- 生成非業務的表的加密腳本SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS, CONCAT("ALTER TABLE `",TABLE_SCHEMA,"`.`",table_name,"` ENCRYPTION='Y';") AS enable_tde FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND CREATE_OPTIONS NOT LIKE '%ENCRYPTION%' ORDER BY TABLE_SCHEMA;

3.查看已經加密的表

-- 查看加密的表SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';