数据加密 #

安全控制一直是治理的重要环节，数据安全一直是极为重视和敏感的话题。数据加密是指对某些敏感信息通过加密规则进行数据的变形，实现敏感隐私数据的可靠保护。涉及客户安全数据或者一些商业性敏感数据，如身份证号、手机号、卡号、客户号等个人信息按照相关部门规定，都需要进行数据加密。

对于数据加密的需求，在现实的业务场景中一般分为两种情况：

新业务上线，安全部门规定需将涉及用户敏感信息，例如银行、手机号码等进行加密后存储到数据库，在使用的时候再进行解密处理。因为是全新系统，因而没有存量数据清洗问题，所以实现相对简单。
已上线业务，之前一直将明文存储在数据库中。相关部门突然需要对已上线业务进行加密整改。这种场景一般需要处理 3 个问题：

历史数据需要如何进行加密处理，即洗数。
如何能在不改动业务 SQL 和逻辑情况下，将新增数据进行加密处理，并存储到数据库；在使用时，再进行解密取出。
如何较为安全、无缝、透明化地实现业务系统在明文与密文数据间的迁移。

概述 #

SphereEx-DBPlusEngine 根据业界对加密的需求及业务改造痛点，提供了一套完整、安全、透明化、低改造成本的数据加密整合解决方案。目前 SphereEx-DBPlusEngine 内置了 MD5、AES、RC4、SM4 和 SM3 算法，支持 MySQL、PostgreSQL、openGauss、Oracle 等常见数据库类型。

在真实业务场景中，相关业务开发团队则往往需要针对公司安全部门需求，自行实行并维护一套加解密系统。而当加密场景发生改变时，自行维护的加密系统往往又面临着重构或修改风险。此外，对于已经上线的业务，在不修改业务逻辑和 SQL 的情况下，透明化、安全低风险地实现无缝进行加密改造也相对复杂。对于一些安全级别更高的非幂等加密算法，提供不可逆的幂等列用于查询，即查询辅助列。

为了提升功能的易用性，SphereEx-DBPlusEngine 数据加密插件提供了云端密钥管理、洗数以及反洗数能力。

目前 SphereEx-DBPlusEngine 数据加密插件所支持的数据库产品为 MySQL、PostgreSQL、openGauss、SQLServer、Oracle、Hive 和 Presto 等，具体支持版本如下。

	数据库	版本支持
1	MySQL	5.7.x ~ 8.x
2	PostgreSQL	9.6 及以上版本
3	openGauss	2.1.0 及以上版本
4	SQLServer	15.0 及以上版本
5	Oracle	10g 及以上版本
6	Hive	2.3.2
7	Presto	0.181, 0.272

SphereEx-DBPlusEngine 还提供了解密后类型自动转换的功能。

如果加密规则中配置了 DATA_TYPE，那么解密后会自动转换成 DATA_TYPE 对应的 java 数据类型，数字类型精度也会按照定义自动转换。

当前已支持 MySQL（5.7.x ~ 8.x）、ORACLE（10g 及以上版本）。

具体支持情况如下：

Oracle 支持情况

数据库字段类型	是否支持类型自动转换	说明
VARCHAR2	支持
NCHAR	支持
NVARCHAR2	支持
NUMBER	支持
FLOAT	支持	相同精度的转换暂未实现，建议使用其它数字类型
BINARY_FLOAT	支持
BINARY_DOUBLE	支持
RAW	不支持
BLOB	不支持
DATE	不支持
TIMESTAMP	不支持
INTERVAL YEAR TO MONTH	不支持
INTERVAL DAY TO SECOND	不支持
CLOB	不支持
NCLOB	不支持
BFILE	不支持
ROWID	不支持
UROWID	不支持
CHARACTER VARYING	支持
CHAR VARYING	支持
NCHAR VARYING	支持
VARCHAR	支持
NATIONAL CHARACTER	支持
NATIONAL CHAR VARYING	支持
NUMERIC	支持
DECIMAL	支持
DEC	支持
INTEGER	支持
INT	支持
SMALLINT	支持
DOUBLE PRECISION	支持
REAL	支持
ANYDATA	不支持
ANYTYPE	不支持
ANYDATASET	不支持
XMLTYPE	不支持
URITYPE	不支持
SDO_GEOMETRY	不支持
SDO_TOPO_GEOMETRY	不支持
SDO_GEORASTER	不支持

MySQL 支持情况

数据库字段类型	是否支持类型自动转换	说明
INT	支持
BIT	不支持
TINYINT	支持
BOOL	不支持
BOOLEAN	不支持
SMALLINT	支持
MEDIUMINT	支持
INTEGER	支持
BIGINT	支持
DECIMAL	支持
DEC	支持
NUMERIC	支持
FIXED	支持
FLOAT	支持	可能存在加密后返回 FLOAT 但是精度和数据库不同，不同硬件和平台可能有区别
DOUBLE	支持
REAL	支持
DATE	不支持
DATETIME	不支持
TIME	不支持
YEAR	不支持
NATIONAL CHAR	支持
CHAR	支持
NATIONAL VARCHAR	支持
BINARY	不支持
VARBINARY	不支持
TINYBLOB	支持
TINYTEXT	支持
BLOB	不支持
TEXT	不支持
MEDIUMBLOB	不支持
MEDIUMTEXT	不支持
LONGBLOB	不支持
LONGTEXT	不支持
ENUM	不支持
SET	不支持
GEOMETRY	不支持
POINT	不支持
LINESTRING	不支持
POLYGON	不支持
MULTIPOINT	不支持
MULTILINESTRING	不支持
MULTIPOLYGON	不支持
GEOMETRYCOLLECTION	不支持
JSON	不支持

说明：MySQL, Oracle 解密后可以返回字段原始的数据类型。其他数据库类型解密后返回的是字符类型。

基本概念 #

逻辑列

用于计算加解密列的逻辑名称，是 SQL 中列的逻辑标识。逻辑列包含密文列（必须）、查询辅助列（可选）和明文列（可选）。

逻辑列类型（dataType）

用于定义逻辑列的类型，例如 INT NOT NULL，VARCHAR(200) DEFAULT NULL 等，具体可以参见官方文档中各种方言字段的定义。

密文列（cipherColumn）

加密后的数据列。

密文列类型（cipherDataType）

用于定义密文列的类型，可根据算法进行配置，未配置时使用默认类型 VARCHAR (4000)。

查询辅助列（assistedQueryColumn）

用于查询的辅助列。对于一些安全级别更高的非幂等加密算法，提供不可逆的幂等列用于查询。

查询辅助列类型（assistedQueryDataType）

用于定义查询辅助列类型，可根据算法进行配置，未配置时使用默认类型 VARCHAR (4000)。

模糊查询列（likeQueryColumn）

对于需要模糊查询的场景，该字段用于实现模糊查询。

模糊查询列类型（likeQueryDataType）

用于定义模糊查询列类型，可根据算法进行配置，未配置时使用默认类型 VARCHAR (4000)。

排序查询列（orderQueryColumn）

对于需要排序、大小比较和范围查询的场景，该字段用于实现排序、比较和范围查询（如果密文列使用了支持排序的加密算法，那么无需再配置排序查询列）。

排序查询列类型（orderQueryDataType）

用于定义排序查询列类型，可根据算法进行配置，未配置时使用默认类型 VARCHAR (4000)。

明文列（plainColumn）

存储明文的列，用于在加密数据迁移过程中仍旧提供服务。在清洗数据结束后可以删除。

明文列类型（plainDataType）

用于定义明文列类型，应与逻辑列类型一致。

加密算法

指对数据加密过程中，所使用的具体加密方式，如 AES 及 MD5 等。

加密洗数（encrypting）

对数据库中未加密数据进行批量加密。

解密洗数（decrypting）

对数据库中已加密数据进行批量解密。

密钥管理

密钥的存放及管理方式。

特色能力 #

云端密钥管理

由于加密 key 以明文形式配置在 props 文件中存在安全隐患，SphereEx-DBPlusEngine 可通过增加 AWS 云端密钥管理功能来管理加密的 key。例如利用 AWS 的 secretKey 功能保存密钥，从而提升整个加密的安全性和便捷性。程序在初始化加密算法时，会同 AWS 建立连接，从而获取存储在 AWS 中的相关密钥，然后将密钥存储在算法中。在整个数据加密的过程中不涉及与云端的网络交互。

云端密钥管理

加密洗数

目前 SphereEx-DBPlusEngine 已经提供了加密的解决方案，对于新表新业务而言，直接使用加密规则配置即可，但是对于已有数据表，就需要将这些表中的明文字段进行洗数，转化为加密内容，SphereEx-DBPlusEngine 提供了完整的洗数方案。加密洗数通过 DistSQL 来触发洗数任务，程序收到洗数任务的请求之后，会根据当前的洗数规则以及加密规则创建洗数任务。洗数任务主要由两部分构成，一部分是查询任务，一部分是更新任务，查询任务负责查询用户的表数据并获取需要加密的明文字段，然后推送到通道中；更新任务则从通道中获取数据，并加密更新。整个任务的创建以及执行过程都会与治理中心进行交互，因此用户可以通过相关 DistSQL 来查询任务进度以及清理任务。

加密洗数

反洗数

对于已经是密文存储的系统，此时如果需要还原到明文存储的状态，可通过 SphereEx-DBPlusEngine 反向洗数的能力，构建出对应的明文列。在操作上与洗数类似，反洗数前提是需要提前创建好对应的明文列。

重洗数

对于更换加密算法或更换密钥等情况，可通过 SphereEx-DBPlusEngine 来重新洗数。重洗数方案会调用反洗数和洗数流程，需要用户手动操作，并且过程中会有明文数据存储。

在以上洗数过程中，SphereEx-DBPlusEngine 支持断点续传的能力，为用户提供更好地洗数体验。

适用场景 #

新上线业务的的数据加密场景

对于想要快速上线新业务，同时又需要完成安全部门的加密规定的场景，通过 SphereEx-DBPlusEngine 加密插件，用户可以快速完成数据的合规化加密，无需自行开发复杂的加密系统。同时 SphereEx-DBPlusEngine 加密锁具备的灵活性，也能够帮助客户避免加密场景变更带来的复杂重构和修改风险。

已上线业务的数据加密改造场景

对于已经上线的成熟业务，用户不仅需要考虑历史数据的清洗，还需要考虑新旧功能的切换。通过 SphereEx-DBPlusEngine 加密插件，用户可以方便地完成系统的加密改造，它还能够帮助用户安全快速地切换新旧功能，对存量数据进行加密清洗。用户无需改动任何业务逻辑和 SQL 就能够透明化地使用加解密功能。

使用前提 #

SphereEx-DBPlusEngine 尽可能让业务无感完成加密改造，现阶段有部分功能存在受限情况，因此在加密设计阶段，需调研业务系统是否使用了如大于、小于等操作，SphereEx-DBPlusEngine 具体使用限制见下文。

使用限制 #

需自行处理数据库中原始的存量数据；
模糊查询支持 %、_、contact 函数，暂不支持 escape；
加密字段无法支持查询不区分大小写功能；
加密字段无法支持计算操作，如：AVG、SUM 以及计算表达式；
已加密表上创建的视图，无法使用加解密功能；
Hive 要求加密表的存储格式为orc，暂不支持其他存储格式；
涉及洗数的表可以无主键，如果有主键或者唯一键，主键或者唯一键的数据类型要求为整型或者是字符串类型；
不支持对主键进行加密洗数；

注意事项 #

删除明文列是高危且不可逆操作，因此当完成数据加密改造后，建议先稳定运行数月后再执行该操作。

优势 #

自动化 & 透明化数据加密过程，用户无需关注加密中间实现细节。
内置多种加密算法，用户仅需简单配置即可使用。
提供加密算法 API 接口，用户可实现接口，从而使用自定义加密算法进行数据加密。
针对已上线业务，可实现明文数据与密文数据同步存储，并通过配置决定使用明文列还是密文列进行查询。可实现在不改变业务查询 SQL 前提下，已上线系统对加密前后数据进行安全、透明化迁移。

原理介绍 #

SphereEx-DBPlusEngine 通过对用户输入的 SQL 进行解析，并依据用户提供的加密规则对 SQL 进行改写，从而实现对原文数据进行加密，并将原文数据（可选）及密文数据同时存储到底层数据库。在用户查询数据时，它仅从数据库中取出密文数据，并对其解密，最终将解密后的原始数据返回给用户。

SphereEx-DBPlusEngine 自动化 & 透明化了数据加密过程，让用户无需关注数据加密的实现细节，像使用普通数据那样使用加密数据。此外，无论是已在线业务进行加密改造，还是新上线业务使用加密功能，SphereEx-DBPlusEngine 都可以提供一套相对完善的解决方案。

原理图

加密模块将用户发起的 SQL 进行拦截，并通过 SQL 语法解析器进行解析、理解 SQL 行为，再依据用户传入的加密规则，找出需要加密的字段和所使用的加解密算法对目标字段进行加解密处理后，再与底层数据库进行交互。SphereEx-DBPlusEngine 会将用户请求的明文进行加密后存储到底层数据库；并在用户查询时，将密文从数据库中取出进行解密后返回给终端用户。通过屏蔽对数据的加密处理，使用户无需感知解析 SQL、数据加密、数据解密的处理过程，就像在使用普通数据一样使用加密数据。

举例说明，假如数据库里有一张表叫做 t_user，这张表里实际有两个字段 pwd_plain，用于存放明文数据、pwd_cipher，用于存放密文数据、pwd_assisted_query，用于存放辅助查询数据，同时定义 logicColumn 为 pwd。那么，用户在编写 SQL 时应该面向 logicColumn 进行编写，即 INSERT INTO t_user SET pwd = '123'。SphereEx-DBPlusEngine 接收到该 SQL，通过用户提供的加密配置，发现 pwd 是 logicColumn，于是便对逻辑列及其对应的明文数据进行加密处理。 SphereEx-DBPlusEngine 将面向用户的逻辑列与面向底层数据库的明文列和密文列进行了列名以及数据的加密映射转换。如下图所示：

模型图

即依据用户提供的加密规则，将用户 SQL 与底层数据表结构割裂开来，使得用户的 SQL 编写不再依赖于真实的数据库表结构。而用户与底层数据库之间的衔接、映射、转换交由 SphereEx-DBPlusEngine 进行处理。

下方图片展示了使用加密模块进行增删改查时，其中的处理流程和转换逻辑，如下图所示。

加密流程图

加密算法解析 #

SphereEx-DBPlusEngine 提供了两种加解密的接口，即 EncryptAlgorithm 和 QueryAssistedEncryptAlgorithm。一方面，SphereEx-DBPlusEngine 为用户提供了内置的加解密实现类，用户只需进行配置即可使用；另一方面，为了满足用户不同场景的需求，我们还开放了相关加解密接口，用户可依据这两种类型的接口提供具体实现类。再进行简单配置，即可让 SphereEx-DBPlusEngine 调用用户自定义的加解密方案进行数据加密。

