我要评论nginx代理mysql端口并开启ssl
第一步：配置nginx的stream
- 在安装nginx时需要安装stream模块，stream配置如下：
#> ./configure --prefix=/usr/local/nginx --with-http_ssl_module --with-stream --with-stream_ssl_module
注：stream模块只有nginx1.9之后的版本才支持
- nginx安装完成后stream模块的配置信息如下：
stream {
# 负载均衡
upstream mysqlbackend {
hash $remote_addr consistent;
server 127.0.0.1:61666;
# server 192.168.0.10:3306 weight=5;
# server 192.168.0.11:3306 max_fails=3 fail_timeout=30s;
}
# 对外提供端口映射的服务配置，同时也支持ssl数据加密传输
server {
# 监听端口，可自定义
listen 56789;
# 反向代理，负载均衡
proxy_pass mysqlbackend;
# 其他配置根据需要添加
}
}
注：stream和http同级，stream中的配置可参考http的部分配置。
第二步：开启mysql的ssl
- 登陆mysql检查是否开启ssl
#> mysql -uroot -p # 直接回车 mysql> show variables like '%ssl%'; # 查看mysql是否开启ssl +---------------+----------+ | variable_name | value | +---------------+----------+ | have_openssl | disabled | | have_ssl | disabled | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | | +---------------+----------+ 9 rows in set (0.00 sec)
注：当have_opensslhe have_ssl为“disabled”时，表示当前mysql未开启ssl
- 如果没有开启ssl，开启ssl
#> cd /usr/local/mysql/bin # 进入mysql的bin路径 #> ./mysql_ssl_rsa_setup # 执行mysql开启ssl证书 generating a 2048 bit rsa private key .......................+++ ............................................................................+++ writing new private key to 'ca-key.pem' ----- generating a 2048 bit rsa private key ...............................................+++ .....+++ writing new private key to 'server-key.pem' ----- generating a 2048 bit rsa private key .............+++ ...................................+++ writing new private key to 'client-key.pem' ----- #> chown -r mysql:mysql *.pem # 给mysql生成的证书更换用户组
注：证书生成路径为：mysql安装路径下的data目录中，后缀为pem的文件
- mysql生成的pem文件说明
ca.pem self-signed ca certificate ca-key.pem ca private key server-cert.pem server certificate server-key.pem server private key client-cert.pem client certificate client-key.pem client private key 启动时产生rsa密钥对 private_key.pem private member of private/public key pair public_key.pem public member of private/public key pair 参考http://dev.mysql.com/doc/refman/5.7/en/creating-ssl-rsa-files-using-mysql.html 使用ssl连接http://dev.mysql.com/doc/refman/5.7/en/using-ssl-connections.html 客户端证书导出 # sz ca.pem # sz client-cert.pem # sz client-key.pem
- 在my.cnf文件中配置ssl
[mysqld] ssl-ca=/usr/local/mysql/data/ca.pem ssl-cert=/usr/local/mysql/data/server-cert.pem ssl-key=/usr/local/mysql/data/server-key.pem [client] ssl-ca=/usr/local/mysql/data/ca.pem ssl-cert=/usr/local/mysql/data/client-cert.pem ssl-key=/usr/local/mysql/data/client-key.pem
5.重新启动mysql服务，并查看mysql的ssl开启情况
#> systemctl start mysqld # 重启mysql服务器 #> mysql -uroot -p # 登陆mysql数据库 mysql> show variables like '%ssl%'; # 查询mysql的ssl是否开启 +---------------+---------------------------------------+ | variable_name | value | +---------------+---------------------------------------+ | have_openssl | yes | | have_ssl | yes | | ssl_ca | /usr/local/mysql/data/ca.pem | | ssl_capath | | | ssl_cert | /usr/local/mysql/data/server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /usr/local/mysql/data/server-key.pem | +---------------+---------------------------------------+ 9 rows in set (0.00 sec)
第三步：指定mysql用户使用ssl访问
#> grant all privileges on *.* to '用户名'@'%' identified by '密码' require ssl; # 创建只能用ssl访问的用户 #> flush privileges; # 刷新数据，使其生效
客户端验证
λ mysql -h172.16.8.244 -p61666 -ussltest -p
enter password: ********
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 3
server version: 5.7.27-log mysql community server (gpl)
copyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
mysql> status;
--------------
mysql ver 14.14 distrib 5.7.28, for win64 (x86_64)
connection id: 3
current database:
current user: ssltest@172.16.8.18
ssl: cipher in use is dhe-rsa-aes256-sha
using delimiter: ;
server version: 5.7.27-log mysql community server (gpl)
protocol version: 10
connection: 172.16.8.244 via tcp/ip
server characterset: utf8mb4
db characterset: utf8mb4
client characterset: gbk
conn. characterset: gbk
tcp port: 61666
uptime: 18 min 11 sec
threads: 1 questions: 10 slow queries: 0 opens: 116 flush tables: 1 open tables: 109 queries per second avg: 0.009
--------------
第四步：在应用中配置jdbc连接
指定用户使用ssl进行数据库连接后，需要在对应的jdbc连接上增加“ &usessl=true ”配置，否则mysql将拒绝连接。例如：
jdbc:mysql://127.0.0.1:3306/[数据用户名]?servertimezone=gmt%2b8&characterencoding=utf-8&usessl=false&failoverreadonly=false&autoreconnect=true&roundrobinloadbalance=true&nullcatalogmeanscurrent=true
第五步：mysql客户端工具
需要在客户端工具类上设置上【使用ssl】连接数据库，如navicat工具上如下图：
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论