mysql安装TDE

环境:
OS:
Centos 7
mysql:5.7.39

 mysql5.7自带TDE插件

1.创建目录
mkdir -p /opt/mysql57/keyring
chown -R mysql:mysql /opt/mysql57/keyring

2.修改配置文件
early-plugin-load=keyring_file.so
keyring_file_data=/opt/mysql57/keyring/keyring

3.启动
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &

4.安装插件(配置文件中配置了,可以不需要使用install命令安装)
/opt/mysql57/bin/mysql -h localhost -uroot -pmysql -P13306 -S /opt/mysql57/data/mysql.sock

mysql> show variables like '%keyring%';
+--------------------+------------------------------+
| Variable_name | Value |
+--------------------+------------------------------+
| keyring_file_data | /opt/mysql57/keyring/keyring |
| keyring_operations | ON |
+--------------------+------------------------------+
2 rows in set (0.01 sec)

mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name='keyring_file';
+--------------+---------------+
| plugin_name | plugin_status |
+--------------+---------------+
| keyring_file | ACTIVE |
+--------------+---------------+
1 row in set (0.00 sec)

好像不需要单独安装了
mysql> install plugin keyring_file soname 'keyring_file.so';
ERROR 1125 (HY000): Function 'keyring_file' already exists

5.查看插件
mysql> show plugins;
+----------------------------+----------+--------------------+-----------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+-----------------+---------+
| keyring_file | ACTIVE | KEYRING | keyring_file.so | GPL |

6.创建数据库
mysql> create database db_test;
Query OK, 1 row affected (0.10 sec)

7.创建加密表

CREATE TABLE tb_tde
(
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
name varchar(32),
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（提交时间）',
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='加密测试表' ENCRYPTION='Y';

写入测试数据
insert into tb_tde(name) values('name1');
insert into tb_tde(name) values('name2');
insert into tb_tde(name) values('name3');
insert into tb_tde(name) values('name4');
insert into tb_tde(name) values('name5');
insert into tb_tde(name) values('name6');
insert into tb_tde(name) values('name7');
insert into tb_tde(name) values('name8');
insert into tb_tde(name) values('name9');
insert into tb_tde(name) values('name10');

添加字段
alter table tb_tde ADD name1 varchar(32);

删除字段
alter table tb_tde drop column name1;

修改字段
alter table tb_tde modify column name varchar(64);

增删改字段与非加密表的操作一致;

8.卸载插件命令
mysql> UNINSTALL PLUGIN keyring_file;
Query OK, 0 rows affected (0.00 sec)

修改配置文件 my.cnf (注释掉如下2句)
##early-plugin-load=keyring_file.so
##keyring_file_data=/opt/mysql57/keyring/keyring

然后重启数据库

9.创建新表,看默认是否是加密的:

CREATE TABLE tb_no_tde
(
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
name varchar(32),
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（提交时间）',
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='加密测试表01';


show create table 看默认是不加密的
| tb_no_tde | CREATE TABLE `tb_no_tde` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(32) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（提交时间）',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='加密测试表01'