EncryptAlgorithm 该解决方案通过提供 encrypt(), decrypt() 两种方法对需要加密的数据进行加解密。在用户进行 INSERT, DELETE, UPDATE时，SphereEx-DBPlusEngine 会按照用户配置，对SQL进行解析、改写、路由，并调用 encrypt() 将数据加密后存储到数据库, 而在 SELECT 时，则调用 decrypt() 方法将从数据库中取出的加密数据进行逆向解密，最终将原始数据返回给用户。当前，SphereEx-DBPlusEngine 针对这种类型的加密解决方案提供了五种具体实现类，分别是 MD5(不可逆)，AES(可逆)，RC4(可逆)，SM3（不可逆）和 SM4（可逆），用户只需配置即可使用这五种内置的方案。
QueryAssistedEncryptAlgorithm

相比较于第一种加密方案，该方案更为安全和复杂。它的理念是：即使是相同的数据，如两个用户的密码相同，它们在数据库里存储的加密数据也应当是不一样的。这种理念更有利于保护用户信息，防止撞库成功。

它提供三种函数进行实现，分别是 encrypt(), decrypt(), queryAssistedEncrypt()。在 encrypt() 阶段，用户通过设置某个变动种子，例如时间戳。针对原始数据 + 变动种子组合的内容进行加密，就能保证即使原始数据相同，也因为有变动种子的存在，致使加密后的加密数据是不一样的。在 decrypt() 可依据之前规定的加密算法，利用种子数据进行解密。虽然这种方式确实可以增加数据的保密性，但是另一个问题却随之出现：相同的数据在数据库里存储的内容是不一样的，那么当用户按照这个加密列进行等值查询 (SELECT FROM table WHERE encryptedColumnn = ?) 时会发现无法将所有相同的原始数据查询出来。

为此，我们提出了辅助查询列的概念。该辅助查询列通过 queryAssistedEncrypt() 生成，与 decrypt() 不同的是，该方法通过对原始数据进行另一种方式的加密，但是针对原始数据相同的数据，这种加密方式产生的加密数据是一致的。将 queryAssistedEncrypt() 后的数据存储到数据中用于辅助查询真实数据。因此，数据库表中多出这一个辅助查询列。

由于 queryAssistedEncrypt() 和 encrypt() 产生不同加密数据进行存储，而 decrypt() 可逆，queryAssistedEncrypt() 不可逆。在查询原始数据的时候，我们会自动对 SQL 进行解析、改写、路由，利用辅助查询列进行WHERE条件的查询，却利用 decrypt() 对 encrypt() 加密后的数据进行解密，并将原始数据返回给用户。这一切都是对用户透明化的。当前，SphereEx-DBPlusEngine 针对这种类型的加密解决方案并没有提供具体实现类，却将该理念抽象成接口，提供给用户自行实现。SphereEx-DBPlusEngine 将调用用户提供的该方案的具体实现类进行数据加密。

加密配置 #

加密配置元素分为四部分：数据源配置，加密算法配置，加密表配置以及查询属性配置，其详情如下图所示：

规则配置图

数据源配置：指数据源配置。

加密算法配置：指使用什么加密算法进行加解密。目前 SphereEx-DBPlusEngine 内置了五种加解密算法：AES，MD5，RC4，SM3 和 SM4。

加密表配置：用于告诉 SphereEx-DBPlusEngine 数据表里哪个列用于存储密文数据（cipherColumn）、使用什么算法加解密（encryptorName）、哪个列用于存储辅助查询数据（assistedQueryColumn）、使用什么算法加解密（assistedQueryEncryptorName）、哪个列用于存储明文数据（plainColumn）以及用户想使用哪个列进行 SQL 编写（logicColumn）。

我们可以从加密模块存在的意义来理解。加密模块最终目的是希望屏蔽底层对数据的加密处理，也就是说我们不希望用户知道数据是如何被加解密的、如何将明文数据存储到 plainColumn，将密文数据存储到 cipherColumn。换句话说，我们不希望用户知道 plainColumn 和 cipherColumn 的存在和使用。所以，我们需要给用户提供一个概念意义上的列，这个列可以脱离底层数据库的真实列，它可以是数据库表里的一个真实列，也可以不是，从而使得用户可以随意改变底层数据库的 plainColumn 和 cipherColumn 的列名。或者删除 plainColumn，选择永远不再存储明文，只存储密文。只要用户的 SQL 面向这个逻辑列进行编写，并在加密规则里给出 logicColumn 和 plainColumn、cipherColumn 之间正确的映射关系即可。

查询属性的配置：当底层数据库表里同时存储了明文数据、密文数据后，该属性开关用于决定是直接查询数据库表里的明文数据进行返回，还是查询密文数据通过 SphereEx-DBPlusEngine 解密后返回。该属性开关支持字段级、表级别和全局规则级别配置，由 QUERY_WITH_CIPHER_COLUMN 进行配置，字段级别优先级最高。

使用指南 #

加密-新上线业务 #

新上线业务由于一切从零开始，不存在历史数据清洗问题，所以相对简单。选择合适的加密算法，如 AES 后，只需配置逻辑列（面向用户编写 SQL ）和密文列（数据表存密文数据）即可，逻辑列和密文列可以相同也可以不同。建议配置如下（YAML 格式展示）：

-!ENCRYPT
  encryptors:
    aes_encryptor:
      type: AES
      props:
        aes-key-value: 123456abc
  tables:
    t_user:
      columns:
        pwd:
          cipherColumn: pwd
          encryptorName: aes_encryptor

使用这套配置， SphereEx-DBPlusEngine 只需将 logicColumn 和 cipherColumn 进行转换，底层数据表不存储明文，只存储了密文，这也是安全审计部分的要求所在。如果用户希望将明文、密文一同存储到数据库，只需添加 plainColumn 配置即可。操作流程如下：

设计加密表，确定加密字段，可结合表结构、数据特点及应用情况综合考虑；
在 SphereEx-DBPlusEngine 创建逻辑库，注册存储节点（空）；
创建加密规则，可使用 SphereEx-Console 或 DistSQL 完成；
创建加密表，完成加密表构建；
根据业务实际情况，再选择保留或删除明文字段。

洗数-已上线业务改造 #

对于已上线的业务，需评估改造后 SQL 的支持度，另外存量数据洗数是必不可少的一个环节。

支持3种类型的洗数：

按表洗数：初次洗数的表，所有加密列可以一次性完成洗数
按列洗数：表执行过洗数之后，额外给其它某些列洗数
按衍生列洗数：列执行过洗数之后，额外新增衍生列，比如模糊查询列

确认加密范围根据合规需求，明确具体的加密数据范围。
确认加密算法选择并确认适合的加密算法。
梳理业务 SQL，确认支持情况确认当前业务中是否存在非等值的密态计算情况，如 like、>、< 等，若存在，则需考虑业务改造。
洗数完成以上内容后，对于已上线的项目，需要对存量数据进行洗数。该过程无需引用外部组件，SphereEx-DBPlusEngine 可完成洗数过程，该过程可在线进行。
启用密文列启用密文列之后，应用的请求将通过 SphereEx-DBPlusEngine 与密文字段产生交互，此时明文字段依然在写入数据。
下线明文列当系统经过足够长时间的使用和观察，可将明文字段进行下线。敏感字段只以密文形式存在系统中。

反洗数 #

新增对应明文字段
修改加密配置，在配置中增加明文字段，此时 queryWithCipher 为 true
开始反洗数
修改 queryWithCipher 为 false
删除加密规则
最后再情况加密字段

重洗数 #

启动自动换密钥流程
执行重洗数第二阶段
提交重洗数任务

操作指南 #

数据加密-新业务 #

设计加密表，确定加密字段，可结合表结构、数据特点及应用情况综合考虑；
在 SphereEx-DBPlusEngine 创建逻辑库，注册存储节点（空）；
创建加密规则，可使用 SphereEx-Console 或 DistSQL 完成；
创建加密表，完成加密表构建；
根据业务实际情况，再选择保留或删除明文字段。

加密洗数-成熟业务 #

设计加密表，确定加密字段，可结合表结构、数据特点及应用情况综合考虑；
在 SphereEx-DBPlusEngine 创建逻辑库，注册数据源（明文数据）；
使用 DistSQL 创建加密规则；
通过 ENCRYPT TABLE 命令开始洗数；
通过 ALTER ENCRYPT RULE 命令启用加密字段；
根据业务实际情况，再选择保留或删除明文字段。

配置示例 #

新上线业务加密配置-DistSQL #

业务场景分析：新上线业务由于一切从零开始，不存在历史数据清洗问题，所以相对简单。

解决方案说明：选择合适的加密算法，如 AES 后，只需配置逻辑列（面向用户编写 SQL ）和密文列（数据表存密文数据）即可，逻辑列和密文列可以相同也可以不同。

SphereEx-DBPlusEngine 只需将 logicColumn 和 cipherColumn，assistedQueryColumn 进行转换，底层数据表不存储明文，只存储了密文，这也是安全审计部分的要求所在。如果用户希望将明文、密文一同存储到数据库，只需添加 plainColumn配置即可。整体处理流程如下图所示：

整体处理流程图

环境说明 #

	实例	IP 地址	服务端口	主机名	备注
1	DBPlusEngine 1.2.0	192.168.xx.102	3307	dbplusengine
2	MySQL 5.7.26	192.168.xx.106	3306	ds_encryption

拓扑图 #

拓扑图

配置过程 #

准备数据源

在 MySQL 中创建名为 ds_encryption 的数据库。

# mysql -utest -p -h192.168.xx.106

mysql> CREATE DATABASE ds_encryption;
Query OK, 1 row affected (0.05 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| ds_encryption      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

在 SphereEx-DBPlusEngine 中创建名为 testdb 的逻辑库。

# mysql -uroot -p -P3307 -h127.0.0.1

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.85 sec)

mysql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

将数据源注册到 SphereEx-DBPlusEngine 中

mysql> USE testdb;
Database changed

mysql> REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://192.168.xx.103:3306/mysql_0?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="Test@123"
);
Query OK, 0 rows affected (0.81 sec)

在 SphereEx-DBPlusEngine 中创建加密规则

mysql> CREATE ENCRYPT RULE t_user (
  COLUMNS(
    (
      NAME = id_card_no,
      PLAIN = id_card_no_plain,
      CIPHER = id_card_no_cipher,
      ENCRYPT_ALGORITHM(
        TYPE(
          NAME = 'AES',
          PROPERTIES('aes-key-value' = '123456abc')
        )
      )
    ),
    (
      NAME = mobile,
      PLAIN = mobile_plain,
      CIPHER = mobile_cipher,
      ENCRYPT_ALGORITHM(
        TYPE(
          NAME = 'AES',
          PROPERTIES('aes-key-value' = '123456abc')
        )
      )
    )
  ),
  QUERY_WITH_CIPHER_COLUMN = true
);
Query OK, 0 rows affected (6.56 sec)

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: id_card_no
         logic_data_type:
           cipher_column: id_card_no_cipher
        cipher_data_type:
            plain_column: id_card_no_plain
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: true
*************************** 2. row ***************************
                   table: t_user
            logic_column: mobile
         logic_data_type:
           cipher_column: mobile_cipher
        cipher_data_type:
            plain_column: mobile_plain
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: true
2 rows in set (0.00 sec)

在以上配置中，对 t_user 表中 mobile 和 id_card_no 字段通过 AES 算法进行加密。

创建表并插入若干条测试记录

mysql> CREATE TABLE t_user (
    id INT(8), 
    mobile VARCHAR(50), 
    id_card_no VARCHAR(50)
);
Query OK, 0 rows affected (1.90 sec)

mysql> INSERT INTO t_user (id, mobile, id_card_no)
VALUES (1, 18236483857, 220605194709308170),
       (2, 15686689114, 360222198806088804),
       (3, 14523360225, 411601198601098107),
       (4, 18143924353, 540228199804231247),
       (5, 15523349333, 360924195311103360),
       (6, 13261527931, 513229195302236086),
       (7, 13921892133, 500108194806107214),
       (8, 15993370854, 451322194405305441),
       (9, 18044280924, 411329199808285772),
       (10, 13983621809, 430204195612042092);
Query OK, 10 rows affected (0.61 sec)

确认测试数据插入结果，同时执行 join 的操作进行验证。

mysql> SELECT * FROM t_user;
+------+-------------+--------------------+
| id   | mobile      | id_card_no         |
+------+-------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |
|    2 | 15686689114 | 360222198806088804 |
|    3 | 14523360225 | 411601198601098107 |
|    4 | 18143924353 | 540228199804231247 |
|    5 | 15523349333 | 360924195311103360 |
|    6 | 13261527931 | 513229195302236086 |
|    7 | 13921892133 | 500108194806107214 |
|    8 | 15993370854 | 451322194405305441 |
|    9 | 18044280924 | 411329199808285772 |
|   10 | 13983621809 | 430204195612042092 |
+------+-------------+--------------------+
10 rows in set (0.25 sec)

mysql> SELECT * FROM t_user WHERE mobile = '18236483857';
+------+-------------+--------------------+
| id   | mobile      | id_card_no         |
+------+-------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |
+------+-------------+--------------------+
1 row in set (0.50 sec)

mysql>
mysql> SELECT * FROM t_user WHERE mobile = '18236483857'
    -> AND id_card_no = '220605194709308170';
+------+-------------+--------------------+
| id   | mobile      | id_card_no         |
+------+-------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |
+------+-------------+--------------------+
1 row in set (0.26 sec)

在 MySQL 中进行查看，可见数据库中 mobile 和 id_card_no 字段均存储的是密文数据。

mysql -utest -p -h192.168.xx.103

mysql> USE ds_encryption;
Database changed

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_ds_encryption |
+-------------------------+
| t_user                  |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t_user;
+------+--------------------------+----------------------------------------------+
| id   | mobile_cipher            | id_card_no_cipher                            |
+------+--------------------------+----------------------------------------------+
|    1 | p31Pkl9nIunYdH+AngyNUA== | pQv0JEkM94QzktJdM8UMg/uLrU71G6n6DALdPp9w6L0= |
|    2 | CV8+uYRaWOzcTQnQX3RcwA== | dCF7k4haK0aIV/d7dtwgzIb4lIFlJ913hrPim1+J278= |
|    3 | jnfu7o44KgN/PV1zhiu7jw== | 8iulp3+XTSv2XHGUUHKV0UsLuFx7yEpQVT+47EFfg94= |
|    4 | ZJDrTv/XIjdqdG1yp0t95w== | iqU6myMGfgI/XnxCtjhbMrwIauriWu8crxPS6BH2pMk= |
|    5 | FnQMYGnFJaiWmTHeNYzbFA== | KAPrCXoo1svMt5NWe0UaKYZIl1rSEVddHbBJO1jPIqw= |
|    6 | lv2ECfTCgQQksvdPp6k3Ug== | BBBPAuwU+iJluI9d9TA+H81BPnVXBaly1BE3EplN4e8= |
|    7 | z46vpnHCFTkIF2EtntxpHQ== | Bc39nPtyz1ji9Rc8k4f7G9CKfPew23mKFwp8guK7ybg= |
|    8 | p/IJdGcCikhpCu5gVZj4jg== | nnv/kS1i7uHXKncUOuLzE8OWM0nGlcGkLokT2dltSaQ= |
|    9 | NvPcQv4w3EqD77+VAX0KCA== | +yeo5LWKNWcekFqYawCKjsctAZqe104DrI7AeZdR/Uk= |
|   10 | xOyg9E0X9lhy9mUx0QyL0A== | U7P1CMcxn6VPHYHPgTAtjHEbb6N6vhGOpdJtVjAdHlA= |
+------+--------------------------+----------------------------------------------+
10 rows in set (0.00 sec)

