Mysql SSL

影响近20%的性能

线上nacos

url=jdbc:mysql://10.20.20.43:30731/?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=true&verifyServerCertificate=true&requireSSL=true&trustCertificateKeyStoreUrl=http://192.168.0.1/mysql_cert/truststore.jks&trustCertificateKeyStorePassword=123456&clientCertificateKeyStoreUrl=http://192.168.0.1/mysql_cert/keystore.jks&clientCertificateKeyStorePassword=123456

线上mysql

证书位置：http://192.168.0.1/mysql_cert/

#仅让test用户强制使用证书登录
mysql> 
alter user 'test'@'%' require x509;
flush privileges;

#并导入证书
[client]
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/client-cert.pem
ssl-key=/var/lib/mysql/client-key.pem
[mysqld]
全部都为
require_secure_transport=ON
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

登录方式

mysql --ssl-ca=/var/lib/mysql/cert/ca.pem --ssl-cert=/var/lib/mysql/cert/client-cert.pem --ssl-key=/var/lib/mysql/cert/client-key.pem --ssl-cipher=AES128-SHA -utest-p123456

其他

证书生成

官网参考：https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem -subj "/C=CH/ST=CQ/L=CQ/O=test/OU=Go/CN=ca/emailAddress=test.com"

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem -subj "/C=CH/ST=CQ/L=CQ/O=test/OU=Go/CN=Server/emailAddress=test.com"

openssl rsa -in server-key.pem -out server-key.pem

openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem -subj "/C=CH/ST=CQ/L=CQ/O=test/OU=Go/CN=Client/emailAddress=test.com"

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

#验证证书是否正确
[root@node1 data]# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

#查看证书的内容（例如，检查证书有效的日期范围）
openssl x509 -text -in ca.pem
openssl x509 -text -in server-cert.pem
openssl x509 -text -in client-cert.pem

JDBC 导入 Java 默认信任库

服务器验证

keytool -importcert -alias MySQLCACert -file ca.pem -keystore truststore.jks -storepass 123456

url=jdbc:mysql://10.20.20.43:30731/?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=true&verifyServerCertificate=true&requireSSL=true&trustCertificateKeyStoreUrl=http://192.168.0.1/mysql_cert/truststore.jks&trustCertificateKeyStorePassword=123456

设置客户端身份验证

openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -name "mysqlclient" -passout pass:123456 -out client-keystore.p12

keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 -srcstorepass 123456 -destkeystore keystore.jks -deststoretype JKS -deststorepass 123456

url=jdbc:mysql://10.20.20.43:30731/?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=true&verifyServerCertificate=true&requireSSL=true&trustCertificateKeyStoreUrl=http://192.168.0.1/mysql_cert/truststore.jks&trustCertificateKeyStorePassword=123456&clientCertificateKeyStoreUrl=http://192.168.0.1/mysql_cert/keystore.jks&clientCertificateKeyStorePassword=123456

参数含义

set global validate_password_length=4;set global validate_password_policy=0;
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'test'@'%';
ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
ALTER USER 'test'@'%' REQUIRE SSL;

alter user 'test'@'%' require none; #非强制，也是默认选项
alter user 'test'@'%' require ssl;  #强制用户使用SSL连接
alter user 'test'@'%' require x509; #使用X509方式的SSL，客户端在登录的时候必须提供CA文件才可以登录，CA文件就是生成的PEM了

SELECT ssl_type From mysql.user Where user="test"; #查看用户情况
alter user 'test'@'%' require none; #取消ssl && 或者版本不同#(update user set ssl_type='' where user='test';)
FLUSH PRIVILEGES;

区别

1、alter user ‘test’@‘%’ require none

非强制，也是默认选项

2、alter user ‘test’@‘%’ require ssl

强制用户使用SSL连接，但可以不用强制填写证书

nacos jdbc配置

添加useSSL=true

url=jdbc:mysql://10.20.20.43:30731/?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=true

navicat工具使用

强制用户使用SSL连接，但可以不用强制填写证书，勾选使用ssl即可

3、alter user ‘test’@‘%’ require x509

使用X509方式的SSL，客户端在登录的时候必须提供CA文件才可以登录，CA文件就是生成的PEM了

nacos jdbc配置

url=jdbc:mysql://10.20.20.43:30731/?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=true&verifyServerCertificate=true&requireSSL=true&trustCertificateKeyStoreUrl=http://192.168.0.1/mysql_cert/truststore.jks&trustCertificateKeyStorePassword=123456&clientCertificateKeyStoreUrl=http://192.168.0.1/mysql_cert/keystore.jks&clientCertificateKeyStorePassword=123456

navicat工具使用

• nacos jdbc 配置