mysql数据库中的数据加密与备份安全

MySQL字段加密须用AES_ENCRYPT()并由应用层管理密钥，社区版不支持TDE；备份需SSL传输、文件加密及权限最小化，并定期验证解密有效性。

MySQL 数据加密：字段级加密用 AES_ENCRYPT()，别碰透明数据加密（TDE）除非有企业版

MySQL 社区版不支持原生 TDE（Transparent Data Encryption），所谓“开启 innodb_encrypt_tables”在社区版里只是占位符，实际无效。真要加密敏感字段（如身份证、手机号），必须手动用 AES_ENCRYPT() 和 AES_DECRYPT()，且密钥必须由应用层管理——数据库里存密钥等于白加密。

常见错误是把密钥硬编码在 SQL 里：

SELECT AES_ENCRYPT('123456789', 'mykey');

这会让密钥暴露在 binlog、慢查询日志甚至客户端历史中。正确做法是：应用生成随机密钥，用 SHA2('用户密码+盐', 256) 衍生出 32 字节密钥，再传入 AES_ENCRYPT(data, key)；解密同理，绝不让密钥出现在 SQL 文本中。

• AES_ENCRYPT() 输出是 VARBINARY，字段类型必须设为 VARBINARY(255) 或更长，不能用 VARCHAR
• 密钥长度必须是 16/24/32 字节，否则函数静默失败或返回 NULL
• MySQL 5.7+ 默认用 ECB 模式（不安全），8.0.13+ 才支持 AES_ENCRYPT(data, key, 'aes-256-cbc') 指定模式

备份文件本身必须加密，mysqldump 不带 --ssl 或 --compress 就别跑

直接 mysqldump -u root -p db > backup.sql 产生的明文文件，和裸表文件一样危险。备份过程若走网络（比如远程 dump），未启用 SSL 会导致密码、SQL 内容全程明文传输。

强制启用加密传输：

mysqldump --ssl-mode=REQUIRED -u appuser -p db_name > backup.sql

同时确保 MySQL 服务端已配置 require_secure_transport = ON，否则客户端加了 --ssl-mode 也没用。

• 备份后立刻用 gpg 或 openssl enc 加密文件：

  openssl enc -aes-256-cbc -salt -in backup.sql -out backup.sql.enc

• 不要用 mysqldump --single-transaction 备份含 MyISAM 表的库——它只对 InnoDB 有效，MyISAM 会锁表，导致备份卡住或不一致
• 物理备份（xtrabackup）比逻辑备份更难加密，必须在拷贝完成后立即加密文件，不能依赖“备份工具内置加密”，xtrabackup 2.4 的 --encrypt 已废弃，3.0+ 的加密需额外 license

权限最小化：备份账号不能有 FILE、PROCESS、SUPER

很多脚本用 root@localhost 做自动备份，这是最常见也最危险的配置。一旦备份机被入侵，攻击者可直接执行 SELECT ... INTO OUTFILE 写 shell，或用 SHOW PROCESSLIST 窃取其他连接密码。

专用备份账号应仅授予：

GRANT SELECT, LOCK TABLES, RELOAD ON *.* TO 'backup'@'192.168.1.%';

注意：RELOAD 是为了 FLUSH TABLES WITH READ LOCK（仅 MyISAM 需要），InnoDB 备份通常不需要它；LOCK TABLES 权限不能省，否则 --single-transaction 在某些隔离级别下可能失效。

• 绝对禁止给备份账号 FILE 权限——它允许读写任意服务器文件系统路径
• 不要用 % 通配主机，限定具体 IP 段（如 '192.168.1.%'），避免 DNS 反查绕过
• 密码必须用强随机字符串，且定期轮换；避免用 mysql_config_editor 存密，它的加密强度极低，~/.mylogin.cnf 文件权限设错就会泄露

备份恢复验证不是可选项，mysqlcheck --check 只能验结构，不能验加密数据是否可解

定期恢复备份到测试库并执行业务查询，是唯一能确认备份有效的手段。只跑 mysqlcheck --check 或 head -20 backup.sql | grep INSERT，完全无法发现 AES 密钥丢失、字符集错乱、或 SET NAMES latin1 导致中文变问号这类问题。

验证脚本必须包含真实解密逻辑：

SELECT id, AES_DECRYPT(phone_enc, UNHEX(SHA2('real_app_key', 256))) FROM users LIMIT 1;

如果返回 NULL，说明要么密钥不对，要么备份时用了错误的字符集（比如导出时没加 --default-character-set=utf8mb4）。

• 自动化验证任务必须独立于备份任务运行，不能共用同一套密钥环境变量——否则密钥泄漏时验证也会失效
• 备份压缩包（.sql.gz）解压后要先 file backup.sql 确认编码，再导入；UTF-8 BOM 会导致 MySQL 解析失败且报错模糊
• 时间点恢复（PITR）依赖 binlog，务必确认 binlog_format = ROW 且 expire_logs_days 设置合理，否则加密字段更新后无法还原原始值

加密和备份安全的真正难点不在技术实现，而在于密钥生命周期管理与权限边界的持续收敛——一次疏忽的权限扩大、一个未轮换的备份密钥、一次跳过的恢复演练，都可能让所有防护形同虚设。