删除明文字段

（注：删除明文列是高危且不可逆操作，建议先稳定运行数月后再执行该操作。）

--首先在数据库中删除铭文字段
--然后在 Proxy 中刷新元数据信息即可
mysql> REFRESH table METADATA;

以上为使用 DistSQL 的方式完成了加密表的配置，下面将演示通过 Console 来完成加密的配置。

新上线业务加密配置-Console #

环境说明 #

	实例	IP 地址	服务端口	主机名
1	DBPlusEngine 1.2.0	192.168.xx.102	3307	dbplusengine
2	Console 1.1.0	192.168.xx.101	8089	console
3	MySQL 5.7.26	192.168.xx.106	3306	ds_encryption

拓扑图 #

拓扑图

配置过程 #

登陆 SphereEx-Console，在“集群”-“对象”页中，点击“新建表”按钮。

根据参考示例，输入如下建表语句，点击“确定”。

CREATE TABLE t_encrypt(
    id INT(8), 
    mobile VARCHAR(50), 
    id_card_no VARCHAR(50)
);

添加加密插件

选择创建的数据表：t_encrypt，点击“添加加密”–>选择“常规”加密–>点击“添加加密插件”，选择“加密算法”，选择 “需要加密的列”，勾选“存储明文”，点击“添加”–>点击“确定”。

插入测试数据

# mysql -uroot -proot -P3307 -h192.168.xx.102

mysql>use logical001;
mysql> INSERT INTO t_encrypt (id, mobile_plain, id_card_no)
    -> VALUES (1, 18236483857, 220605194709308170),
    ->        (2, 15686689114, 360222198806088804),
    ->        (3, 13983621809, 430204195612042092);
Query OK, 3 rows affected (0.02 sec)

确认数据加密结果

（说明：如果需要 SphereEx-DBPlusEngine 中显示明文，不勾选 “是否使用加密列查询”。）

mysql -uroot -proot -P3307 -h192.168.xx.102

mysql>use logical001;
mysql> SELECT * FROM t_encrypt;
+------+----------------------------------+--------------------+
| id   | mobile                           | id_card_no         |
+------+----------------------------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |
|    2 | 15686689114 | 360222198806088804 |
|    3 | 13983621809 | 430204195612042092 |
+------+----------------------------------+--------------------+
3 rows in set (0.00 sec)

mysql -uroot -proot -P3306 -h192.168.xx.106

mysql> use t_encrypt_db;
mysql> select * from t_encrypt;
+------+----------------------------------+--------------+--------------------+
| id   | mobile_cipher                    | mobile_plain | id_card_no         |
+------+----------------------------------+--------------+--------------------+
|    1 | 6f655ea3b73e32d54ae2d34d672c0c55 | 18236483857  | 220605194709308170 |
|    2 | a2193d0e8de4c2c4f166448e22ba97ae | 15686689114  | 360222198806088804 |
|    3 | 3cf1fd442630071ca664bbd5e683f7f6 | 13983621809  | 430204195612042092 |
+------+----------------------------------+--------------+--------------------+

以上是 Console 配置加密表的过程，操作相对更简化。

已上线业务洗数 #

按表洗数 #

对于已经上线运行一段时间的业务系统，数据库里会存在大量明文历史数据，本示例将演示如何对历史项目中的数据进行洗数。

准备 MySQL 环境（明文），模拟已上线数据库

mysql> CREATE DATABASE ds_mysql;
Query OK, 1 row affected (0.00 sec)

mysql> USE ds_mysql;
Database changed

mysql> CREATE TABLE t_user (id int NOT NULL PRIMARY KEY, username varchar(50), password varchar(50), email varchar(50));
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO t_user (id, username, password, email) VALUES (1,'a','aa','a@t.c'),(2,'b','bb','b@t.c'),(3,'c','cc','c@t.c');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+
| id | username | password | email |
+----+----------+----------+-------+
|  1 | a        | aa       | a@t.c |
|  2 | b        | bb       | b@t.c |
|  3 | c        | cc       | c@t.c |
+----+----------+----------+-------+
3 rows in set (0.02 sec)

此时，已经完成了 MySQL 环境的准备，在 ds_mysql 数据库中存在一张名为 t_user 的明文表。

在 SphereEx-DBPlusEngine 中创建明为 testdb 的逻辑库，并将 MySQL 数据源注册到其中

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.00 sec)

mysql> USE testdb;
Database changed

mysql> REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://192.168.xx.103:3306/ds_mysql?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="Test@123"
);
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW STORAGE UNITS\G
*************************** 1. row ***************************
                           name: ds_0
                           type: MySQL
                           host: 192.168.xx.103
                           port: 3306
                             db: ds_mysql
connection_timeout_milliseconds: 30000
      idle_timeout_milliseconds: 60000
      max_lifetime_milliseconds: 2100000
                  max_pool_size: 50
                  min_pool_size: 1
                      read_only: false
               other_attributes: {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"keepaliveTime":0,"leakDetectionThreshold":0,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false}
1 row in set (0.00 sec)

mysql> LOAD SINGLE TABLE ds_0.t_user;
Query OK, 0 rows affected (3.87 sec)

mysql> SHOW TABLES;
+------------------+------------+
| Tables_in_testdb | Table_type |
+------------------+------------+
| t_user           | BASE TABLE |
+------------------+------------+
1 row in set (0.01 sec)

通过如上输出可确认，逻辑库中已经识别到明文表 t_user。

洗数工作前的准备

在开始洗数前，需要在 MySQL 中增加对应的密文字段。这里将增加 1 个密文字段，对应 username。

mysql> ALTER TABLE t_user ADD COLUMN username_cipher VARCHAR(50);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_user;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | NO   | PRI | NULL    |       |
| username        | varchar(50) | YES  |     | NULL    |       |
| password        | varchar(50) | YES  |     | NULL    |       |
| email           | varchar(50) | YES  |     | NULL    |       |
| username_cipher | varchar(50) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+-----------------+
| id | username | password | email | username_cipher |
+----+----------+----------+-------+-----------------+
|  1 | a        | aa       | a@t.c | NULL            |
|  2 | b        | bb       | b@t.c | NULL            |
|  3 | c        | cc       | c@t.c | NULL            |
+----+----------+----------+-------+-----------------+
3 rows in set (0.02 sec)

当前密文字段内容为空，稍后将会通过洗数操作在线转换密文数据。

在 SphereEx-DBPlusEngine 中配置加密规则

CREATE ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=false)
));
Query OK, 0 rows affected (0.05 sec)


-- NAME：为业务中字段名称，基于应用免改造的出发点，可设置与明文字段名称一致
-- PLAIN：明文字段名称
-- CIPHER：密文字段名称
-- ENCRYPT_ALGORITHM：加密算法相关配置
-- QUERY_WITH_CIPHER_COLUMN：是否启用密文字段，当前密文字段为空，因此需要设置为 false

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: true
1 row in set (0.05 sec)

mysql> PREVIEW SELECT * FROM t_user;
+------------------+----------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                         |
+------------------+----------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_user.`id`, t_user.`username` AS `username`, t_user.`password`, t_user.`email` FROM t_user |
+------------------+----------------------------------------------------------------------------------------------------+
1 row in set (0.99 sec)

通过 PREVIEW 结果来看，当前是通过明文字段来响应请求，与预期一致。

开始洗数在配置好加密字段及加密规则后，可开始洗数。

mysql> ENCRYPT TABLE t_user;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW ENCRYPTING LIST;
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| id                                         | tables | columns  | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789 | t_user | username | 1              | true   | 2023-09-25 17:21:27 | NULL      |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
1 row in set (0.05 sec)

mysql> SHOW ENCRYPTING STATUS j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | ds_0        | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.03 sec)

如上所示，加密完成进度为 100%，可登陆 MySQL 查看真实表的数据。此时 MySQL 中的数据如下：

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+--------------------------+
| id | username | password | email | username_cipher          |
+----+----------+----------+-------+--------------------------+
|  1 | a        | aa       | a@t.c | T25iRZWea6YFzmT4LqH9ag== |
|  2 | b        | bb       | b@t.c | fRwca8JpNUTHo0xBYTfWuw== |
|  3 | c        | cc       | c@t.c | GnlS+xZTncXuH5D7JLi4CA== |
+----+----------+----------+-------+--------------------------+
3 rows in set (0.01 sec)

表中明文密文同时存在，此时为业务提供响应的是明文字段。

启用密文字段

当明文密文并行运行一段时间后，可结合实际情况启用密文字段。

ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true)
));
Query OK, 0 rows affected (0.02 sec)


mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: false
1 row in set (0.01 sec)

mysql> PREVIEW SELECT * FROM t_user;
+------------------+-----------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                |
+------------------+-----------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_user.`id`, t_user.`username_cipher` AS `username`, t_user.`password`, t_user.`email` FROM t_user |
+------------------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

此时，通过 PREVIEW 输出所示，业务已开始使用了密文字段。洗数操作完成，明文字段可根据实际的使用需求进行变更调整。

完成洗数

mysql> COMMIT ENCRYPTING j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789;
Query OK, 0 rows affected (0.76 sec)

按列洗数 #

洗数完成后如果后面需要对额外的列进行洗数，也可以实现对指定列的洗数。假如这次需要对password字段洗数，操作步骤如下：

在 MySQL 新增列：

mysql> ALTER TABLE t_user ADD COLUMN password_cipher VARCHAR(50);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_user;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | NO   | PRI | NULL    |       |
| username        | varchar(50) | YES  |     | NULL    |       |
| password        | varchar(50) | YES  |     | NULL    |       |
| email           | varchar(50) | YES  |     | NULL    |       |
| username_cipher | varchar(50) | YES  |     | NULL    |       |
| password_cipher | varchar(50) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+--------------------------+-----------------+
| id | username | password | email | username_cipher          | password_cipher |
+----+----------+----------+-------+--------------------------+-----------------+
|  1 | a        | aa       | a@t.c | T25iRZWea6YFzmT4LqH9ag== | NULL            |
|  2 | b        | bb       | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | NULL            |
|  3 | c        | cc       | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | NULL            |
+----+----------+----------+-------+--------------------------+-----------------+
3 rows in set (0.01 sec)

修改加密规则为查询明文列（假如之前已经删除了明文列，那需要先执行反洗数）：

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=false)
, (NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=false)
));

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: true
*************************** 2. row ***************************
                   table: t_user
            logic_column: password
         logic_data_type:
           cipher_column: password_cipher
        cipher_data_type:
            plain_column: password
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: true
2 rows in set (0.02 sec)

开始洗数（password 列）：

mysql> ENCRYPT TABLE t_user (password);
Query OK, 0 rows affected (0.27 sec)

mysql> SHOW ENCRYPTING LIST;
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| id                                         | tables | columns  | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789 | t_user | password | 1              | true   | 2023-09-25 17:36:41 | NULL      |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
1 row in set (0.03 sec)

mysql> SHOW ENCRYPTING STATUS j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | ds_0        | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.03 sec)

加密进度达到 100% 之后，在 MySQL 验证：

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+--------------------------+--------------------------+
| id | username | password | email | username_cipher          | password_cipher          |
+----+----------+----------+-------+--------------------------+--------------------------+
|  1 | a        | aa       | a@t.c | T25iRZWea6YFzmT4LqH9ag== | gg9Qid/hjjHgNk8TgeBK5g== |
|  2 | b        | bb       | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 7i7GQMpZxkWXoXB01NJryQ== |
|  3 | c        | cc       | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | TTDgqe6nMJhnqmy2soADnw== |
+----+----------+----------+-------+--------------------------+--------------------------+
3 rows in set (0.01 sec)

改为查询加密列并验证：

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true),
(NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true)
));

mysql> PREVIEW SELECT * FROM t_user;
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                     |
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_user.`id`, t_user.`username_cipher` AS `username`, t_user.`password_cipher` AS `password`, t_user.`email` FROM t_user |
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+
| id | username | password | email |
+----+----------+----------+-------+
|  1 | a        | aa       | a@t.c |
|  2 | b        | bb       | b@t.c |
|  3 | c        | cc       | c@t.c |
+----+----------+----------+-------+
3 rows in set (0.02 sec)

完成洗数：

mysql> COMMIT ENCRYPTING j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789;
Query OK, 0 rows affected (0.69 sec)

按衍生列洗数 #

当某个列完成洗数之后，光做加密在功能不够用的时候，比如需要模糊查询，也可以实现。假设username需要模糊查询，操作步骤如下：

在 MySQL 新增列：

mysql> ALTER TABLE t_user ADD COLUMN username_like VARCHAR(50);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_user;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | NO   | PRI | NULL    |       |
| username        | varchar(50) | YES  |     | NULL    |       |
| password        | varchar(50) | YES  |     | NULL    |       |
| email           | varchar(50) | YES  |     | NULL    |       |
| username_cipher | varchar(50) | YES  |     | NULL    |       |
| password_cipher | varchar(50) | YES  |     | NULL    |       |
| username_like   | varchar(50) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
| id | username | password | email | username_cipher          | password_cipher          | username_like |
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
|  1 | a        | aa       | a@t.c | T25iRZWea6YFzmT4LqH9ag== | gg9Qid/hjjHgNk8TgeBK5g== | NULL          |
|  2 | b        | bb       | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 7i7GQMpZxkWXoXB01NJryQ== | NULL          |
|  3 | c        | cc       | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | TTDgqe6nMJhnqmy2soADnw== | NULL          |
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
3 rows in set (0.01 sec)

修改加密规则为查询明文列（假如之前已经删除了明文列，那需要先执行反洗数），并加上LIKE_QUERY_COLUMN配置：

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, LIKE_QUERY_COLUMN=username_like, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE')), QUERY_WITH_CIPHER_COLUMN=false)
, (NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=false)
));

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column: username_like
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type: CHAR_DIGEST_LIKE
        like_query_props:
        query_with_plain: true
*************************** 2. row ***************************
                   table: t_user
            logic_column: password
         logic_data_type:
           cipher_column: password_cipher
        cipher_data_type:
            plain_column: password
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: true
2 rows in set (0.02 sec)

开始洗数：

mysql> ENCRYPT TABLE t_user (username(LIKE_QUERY));
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW ENCRYPTING LIST;
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| id                                         | tables | columns  | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789 | t_user | username | 1              | true   | 2023-09-25 17:47:26 | NULL      |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> SHOW ENCRYPTING STATUS j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | ds_0        | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.03 sec)

