MySQL 5.7.x开启SSL连接

1 理论部分

1.1 SSL的理解

1.1.1 基本概念

SSL即Secure Socket Layer)- 安全套接字层(由Netscape提出

1.1.2 SSL的作用

SSL – 实现客户端和服务器之间的安全通讯(加密和完整性校验)

1.1.3 协议组成

1) SSL Record Protocol(记录协议)

– 建立于TCP之上

– 为高层提供数据封装、压缩、加密等基本功能

2) SSL Handshake Protocol(握手协议)

– 建立于Record Protocol协议之上

– 用户数据传输前的双方身份认证、协商加密算法、交换机密秘钥等

1.1.4 ISO层次

SSL工作于网络层和应用层之间

1.2 MySQL SSL

与包括MySQL 5.6版本在内的旧版本相比，5.7.x增加了连接加密功能，防止通讯过程中数据库信息被窃取

2 实践部分

2.1 环境配置

2.1.1 基本信息

OS=CentOS 7.3 x86_64

IP Address=10.168.0.2[4-5]

HostName=hd0[1-2].cmdschool.org

注：以上隐含名称解析服务

2.1.2 防火墙配置

firewall-cmd –permanent –add-service mysql
firewall-cmd –reload
firewall-cmd –list-all
2.1.3 配置安装源

In hd0[1-2]

yum install -y https: //dev .mysql.com /get/mysql57-community-release-el7-10 .noarch.rpm
2.1.4 配置安装包

In hd01

yum install -y mysql-community-server mysql-community-devel mysql-community-client
In hd02

yum install -y mysql-community-client
2.1.5 启动数据库

In hd01

systemctl start mysqld
systemctl enable mysqld
2.1.6 初始化数据库

In hd01

获取临时密码：

cat /var/log/mysqld .log | grep ‘A temporary password’
显示如下：

2017-04-22T07:10:18.747550Z 1 [Note] A temporary password is generated for root@localhost: ufqLq&R6tgl%
初始化数据库：

mysql_secure_installation
向导如下：

[…]
Enter password for user root:ufqLq&R6tgl%
[…]
New password:*******
Re-enter new password:*******
[…]
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
New password:*******
Re-enter new password:*******
[…]
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
[…]
Remove anonymous users ? (Press y|Y for Yes, any other key for No) : y
[…]
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
[…]
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
[…]
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
[…]
2.1.7 关闭密码复杂度要求

In hd01

cp /etc/my .cnf /etc/my .cnf.default
vim /etc/my .cnf
加入如下配置

[mysqld]
# Disable password validaion plugin
validate-password=off
重启数据库服务

systemctl restart mysqld
注：此操作方便后面配置用户权限，降低MySQL服务对密码复杂度的要求，这也是5.7的新特征，说真的笔者认同MySQL官方的安全主张，但不喜欢（麻烦）。

验证插件的禁用

show plugins;
显示如下：

+—————————-+———-+——————–+———————-+———+
| Name | Status | Type | Library | License |
+—————————-+———-+——————–+———————-+———+
| validate_password | DISABLED | VALIDATE PASSWORD | validate_password.so | GPL |
+—————————-+———-+——————–+———————-+———+
45 rows in set (0.00 sec)
2.2 配置MySQL SSL

2.2.1 确保本机安装openssl

In hd0[1-2]
查询MySQL是基于那种SSL

mysql -uroot -p
show status like ‘rsa_public_key’ ;
返回如下提示：

Empty set (0.00 sec)
以上表明官方的编译基于yaSSL，如果是基于openSSL，以下命令查看openSSL的版本

openssl version
2.2.2 生成所需的证书

In hd01

mysql_ssl_rsa_setup
ls -l /var/lib/mysql/ *.pem
会看到如下证书

-rw——- 1 mysql mysql 1679 Apr 22 10:38 /var/lib/mysql/ca-key .pem
-rw-r–r– 1 mysql mysql 1074 Apr 22 10:38 /var/lib/mysql/ca .pem
-rw-r–r– 1 mysql mysql 1078 Apr 22 10:38 /var/lib/mysql/client-cert .pem
-rw——- 1 mysql mysql 1679 Apr 22 10:38 /var/lib/mysql/client-key .pem
-rw——- 1 mysql mysql 1675 Apr 22 10:38 /var/lib/mysql/private_key .pem
-rw-r–r– 1 mysql mysql 451 Apr 22 10:38 /var/lib/mysql/public_key .pem
-rw-r–r– 1 mysql mysql 1078 Apr 22 10:38 /var/lib/mysql/server-cert .pem
-rw——- 1 mysql mysql 1679 Apr 22 10:38 /var/lib/mysql/server-key .pem
2.2.3 MySQL配置文件中开启SSL

In hd01

vim /etc/my .cnf
加入如下配置

[mysqld]
ssl-ca = /var/lib/mysql/ca .pem
ssl-cert = /var/lib/mysql/server-cert .pem
ssl-key = /var/lib/mysql/server-key .pem
重启服务

systemctl restart mysqld
2.2.4 确认是否开启SSL

In hd01

mysql -uroot -p
show global variables like ‘have_%ssl’ ;
显示如下：

