MySQL存储安全 设置透明数据加密TDE、自动备份数据等。 一、mysql启用tde透明加密 MySQL数据库存储加密有多种方式可供选择，主要分为两大类： 利用字段加密，在对数据库存储/读取时进行加密/解密操作，需要用户修改应用程序 利用TDE透明加密客户端，实现数据库实例文件存储加密，用户无需修改应用程序 下面以 MySQL 表空间加密为例，介绍mysql如何实现TDE透明加密。 tde 概述 TDE(Transparent Data Encryption,透明数据加密) 即对数据文件执行实时I/O加密和解密。数据在写入磁盘之前进行加密，从磁盘读入内存时进行解密。TDE通过对MySQL数据库中的表空间进行加密，阻止可能的攻击者绕过数据库直接从存储中读取敏感信息。TDE加密具有如下优点： 开发人员无需更改任何应用程序 根据官方文档，启用后性能影响百分比在个位数（经验5%），启用TDE不会增加数据文件的大小 mysql对tde的支持情况 MySQL 5.7 开始支持TDE，8.0提供更多的加密选项，社区版和企业版都可以，区别是企业版提供额外的keyring组件和插件，可以提供更好的安全性（如果要达到合规的要求，需要使用企业版），详细支持情况如下表所示： keyring插件名称 社区版 企业版 备注 component_keyring_file 8.0.24+ 8.0.24+ component_keyring_encrypted_file 不支持 8.0.24+- component_keyring_oci 不支持 8.0.31+- keyring_file 5.7.11+ 5.7.11+ 将秘钥数据保存在本地的文件 keyring_encrypted_file 不支持 5.7.21+ 将秘钥数据保存在本地的加密文件 keyring_okv 不支持 5.7.12+- keyring_aws 不支持 5.7.19+ 与Amazon KMS通信，用于后端存储 keyring_hashicorp 不支持 8.0.18+ 与HashiCorp Vault通信，用于后端存储 keyring_oci 不支持 8.0.22+- 2）基于keyring_file插件的tde透明数据加密实战 准备工作：安全起见，启用TDE之前建议先做一次完整备份 mysqldump --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -uroot -p -A > alldb_2023.sql 第1步，创建密钥文件（比如keyring）的保存目录，比如 /data/mysql_3306/keyring mkdir -p /data/mysql_3306/keyring chown -R mysql:mysql /data/mysql_3306/keyring chmod -R 775 /data/mysql_3306/keyring 第2步，找到加密插件文件 keyring_file.so 所在的目录，比如 /opt/mysql/lib/mysql/plugin/ [root@localhost ~]# find / -name keyring_file.so /opt/mysql/lib/mysql/plugin/keyring_file.so 第3步，修改MySQL配置文件，启用数据加密功能（持久化启用） [mysqld] plugin_dir=/opt/mysql/lib/mysql/plugin/early-plugin-load=keyring_file.so keyring_file_data=/data/mysql_3306/keyring/keyring innodb_file_per_table=1 第4步，确认上述配置已修改，并重启mysql示例使配置生效 [root@localhost ~]# cat /data/mysql_3306/my_3306.cnf | egrep "(plugin_dir|keyring_file|innodb_file_per_table)" plugin_dir=/opt/mysql/lib/mysql/plugin/early-plugin-load=keyring_file.so keyring_file_data=/data/mysql_3306/keyring/keyring innodb_file_per_table = 1 [root@localhost ~]# ps -ef | grep mysql | grep -v grep | awk '{print $2}' | xargs kill -9 2> /dev/null [root@localhost ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf & 第5步，确认加密插件相关配置已启用 mysql> show variables like 'plugin_dir'; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | plugin_dir | /opt/mysql/lib/mysql/plugin/ | +---------------+------------------------------+ mysql> SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_Library FROM information_schema.plugins WHERE PLUGIN_NAME LIKE 'keyring_file'; +--------------+---------------+-----------------+ | PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_Library | +--------------+---------------+-----------------+ | keyring_file | ACTIVE | keyring_file.so | +--------------+---------------+-----------------+ mysql> show global variables like 'keyring_file_data'; +-------------------+----------------------------------+ | Variable_name | Value | +-------------------+----------------------------------+ | keyring_file_data | /data/mysql_3306/keyring/keyring | +-------------------+----------------------------------+ mysql> show global variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 第6步，先选择要加密的数据库，再选择要加密的表，并将其加密 mysql> CREATE DATABASE IF NOT EXISTS testdb; mysql> CREATE TABLE testdb.tde_yes(c1 varchar(20)) ENCRYPTION='Y'; mysql> CREATE TABLE testdb.tde_no(c1 varchar(20)); mysql> ALTER TABLE testdb.tde_yes ENCRYPTION='Y'; mysql> INSERT INTO testdb.tde_yes VALUES('aaa'); mysql> INSERT INTO testdb.tde_no VALUES('aaa'); 第7步，查看哪些表已启用TDE解密 mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%'; +--------------+------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | +--------------+------------+----------------+ | testdb | tde_yes | ENCRYPTION="Y" | +--------------+------------+----------------+ 注意事项 MySQL的keyring_file加密会有以下限制，使用时需要注意： 使用keyring_file 和 keyring_encrypted file 插件时，无法满足某些规范要求的密钥集中管理 高级加密标准（AES）是唯一支持的加密算法 表空间加密仅支持存储在单独表空间的InnoDB表，存储在其他InnoDB表空间类型（包括常规表空间，系统表空间，撤消日志表空间和临时表空间）中的表不支持加密。 一次只能启用一个keyring插件，不支持启用多个keyring插件 开启TDE后，由于查询表数据时会解密并读取到内存中，因此查询加密的数据时结果仍是明文数据，而常用数据库工具（Navicat等）可以正常使用 如果数据库实例上拥有至少一个加密的数据库，那么也可能会降低未加密数据库的性能，所以建议加密数据库和未加密数据库保存在不同的数据库实例上 更改密钥插件需要解密表，卸载当前的密钥插件，安装和配置其他密钥插件，并重新加密表 已经被加密的表（Innodb引擎）不允许修改成其它存储引擎 mysql> alter table testdb.tde_yes ENGINE=MyISAM; ERROR 1478 (HY000): Table storage engine 'MyISAM' does not support the create option 'ENCRYPTION' [实测] 先配置主从复制，再分别启用tde加密，不会导致主从复制失败（即使主库与从库使用的不是同一个密钥文件） 场景1：模拟秘钥文件被误删 第1步，备份秘钥之后将其删除，可模拟秘钥文件被误删 [root@localhost ~]# ll /data/mysql_3306/keyring/ -rw-r----- 1 mysql mysql 0 Oct 18 10:24 keyring [root@localhost ~]# cp /data/mysql_3306/keyring/keyring /tmp/keyring.20231019 [root@localhost ~]# rm -f /data/mysql_3306/keyring/keyring 第2步，向加密表（示例testdb.tde_yes）写入数据，未重启mysql示例之前读写正常 mysql> insert into testdb.tde_yes VALUES('bbb'); mysql> select * from testdb.tde_yes; +------+ | c1 | +------+ | aaa | | bbb | +------+ 第3步，重启mysql示例，自动生成新的秘钥文件，可模拟秘钥文件错误 [root@localhost ~]# ps -ef | grep mysql | grep -v grep | awk '{print $2}' | xargs kill -9 2> /dev/null [root@localhost ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf & [root@localhost ~]# ll /data/mysql_3306/keyring/ -rw-r----- 1 mysql mysql 0 Oct 19 16:12 keyring 第4步，加密表仍存在但查询数据失败 mysql> show tables from testdb; +------------------+ | Tables_in_testdb | +------------------+ | tde_no | | tde_yes | +------------------+ mysql> select * from testdb.tde_no; +------+ | c1 | +------+ | aaa | +------+ mysql> select * from testdb.tde_yes; ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully. 第5步，杀掉mysql进程，用老的秘钥文件覆盖，再启动mysql进程 [root@localhost ~]# ps -ef | grep mysql | grep -v grep | awk '{print $2}' | xargs kill -9 2> /dev/null [root@localhost ~]# \cp /tmp/keyring.20231019 /data/mysql_3306/keyring/keyring [root@localhost ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf & 第6步，查询加密表正常 mysql> select * from testdb.tde_yes; +------+ | c1 | +------+ | aaa | | bbb | +------+ 结论：秘钥在mysql实例启动时会对加密表空间进行解密，如果秘钥丢失会导致无法打开加密表空间，造成数据丢失，必须妥善保管秘钥。 场景2：秘钥轮转之后老秘钥还能用吗 第1步，将秘钥文件备份一下 [root@localhost ~]# cd /data/mysql_3306/keyring/ [root@localhost ~]# cp keyring keyring.old [root@localhost ~]# ll -rw-r----- 1 mysql mysql 283 Oct 20 16:49 keyring -rw-r----- 1 root root 283 Oct 20 16:49 keyring.old 第2步，对主秘钥执行轮换，生成新的秘钥文件（注意修改时间） mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY; [root@localhost ~]# ll /data/mysql_3306/keyring -rw-r----- 1 mysql mysql 667 Oct 24 11:04 keyring -rw-r----- 1 root root 283 Oct 20 16:49 keyring.old 第3步，先杀掉mysql进程（保证在mysql服务停止状态下替换秘钥），再用老的秘钥文件替换 [root@localhost ~]# ps -ef | grep mysql | grep -v grep | awk '{print $2}' | xargs kill -9 2> /dev/null [root@localhost ~]# mv keyring keyring.new [root@localhost ~]# mv keyring.old keyring [root@localhost ~]# chown -R mysql.mysql keyring [root@localhost ~]# ll -rw-r----- 1 mysql mysql 283 Oct 20 16:49 keyring -rw-r----- 1 mysql mysql 667 Oct 24 11:04 keyring.new 第4步，启动mysql进程 [root@localhost ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf & 第5步，加密表仍存在但查询数据失败 mysql> show tables from testdb; +------------------+ | Tables_in_testdb | +------------------+ | tde_yes | +------------------+ mysql> select * from testdb.tde_no; +------+ | c1 | +------+ | aaa | +------+ mysql> select * from testdb.tde_yes; ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully. 第6步，杀掉mysql进程，用轮转后的秘钥文件覆盖，再启动mysql进程 [root@localhost ~]# ps -ef | grep mysql | grep -v grep | awk '{print $2}' | xargs kill -9 2> /dev/null [root@localhost ~]# mv keyring keyring.old [root@localhost ~]# mv keyring.new keyring [root@localhost ~]# chown -R mysql.mysql keyring [root@localhost ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf & 第7步，查询加密表正常 mysql> select * from testdb.tde_yes; +------+ | c1 | +------+ | aaa | | bbb | +------+ 结论：当key不安全时，建议进行秘钥轮换。轮换后老的秘钥将立即失效，新的秘钥一定要妥善保存。 场景3：模拟mysqldump备份文件泄漏 第1步，对启用TDE加密的database（比如testdb）执行mysqldump逻辑备份 mysqldump --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --triggers --events --routines --skip-tz-utc -uroot -p -S /tmp/mysql_3306.sock --databases testdb > /opt/testdb_2023.sql 第2步，将mysqldump文件拷贝到另外2台mysql服务器（比如 10.30.3.232及10.30.3.233），模拟mysqldump备份文件泄漏 scp /opt/testdb_2023.sql 10.30.3.232:/opt/ scp /opt/testdb_2023.sql 10.30.3.233:/opt/ 第3步，在未启用加密插件的mysql示例（示例10.30.3.232）上，导入备份文件报错且表未创建 mysql> SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_Library FROM information_schema.plugins WHERE PLUGIN_NAME LIKE 'keyring_file'; Empty set (0.00 sec) mysql> source /opt/testdb_2023.sql; Query OK, 0 rows affected (0.00 sec) ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully. Query OK, 0 rows affected (0.00 sec) ERROR 1146 (42S02): Table 'testdb.tde_yes' doesn't exist mysql> show tables from testdb; Empty set (0.00 sec) 第4步，在已启用加密插件的mysql示例（示例10.30.3.233）上，导入备份文件无报错 mysql> SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_Library FROM information_schema.plugins WHERE PLUGIN_NAME LIKE 'keyring_file'; +--------------+---------------+-----------------+ | PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_Library | +--------------+---------------+-----------------+ | keyring_file | ACTIVE | keyring_file.so | +--------------+---------------+-----------------+ mysql> source /opt/testdb_2023.sql; Query OK, 0 rows affected (0.00 sec) mysql> select * from testdb.tde_yes; +------+ | c1 | +------+ | aaa | | bbb | +------+ 结论：mysqldump备份文件可以还原到开启加密插件的mysql实例上，自动生成master key 场景4：模拟xtrabackup备份文件泄漏 第1步，所有节点：安装 xtrabackup 2.4.28 [root@localhost ~]# xtrabackup --version xtrabackup: recognized server arguments: xtrabackup version 2.4.28 based on MySQL server 5.7.40 Linux (x86_64) (revision id: 44a8f7b) 第2步，源实例：执行xtrabackup物理备份（注：xtrabackup不会把秘钥文件拷贝到备份目录） xtrabackup --defaults-file=/data/mysql_3306/my_3306.cnf --user=root --password=Q8yWuLJ1q_Sth9fA --socket=/tmp/mysql_3306.sock --compress --history --kill-long-queries-timeout=40 --kill-long-query-type=all --tmpdir=/tmp/ --backup --stream=xbstream --slave-info --safe-slave-backup > /opt/xtrabackup_2023.xbstream 第3步，源实例：将xtrabackup文件拷贝到目标服务器（比如 10.30.3.232），模拟xtrabackup备份文件泄漏 scp /opt/xtrabackup_2023.xbstream 10.30.3.232:/opt/ 第4步，目标实例：假设目标服务器启用了tde加密插件，但此秘钥非彼秘钥，即备份文件已泄漏但秘钥文件未泄漏 mysql> SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_Library FROM information_schema.plugins WHERE PLUGIN_NAME LIKE 'keyring_file'; +--------------+---------------+-----------------+ | PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_Library | +--------------+---------------+-----------------+ | keyring_file | ACTIVE | keyring_file.so | +--------------+---------------+-----------------+ 第5步，目标实例：在目标示例上进行恢复，在 xtrabackup --prepare 阶段就报错了 [root@localhost ~]# rm -r -f /data/mysql_3306/xtra_bak [root@localhost ~]# mkdir -p /data/mysql_3306/xtra_bak [root@localhost ~]# xbstream -x < /opt/xtrabackup_2023.xbstream -C /data/mysql_3306/xtra_bak [root@localhost ~]# xtrabackup --decompress --target-dir=/data/mysql_3306/xtra_bak [root@localhost ~]# xtrabackup --prepare --target-dir=/data/mysql_3306/xtra_bak ...... InnoDB: Highest supported file format is Barracuda. InnoDB: Encryption information in datafile: ./testdb/tde_yes.ibd can't be decrypted, please check if a keyring plugin is loaded and initialized successfully. 结论：若没有源实例的密钥文件，无法恢复xtrabackup备份。 附录：下面是正确的xtrabackup备份及恢复步骤，注意 --keyring-file-data 参数 #.1.源实例：执行xtrabackup备份（指定--keyring-file-data） xtrabackup --defaults-file=/data/mysql_3306/my_3306.cnf --user=root --password=Q8yWuLJ1q_Sth9fA --socket=/tmp/mysql_3306.sock --compress --history --kill-long-queries-timeout=40 --kill-long-query-type=all --tmpdir=/tmp/ --backup --no-timestamp --stream=xbstream --slave-info --safe-slave-backup --keyring-file-data=/data/mysql_3306/keyring/keyring > /opt/xtrabackup_2023.xbstream #.2.源实例：将备份文件和秘钥拷贝到目标示例（比如10.30.3.232） scp /opt/xtrabackup_2023.xbstream 10.30.3.232:/opt/ scp /data/mysql_3306/keyring/keyring 10.30.3.232:/opt/ #.3.目标实例：解压备份文件 rm -r -f /data/mysql_3306/xtra_bak mkdir -p /data/mysql_3306/xtra_bak xbstream -x < /opt/xtrabackup_2023.xbstream -C /data/mysql_3306/xtra_bak #.4.目标实例：执行prepare（指定--keyring-file-data） xtrabackup --decompress --target-dir=/data/mysql_3306/xtra_bak --keyring-file-data=/opt/keyring xtrabackup --prepare --target-dir=/data/mysql_3306/xtra_bak --keyring-file-data=/opt/keyring #.5.目标实例：先关掉mysql实例，再改名datadir目录 ps -ef | grep mysql | grep -v grep | awk '{print $2}' | xargs kill -9 2> /dev/null mv /data/mysql_3306/iblog /data/mysql_3306/iblog_$(date +%Y%m%d%H%M%S) mv /data/mysql_3306/var /data/mysql_3306/var_$(date +%Y%m%d%H%M%S) #.6.目标实例：执行copy-back（指定--keyring-file-data） xtrabackup --defaults-file=/data/mysql_3306/my_3306.cnf --copy-back --target-dir=/data/mysql_3306/xtra_bak --keyring-file-data=/opt/keyring #.7.目标示例：用源实例的密钥文件覆盖，并启动mysql服务 mv /data/mysql_3306/keyring/keyring /tmp/keyring.$(date +%Y%m%d%H%M%S) cp /opt/keyring /data/mysql_3306/keyring/keyring chown -R mysql.mysql /data/mysql_3306 /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf & #.8.目标示例：查询加密表确认一下 mysql> select * from testdb.tde_yes; +------+ | c1 | +------+ | aaa | | bbb | +------+ 遇到的坑：若使用xtrabackup 2.4.13， 即便参考上述步骤对mysql 5.7.42进行备份和恢复都正常，但执行select提示 Table xxx doesn't exist，而使用xtrabackup 2.4.28则备份及恢复正常。 [root@localhost ~]# mysql --version mysql Ver 14.14 Distrib 5.7.42-45, for Linux (x86_64) using 6.2 [root@localhost ~]# xtrabackup --version xtrabackup: recognized server arguments: xtrabackup version 2.4.13 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3e7ca7c) 常用操作 场景1：假设老业务刚配置了tde加密，有大量的表需要启用加密，如何批量生成加密脚本？ mysql> CREATE TABLE testdb.tb2(c1 int); mysql> CREATE TABLE testdb.tb3(c1 int); mysql> 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 = 'testdb' AND CREATE_OPTIONS NOT LIKE '%ENCRYPTION%'; +--------------+------------+----------------+--------------------------------------------+ | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | enable_tde | +--------------+------------+----------------+--------------------------------------------+ | testdb | tb2 | | ALTER TABLE `testdb`.`tb2` ENCRYPTION='Y'; | | testdb | tb3 | | ALTER TABLE `testdb`.`tb3` ENCRYPTION='Y'; | +--------------+------------+----------------+--------------------------------------------+ keyring管理 定时备份：可以固定时间进行备份，需要还原的时候再拷贝至目标文件 mkdir -p /root/backup crontab -l | grep keyring || echo '59 23 * * * /usr/bin/cp /data/mysql_3306/keyring/keyring /root/backup/keyring.$(date +\%Y\%m\%d\%H\%M\%S) >/dev/null 2>&1' >> /var/spool/cron/`whoami` 定时更新：为了考虑安全性，当key不安全时，需要进行更新。更新后原先的表依旧可以正常方案，因为更新置会改变 master encryption key 并重新加密 tablespace keys，不会对表空间重新加密或解密。 #.主秘钥轮换 mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY; 务必妥善保管私钥，确保只有授权的人员可以访问它。私钥的泄露将导致数据不再安全。 需要定期更换密钥，以降低破解的风险。定期更换密钥可以提高系统的安全性，并且保护数据不会长期处于相同的密钥下。 如何卸载插件 第1步，确认没有已加密的数据表，如果存在密文表则建议先解密再卸载插件 mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%'; 第2步，对已启用TDE加密的表执行解密，请执行如下命令： mysql> alter table testdb.tde_yes encryption='N'; 第3步，在线卸载keyring_file插件 mysql> UNINSTALL PLUGIN keyring_file; 第4步，修改配置文件移除插件，删除秘钥文件（卸载插件并不会删除秘钥文件），并重启mysql示例使配置生效 [root@localhost ~]# sed -i '/keyring_file/d' /data/mysql_3306/my_3306.cnf [root@localhost ~]# cat /data/mysql_3306/my_3306.cnf | egrep "(keyring_file)" [root@localhost ~]# mv /data/mysql_3306/keyring/keyring /tmp/keyring.delete [root@localhost ~]# ps -ef | grep mysql | grep -v grep | awk '{print $2}' | xargs kill -9 2> /dev/null [root@localhost ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf & 第5步，确认TDE加密已禁用 mysql> SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_Type,PLUGIN_Library FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring_file'; Empty set (0.00 sec) 参考 MySQL 5.7 - https://dev.mysql.com/doc/refman/5.7/en/keyring.html MySQL 5.7 - https://dev.mysql.com/doc/refman/5.7/en/innodb-data-encryption.html Percona - https://docs.percona.com/percona-xtrabackup/2.4/advanced/encrypted_innodb_tablespace_backups.html 阿里云-设置透明数据加密功能：https://help.aliyun.com/zh/rds/apsaradb-rds-for-mysql/configure-tde-for-an-apsaradb-rds-for-mysql-instance?spm=a2c4g.11186623.0.i35 华为云-开启透明数据加密功能：https://support.huaweicloud.com/usermanual-rds/rds_05_0031.html
标签：--,备份,MySQL,keyring,TDE,file,mysql,3306,data From： https://www.cnblogs.com/rcsy/p/18280725