加密进度达到 100% 之后，在 MySQL 验证：

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
| id | username | password | email | username_cipher          | password_cipher          | username_like |
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
|  1 | a        | aa       | a@t.c | T25iRZWea6YFzmT4LqH9ag== | gg9Qid/hjjHgNk8TgeBK5g== | `             |
|  2 | b        | bb       | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 7i7GQMpZxkWXoXB01NJryQ== | a             |
|  3 | c        | cc       | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | TTDgqe6nMJhnqmy2soADnw== | d             |
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
3 rows in set (0.00 sec)

改为查询加密列并验证：

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, LIKE_QUERY_COLUMN=username_like, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE')), QUERY_WITH_CIPHER_COLUMN=true)
, (NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true)
));

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column: username_like
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type: CHAR_DIGEST_LIKE
        like_query_props:
        query_with_plain: false
*************************** 2. row ***************************
                   table: t_user
            logic_column: password
         logic_data_type:
           cipher_column: password_cipher
        cipher_data_type:
            plain_column: password
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: false
2 rows in set (0.01 sec)

mysql> PREVIEW SELECT * FROM t_user;
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                     |
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_user.`id`, t_user.`username_cipher` AS `username`, t_user.`password_cipher` AS `password`, t_user.`email` FROM t_user |
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+
| id | username | password | email |
+----+----------+----------+-------+
|  1 | a        | aa       | a@t.c |
|  2 | b        | bb       | b@t.c |
|  3 | c        | cc       | c@t.c |
+----+----------+----------+-------+
3 rows in set (0.03 sec)

模糊查询 #

SphereEx-DBPlusEngine 支持加密列 like 查询，以下为验证步骤。

配置 MySQL 配置文件

为了避免 MySQL 存储中文乱码导致 LIKE 功能错乱，需要提前修改 my.cnf，确保进行了如下配置：

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
port=3306
character-set-server=utf8mb4
character-set-filesystem=utf8mb4
collation-server=utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'

创建逻辑库，并添加资源

CREATE DATABASE encrypt_db;

USE encrypt_db;

REGISTER STORAGE UNIT ds_0 (
    HOST="127.0.0.1",
    PORT=3306,
    DB="demo_ds_0",
    USER="root",
    PASSWORD="123456"
), ds_1 (
    HOST="127.0.0.1",
    PORT=3306,
    DB="demo_ds_1",
    USER="root",
    PASSWORD="123456"
);

创建加密规则

CREATE ENCRYPT KEY MANAGER aws_kms_key_manager (TYPE(NAME='SphereEx:AWS_KMS', PROPERTIES('access-key'='AKIAXRZPPZNGXS6VJUGY','secret-key'='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','aws-region'='ap-northeast-1','secret-name'='dbplus-engine/encrypt-key')));

CREATE ENCRYPT RULE t_user (
        COLUMNS(
                (
                        NAME=user_name,PLAIN=user_name_plain,CIPHER=user_name_cipher,LIKE_QUERY_COLUMN=user_name_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                ),
                (
                        NAME=password, CIPHER =password_cipher,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
                ),
                (
                        NAME=email, CIPHER =email_cipher,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
                ),
                (
                        NAME=telephone,PLAIN=telephone_plain,CIPHER=telephone_cipher,LIKE_QUERY_COLUMN=telephone_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                )
        ), QUERY_WITH_CIPHER_COLUMN = TRUE
), t_merchant (
        COLUMNS(
                (
                        NAME=business_code,PLAIN=business_code_plain,CIPHER=business_code_cipher,LIKE_QUERY_COLUMN=business_code_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                ),
                (
                        NAME=telephone,PLAIN=telephone_plain,CIPHER=telephone_cipher,LIKE_QUERY_COLUMN=telephone_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                )
        ), QUERY_WITH_CIPHER_COLUMN = TRUE
);

创建逻辑库，并创建测试表，插入若干条测试记录

USE encrypt_db;

DROP TABLE IF EXISTS t_merchant;


CREATE TABLE t_merchant (
  merchant_id INT PRIMARY KEY, 
  country_id SMALLINT NOT NULL, 
  merchant_name VARCHAR(50) NOT NULL, 
  business_code VARCHAR(50) NOT NULL, 
  telephone CHAR(50) NOT NULL, 
  creation_date DATE NOT NULL);

INSERT INTO t_merchant ......

CREATE TABLE t_user (
  user_id INT PRIMARY KEY, 
  user_name VARCHAR(50) NOT NULL, 
  password VARCHAR(50) NOT NULL, 
  email VARCHAR(50) NOT NULL, 
  telephone CHAR(50) NOT NULL, 
  creation_date DATE NOT NULL);

INSERT INTO t_user ......

测试数字 LIKE 查询

-- 测试 % 前缀匹配
SELECT * FROM t_merchant WHERE business_code LIKE '%1';
+-------------+------------+---------------+---------------+-------------+---------------+
| merchant_id | country_id | merchant_name | business_code | telephone   | creation_date |
+-------------+------------+---------------+---------------+-------------+---------------+
|           1 |         86 | test101       | 86000001      | 86100000001 | 2017-08-08    |
|          11 |          1 | test111       | 01000011      | 01100000011 | 2017-08-08    |
+-------------+------------+---------------+---------------+-------------+---------------+
2 rows in set (0.01 sec)

-- 测试 % 后缀匹配
SELECT * FROM t_merchant WHERE business_code LIKE '86%';
+-------------+------------+---------------+---------------+-------------+---------------+
| merchant_id | country_id | merchant_name | business_code | telephone   | creation_date |
+-------------+------------+---------------+---------------+-------------+---------------+
|           1 |         86 | test101       | 86000001      | 86100000001 | 2017-08-08    |
|           2 |         86 | test102       | 86000002      | 86100000002 | 2017-08-08    |
|           3 |         86 | test103       | 86000003      | 86100000003 | 2017-08-08    |
|           4 |         86 | test104       | 86000004      | 86100000004 | 2017-08-08    |
|           5 |         86 | test105       | 86000005      | 86100000005 | 2017-08-08    |
|           6 |         86 | test106       | 86000006      | 86100000006 | 2017-08-08    |
|           7 |         86 | test107       | 86000007      | 86100000007 | 2017-08-08    |
|           8 |         86 | test108       | 86000008      | 86100000008 | 2017-08-08    |
|           9 |         86 | test109       | 86000009      | 86100000009 | 2017-08-08    |
|          10 |         86 | test110       | 86000010      | 86100000010 | 2017-08-08    |
+-------------+------------+---------------+---------------+-------------+---------------+
10 rows in set (0.01 sec)

测试字母 LIKE 查询

-- 测试 % 前缀匹配
SELECT * FROM t_user WHERE user_name LIKE '%san';
+---------+-----------+----------+--------------------+-------------+---------------+
| user_id | user_name | password | email              | telephone   | creation_date |
+---------+-----------+----------+--------------------+-------------+---------------+
|      10 | zhangsan  | 111111   | zhangsan@gmail.com | 12345678900 | 2017-08-08    |
|      19 | zhousan   | 123456   | zhousan@gmail.com  | 12345678909 | 2017-08-08    |
+---------+-----------+----------+--------------------+-------------+---------------+
2 rows in set (0.01 sec)

-- 测试 % 后缀匹配
SELECT * FROM t_user WHERE user_name LIKE 'j%';
+---------+-----------+----------+------------------+-------------+---------------+
| user_id | user_name | password | email            | telephone   | creation_date |
+---------+-----------+----------+------------------+-------------+---------------+
|      22 | jerry     | 456789   | jerry@gmail.com  | 12345678912 | 2017-08-08    |
|      23 | james     | 567890   | james@gmail.com  | 12345678913 | 2017-08-08    |
|      27 | jack      | 222333   | jack@gmail.com   | 12345678917 | 2017-08-08    |
|      28 | jordan    | 333444   | jordan@gmail.com | 12345678918 | 2017-08-08    |
|      29 | julie     | 444555   | julie@gmail.com  | 12345678919 | 2017-08-08    |
+---------+-----------+----------+------------------+-------------+---------------+
5 rows in set (0.01 sec)

测试汉字 LIKE 查询

- 测试 % 前缀匹配
SELECT * FROM t_user WHERE user_name LIKE '%靖';
+---------+-----------+----------+-------------------+-------------+---------------+
| user_id | user_name | password | email             | telephone   | creation_date |
+---------+-----------+----------+-------------------+-------------+---------------+
|      34 | 郭靖      | 123123   | guojing@gmail.com | 18012512345 | 2017-08-08    |
+---------+-----------+----------+-------------------+-------------+---------------+
1 row in set (0.01 sec)

-- 测试 % 后缀匹配
SELECT * FROM t_user WHERE user_name LIKE '王%';
+---------+-----------+----------+--------------------------+-------------+---------------+
| user_id | user_name | password | email                    | telephone   | creation_date |
+---------+-----------+----------+--------------------------+-------------+---------------+
|      35 | 王之涣    | 234234   | wangzhihuan@gmail.com | 18012512345 | 2017-08-08    |
|      36 | 王安石    | 345345   | jeff@gmail.com           | 18012512345 | 2017-08-08    |
|      37 | 王羲之    | 345345   | wangxizhi@gmail.com      | 18012512345 | 2017-08-08    |
|      38 | 王莽      | 456456   | wangmang@gmail.com       | 18012512345 | 2017-08-08    |
|      39 | 王勃      | 567567   | wangbo@gmail.com         | 18012512345 | 2017-08-08    |
+---------+-----------+----------+--------------------------+-------------+---------------+
5 rows in set (0.01 sec)

反洗数 #

假设加密或洗数并切换到查询密文列稳定运行了一段时间，明文列已删除，示例操作：

-- 在加密规则删除明文列：

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, CIPHER=username_cipher, LIKE_QUERY_COLUMN=username_like, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE')), QUERY_WITH_CIPHER_COLUMN=true)
, (NAME=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true)
));

-- 在 MySQL 删除明文列：

mysql> ALTER TABLE t_user DROP COLUMN username;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_user DROP COLUMN password;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果后面需要明文列，那可以通过反洗数来实现。

环境准备

还是前面洗数的环境。

在 MySQL 新增明文列：

mysql> ALTER TABLE t_user ADD COLUMN username VARCHAR(50);
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_user ADD COLUMN password VARCHAR(50);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_user;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | NO   | PRI | NULL    |       |
| email           | varchar(50) | YES  |     | NULL    |       |
| username_cipher | varchar(50) | YES  |     | NULL    |       |
| password_cipher | varchar(50) | YES  |     | NULL    |       |
| username_like   | varchar(50) | YES  |     | NULL    |       |
| username        | varchar(50) | YES  |     | NULL    |       |
| password        | varchar(50) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
7 rows in set (0.02 sec)

mysql> SELECT * FROM t_user;
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
| id | email | username_cipher          | password_cipher          | username_like | username | password |
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
|  1 | a@t.c | T25iRZWea6YFzmT4LqH9ag== | gg9Qid/hjjHgNk8TgeBK5g== | `             | NULL     | NULL     |
|  2 | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 7i7GQMpZxkWXoXB01NJryQ== | a             | NULL     | NULL     |
|  3 | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | TTDgqe6nMJhnqmy2soADnw== | d             | NULL     | NULL     |
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
3 rows in set (0.01 sec)

修改加密规则，加上明文列配置：

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, LIKE_QUERY_COLUMN=username_like, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE')), QUERY_WITH_CIPHER_COLUMN=true)
, (NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true)
));

mysql> SELECT * FROM t_user;
+----+-------+----------+----------+
| id | email | username | password |
+----+-------+----------+----------+
|  1 | a@t.c | a        | aa       |
|  2 | b@t.c | b        | bb       |
|  3 | c@t.c | c        | cc       |
+----+-------+----------+----------+
3 rows in set (0.04 sec)

创建反洗数配置

mysql> CREATE DECRYPTING PROCESS CONFIGURATION(
READ(
  WORKER_THREAD=20,
  BATCH_SIZE=1000,
  SHARDING_SIZE=1000000
),
WRITE(
  WORKER_THREAD=20,
  BATCH_SIZE=1000
),
STREAM_CHANNEL(TYPE(NAME="MEMORY", PROPERTIES("block-queue-size"=1000)))
);

mysql> SHOW DECRYPTING PROCESS CONFIGURATION;

开始反洗数

-- 指定任意多个反洗数字段，即按列反洗数。不指定的话就反洗数所有加密字段，即按表反洗数。
mysql> DECRYPT table t_user (username, password);
Query OK, 0 rows affected (1.18 sec)

mysql> SHOW DECRYPTING LIST;
+--------------------------------------------+--------+-------------------+----------------+--------+---------------------+-----------+
| id                                         | tables | columns           | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+--------+-------------------+----------------+--------+---------------------+-----------+
| j5302p0000e2af8bea809d29f6ab4b3ac4c99b5789 | t_user | username,password | 1              | true   | 2023-09-25 18:20:24 | NULL      |
+--------------------------------------------+--------+-------------------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> SHOW DECRYPTING STATUS j5302p0000e2af8bea809d29f6ab4b3ac4c99b5789;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | testdb      | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.01 sec)

在 MySQL 数据库中验证数据

反洗数进度达到 100% 之后，去 MySQL 查询验证：

mysql> SELECT * FROM t_user;
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
| id | email | username_cipher          | password_cipher          | username_like | username | password |
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
|  1 | a@t.c | T25iRZWea6YFzmT4LqH9ag== | gg9Qid/hjjHgNk8TgeBK5g== | `             | a        | aa       |
|  2 | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 7i7GQMpZxkWXoXB01NJryQ== | a             | b        | bb       |
|  3 | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | TTDgqe6nMJhnqmy2soADnw== | d             | c        | cc       |
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
3 rows in set (0.00 sec)

完成反洗数

mysql> COMMIT DECRYPTING j5302p0000e2af8bea809d29f6ab4b3ac4c99b5789;
Query OK, 0 rows affected (0.60 sec)

重洗数 #

假设需要对 password 列换密钥，可以通过重洗数来实现。

复用之前洗数反洗数的环境，操作步骤如下：

开始重洗数

mysql> REENCRYPT TABLE WITH RULE t_user (COLUMNS((NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='445533ccc'))))));
Query OK, 0 rows affected (0.46 sec)

mysql> SHOW REENCRYPTING LIST;
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| id                                         | tables | columns  | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| j5402p0000e2af8bea809d29f6ab4b3ac4c99b5789 | t_user | password | 1              | true   | 2023-09-25 20:06:05 | NULL      |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
1 row in set (0.15 sec)

mysql> SHOW REENCRYPTING STATUS j5402p0000e2af8bea809d29f6ab4b3ac4c99b5789;
+------+-------------+---------+--------+---------------------+---------------+
| item | data_source | status  | active | finished_percentage | error_message |
+------+-------------+---------+--------+---------------------+---------------+
| 0    | testdb      | SUSPEND | true   | 50                  |               |
+------+-------------+---------+--------+---------------------+---------------+
1 row in set (0.07 sec)

状态达到 SUSPEND 后开启重洗数第二阶段

mysql> START REENCRYPTING j5402p0000e2af8bea809d29f6ab4b3ac4c99b5789;
Query OK, 0 rows affected (6.22 sec)

mysql> SHOW REENCRYPTING STATUS j5402p0000e2af8bea809d29f6ab4b3ac4c99b5789;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | testdb      | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.01 sec)

进度达到 100% 后在 MySQL 验证数据

mysql> SELECT * FROM t_user;
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
| id | email | username_cipher          | password_cipher          | username_like | username | password |
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
|  1 | a@t.c | T25iRZWea6YFzmT4LqH9ag== | 3rETONuGGgjTmUN5JiGjyg== | `             | a        | aa       |
|  2 | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 43fcuOqglZ5QzJIQdq6qew== | a             | b        | bb       |
|  3 | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | T3YCxMH71NfzFQRXXk0osQ== | d             | c        | cc       |
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
3 rows in set (0.01 sec)

