共计 8913 个字符，预计需要花费 23 分钟才能阅读完成。
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-all2.1.3 配置安装源
In hd0[1-2]
yum install -y https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm2.1.4 配置安装包
In hd01
yum install -y mysql-community-server mysql-community-devel mysql-community-clientIn 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 version2.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.pem2.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 mysqld2.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 -p2) 禁用 SSL 链接
In hd01
mysql -uscm -hhd01.cmdschool.org -p --ssl-mode=disable3) 使用证书登录（可选，不用也能 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.org4) 配置文件指定证书登录（可选，不用也能 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.pem2.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/IPServer 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
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-04/143147.htm
