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 防火墙配置
|
1
2
3
|
firewall-cmd --permanent --add-service mysql
firewall-cmd --reload
firewall-cmd --list-all
|
2.1.3 配置安装源
In hd0[1-2]
|
1
|
yum
install
-y https:
//dev
.mysql.com
/get/mysql57-community-release-el7-10
.noarch.rpm
|
2.1.4 配置安装包
In hd01
|
1
|
yum
install
-y mysql-community-server mysql-community-devel mysql-community-client
|
In hd02
|
1
|
yum
install
-y mysql-community-client
|
2.1.5 启动数据库
In hd01
|
1
2
|
systemctl start mysqld
systemctl
enable
mysqld
|
2.1.6 初始化数据库
In hd01
获取临时密码：
|
1
|
cat
/var/log/mysqld
.log |
grep
'A temporary password'
|
显示如下：
|
1
|
2017-04-22T07:10:18.747550Z 1 [Note] A temporary password is generated
for
root@localhost: ufqLq&R6tgl%
|
初始化数据库：
|
1
|
mysql_secure_installation
|
向导如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
[...]
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
|
1
2
|
cp
/etc/my
.cnf
/etc/my
.cnf.default
vim
/etc/my
.cnf
|
加入如下配置
|
1
2
3
|
[mysqld]
# Disable password validaion plugin
validate-password=off
|
重启数据库服务
|
1
|
systemctl restart mysqld
|
注：此操作方便后面配置用户权限，降低MySQL服务对密码复杂度的要求，这也是5.7的新特征，说真的笔者认同MySQL官方的安全主张，但不喜欢（麻烦）。
验证插件的禁用
|
1
|
show plugins;
|
显示如下：
|
1
2
3
4
5
6
|
+----------------------------+----------+--------------------+----------------------+---------+
| 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 确保本机安装SSL
In hd0[1-2]
查询MySQL是基于那种SSL
|
1
2
|
mysql -uroot -p
show status like
'rsa_public_key'
;
|
返回如下提示：
|
1
|
Empty
set
(0.00 sec)
|
以上表明官方的编译基于yaSSL，如果是基于openSSL，以下命令查看openSSL的版本
|
1
|
openssl version
|
2.2.2 生成所需的证书
In hd01
|
1
2
|
mysql_ssl_rsa_setup
ls
-l
/var/lib/mysql/
*.pem
|
会看到如下证书
|
1
2
3
4
5
6
7
8
|
-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
|
1
|
vim
/etc/my
.cnf
|
加入如下配置
|
1
2
3
4
|
[mysqld]
ssl-ca =
/var/lib/mysql/ca
.pem
ssl-cert =
/var/lib/mysql/server-cert
.pem
ssl-key =
/var/lib/mysql/server-key
.pem
|
重启服务
|
1
|
systemctl restart mysqld
|
2.2.4 确认是否开启SSL
In hd01
|
1
2
|
mysql -uroot -p
show global variables like
'have_%ssl'
;
|
显示如下：
|
1
2
3
4
5
6
7
|
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| have_ssl | YES |
+---------------+-------+
2 rows
in
set
(0.00 sec)
|
2.2.5 查看SSL的加密方式
In hd01
|
1
2
|
mysql -uroot -p
show global variables like
'tls_version'
;
|
显示如下：
|
1
2
3
4
5
6
|
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| tls_version | TLSv1,TLSv1.1 |
+---------------+---------------+
1 row
in
set
(0.00 sec)
|
2.2.6 配置SSL用户
In hd01
|
1
2
3
4
|
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
|
1
|
select
user,host,ssl_type from mysql.user where user=
'scm'
;
|
显示如下：
|
1
2
3
4
5
6
7
|
+------+--------------------+----------+
| 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
|
1
|
mysql -uscm -hhd01.cmdschool.org -p
|
2) 禁用SSL链接
In hd01
|
1
|
mysql -uscm -hhd01.cmdschool.org -p --ssl-mode=disable
|
3) 使用证书登录（可选，不用也能SSL登陆）
In hd01
|
1
2
3
4
|
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
|
1
|
vim ~/.my.cnf
|
输入如下配置：
|
1
2
3
4
|
[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
|
1
|
status
|
显示如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
--------------
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
|
1
|
show session status like
'ssl_version'
;
|
显示如下：
|
1
2
3
4
5
6
|
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Ssl_version | TLSv1.1 |
+---------------+---------+
1 row
in
set
(0.00 sec)
|
3) 查看加密方式
In hd02
|
1
|
show session status like
'ssl_cipher'
;
|
显示如下：
|
1
2
3
4
5
6
|
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| Ssl_cipher | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row
in
set
(0.01 sec)
|
4) 支持的加密方式
In hd02
|
1
|
show session status like
'ssl_cipher_list'
;
|
显示如下：
|
1
2
3
4
5
6
|
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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）
|
1
2
3
|
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：
|
1
|
url=jdbc:mysql:
//127
.0.0.1:3306
/framework
?characterEncoding=utf8&useSSL=
true
|
注：本文只是笔着希望可以在MySQL的服务端解决以上错误提示而整理，如果网友有方案提供，笔者感激不尽。
本文转自 tanzhenchao 51CTO博客，原文链接：http://blog.51cto.com/cmdschool/1918504，如需转载请自行联系原作者