状态达到 FINISHED 后验证规则及数据

查看当前规则

-- 发现密钥已经替换，并且使用加密列查询
mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: password
         logic_data_type:
           cipher_column: password_cipher
        cipher_data_type:
            plain_column: password
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=445533ccc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: false
*************************** 2. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column: username_like
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type: CHAR_DIGEST_LIKE
        like_query_props:
        query_with_plain: false
2 rows in set (0.06 sec)

mysql> SELECT * FROM t_user;
+----+-------+----------+----------+
| id | email | username | password |
+----+-------+----------+----------+
|  1 | a@t.c | a        | aa       |
|  2 | b@t.c | b        | bb       |
|  3 | c@t.c | c        | cc       |
+----+-------+----------+----------+
3 rows in set (0.17 sec)

提交重洗数任务

mysql> COMMIT REENCRYPTING j5402p0000e2af8bea809d29f6ab4b3ac4c99b5789;

加密场景使用示例 #

这里使用忽略了 Proxy 和逻辑库的配置。

构造数据 #

CREATE TABLE t_data_type (
    id INT PRIMARY KEY,
    varchar_data VARCHAR(100),
    int_data INT,
    decimal_data DECIMAL (10, 2),
    longtext_data  longtext
);


INSERT INTO t_data_type (id, VARCHAR_data, INT_data, DECIMAL_data,longtext_data) VALUES
    (1, 'John', 25, 11.11,'john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com'),
    (2, 'Alice', 30, 12.12,'alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com'),
    (3, 'Bob', 28, 13.13,'bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com'),
    (4, 'Emily', 21, 14.14,'emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com'),
    (5, 'Michael', 35, 15.15,'michael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.com'),
    (6, 'Sarah', 19,15.15, 'sarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.com'),
    (7, 'David', 27,15.15, 'david@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.com'),
    (8, 'Olivia', 23, 15.15,'olivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.com'),
    (9, 'Daniel', 32,15.15, 'daniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.com'),
    (10, 'Sophia', 26,15.15, 'sophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.com'),
    (11, 'Matthew', 29, 15.15,'matthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.com'),
    (12, 'Emma', 22, 15.15,'emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com'),
    (13, 'James', 31, 15.15,'james@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.com'),
    (14, 'Grace', 24, 15.15,'grace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.com'),
    (15, 'Benjamin', 33, 15.15,'benjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.com'),
    (16, 'Ava', 20, 15.15,'ava@example.com'),
    (17, 'William', 34, 15.15,'william@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.com'),
    (18, 'Lily', 37, 15.15,'lily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.com'),
    (19, 'Ethan', 39, 15.15,'ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com'),
    (20, 'Mia', 36, 15.15,'mia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.com');

使用四种加密算法，完成表中四列数据的加密和洗数 #

将非加密表转为加密表，并启动洗数任务

CONVERT TABLE t_data_type BY ENCRYPT RULE (
COLUMNS(
(NAME=varchar_data, DATA_TYPE='varchar(100)', PLAIN=varchar_data,
CIPHER=cipher_varchar_data,CIPHER_DATA_TYPE='varchar(100)',
LIKE_QUERY=like_column ,LIKE_QUERY_DATA_TYPE='varchar(100)',
ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE'))
),
(NAME=INT_data, DATA_TYPE='INT', PLAIN=INT_data,
CIPHER=cipher_int_data,CIPHER_DATA_TYPE='varchar(100)',
ENCRYPT_ALGORITHM(TYPE(NAME='RC4',PROPERTIES('rc4-key-value'='123456abc')))
),
(NAME=DECIMAL_data, DATA_TYPE='DECIMAL (10, 2)', PLAIN=DECIMAL_data,
CIPHER=cipher_decimal_data,CIPHER_DATA_TYPE='varchar(100)',
ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='78910edc')))
),
(NAME=longtext_data, DATA_TYPE='longtext', PLAIN=longtext_data,
CIPHER=cipher_longtext_data, CIPHER_DATA_TYPE='varchar(2000)',
ENCRYPT_ALGORITHM(TYPE(NAME='SM4',PROPERTIES('sm4-key'='4D744E003D713D054E7E407C350E447E','sm4-mode'='ECB','sm4-padding'='PKCS5Padding')))
)), QUERY_WITH_PLAIN=true, START_ENCRYPTING_JOB=TRUE);

查看加密规则

show encrypt rules;
+-------------+---------------+-----------------+----------------------+------------------+---------------+-----------------+-----------------------+--------------------------+-------------------+----------------------+----------------+--------------------------------------------------------------------------------+---------------------+----------------------+------------------+------------------+------------------+
| table       | logic_column  | logic_data_type | cipher_column        | cipher_data_type | plain_column  | plain_data_type | assisted_query_column | assisted_query_data_type | like_query_column | like_query_data_type | encryptor_type | encryptor_props                                                                | assisted_query_type | assisted_query_props | like_query_type  | like_query_props | query_with_plain |
+-------------+---------------+-----------------+----------------------+------------------+---------------+-----------------+-----------------------+--------------------------+-------------------+----------------------+----------------+--------------------------------------------------------------------------------+---------------------+----------------------+------------------+------------------+------------------+
| t_data_type | varchar_data  | varchar(100)    | cipher_varchar_data  | varchar(100)     | varchar_data  | varchar(100)    |                       |                          | like_column       | varchar(100)         | AES            | aes-key-value=123456abc                                                        |                     |                      | CHAR_DIGEST_LIKE |                  | true             |
| t_data_type | INT_data      | INT             | cipher_int_data      | varchar(100)     | INT_data      | INT             |                       |                          |                   |                      | RC4            | rc4-key-value=123456abc                                                        |                     |                      |                  |                  | true             |
| t_data_type | DECIMAL_data  | DECIMAL (10, 2) | cipher_decimal_data  | varchar(100)     | DECIMAL_data  | DECIMAL (10, 2) |                       |                          |                   |                      | AES            | aes-key-value=78910edc                                                         |                     |                      |                  |                  | true             |
| t_data_type | longtext_data | longtext        | cipher_longtext_data | varchar(2000)    | longtext_data | longtext        |                       |                          |                   |                      | SM4            | sm4-key=4D744E003D713D054E7E407C350E447E,sm4-mode=ECB,sm4-padding=PKCS5Padding |                     |                      |                  |                  | true             |
+-------------+---------------+-----------------+----------------------+------------------+---------------+-----------------+-----------------------+--------------------------+-------------------+----------------------+----------------+--------------------------------------------------------------------------------+---------------------+----------------------+------------------+------------------+------------------+
4 rows in set (0.01 sec)

查看洗数任务

mysql> show encrypting list;
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns                                                      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| j5202p000008f1738eecad3985aaf91e46d829fd92 | t_data_type | t_data_type.DECIMAL_data,varchar_data,INT_data,longtext_data | 1              | true   | 2024-03-21 16:54:22 | NULL      |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> show encrypting status j5202p000008f1738eecad3985aaf91e46d829fd92;
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| table_name  | status   | active | processed_records_count | inventory_finished_percentage | error_message |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| t_data_type | FINISHED | true   | 20                      | 100                           |               |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
1 row in set (0.01 sec)

确认洗数任务执行完成后，提交洗数任务

mysql> commit encrypting j5202p000008f1738eecad3985aaf91e46d829fd92;
Query OK, 0 rows affected (0.11 sec)

将使用密文查询

ALTER ENCRYPT RULE t_data_type SET QUERY_WITH_PLAIN = false;

从 Proxy 查询出解密的数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 | 25       | 11.11        | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 | 30       | 12.12        | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 | 28       | 13.13        | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

从 MySQL 查询出密文存储

select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------------------+-------------+-----------------+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data | cipher_varchar_data      | like_column | cipher_int_data | cipher_decimal_data      | cipher_longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------------------+-------------+-----------------+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         | 7bDV86+VlIfykt2qARsIyg== | Ipim        | RGU=            | zBEpzACI8fkb7Xd8wiMMAw== | eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb09eddb2365c89803e1a2aaa93eeb2ccb097fa66481b67325394b39825602f10993                                                                                                                                                                                                                                                                                                 |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        | oZFK0ARtiUd9BK+xhY7nxg== | @mhdd       | RWA=            | buVuaka0guFpmYrY1SW/cQ== | 04c51eea4fd2975f11cf68197f342dbf09984584f9fbf1889b1f576150beb85156de3f637558ac4cf2c7cb824312e39a5ae786ce6bed35378fe327ca791412869a3366f61d563884c40e4636fbaecc3c3a7b4f87cc1fee9cade27321da3e0f131dec397d9f641485bd1cac718c6a5da923fe51db4e3ac86ed0600e0c8ac93e2a18848f499a77b4f5f3c56f424bd6a606a50573a402275cab455b959357dbb9a99132eac1a8afe2e21ab5a57b3fadcfd8ef12f8737a62edd8662bede1d7db6b4dedfd9d081f56727b241cbfad5666782365781a6414d496473ada9d103de66f50cb7aba72ba6bcbf85ecc7407fbc58cff13bc93eac4e2a5188dafca400d16c6bc3f8d382d06fd80ce23e2c70034a39dc404c51eea4fd2975f11cf68197f342dbf09984584f9fbf1889b1f576150beb8513658757add4dd978fd27604fee5ed330                                                                                                                                                                                                                                                                 |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com                                                                                                                      | Bob          | zoF+YZ2OZYwUlEtThkgl+A== | Apa         | RGg=            | PpPzblgCtcmqxNGvOJXv8A== | f633a4807c6facb48017738f55038fce899856c155dff6d84d8fd14fafa406581c8684b1388158f5694c9d13de196a254186c54838ed2c7ea53d4f56d113858ba08b61ef5ccd1dcb466a643e1f5a699070bca316b80991d7468c72c4a7730a0f655e081f5fd00338c5c9076004751a3beec783d4f867bf83f03943a988c6c8d34643a6bbe2dab77e7a55ddcde9d40b7d7685717f8894bd68731a3de067ccdc2d678d8675e2c9befb5fc3cfa7392fb1b6527e61b17007ce3c6c170192556c811a119082880324df338de8c92c2693aeaca8640dd55e0c3cfa68a7e09939083885ffb8c143926d254dd8772bb6213930b9f633a4807c6facb48017738f55038fce899856c155dff6d84d8fd14fafa406581c8684b1388158f5694c9d13de196a254186c54838ed2c7ea53d4f56d113858bbb5ac8fafb25858063155e29178bdbcd

基本 CRUD 查询 a. 通过 Proxy 执行查询，Preview 查看到的是通过密文列查询的

mysql> PREVIEW SELECT * FROM t_data_type WHERE varchar_data = 'Lily';
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                                                                                                                                                                                                    |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_data_type.`id`, t_data_type.`cipher_int_data` AS `INT_data`, t_data_type.`cipher_decimal_data` AS `DECIMAL_data`, t_data_type.`cipher_longtext_data` AS `longtext_data`, t_data_type.`cipher_varchar_data` AS `varchar_data` FROM t_data_type WHERE cipher_varchar_data = 'RHy6cfnXlYuLWVKAV0OJ1w==' |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t_data_type WHERE varchar_data = 'Lily';
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| 18 | 37       | 15.15        | lily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.com | Lily         |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
1 row in set (0.01 sec)

b. 通过 Proxy 执行查询，Preview 查看到的是通过密文列查询的

mysql> PREVIEW SELECT * FROM t_data_type WHERE varchar_data in ( 'Lily','John','Bob');
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                                                                                                                                                                                                                                                               |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_data_type.`id`, t_data_type.`cipher_int_data` AS `INT_data`, t_data_type.`cipher_decimal_data` AS `DECIMAL_data`, t_data_type.`cipher_longtext_data` AS `longtext_data`, t_data_type.`cipher_varchar_data` AS `varchar_data` FROM t_data_type WHERE cipher_varchar_data in ('RHy6cfnXlYuLWVKAV0OJ1w==', '7bDV86+VlIfykt2qARsIyg==', 'zoF+YZ2OZYwUlEtThkgl+A==') |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t_data_type WHERE varchar_data in ( 'Lily','John','Bob');
+----+----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                               | varchar_data |
+----+----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 | 25       | 11.11        | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                            | John         |
|  3 | 28       | 13.13        | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com | Bob          |
| 18 | 37       | 15.15        | lily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.com                            | Lily         |
+----+----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
3 rows in set (0.01 sec)

c. 通过 Proxy 执行密文列新增语句，Preivew 查看写入的是密文：

INSERT INTO t_data_type (id, VARCHAR_data, INT_data, DECIMAL_data,longtext_data) VALUES
    (21, 'Zap', 25, 11.11,'Zap@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com');

mysql> PREVIEW INSERT INTO t_data_type (id, VARCHAR_data, INT_data, DECIMAL_data,longtext_data) VALUES
    ->     (21, 'Zap', 25, 11.11,'Zap@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com');  
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                       |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0             | INSERT INTO t_data_type (id, cipher_varchar_data, like_column, varchar_data, cipher_int_data, INT_data, cipher_decimal_data, DECIMAL_data,cipher_longtext_data, longtext_data) VALUES
    (21, 'UgNzaNcbf5CIlVUpcxEpWA==', 'Yitqdhm`', 'Zap', 'RGU=', 25, 'zBEpzACI8fkb7Xd8wiMMAw==', 11.11, 'edbba1d359f7bbc7f27e0fc7e55feb40c4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec4ee34d701aadcb23d32a30cdb800daec500cff999151fb5e098c620c4763db8', 'Zap@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com') |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

mysql> INSERT INTO t_data_type (id, VARCHAR_data, INT_data, DECIMAL_data,longtext_data) VALUES
    ->     (21, 'Zap', 25, 11.11,'Zap@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com');
Query OK, 1 row affected (0.02 sec)

d. 通过密文列字段更新密文列，Preview 查看是通过加密结果查找和更新：

