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 确保本机安装SSL

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

SSL
http://cmdschool.blog.51cto.com/2420395/1785732