+—————+——-+
| Variable_name | Value |
+—————+——-+
| have_openssl | YES |
| have_ssl | YES |
+—————+——-+
2 rows in set (0.00 sec)
2.2.5 查看SSL的加密方式

In hd01

mysql -uroot -p
show global variables like ‘tls_version’ ;
显示如下：

+—————+—————+
| Variable_name | Value |
+—————+—————+
| tls_version | TLSv1,TLSv1.1 |
+—————+—————+
1 row in set (0.00 sec)
2.2.6 配置SSL用户

In hd01

mysql -uroot -p
grant all privileges on *.* to scm@ ‘hd01.cmdschool.org’ identified by ‘scm’ require none;
grant all privileges on *.* to scm@ ‘hd02.cmdschool.org’ identified by ‘scm’ require ssl;
flush privileges;
查看是否开启强制用户使用SSL

select user,host,ssl_type from mysql.user where user= ‘scm’ ;
显示如下：

+——+——————–+———-+
| user | host | ssl_type |
+——+——————–+———-+
| scm | hd01.cmdschool.org | |
| scm | hd02.cmdschool.org | ANY |
+——+——————–+———-+
2 rows in set (0.00 sec)
注：帐号“scm@hd01.cmdschool.org”不强制使用SSL链接而“scm@hd02.cmdschool.org”被强制使用SSL链接，不使用SSL无法登陆。

2.2.7 登录测试

1) 使用SSL链接

In hd02

mysql -uscm -hhd01.cmdschool.org -p
2) 禁用SSL链接

In hd01

mysql -uscm -hhd01.cmdschool.org -p –ssl-mode=disable
3) 使用证书登录（可选，不用也能SSL登陆）

In hd01

mysql –ssl-ca= /var/lib/mysql/ca .pem \
–ssl-cert= /var/lib/mysql/client-cert .pem \
–ssl-key= /var/lib/mysql/client-key .pem \
-uscm -p -hhd01.cmdschool.org
4) 配置文件指定证书登录（可选，不用也能SSL登陆）

In hd01

vim ~/.my.cnf
输入如下配置：

[client]
ssl-ca = /var/lib/mysql/ca .pem
ssl-cert = /var/lib/mysql/client-cert .pem
ssl-key = /var/lib/mysql/client-key .pem
2.2.8 客户端查看SSL状态

1) 从状态中查看

In hd02

status
显示如下：

————–
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper
Connection id : 8
Current database:
Current user: scm@HD02.cmdschool.org
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: hd01.cmdschool.org via TCP /IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 12 min 51 sec

Threads: 6 Questions: 1446 Slow queries: 0 Opens: 156 Flush tables: 1 Open tables: 149 Queries per second avg: 1.875
————–
注：正常会看到“SSL: Cipher in use is DHE-RSA-AES256-SHA”字样

2) 查看SSL版本

In hd02

show session status like ‘ssl_version’ ;
显示如下：

+—————+———+
| Variable_name | Value |
+—————+———+
| Ssl_version | TLSv1.1 |
+—————+———+
1 row in set (0.00 sec)
3) 查看加密方式

In hd02

show session status like ‘ssl_cipher’ ;
显示如下：

+—————+——————–+
| Variable_name | Value |
+—————+——————–+
| Ssl_cipher | DHE-RSA-AES256-SHA |
+—————+——————–+
1 row in set (0.01 sec)
4) 支持的加密方式

In hd02

show session status like ‘ssl_cipher_list’ ;
显示如下：

+—————–+———————————————————————————————————————————————————————————————————————————————+
| Variable_name | Value |
+—————–+———————————————————————————————————————————————————————————————————————————————+
| Ssl_cipher_list | DHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA:AES128-RMD:DES-CBC3-RMD:DHE-RSA-AES256-RMD:DHE-RSA-AES128-RMD:DHE-RSA-DES-CBC3-RMD:AES256-SHA:RC4-SHA:RC4-MD5:DES-CBC3-SHA:DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA:AES128-SHA:AES256-RMD |
+—————–+———————————————————————————————————————————————————————————————————————————————+
1 row in set (0.00 sec)
3 附录

3.1 JDBC的链接处理方式

3.1.1 错误提示（Error）

JAVA_HOME= /usr/java/jdk1 .8.0_121
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
Sat Apr 22 19:09:20 CST 2017 WARN: Establishing SSL connection without server ‘s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’ t set . For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’ . You need either to explicitly disable SSL by setting useSSL= false , or set useSSL= true and provide truststore for server certificate verification.
3.1.2 JDBC客户端的解决方法

连接字符串url中加入ssl=true或false：

url=jdbc:mysql: //127 .0.0.1:3306 /framework ?characterEncoding=utf8&useSSL= true
注：本文只是笔着希望可以在MySQL的服务端解决以上错误提示而整理，如果网友有方案提供，笔者感激不尽。

参阅文档

MySQL Database

https://www.cloudera.com/documentation/enterprise/latest/topics/cm_ig_mysql.html

https://dev.mysql.com/doc/refman/5.7/en/validate-password-plugin.html

https://dev.mysql.com/doc/refman/5.7/en/secure-connection-options.html

MySQL开启SSL

https://dev.mysql.com/doc/refman/5.7/en/using-secure-connections.html