mysql> PREVIEW UPDATE t_data_type SET varchar_data = 'Rose' WHERE varchar_data = 'Zap';
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                                                                  |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0             | UPDATE t_data_type SET cipher_varchar_data = 'e6gy49iJyP1XJkd7pZDafw==', like_column = 'Qptd', varchar_data = 'Rose' WHERE cipher_varchar_data = 'UgNzaNcbf5CIlVUpcxEpWA==' |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> UPDATE t_data_type SET varchar_data = 'Rose' WHERE varchar_data = 'Zap';
Query OK, 1 row affected (0.01 sec)


mysql> SELECT * FROM t_data_type WHERE id = 21;  
+----+----------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                        | varchar_data |
+----+----------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| 21 | 25       | 11.11        | Zap@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com | Rose         |
+----+----------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
1 row in set (0.02 sec)

密文列删除语句：

mysql> PREVIEW DELETE FROM t_data_type WHERE varchar_data = 'Rose';
+------------------+--------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                     |
+------------------+--------------------------------------------------------------------------------+
| ds_0             | DELETE FROM t_data_type WHERE cipher_varchar_data = 'e6gy49iJyP1XJkd7pZDafw==' |
+------------------+--------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> DELETE FROM t_data_type WHERE varchar_data = 'Rose';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t_data_type WHERE varchar_data = 'Rose';
Empty set (0.01 sec)

模糊查询，从Proxy和MySQL上查询到的结果是一致的

a. 从 Proxy 查询数据

mysql> select * from t_data_type where VARCHAR_data like 'E%';
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  4 | 21       | 14.14        | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              | Emily        |
| 12 | 22       | 15.15        | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com | Emma         |
| 19 | 39       | 15.15        | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                | Ethan        |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
3 rows in set (0.03 sec)

b. 从 MySQL 查询数据

mysql> select * from db_encrypt.t_data_type where VARCHAR_data like 'E%';
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------------------+-------------+-----------------+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data | cipher_varchar_data      | like_column | cipher_int_data | cipher_decimal_data      | cipher_longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------------------+-------------+-----------------+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  4 |       21 |        14.14 | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              | Emily        | ja0m93+Ygg2aLmP3Eji4uw== | Dlhmx       | RGE=            | /DHM+oFvqgy7aNiavGoq3w== | 1a21530403b766d23d8336bd2b9d393152110428a6d29ba5847b2037d7c768874d944fb37a6260c74ea9bb1352ee9039fe1ab302c4565b8d84146877e5280a002406cbbee48a4d1eba4e3c792641db9baeffe2f768412ba03d80ff1f83a64b950e51bee407e205246d7bb9936dcec974eb93c036aa6dce4000a33e70383f5de087532d6a34a34124c4cf050d2bef81127ee0465b430abdcdbecdc4ce8e8c8d63cca261afe968c54eee131ab61dfd1772f07c036678ba6e0a8b78ebaf65fe70a99bdc0450a6a15b4356418de9bf5c2a35078440e8debddf3aaaff56ee01dbf7f93bff5dc157a75895f37323d8906a4725a7bc7494dc391339c40d108a0a26379d6d7eaaa2c1272804da30f4df5f9e7d141a21530403b766d23d8336bd2b9d393152110428a6d29ba5847b2037d7c768874d944fb37a6260c74ea9bb1352ee9039ad800739bbd760f09ca77985cb75115c                                                                                                                                                                                                                                 |
| 12 |       22 |        15.15 | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com | Emma         | 4Gl4ifZe1oGw3syjvoCL8g== | Dll`        | RGI=            | H7md4zvDeGa9rFk/BvleRw== | 31d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af66531d0eebd4d9fa44eb28c057bb79af6657fa66481b67325394b39825602f10993 |
| 19 |       39 |        15.15 | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                | Ethan        | sFyO1sGKF6jf7Ycmt2JjGQ== | Dui`m       | RWk=            | H7md4zvDeGa9rFk/BvleRw== | 50984bade51b9a8d2cb33178e2fa7ebac1184221e7866dea90456b52041236a9832543c4baec3b911e79b87bfc6e7485fb74a710686564ac5e87836d3bab4b4accbfbeede7acf3e35eaa52331a4b49f1c601f3407a456b4c5d9131175a4212a742a295ee85c0f4ce1bf2775e993919a0dfeb36320457f48cfd73d46df6b4610d0a6a091165d17143dbeb3107a86939a122400d5330a4d64fb695fb5cf44f3cdf015d2fe82c2b810a4ddf551228023703c89fac597b704b010db75b937475b85291326239ca9eded8e13ad25361e82a9871b46d64c0d2e39955152787b51d0b99ddc2a8cb4e29a438eacba2bab2a432c6dd7fc9daf3fddddb54575385cac01940523413bbf69334838a32738d7b34ebe050984bade51b9a8d2cb33178e2fa7eba64fa390eb1f05ddb7c7f7ee8cb4aefc8                                                                                                                                                                                                                                                                                                 |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------------------+-------------+-----------------+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

修改规则使用明文查询

ALTER ENCRYPT RULE t_data_type SET QUERY_WITH_PLAIN = true;

将加密表转为非加密表

CONVERT TABLE t_data_type DROP ENCRYPT RULE;

从 Proxy 和从 MySQL 查询数据的结果是一致的内容从 Proxy 查询数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

从 MySQL 查询数据

mysql> select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

反洗数 #

将非加密表转为加密表

CONVERT TABLE t_data_type BY ENCRYPT RULE (
COLUMNS(
(NAME=varchar_data, DATA_TYPE='varchar(100)', PLAIN=varchar_data,
CIPHER=cipher_VARCHAR_data,CIPHER_DATA_TYPE='varchar(100)',
ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
)), QUERY_WITH_PLAIN=true, START_ENCRYPTING_JOB=TRUE);

查看洗数任务

mysql> show encrypting list;
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns                                                      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| j5202p000008f1738eecad3985aaf91e46d829fd92 | t_data_type | t_data_type.DECIMAL_data,VARCHAR_data,INT_data,longtext_data | 1              | true   | 2024-03-20 17:32:40 | NULL      |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> show encrypting status j5202p000008f1738eecad3985aaf91e46d829fd92;
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| table_name  | status   | active | processed_records_count | inventory_finished_percentage | error_message |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| t_data_type | FINISHED | true   | 20                      | 100                           |               |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
1 row in set (0.04 sec)

确认洗数任务执行完成后，提交洗数任务

commit encrypting j5202p000008f1738eecad3985aaf91e46d829fd92;

将使用密文查询

ALTER ENCRYPT RULE t_data_type SET QUERY_WITH_PLAIN = false;

分别在 Proxy 和 MySQL 查询数据

从 Proxy 查询出解密的数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

从 MySQL 查询出密文存储

mysql> select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data | cipher_VARCHAR_data      |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         | 7bDV86+VlIfykt2qARsIyg== |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        | oZFK0ARtiUd9BK+xhY7nxg== |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

删除明文列

CONVERT TABLE t_data_type DROP PLAIN COLUMNS(varchar_data);

从 Proxy 查询出解密后的数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

从 MySQL 查询出密文存储的数据

mysql> select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | cipher_VARCHAR_data      |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | 7bDV86+VlIfykt2qARsIyg== |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | oZFK0ARtiUd9BK+xhY7nxg== |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

添加明文列

CONVERT TABLE t_data_type ADD PLAIN COLUMNS(varchar_data), START_DECRYPTING_JOB=TRUE;

查看反洗数任务

mysql> show DECRYPTING list;
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
| j5302p0000418e5362856b22af6b28bec7371d92fc | t_data_type | varchar_data | 1              | true   | 2024-03-21 16:28:25 | NULL      |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
1 row in set (0.04 sec)

mysql> show DECRYPTING status j5302p0000418e5362856b22af6b28bec7371d92fc;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | encrypt_db  | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.04 sec)

确认反洗数任务执行完成后，提交反洗数任务

mysql> COMMIT DECRYPTING j5302p0000418e5362856b22af6b28bec7371d92fc;
Query OK, 0 rows affected (0.17 sec)

修改规则使用明文查询

ALTER ENCRYPT RULE t_data_type(varchar_data) SET QUERY_WITH_PLAIN = true;

将加密表转为非加密表

CONVERT TABLE t_data_type DROP ENCRYPT RULE;

从 Proxy 和从 MySQL 查询数据的结果是一致的内容从 Proxy 查询数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

从 MySQL 查询数据

mysql> select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

重洗数 #

使用密钥管理器配置将非加密表转为加密表

CONVERT TABLE t_data_type BY ENCRYPT RULE (
COLUMNS(
(NAME=varchar_data, DATA_TYPE='varchar(100)', PLAIN=varchar_data,
CIPHER=cipher_VARCHAR_data,CIPHER_DATA_TYPE='varchar(100)',
ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
)), QUERY_WITH_PLAIN=true, START_ENCRYPTING_JOB=TRUE);

查看洗数任务

mysql> show encrypting list;
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns                                                      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| j5202p000008f1738eecad3985aaf91e46d829fd92 | t_data_type | t_data_type.DECIMAL_data,VARCHAR_data,INT_data,longtext_data | 1              | true   | 2024-03-20 17:32:40 | NULL      |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> show encrypting status j5202p000008f1738eecad3985aaf91e46d829fd92;
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| table_name  | status   | active | processed_records_count | inventory_finished_percentage | error_message |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| t_data_type | FINISHED | true   | 20                      | 100                           |               |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
1 row in set (0.04 sec)

确认洗数任务执行完成后，提交洗数任务

commit encrypting j5202p000008f1738eecad3985aaf91e46d829fd92;

将使用密文查询

ALTER ENCRYPT RULE t_data_type SET QUERY_WITH_PLAIN = false;

分别在 Proxy 和 MySQL 查询数据从 Proxy 查询出解密的数据

mysql> select * from t_data_type;
+----+--------------+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | varchar_data | int_data | decimal_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+----+--------------+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | John         |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 |
|  2 | Alice        |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               |
|  3 | Bob          |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com                                                                                                                      |
|  4 | Emily        |       21 |        14.14 | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              |
|  5 | Michael      |       35 |        15.15 | michael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.com                                                                                                              |
|  6 | Sarah        |       19 |        15.15 | sarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.com                                                                                                              |
|  7 | David        |       27 |        15.15 | david@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.com                                                                                                              |
|  8 | Olivia       |       23 |        15.15 | olivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.com                                                                                                                                                                                                                                           |
|  9 | Daniel       |       32 |        15.15 | daniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.com                                                                                                             |
| 10 | Sophia       |       26 |        15.15 | sophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.com                                                       |
| 11 | Matthew      |       29 |        15.15 | matthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.com                                                                                                                                 |
| 12 | Emma         |       22 |        15.15 | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com |
| 13 | James        |       31 |        15.15 | james@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.com                                                                                                              |
| 14 | Grace        |       24 |        15.15 | grace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.com                                                                                                              |
| 15 | Benjamin     |       33 |        15.15 | benjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.com                                                     |
| 16 | Ava          |       20 |        15.15 | ava@example.com                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| 17 | William      |       34 |        15.15 | william@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.com                                                                                                              |
| 18 | Lily         |       37 |        15.15 | lily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.com                                                                                                                                                 |
| 19 | Ethan        |       39 |        15.15 | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                |
| 20 | Mia          |       36 |        15.15 | mia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.com                                                                                                                                     |
+----+--------------+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
20 rows in set (0.01 sec)

从 MySQL 查询出密文存储

mysql> select * from db_encrypt.t_data_type;
+----+--------------+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
| id | varchar_data | int_data | decimal_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | cipher_VARCHAR_data      |
+----+--------------+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
|  1 | John         |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | 7bDV86+VlIfykt2qARsIyg== |
|  2 | Alice        |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | oZFK0ARtiUd9BK+xhY7nxg== |
|  3 | Bob          |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com                                                                                                                      | zoF+YZ2OZYwUlEtThkgl+A== |
|  4 | Emily        |       21 |        14.14 | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              | ja0m93+Ygg2aLmP3Eji4uw== |
|  5 | Michael      |       35 |        15.15 | michael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.com                                                                                                              | zNcKIfYqj3JXtO1AVSDJbw== |
|  6 | Sarah        |       19 |        15.15 | sarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.com                                                                                                              | TzmWRjYL0qJuK5v4GIt8gQ== |
|  7 | David        |       27 |        15.15 | david@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.com                                                                                                              | EU4+fEg1KkxfP4ADu9A0nA== |
|  8 | Olivia       |       23 |        15.15 | olivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.com                                                                                                                                                                                                                                           | bieXDkIpZN4fuGQIo3IV6w== |
|  9 | Daniel       |       32 |        15.15 | daniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.com                                                                                                             | hAGrNbNAekMM61IAUMgC8g== |
| 10 | Sophia       |       26 |        15.15 | sophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.com                                                       | 7j16U2EMKxdD383xBdgv9Q== |
| 11 | Matthew      |       29 |        15.15 | matthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.com                                                                                                                                 | 79s0Z3/S4EGqH8yQTMmGaQ== |
| 12 | Emma         |       22 |        15.15 | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com | 4Gl4ifZe1oGw3syjvoCL8g== |
| 13 | James        |       31 |        15.15 | james@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.com                                                                                                              | TqukQxh8Y0WFDBflV+ZGSA== |
| 14 | Grace        |       24 |        15.15 | grace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.com                                                                                                              | fgzjxoenYRdaZo3H+QLrDQ== |
| 15 | Benjamin     |       33 |        15.15 | benjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.com                                                     | QjLJntl0X21pUUA6TE90Gg== |
| 16 | Ava          |       20 |        15.15 | ava@example.com                                                                                                                                                                                                                                                                                                                                                                                                                                  | d6yH0PR/1TfH7phqwB9phQ== |
| 17 | William      |       34 |        15.15 | william@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.com                                                                                                              | Fl9jE+qOSr18swc6RIrhAA== |
| 18 | Lily         |       37 |        15.15 | lily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.com                                                                                                                                                 | RHy6cfnXlYuLWVKAV0OJ1w== |
| 19 | Ethan        |       39 |        15.15 | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                | sFyO1sGKF6jf7Ycmt2JjGQ== |
| 20 | Mia          |       36 |        15.15 | mia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.com                                                                                                                                     | TjZ7+YJT0WqSHxYxzuFLww== |
+----+--------------+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
20 rows in set (0.00 sec)

删除明文列

CONVERT TABLE t_data_type DROP PLAIN COLUMNS(varchar_data);

