MySQL配置SSL安全连接 – 方山子博客

SSL(Secure Sockets Layer 安全套接层)，是为网络通信提供安全及数据完整性的一种安全协议，其利用公开密钥数据加密(Encryption)技术，确保数据在网络上之传输过程中不会被截取及窃听。

SSL协议提供的服务主要有：

认证用户和服务器，确保数据发送到正确的客户机和服务器；
加密数据以防止数据中途被窃取；
维护数据的完整性，确保数据在传输过程中不被改变。
为了在MySQL服务器和客户端之间建立SSL联接，服务器系统必须满足：

操作系统安装有OpenSSL或yaSSL；
安装的MySQL版本必须支持SSL。
这里使用OpenSSL。

一、检查是否满足要求：

shell>rpm -qa | grep openssl #检查是否安装OpenSSL。MySQL需要openssl的共享库。
openssl-1.0.0-20.el6.x86_64
openssl-devel-1.0.0-20.el6.x86_64
openssl098e-0.9.8e-17.el6.x86_64

mysql> show global variables like ‘have%ssl’;

#检查是否支持ssl。NO表示不支持，DISABLE表示支持但未使用。

为使客户端能够使用SSL方式进行连接，需要配置合适的证书和密钥文件，以及为用户授予合适的权限。

在mysql启动配置文件my.cnf的[mysqld]这段加上ssl，如果mysqldump备份要使用ssl连接，则要在[mysqldump]段里面也记得加上ssl，然后重启启动数据库，使用上面的命令mysql> show global variables like ‘have%ssl’;查看就发现状态变成yes了，说明已经开启了ssl安全连接。

二、为MySQL生成证书和密钥

shell>mkdir -p /db/ssl
shell>cd /db/ssl
#以下创建认证机构的数字认证证书，后续服务器端和客户端的证书都使用该认证机构进行签署。
shell>openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
………+++
…………………………………………………………………………………………………..+++
e is 65537 (0×10001)
shell>openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.
—–
Country Name (2 letter code) [GB]:CN
State or Province Name (full name) [Berkshire]:Shanghai
Locality Name (eg, city) [Newbury]:Shanghai
Organization Name (eg, company) [My Company Ltd]:CA
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server’s hostname) []:
Email Address []:
#以下创建服务器端证书
shell>openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
Generating a 2048 bit RSA private key
……………….+++
…………+++
writing new private key to ‘server-key.pem’
—–
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.
—–
Country Name (2 letter code) [GB]:CN
State or Province Name (full name) [Berkshire]:Shanghai
Locality Name (eg, city) [Newbury]:Shanghai
Organization Name (eg, company) [My Company Ltd]:CH
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server’s hostname) []:mysqlserver
Email Address []:

Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:abc123
An optional company name []:
shell>openssl rsa -in server-key.pem -out server-key.pem #移除server-key中的passphrase【可选】
writing RSA key
shell>openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem #签署服务端证书
Signature ok
subject=/C=CN/ST=Shanghai/L=Shanghai/O=CH/CN=mysqlserver
Getting CA Private Key
#以下创建客户端证书
shell>openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
Generating a 2048 bit RSA private key
………………………………………………………………………………………+++
…+++
writing new private key to ‘client-key.pem’
—–
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.
—–
Country Name (2 letter code) [GB]:CN
State or Province Name (full name) [Berkshire]:Shanghai
Locality Name (eg, city) [Newbury]:Shanghai
Organization Name (eg, company) [My Company Ltd]:CH
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server’s hostname) []:mysqlclient
Email Address []:

Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:abc123
An optional company name []:
shell>openssl rsa -in client-key.pem -out client-key.pem #移除client-key中的passphrase【可选】
writing RSA key
shell>openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem #签署客户端证书
Signature ok
subject=/C=CN/ST=Shanghai/L=Shanghai/O=CH/CN=mysqlclient
Getting CA Private Key
#生成完毕后，验证下
shell>openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

经过上述步骤，就生成了如下文件：

ca-cert.pem在服务器端和客户端都是用–ssl-ca=ca-cert.pem
server-cert.pem,server-key.pem 服务器端指定–ssl-cert=server-cert.pem和–ssl-key=server-key.pem
client-cert.pem,client-key.pem 客户端指定–ssl-cert=client-cert.pem和–ssl-key=client-key.pem
三、配置SSL连接

如下两种方案均可以实现使用SSL进行配置和赋权。
【方案一】
Server:
在服务器端的配置文件my.cnf中添加如下参数：
[mysqld]
ssl-cert=/db/ssl/server-cert.pem
ssl-key=/db/ssl/server-key.pem
重启mysqld。

用户赋权，用GRANT语句的REQUIRE SSL选项
如：
mysql>create user user@localhost identified by ‘abc’;
mysql>grant select on testdb.* to user@localhost require ssl;

Client:
mysql -u user -pabc -P 3300 –ssl-ca=ca-cert.pem

【方案二】
Server:
在服务器端的配置文件my.cnf中添加如下参数：
[mysqld]
ssl-ca=/db/ssl/ca-cert.pem
ssl-cert=/db/ssl/server-cert.pem
ssl-key=/db/ssl/server-key.pem
重启mysqld。

用户赋权，用GRANT语句的REQUIRE x509选项
如：
mysql>create user user@localhost identified by ‘abc’;
mysql>grant select on testdb.* to user@localhost require x509;

Client:
mysql -u user -pabc -P 3300 –ssl-ca=ca-cert.pem –ssl-key=client-key.pem –ssl-cert=client-cert.pem
显然方案二的验证要求更严格，需要指定key和cert。

四、检查

附：SSL协议的工作方式简介
客户端要收发几个握手信号：

发送一个“ClientHello”消息，说明它支持的密码算法列表、压缩方法及最高协议版本，也发送稍后将被使用的随机数。
然后收到一个“ServerHello”消息，包含服务器选择的连接参数，源自客户端初期所提供的“ClientHello”。
当双方知道了连接参数，客户端与服务器交换证书（依靠被选择的公钥系统）。这些证书通常基于X.509，不过已有草案支持以OpenPGP为基础的证书。
服务器请求客户端公钥。客户端有证书即双向身份认证，没证书时随机生成公钥。
客户端与服务器通过公钥保密协商共同的主私钥（双方随机协商），这通过精心谨慎设计的伪随机数功能实现。结果可能使用Diffie-Hellman交换，或简化的公钥加密，双方各自用私钥解密。所有其他关键数据的加密均使用这个“主密钥”。
参考：
http://baike.baidu.com/view/16147.htm
http://zh.wikipedia.org/zh-cn/SSL
http://linux.chinaitlab.com/safe/731541.html
http://www.sqlparty.com/mysql%E9%85%8D%E7%BD%AEssl/

使用过程中的报错:

验证秘钥：
Shell> openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: C = IN, ST = KERALA, L = COCHIN, O = ABCD, OU = OPERATIONAL, CN = SATHISH, emailAddress = salley@126.com
error 18 at 0 depth lookup:self signed certificate
OK
client-cert.pem: C = IN, ST = KERALA, L = COCHIN, O = ABCD, OU = OPERATIONAL, CN = sathish, emailAddress = salley@126.com
error 18 at 0 depth lookup:self signed certificate
OK
在客户端登陆发现报错：ERROR 2026

最后发现Common Name在server端和client的配置不能用一样的，一样的就会报错，连接不上，只要设置不同的Common Name就可以连接了。

见一下参考：

Whatever method you use to generate the certificate and key files, the Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL

发表评论 取消回复

发表评论取消回复