Nginx代理Mysql端口并开启SSL

Nginx代理Mysql端口并开启SSL

第一步：配置nginx的stream

1. 在安装nginx时需要安装stream模块，stream配置如下：

#> ./configure --prefix=/usr/local/nginx --with-http_ssl_module  --with-stream --with-stream_ssl_module

注：stream模块只有nginx1.9之后的版本才支持

2. 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

1. 登陆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

2. 如果没有开启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的文件

3. 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

4. 在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工具上如下图：