从 Proxy 查询出解密后的数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | int_data | decimal_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com                                                                                                                      | Bob          |
|  4 |       21 |        14.14 | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              | Emily        |
|  5 |       35 |        15.15 | michael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.com                                                                                                              | Michael      |
|  6 |       19 |        15.15 | sarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.com                                                                                                              | Sarah        |
|  7 |       27 |        15.15 | david@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.com                                                                                                              | David        |
|  8 |       23 |        15.15 | olivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.com                                                                                                                                                                                                                                           | Olivia       |
|  9 |       32 |        15.15 | daniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.com                                                                                                             | Daniel       |
| 10 |       26 |        15.15 | sophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.com                                                       | Sophia       |
| 11 |       29 |        15.15 | matthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.com                                                                                                                                 | Matthew      |
| 12 |       22 |        15.15 | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com | Emma         |
| 13 |       31 |        15.15 | james@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.com                                                                                                              | James        |
| 14 |       24 |        15.15 | grace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.com                                                                                                              | Grace        |
| 15 |       33 |        15.15 | benjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.com                                                     | Benjamin     |
| 16 |       20 |        15.15 | ava@example.com                                                                                                                                                                                                                                                                                                                                                                                                                                  | Ava          |
| 17 |       34 |        15.15 | william@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.com                                                                                                              | William      |
| 18 |       37 |        15.15 | lily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.com                                                                                                                                                 | Lily         |
| 19 |       39 |        15.15 | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                | Ethan        |
| 20 |       36 |        15.15 | mia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.com                                                                                                                                     | Mia          |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
20 rows in set (0.01 sec)

从 MySQL 查询出密文存储的数据

mysql> select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
| id | int_data | decimal_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | cipher_VARCHAR_data      |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | 7bDV86+VlIfykt2qARsIyg== |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | oZFK0ARtiUd9BK+xhY7nxg== |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com                                                                                                                      | zoF+YZ2OZYwUlEtThkgl+A== |
|  4 |       21 |        14.14 | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              | ja0m93+Ygg2aLmP3Eji4uw== |
|  5 |       35 |        15.15 | michael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.com                                                                                                              | zNcKIfYqj3JXtO1AVSDJbw== |
|  6 |       19 |        15.15 | sarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.com                                                                                                              | TzmWRjYL0qJuK5v4GIt8gQ== |
|  7 |       27 |        15.15 | david@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.com                                                                                                              | EU4+fEg1KkxfP4ADu9A0nA== |
|  8 |       23 |        15.15 | olivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.com                                                                                                                                                                                                                                           | bieXDkIpZN4fuGQIo3IV6w== |
|  9 |       32 |        15.15 | daniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.com                                                                                                             | hAGrNbNAekMM61IAUMgC8g== |
| 10 |       26 |        15.15 | sophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.com                                                       | 7j16U2EMKxdD383xBdgv9Q== |
| 11 |       29 |        15.15 | matthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.com                                                                                                                                 | 79s0Z3/S4EGqH8yQTMmGaQ== |
| 12 |       22 |        15.15 | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com | 4Gl4ifZe1oGw3syjvoCL8g== |
| 13 |       31 |        15.15 | james@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.com                                                                                                              | TqukQxh8Y0WFDBflV+ZGSA== |
| 14 |       24 |        15.15 | grace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.com                                                                                                              | fgzjxoenYRdaZo3H+QLrDQ== |
| 15 |       33 |        15.15 | benjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.com                                                     | QjLJntl0X21pUUA6TE90Gg== |
| 16 |       20 |        15.15 | ava@example.com                                                                                                                                                                                                                                                                                                                                                                                                                                  | d6yH0PR/1TfH7phqwB9phQ== |
| 17 |       34 |        15.15 | william@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.com                                                                                                              | Fl9jE+qOSr18swc6RIrhAA== |
| 18 |       37 |        15.15 | lily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.com                                                                                                                                                 | RHy6cfnXlYuLWVKAV0OJ1w== |
| 19 |       39 |        15.15 | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                | sFyO1sGKF6jf7Ycmt2JjGQ== |
| 20 |       36 |        15.15 | mia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.com                                                                                                                                     | TjZ7+YJT0WqSHxYxzuFLww== |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
20 rows in set (0.00 sec)

启动重洗数（密钥轮转）

  - 单列重洗数示例
REENCRYPT TABLE WITH RULE t_data_type (COLUMNS(
(NAME=varchar_data, CIPHER=cipher_VARCHAR_data, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='445533ccc'))))
));

  - 多列重洗数示例
REENCRYPT TABLE WITH RULE t_data_type (COLUMNS(
(NAME=varchar_data, CIPHER=cipher_varchar_data, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='445533ccc')))),
(NAME=int_data, CIPHER=cipher_int_data, ENCRYPT_ALGORITHM(TYPE(NAME='RC4',PROPERTIES('rc4-key-value'='789101def'))))
));

启动重洗数第二阶段

mysql> show REENCRYPTING list;
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+---------------------+
| id                                         | tables      | columns      | job_item_count | active | create_time         | stop_time           |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+---------------------+
| j5402p0000418e5362856b22af6b28bec7371d92fc | t_data_type | varchar_data | 1              | false  | 2024-03-26 17:55:18 | 2024-03-26 17:55:24 |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+---------------------+
1 rows in set (0.02 sec)

mysql> START REENCRYPTING j5402p0000418e5362856b22af6b28bec7371d92fc;
Query OK, 0 rows affected (5.68 sec)

mysql> show REENCRYPTING list;
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+---------------------+
| id                                         | tables      | columns      | job_item_count | active | create_time         | stop_time           |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+---------------------+
| j5402p0000418e5362856b22af6b28bec7371d92fc | t_data_type | varchar_data | 1              | true   | 2024-03-26 17:55:18 | NULL                |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+---------------------+
1 rows in set (0.02 sec)

提交重洗数

mysql> show REENCRYPTING list;
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+---------------------+
| id                                         | tables      | columns      | job_item_count | active | create_time         | stop_time           |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+---------------------+
| j5402p0000418e5362856b22af6b28bec7371d92fc | t_data_type | varchar_data | 1              | true   | 2024-03-26 17:55:18 | NULL                |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+---------------------+
1 rows in set (0.02 sec)

mysql> show REENCRYPTING status j5402p0000418e5362856b22af6b28bec7371d92fc;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | encrypt_db  | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.01 sec)

mysql> commit REENCRYPTING j5402p0000418e5362856b22af6b28bec7371d92fc;
Query OK, 0 rows affected (1.01 sec)

mysql> show encrypt rule t_data_type;
+-------------+--------------+-----------------+---------------------+------------------+--------------+-----------------+-----------------------+--------------------------+-------------------+----------------------+----------------+-------------------------+---------------------+----------------------+-----------------+------------------+------------------+
| table       | logic_column | logic_data_type | cipher_column       | cipher_data_type | plain_column | plain_data_type | assisted_query_column | assisted_query_data_type | like_query_column | like_query_data_type | encryptor_type | encryptor_props         | assisted_query_type | assisted_query_props | like_query_type | like_query_props | query_with_plain |
+-------------+--------------+-----------------+---------------------+------------------+--------------+-----------------+-----------------------+--------------------------+-------------------+----------------------+----------------+-------------------------+---------------------+----------------------+-----------------+------------------+------------------+
| t_data_type | varchar_data | varchar(100)    | cipher_VARCHAR_data | varchar(100)     |              | varchar(100)    |                       |                          |                   |                      | AES            | aes-key-value=445533ccc |                     |                      |                 |                  | false            |
+-------------+--------------+-----------------+---------------------+------------------+--------------+-----------------+-----------------------+--------------------------+-------------------+----------------------+----------------+-------------------------+---------------------+----------------------+-----------------+------------------+------------------+
1 row in set (0.01 sec)

再次从 Proxy 查询出解密后的数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | int_data | decimal_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com                                                                                                                      | Bob          |
|  4 |       21 |        14.14 | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              | Emily        |
|  5 |       35 |        15.15 | michael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.com                                                                                                              | Michael      |
|  6 |       19 |        15.15 | sarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.com                                                                                                              | Sarah        |
|  7 |       27 |        15.15 | david@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.com                                                                                                              | David        |
|  8 |       23 |        15.15 | olivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.com                                                                                                                                                                                                                                           | Olivia       |
|  9 |       32 |        15.15 | daniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.com                                                                                                             | Daniel       |
| 10 |       26 |        15.15 | sophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.com                                                       | Sophia       |
| 11 |       29 |        15.15 | matthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.com                                                                                                                                 | Matthew      |
| 12 |       22 |        15.15 | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com | Emma         |
| 13 |       31 |        15.15 | james@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.com                                                                                                              | James        |
| 14 |       24 |        15.15 | grace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.com                                                                                                              | Grace        |
| 15 |       33 |        15.15 | benjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.com                                                     | Benjamin     |
| 16 |       20 |        15.15 | ava@example.com                                                                                                                                                                                                                                                                                                                                                                                                                                  | Ava          |
| 17 |       34 |        15.15 | william@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.com                                                                                                              | William      |
| 18 |       37 |        15.15 | lily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.com                                                                                                                                                 | Lily         |
| 19 |       39 |        15.15 | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                | Ethan        |
| 20 |       36 |        15.15 | mia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.com                                                                                                                                     | Mia          |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
20 rows in set (0.01 sec)

再次从 MySQL 查询出密文存储的数据，密文结果是不一样的。

mysql> select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
| id | int_data | decimal_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | cipher_VARCHAR_data      |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | eajujYa4QVWZCksA6BTtUw== |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | IJoHseDbV2AJ7zI774SNXw== |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com                                                                                                                      | DD4Cr0caBqkdnZHXn0vHqg== |
|  4 |       21 |        14.14 | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              | BqP40hqIJmoCiKOQW7k6tQ== |
|  5 |       35 |        15.15 | michael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.commichael@example.com                                                                                                              | 1J5VK1oyBHBmsvr5YqnpQw== |
|  6 |       19 |        15.15 | sarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.comsarah@example.com                                                                                                              | Vck1CIgyaRnS9usadyJV1A== |
|  7 |       27 |        15.15 | david@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.comdavid@example.com                                                                                                              | ySg4DQW4wT7xjbDKEdBbbQ== |
|  8 |       23 |        15.15 | olivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.comolivia@example.com                                                                                                                                                                                                                                           | whMpx5atkSahm4oLk+jlyQ== |
|  9 |       32 |        15.15 | daniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.comdaniel@example.com                                                                                                             | POgu8IAEZmPvTmWTxXilow== |
| 10 |       26 |        15.15 | sophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.comsophia@example.com                                                       | oGzfZM7tWYRV+umkxq2Kaw== |
| 11 |       29 |        15.15 | matthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.commatthew@example.com                                                                                                                                 | 0Wrz08+O+U0xLgnse5Sp8w== |
| 12 |       22 |        15.15 | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com | UoGqPUGhiVbRMtDUO0zlmg== |
| 13 |       31 |        15.15 | james@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.comjames@example.com                                                                                                              | 1OCFNtQE7Jcwhdl2L42uNQ== |
| 14 |       24 |        15.15 | grace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.comgrace@example.com                                                                                                              | X+JDyXfCsxX+NWQ5eMvjaQ== |
| 15 |       33 |        15.15 | benjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.combenjamin@example.com                                                     | lE+s/wWRrXoqx7gPamdC6w== |
| 16 |       20 |        15.15 | ava@example.com                                                                                                                                                                                                                                                                                                                                                                                                                                  | GOcRYxyrCDTpNnnUEkB06Q== |
| 17 |       34 |        15.15 | william@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.comwilliam@example.com                                                                                                              | rg22yuqeQoGIQGq23JvaEQ== |
| 18 |       37 |        15.15 | lily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.comlily@example.com                                                                                                                                                 | TZRit9R1nE40TEWVUX/IYA== |
| 19 |       39 |        15.15 | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                | xTkntOCzBMX7sUo4MvZINQ== |
| 20 |       36 |        15.15 | mia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.commia@example.com                                                                                                                                     | Qn20Qsh51w8MSJyAEPNErQ== |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
20 rows in set (0.00 sec)

添加明文列

CONVERT TABLE t_data_type ADD PLAIN COLUMNS(varchar_data), START_DECRYPTING_JOB=TRUE;

查看反洗数任务

mysql> show DECRYPTING list;
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
| j5302p0000418e5362856b22af6b28bec7371d92fc | t_data_type | varchar_data | 1              | true   | 2024-03-21 16:28:25 | NULL      |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
1 row in set (0.04 sec)

mysql> show DECRYPTING status j5302p0000418e5362856b22af6b28bec7371d92fc;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | encrypt_db  | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.04 sec)

确认洗数任务执行完成后，提交洗数任务

mysql> COMMIT DECRYPTING j5302p0000418e5362856b22af6b28bec7371d92fc;
Query OK, 0 rows affected (0.17 sec)

修改规则使用明文查询

ALTER ENCRYPT RULE t_data_type(varchar_data) SET QUERY_WITH_PLAIN = true;

将加密表改为非加密表

CONVERT TABLE t_data_type DROP ENCRYPT RULE;

从 Proxy 和从 MySQL 查询数据的结果是一致的内容从 Proxy 查询数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

从 MySQL 查询数据

mysql> select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

为加密表再新增一列加密列 #

使用密钥管理器配置将非加密表转为加密表

CONVERT TABLE t_data_type BY ENCRYPT RULE (
COLUMNS(
(NAME=varchar_data, DATA_TYPE='varchar(100)', PLAIN=varchar_data,
CIPHER=cipher_VARCHAR_data,CIPHER_DATA_TYPE='varchar(100)',
ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
)), QUERY_WITH_PLAIN=true, START_ENCRYPTING_JOB=TRUE);

查看洗数任务

mysql> show encrypting list;
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns                                                      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| j5202p000008f1738eecad3985aaf91e46d829fd92 | t_data_type | t_data_type.DECIMAL_data,VARCHAR_data,INT_data,longtext_data | 1              | true   | 2024-03-20 17:32:40 | NULL      |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> show encrypting status j5202p000008f1738eecad3985aaf91e46d829fd92;
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| table_name  | status   | active | processed_records_count | inventory_finished_percentage | error_message |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| t_data_type | FINISHED | true   | 20                      | 100                           |               |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
1 row in set (0.04 sec)

确认洗数任务执行完成后，提交洗数任务

commit encrypting j5202p000008f1738eecad3985aaf91e46d829fd92;

将使用密文查询

ALTER ENCRYPT RULE t_data_type SET QUERY_WITH_PLAIN = false;

删除明文列

CONVERT TABLE t_data_type DROP PLAIN COLUMNS(varchar_data);

从 Proxy 查询出解密后的数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

从 MySQL 查询出密文存储的数据

mysql> select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | cipher_VARCHAR_data      |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | 7bDV86+VlIfykt2qARsIyg== |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | oZFK0ARtiUd9BK+xhY7nxg== |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

新增一列的加密（同理也可以支持多列）

mysql> ALTER ENCRYPT RULE t_data_type (
    ->   COLUMNS (
    ->     (NAME=varchar_data, DATA_TYPE='varchar(100)', PLAIN=varchar_data,
    ->     CIPHER=cipher_VARCHAR_data,CIPHER_DATA_TYPE='varchar(100)',
    ->     ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
    ->     ),
    ->     (NAME=int_data, DATA_TYPE='int(11)', PLAIN=int_data,
    ->     CIPHER=cipher_int_data,CIPHER_DATA_TYPE='varchar(100)',
    ->     ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
    ->     )
    ->   ), QUERY_WITH_PLAIN=true
    -> );
Query OK, 0 rows affected (1.59 sec)

在 MySQL上操作，增加列

