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
<code>firewall-cmd --permanent --add-service mysql</code>
<code>firewall-cmd --reload</code>
<code>firewall-cmd --list-all</code>
2.1.3 配置安装源
In hd0[1-2]
<code>yum </code><code>install</code> <code>-y https:</code><code>//dev</code><code>.mysql.com</code><code>/get/mysql57-community-release-el7-10</code><code>.noarch.rpm</code>
2.1.4 配置安装包
In hd01
<code>yum </code><code>install</code> <code>-y mysql-community-server mysql-community-devel mysql-community-client</code>
In hd02
<code>yum </code><code>install</code> <code>-y mysql-community-client</code>
2.1.5 启动数据库
<code>systemctl start mysqld</code>
<code>systemctl </code><code>enable</code> <code>mysqld</code>
2.1.6 初始化数据库
获取临时密码:
<code>cat</code> <code>/var/log/mysqld</code><code>.log | </code><code>grep</code> <code>'A temporary password'</code>
显示如下:
<code>2017-04-22T07:10:18.747550Z 1 [Note] A temporary password is generated </code><code>for</code> <code>root@localhost: ufqLq&R6tgl%</code>
初始化数据库:
<code>mysql_secure_installation</code>
向导如下:
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<code>[...]</code>
<code>Enter password </code><code>for</code> <code>user root:ufqLq&R6tgl%</code>
<code>New password:*******</code>
<code>Re-enter new password:*******</code>
<code>Change the password </code><code>for</code> <code>root ? ((Press y|Y </code><code>for</code> <code>Yes, any other key </code><code>for</code> <code>No) : y</code>
<code>Do you wish to </code><code>continue</code> <code>with the password provided?(Press y|Y </code><code>for</code> <code>Yes, any other key </code><code>for</code> <code>No) : y</code>
<code>Remove anonymous </code><code>users</code><code>? (Press y|Y </code><code>for</code> <code>Yes, any other key </code><code>for</code> <code>No) : y</code>
<code>Disallow root login remotely? (Press y|Y </code><code>for</code> <code>Yes, any other key </code><code>for</code> <code>No) :</code>
<code>Remove </code><code>test</code> <code>database and access to it? (Press y|Y </code><code>for</code> <code>Yes, any other key </code><code>for</code> <code>No) : y</code>
<code>Reload privilege tables now? (Press y|Y </code><code>for</code> <code>Yes, any other key </code><code>for</code> <code>No) : y</code>
2.1.7 关闭密码复杂度要求
<code>cp</code> <code>/etc/my</code><code>.cnf </code><code>/etc/my</code><code>.cnf.default</code>
<code>vim </code><code>/etc/my</code><code>.cnf</code>
加入如下配置
<code>[mysqld]</code>
<code># Disable password validaion plugin</code>
<code>validate-password=off</code>
重启数据库服务
<code>systemctl restart mysqld</code>
注:此操作方便后面配置用户权限,降低MySQL服务对密码复杂度的要求,这也是5.7的新特征,说真的笔者认同MySQL官方的安全主张,但不喜欢(麻烦)。
验证插件的禁用
<code>show plugins;</code>
<code>+----------------------------+----------+--------------------+----------------------+---------+</code>
<code>| Name | Status | Type | Library | License |</code>
<code>| validate_password | DISABLED | VALIDATE PASSWORD | validate_password.so | GPL |</code>
<code>45 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
2.2 配置MySQL SSL
2.2.1 确保本机安装SSL
查询MySQL是基于那种SSL
<code>mysql -uroot -p</code>
<code>show status like </code><code>'rsa_public_key'</code><code>;</code>
返回如下提示:
<code>Empty </code><code>set</code> <code>(0.00 sec)</code>
以上表明官方的编译基于yaSSL,如果是基于openSSL,以下命令查看openSSL的版本
<code>openssl version</code>
2.2.2 生成所需的证书
<code>mysql_ssl_rsa_setup</code>
<code>ls</code> <code>-l </code><code>/var/lib/mysql/</code><code>*.pem</code>
会看到如下证书
<code>-rw------- 1 mysql mysql 1679 Apr 22 10:38 </code><code>/var/lib/mysql/ca-key</code><code>.pem</code>
<code>-rw-r--r-- 1 mysql mysql 1074 Apr 22 10:38 </code><code>/var/lib/mysql/ca</code><code>.pem</code>
<code>-rw-r--r-- 1 mysql mysql 1078 Apr 22 10:38 </code><code>/var/lib/mysql/client-cert</code><code>.pem</code>
<code>-rw------- 1 mysql mysql 1679 Apr 22 10:38 </code><code>/var/lib/mysql/client-key</code><code>.pem</code>
<code>-rw------- 1 mysql mysql 1675 Apr 22 10:38 </code><code>/var/lib/mysql/private_key</code><code>.pem</code>
<code>-rw-r--r-- 1 mysql mysql 451 Apr 22 10:38 </code><code>/var/lib/mysql/public_key</code><code>.pem</code>
<code>-rw-r--r-- 1 mysql mysql 1078 Apr 22 10:38 </code><code>/var/lib/mysql/server-cert</code><code>.pem</code>
<code>-rw------- 1 mysql mysql 1679 Apr 22 10:38 </code><code>/var/lib/mysql/server-key</code><code>.pem</code>
2.2.3 MySQL配置文件中开启SSL
<code>ssl-ca = </code><code>/var/lib/mysql/ca</code><code>.pem </code>
<code>ssl-cert = </code><code>/var/lib/mysql/server-cert</code><code>.pem </code>
<code>ssl-key = </code><code>/var/lib/mysql/server-key</code><code>.pem</code>
重启服务
2.2.4 确认是否开启SSL
<code>show global variables like </code><code>'have_%ssl'</code><code>;</code>
<code>+---------------+-------+</code>
<code>| Variable_name | Value |</code>
<code>| have_openssl | YES |</code>
<code>| have_ssl | YES |</code>
<code>2 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
2.2.5 查看SSL的加密方式
<code>show global variables like </code><code>'tls_version'</code><code>;</code>
<code>+---------------+---------------+</code>
<code>| Variable_name | Value |</code>
<code>| tls_version | TLSv1,TLSv1.1 |</code>
<code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
2.2.6 配置SSL用户
<code>grant all privileges on *.* to scm@</code><code>'hd01.cmdschool.org'</code> <code>identified by </code><code>'scm'</code> <code>require none;</code>
<code>grant all privileges on *.* to scm@</code><code>'hd02.cmdschool.org'</code> <code>identified by </code><code>'scm'</code> <code>require ssl;</code>
<code>flush privileges;</code>
查看是否开启强制用户使用SSL
<code>select</code> <code>user,host,ssl_type from mysql.user where user=</code><code>'scm'</code><code>;</code>
<code>+------+--------------------+----------+</code>
<code>| user | host | ssl_type |</code>
<code>| scm | hd01.cmdschool.org | |</code>
<code>| scm | hd02.cmdschool.org | ANY |</code>
注:帐号“[email protected]”不强制使用SSL链接而“[email protected]”被强制使用SSL链接,不使用SSL无法登陆。
2.2.7 登录测试
1) 使用SSL链接
<code>mysql -uscm -hhd01.cmdschool.org -p</code>
2) 禁用SSL链接
<code>mysql -uscm -hhd01.cmdschool.org -p --ssl-mode=disable</code>
3) 使用证书登录(可选,不用也能SSL登陆)
<code>mysql --ssl-ca=</code><code>/var/lib/mysql/ca</code><code>.pem \</code>
<code> </code><code>--ssl-cert=</code><code>/var/lib/mysql/client-cert</code><code>.pem \</code>
<code> </code><code>--ssl-key=</code><code>/var/lib/mysql/client-key</code><code>.pem \</code>
<code> </code><code>-uscm -p -hhd01.cmdschool.org</code>
4) 配置文件指定证书登录(可选,不用也能SSL登陆)
<code>vim ~/.my.cnf</code>
输入如下配置:
<code>[client]</code>
<code>ssl-ca = </code><code>/var/lib/mysql/ca</code><code>.pem</code>
<code>ssl-cert = </code><code>/var/lib/mysql/client-cert</code><code>.pem</code>
<code>ssl-key = </code><code>/var/lib/mysql/client-key</code><code>.pem</code>
2.2.8 客户端查看SSL状态
1) 从状态中查看
<code>status</code>
21
22
<code>--------------</code>
<code>mysql Ver 14.14 Distrib 5.7.18, </code><code>for</code> <code>Linux (x86_64) using EditLine wrapper</code>
<code>Connection </code><code>id</code><code>: 8</code>
<code>Current database:</code>
<code>Current user: [email protected]</code>
<code>SSL: Cipher </code><code>in</code> <code>use is DHE-RSA-AES256-SHA</code>
<code>Current pager: stdout</code>
<code>Using outfile: </code><code>''</code>
<code>Using delimiter: ;</code>
<code>Server version: 5.7.18-log MySQL Community Server (GPL)</code>
<code>Protocol version: 10</code>
<code>Connection: hd01.cmdschool.org via TCP</code><code>/IP</code>
<code>Server characterset: latin1</code>
<code>Db characterset: latin1</code>
<code>Client characterset: utf8</code>
<code>Conn. characterset: utf8</code>
<code>TCP port: 3306</code>
<code>Uptime: 12 min 51 sec</code>
<code>Threads: 6 Questions: 1446 Slow queries: 0 Opens: 156 Flush tables: 1 Open tables: 149 Queries per second avg: 1.875</code>
注:正常会看到“SSL: Cipher in use is DHE-RSA-AES256-SHA”字样
2) 查看SSL版本
<code>show session status like </code><code>'ssl_version'</code><code>;</code>
<code>+---------------+---------+</code>
<code>| Variable_name | Value |</code>
<code>| Ssl_version | TLSv1.1 |</code>
3) 查看加密方式
<code>show session status like </code><code>'ssl_cipher'</code><code>;</code>
<code>+---------------+--------------------+</code>
<code>| Variable_name | Value |</code>
<code>| Ssl_cipher | DHE-RSA-AES256-SHA |</code>
<code>1 row </code><code>in</code> <code>set</code> <code>(0.01 sec)</code>
4) 支持的加密方式
<code>show session status like </code><code>'ssl_cipher_list'</code><code>;</code>
<code>+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</code>
<code>| Variable_name | Value |</code>
<code>| 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 |</code>
3 附录
3.1 JDBC的链接处理方式
3.1.1 错误提示(Error)
<code>JAVA_HOME=</code><code>/usr/java/jdk1</code><code>.8.0_121</code>
<code>Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed </code><code>in</code> <code>8.0</code>
<code>Sat Apr 22 19:09:20 CST 2017 WARN: Establishing SSL connection without server</code><code>'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'</code><code>t </code><code>set</code><code>. For compliance with existing applications not using SSL the verifyServerCertificate property is </code><code>set</code> <code>to </code><code>'false'</code><code>. You need either to explicitly disable SSL by setting useSSL=</code><code>false</code><code>, or </code><code>set</code> <code>useSSL=</code><code>true</code> <code>and provide truststore </code><code>for</code> <code>server certificate verification.</code>
3.1.2 JDBC客户端的解决方法
连接字符串url中加入ssl=true或false:
<code>url=jdbc:mysql:</code><code>//127</code><code>.0.0.1:3306</code><code>/framework</code><code>?characterEncoding=utf8&useSSL=</code><code>true</code>
注:本文只是笔着希望可以在MySQL的服务端解决以上错误提示而整理,如果网友有方案提供,笔者感激不尽。
本文转自 tanzhenchao 51CTO博客,原文链接:http://blog.51cto.com/cmdschool/1918504,如需转载请自行联系原作者