MySQL8.4创建keyring给InnoDB表进行静态数据加密

参考文档:

https://developer.hashicorp.com/vault/install

https://dev.mysql.com/doc/refman/8.4/en/keyring-plugin-installation.html

https://dev.mysql.com/doc/refman/8.4/en/keyring-hashicorp-plugin.html#keyring-hashicorp-vault-configuration

1.首先创建放key的目录

mkdir -p /u01/mysql3308/keyring
cd /u01/mysql3308/keyring

2.创建公司Key company.key和HashiCorp Vault server的key vault.key

openssl genrsa -aes256 -out company.key 4096
openssl genrsa -aes256 -out vault.key 2048

3.使用公司Key company.key创建公司CA证书company.crt

openssl req -x509 -new -nodes -key company.key -sha256 -days 365 -out company.crt

出现这些可以默认回车

Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address []:

4.创建证书签名配置选项

[root@mysql8_3 keyring]# vim request.conf
[req]
distinguished_name = vault
x509_entensions = v3_req
prompt = no
[vault]
C = US
ST = CA
L = RWC
O = Company
CN = 127.0.0.1
[v3_req]
subjectAltName = @alternatives
authorityKeyIdentifier = keyid,issuer
basicConstraints = CA:TRUE
[alternatives]
IP = 127.0.0.1

5.保存后，执行命令生成签名，生成request.csr 签名

openssl req -new -key vault.key -config request.conf -out request.csr

6.创建 HashiCorp Vault服务证书vault.crt

openssl x509 -req -in request.csr -CA company.crt -CAkey company.key -CAcreateserial -out vault.crt -days 365 -sha256

7.为了让公司证书与服务器证书一起在请求中传递，将 company.crt 公司证书的内容附加到vault.crt 服务器证书后面

cat company.crt >> vault.crt

显示成这样

[root@mysql8_3 keyring]# cat vault.crt
-----BEGIN CERTIFICATE-----
…
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
…
-----END CERTIFICATE-----

8.下载和安装HashiCorp Vault程序，我的是Oracle Linux 8，其他安装方式参考网址https://developer.hashicorp.com/vault/install

sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://rpm.releases.hashicorp.com/RHEL/hashicorp.repo
sudo yum -y install vault

9.创建存储文件路径，创建HashiCorp Vault服务配置文件

mkdir -p /u01/mysql3308/keyring/storage
[root@mysql8_3 keyring]# cat config.hcl
listener "tcp" {
address="127.0.0.1:8200"
tls_cert_file="/u01/mysql3308/keyring/vault.crt"
tls_key_file="/u01/mysql3308/keyring/vault.key"
}
storage "file" {
path = "/u01/mysql3308/keyring/storage"
}
ui = true

10.启动HashiCorp Vault服务

[root@mysql8_3 keyring]# vault server -config=config.hcl

将启动一个8200端口的服务

[root@mysql8_3 keyring]# netstat -ntpla| grep 8200

tcp 0 0 127.0.0.1:8200 0.0.0.0:* LISTEN 21264/vault

11.初始化HashiCorp Vault服务

export VAULT_SKIP_VERIFY=1
vault operator init -n 1 -t 1

12.保存后key和token后面要用

[root@mysql8_3 keyring]# vault operator init -n 1 -t 1
WARNING! VAULT_ADDR and -address unset. Defaulting to https://127.0.0.1:8200.
Unseal Key 1: r81ShPQoJ10O+CYPGlQYq+c9Qn5KHO37/Q8uWjK+PnI=
Initial Root Token: hvs.JX41AXNkdKeMhwXCd7u3caHT

13.启用HashiCorp Vault服务，输入上面的key

[root@mysql8_3 keyring]# vault operator unseal

将启动一个8201端口的服务

[root@mysql8_3 data]# netstat -antupl | grep 8201

tcp 0 0 127.0.0.1:8201 0.0.0.0:* LISTEN 21264/vault

14.用上面token登录HashiCorp Vault服务

[root@mysql8_3 keyring]# vault login hvs.JX41AXNkdKeMhwXCd7u3caHT

15.验证Vault服务状态

[root@mysql8_3 keyring]# vault status

16.设置HashiCorp Vault认证和存储

启用AppRole认证方法并检查

[root@mysql8_3 keyring]# vault auth enable approle
[root@mysql8_3 keyring]# vault auth list

启用Vault KeyValue存储引擎

[root@mysql8_3 keyring]# vault secrets enable -version=1 kv

17.创建并设置一个名为mysql的规则，并在keyring_hashicorp插件使用

[root@mysql8_3 keyring]# vault write auth/approle/role/mysql token_num_uses=0 token_ttl=20m token_max_ttl=30m secret_id_num_uses=0

18.添加AppRole安全策略

[root@mysql8_3 keyring]# more mysql.hcl
path "kv/mysql/*" {
capabilities = ["create", "read", "update", "delete", "list"]
}
[root@mysql8_3 keyring]# vault policy write mysql-policy mysql.hcl
[root@mysql8_3 keyring]# vault write auth/approle/role/mysql policies=mysql-policy

获取role-id和生成secret_id

[root@mysql8_3 keyring]# vault read auth/approle/role/mysql/role-id
[root@mysql8_3 keyring]# vault write -f auth/approle/role/mysql/secret-id

图片

19.配置my.cnf，并写入role_id和secret_id,然后重启服务

[mysqld]
early-plugin-load=keyring_hashicorp.so
keyring_hashicorp_role_id='bda74cbf-a88a-5df3-5a40-e1a6fddab487'
keyring_hashicorp_secret_id='e0a512bc-557d-cc7c-07ab-6ccee5eae66c'
keyring_hashicorp_store_path='/v1/kv/mysql'
keyring_hashicorp_auth_path='/v1/auth/approle/login'
[root@mysql8_3 mysql3308]# systemctl start mysqld83308.service
[root@mysql8_3 mysql3308]# systemctl status mysqld83308.service

20.设置 keyring_hashicorp_server_url访问的IP和端口

mysql> SET GLOBAL keyring_hashicorp_server_url = 'https://127.0.0.1:8201';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT keyring_hashicorp_update_config();
+----------------------------------------------------------------------------+
| keyring_hashicorp_update_config()|
+----------------------------------------------------------------------------+
| 0x436F6E66696775726174696F6E2075706461746520776173207375636365737366756C2E |
+----------------------------------------------------------------------------+
1 row in set (0.01 sec)

出现了16进制的日志信息，转换文本为 Configuration update was successful.

21.查看插件

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

+-------------------+---------------+

| PLUGIN_NAME | PLUGIN_STATUS |

+-------------------+---------------+

| keyring_hashicorp | ACTIVE |

+-------------------+---------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM performance_schema.keyring_keys;

+--------------------------------------------------+-----------+----------------+

| KEY_ID | KEY_OWNER | BACKEND_KEY_ID |

+--------------------------------------------------+-----------+----------------+

| INNODBKey-4966325d-1509-11f0-a15f-525400381583-1 | | |

+--------------------------------------------------+-----------+----------------+

1 row in set (0.00 sec)