mysql> ALTER TABLE t_data_type ADD cipher_int_data varchar(100);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

在 proxy 上执行，启动洗数任务

ENCRYPT TABLE t_data_type (int_data);

查看洗数任务

mysql>  show encrypting list;
+--------------------------------------------+-------------+----------------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns              | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+----------------------+----------------+--------+---------------------+-----------+
| j5202p000008f1738eecad3985aaf91e46d829fd92 | t_data_type | t_data_type.int_data | 1              | true   | 2024-03-25 15:58:33 | NULL      |
+--------------------------------------------+-------------+----------------------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> show encrypting status j5202p000008f1738eecad3985aaf91e46d829fd92；
    -> ;
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| table_name  | status   | active | processed_records_count | inventory_finished_percentage | error_message |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| t_data_type | FINISHED | true   | 20                      | 100                           |               |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
1 row in set (0.02 sec)

确认洗数任务执行完成后，提交洗数任务

mysql> commit encrypting j5202p000008f1738eecad3985aaf91e46d829fd92;
Query OK, 0 rows affected (0.07 sec)

修改规则使用密文查询

ALTER ENCRYPT RULE t_data_type(int_data) SET QUERY_WITH_PLAIN = false;

从 Proxy 和从 MySQL 分别查询明文数据和密文数据

从 Proxy 查询数据

mysql> select int_data from t_data_type;
+----------+
| int_data |
+----------+
|       25 |
|       30 |
|       28 |
|       21 |
|       35 |
|       19 |
|       27 |
|       23 |
|       32 |
|       26 |
|       29 |
|       22 |
|       31 |
|       24 |
|       33 |
|       20 |
|       34 |
|       37 |
|       39 |
|       36 |
+----------+
20 rows in set (0.01 sec)

从 MySQL 查询数据

mysql> select cipher_int_data,int_data from t_data_type;
+--------------------------+----------+
| cipher_int_data          | int_data |
+--------------------------+----------+
| F+ZkQH7eYZDXZj60JrGO8g== |       25 |
| f+Mr+Wf6ua2935JadQj5dw== |       30 |
| GnXvN3TAEIzQd/KZeGvSTQ== |       28 |
| rzVh2T2AJ/EWqzRGXaGlOw== |       21 |
| edSBPHzFfpqDIiDh1e/3jA== |       35 |
| 9JCRWuCaYo7ruitT4CPVTA== |       19 |
| HVD21IqmVGtwl1lhn4ikhA== |       27 |
| 0ymmdvKmWktz/c2fvuUhOQ== |       23 |
| Oh1s1ePQNGiljkLHjQExWw== |       32 |
| XW8Q3kZDSlmKaoSeHsYHSA== |       26 |
| iN2/Wzw6zYY3qUCmfOHNDQ== |       29 |
| SXKNdocwgj07uyGV/UEkew== |       22 |
| 5P3xUVad04S3hG7s2TVxMA== |       31 |
| ktKZHXuxBto55OheAyUpbw== |       24 |
| VssfXD7x9pB1F/d4FfbpKg== |       33 |
| pY+lx4uzPQiDIOdr+f/Pnw== |       20 |
| uhjNOpiSGCfow/tp2UPJrQ== |       34 |
| tFynvuc9xULfcQsyYVlydw== |       37 |
| kMPKX2EfmzScf82t+Vjk8A== |       39 |
| eubx44TNNBYQh0S2fsZsYQ== |       36 |
+--------------------------+----------+
20 rows in set (0.00 sec)

将表改为使用明文查询

ALTER ENCRYPT RULE t_data_type SET QUERY_WITH_PLAIN = true;

将加密表转为非加密表

CONVERT TABLE t_data_type DROP ENCRYPT RULE;

为加密表单独删除一列加密 #

使用密钥管理器配置将非加密表转为加密表

CONVERT TABLE t_data_type BY ENCRYPT RULE (
  COLUMNS (
    (NAME=varchar_data, DATA_TYPE='varchar(100)', PLAIN=varchar_data,
    CIPHER=cipher_VARCHAR_data,CIPHER_DATA_TYPE='varchar(100)',
    ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
    ),
    (NAME=int_data, DATA_TYPE='int(11)', PLAIN=int_data,
    CIPHER=cipher_int_data,CIPHER_DATA_TYPE='varchar(100)',
    ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
    )
  ), QUERY_WITH_PLAIN=true, START_ENCRYPTING_JOB=TRUE
);

查看洗数任务

mysql> show encrypting list;
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns                                                      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| j5202p000008f1738eecad3985aaf91e46d829fd92 | t_data_type | t_data_type.DECIMAL_data,VARCHAR_data,INT_data,longtext_data | 1              | true   | 2024-03-20 17:32:40 | NULL      |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> show encrypting status j5202p000008f1738eecad3985aaf91e46d829fd92;
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| table_name  | status   | active | processed_records_count | inventory_finished_percentage | error_message |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| t_data_type | FINISHED | true   | 20                      | 100                           |               |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
1 row in set (0.04 sec)

确认洗数任务执行完成后，提交洗数任务

commit encrypting j5202p000008f1738eecad3985aaf91e46d829fd92;

将使用密文查询

ALTER ENCRYPT RULE t_data_type SET QUERY_WITH_PLAIN = false;

删除其中一列的明文列

CONVERT TABLE t_data_type DROP PLAIN COLUMNS(varchar_data);

从 Proxy 查询出解密后的数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

从 MySQL 查询出密文存储的数据

mysql> select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | cipher_VARCHAR_data      |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | 7bDV86+VlIfykt2qARsIyg== |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | oZFK0ARtiUd9BK+xhY7nxg== |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

添加这一列的明文列

CONVERT TABLE t_data_type ADD PLAIN COLUMNS(varchar_data), START_DECRYPTING_JOB=TRUE;

查看反洗数任务

mysql> show DECRYPTING list;
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
| j5302p0000418e5362856b22af6b28bec7371d92fc | t_data_type | varchar_data | 1              | true   | 2024-03-21 16:28:25 | NULL      |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
1 row in set (0.04 sec)

mysql> show DECRYPTING status j5302p0000418e5362856b22af6b28bec7371d92fc;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | encrypt_db  | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.04 sec)

确认洗数任务执行完成后，提交反洗数任务

mysql> COMMIT DECRYPTING j5302p0000418e5362856b22af6b28bec7371d92fc;
Query OK, 0 rows affected (0.17 sec)

修改规则使明文查询

 ALTER ENCRYPT RULE t_data_type(varchar_data) SET QUERY_WITH_PLAIN = true;

删除这一列的加密规则

ALTER ENCRYPT RULE t_data_type (
  COLUMNS (
    (NAME=int_data, DATA_TYPE='int(11)', PLAIN=int_data,
    CIPHER=cipher_int_data,CIPHER_DATA_TYPE='varchar(100)',
    ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
    )
  ), QUERY_WITH_PLAIN=false
);

在 Proxy上操作，删除加密（衍生）列

mysql> ALTER TABLE t_data_type drop cipher_VARCHAR_data;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

从 Proxy 和从 MySQL 分别查询明文数据和密文数据从 Proxy 查询数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | int_data | decimal_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com                                                                                                                      | Bob          |

从 MySQL 查询数据

mysql> select * from  t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+--------------+
| id | int_data | decimal_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | cipher_int_data          | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | F+ZkQH7eYZDXZj60JrGO8g== | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | f+Mr+Wf6ua2935JadQj5dw== | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com                                                                                                                      | GnXvN3TAEIzQd/KZeGvSTQ== | Bob          |

为加密表中已经加密的列，添加like列 #

使用密钥管理器配置将非加密表转为加密表

CONVERT TABLE t_data_type BY ENCRYPT RULE (
COLUMNS(
(NAME=varchar_data, DATA_TYPE='varchar(100)', PLAIN=varchar_data,
CIPHER=cipher_VARCHAR_data,CIPHER_DATA_TYPE='varchar(100)',
ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
)), QUERY_WITH_PLAIN=true, START_ENCRYPTING_JOB=TRUE);

查看洗数任务

mysql> show encrypting list;
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns                                                      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| j5202p000008f1738eecad3985aaf91e46d829fd92 | t_data_type | t_data_type.DECIMAL_data,VARCHAR_data,INT_data,longtext_data | 1              | true   | 2024-03-20 17:32:40 | NULL      |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> show encrypting status j5202p000008f1738eecad3985aaf91e46d829fd92;
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| table_name  | status   | active | processed_records_count | inventory_finished_percentage | error_message |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| t_data_type | FINISHED | true   | 20                      | 100                           |               |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
1 row in set (0.04 sec)

确认洗数任务执行完成后，提交洗数任务

commit encrypting j5202p000008f1738eecad3985aaf91e46d829fd92;

将使用密文查询

ALTER ENCRYPT RULE t_data_type SET QUERY_WITH_PLAIN = false;

删除明文列

CONVERT TABLE t_data_type DROP PLAIN COLUMNS(varchar_data);

从 Proxy 查询出解密后的数据

mysql> select * from t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | John         |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | Alice        |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

从 MySQL 查询出密文存储的数据

mysql> select * from db_encrypt.t_data_type;
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | cipher_VARCHAR_data      |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+
|  1 |       25 |        11.11 | john@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.comjohn@example.com                                                                                                                                                 | 7bDV86+VlIfykt2qARsIyg== |
|  2 |       30 |        12.12 | alice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.comalice@example.com                                                                                                                               | oZFK0ARtiUd9BK+xhY7nxg== |
|  3 |       28 |        13.13 | bob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.combob@example.com

新增一列的加密列的like列

CONVERT TABLE t_data_type ADD like (
COLUMNS(
(name=varchar_data,
LIKE_QUERY=varchar_data_like,
LIKE_QUERY_DATA_TYPE='varchar(200)',
LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE')))
), START_ENCRYPTING_JOB=TRUE
);

查看洗数任务

mysql> show encrypting list;
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns                                                      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
| j5202p000008f1738eecad3985aaf91e46d829fd92 | t_data_type | t_data_type.DECIMAL_data,VARCHAR_data,INT_data,longtext_data | 1              | true   | 2024-03-20 17:32:40 | NULL      |
+--------------------------------------------+-------------+--------------------------------------------------------------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> show encrypting status j5202p000008f1738eecad3985aaf91e46d829fd92;
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| table_name  | status   | active | processed_records_count | inventory_finished_percentage | error_message |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
| t_data_type | FINISHED | true   | 20                      | 100                           |               |
+-------------+----------+--------+-------------------------+-------------------------------+---------------+
1 row in set (0.04 sec)

确认洗数任务执行完成后，提交洗数任务

commit encrypting j5202p000008f1738eecad3985aaf91e46d829fd92;

查看加密规则

mysql> show encrypt rules;
+-------------+--------------+-----------------+---------------------+------------------+--------------+-----------------+-----------------------+--------------------------+-------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+------------------+
| table       | logic_column | logic_data_type | cipher_column       | cipher_data_type | plain_column | plain_data_type | assisted_query_column | assisted_query_data_type | like_query_column | like_query_data_type | encryptor_type | encryptor_props         | assisted_query_type | assisted_query_props | like_query_type  | like_query_props | query_with_plain |
+-------------+--------------+-----------------+---------------------+------------------+--------------+-----------------+-----------------------+--------------------------+-------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+------------------+
| t_data_type | varchar_data | varchar(100)    | cipher_VARCHAR_data | varchar(100)     |              | varchar(100)    |                       |                          | varchar_data_like | varchar(200)         | AES            | aes-key-value=123456abc |                     |                      | CHAR_DIGEST_LIKE |                  | false            |
+-------------+--------------+-----------------+---------------------+------------------+--------------+-----------------+-----------------------+--------------------------+-------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+------------------+
1 row in set (0.01 sec)

从 Proxy 和从 MySQL 分别查询数据从 Proxy 查询数据

mysql> select * from t_data_type where VARCHAR_data  like 'E%';
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  4 |       21 |        14.14 | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              | Emily        |
| 12 |       22 |        15.15 | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com | Emma         |
| 19 |       39 |        15.15 | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                | Ethan        |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
3 rows in set (0.01 sec)

从 MySQL 查询数据，无法执行like

mysql> select * from t_data_type where VARCHAR_data  like 'E%';
ERROR 1054 (42S22): Unknown column 'VARCHAR_data' in 'where clause'

添加明文列

CONVERT TABLE t_data_type ADD PLAIN COLUMNS(varchar_data), START_DECRYPTING_JOB=TRUE;

查看反洗数任务

mysql> show DECRYPTING list;
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
| id                                         | tables      | columns      | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
| j5302p0000418e5362856b22af6b28bec7371d92fc | t_data_type | varchar_data | 1              | true   | 2024-03-21 16:28:25 | NULL      |
+--------------------------------------------+-------------+--------------+----------------+--------+---------------------+-----------+
1 row in set (0.04 sec)

mysql> show DECRYPTING status j5302p0000418e5362856b22af6b28bec7371d92fc;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | encrypt_db  | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.04 sec)

确认洗数任务执行完成后，提交反洗数任务

mysql> COMMIT DECRYPTING j5302p0000418e5362856b22af6b28bec7371d92fc;
Query OK, 0 rows affected (0.17 sec)

修改规则使用明文查询

ALTER ENCRYPT RULE t_data_type(varchar_data) SET QUERY_WITH_PLAIN = true;

删除这一列的加密配置

CONVERT TABLE t_data_type DROP ENCRYPT RULE;

从 Proxy 和从 MySQL 分别查询数据，查询结果一致从 Proxy 查询数据

mysql> select * from t_data_type where VARCHAR_data  like 'E%';
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  4 |       21 |        14.14 | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              | Emily        |
| 12 |       22 |        15.15 | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com | Emma         |
| 19 |       39 |        15.15 | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                | Ethan        |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
3 rows in set (0.01 sec)

从 MySQL 查询数据

mysql> select * from t_data_type where VARCHAR_data  like 'E%';
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| id | INT_data | DECIMAL_data | longtext_data                                                                                                                                                                                                                                                                                                                                                                                                                                    | varchar_data |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
|  4 |       21 |        14.14 | emily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.comemily@example.com                                                                                                              | Emily        |
| 12 |       22 |        15.15 | emma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.comemma@example.com | Emma         |
| 19 |       39 |        15.15 | ethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.comethan@example.com                                                                                                                                                | Ethan        |
+----+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
3 rows in set (0.00 sec)

表加密后索引支持说明 #

加密列索引效率需要根据加密算法实测
同态、保型、保序算法目前在研发中，同态和保序可以快速实现。

详细说明

FAQ #

洗数操作是否可以在线进行？对性能是否有影响？

可在线进行，SphereEx-DBPlusEngine 提供了线程配置方法，可尽量减少对业务的性能影响。同时，建议洗数据操作尽可能的在停机状态下操作，性能会更好。

如果洗数操作意外中断，是否可以继续洗数？

可以，可重新执行洗数命令继续操作。

洗数过程中如果发生了数据库切换，洗数任务是否会中断？

不会，但前提是已配置了主从切换的数据库发现的插件。