MySQL 5.7.16 SSL 实践指南

 

1，关于SSL/VPN/SSH隧道

         平常应用程序与MySQL服务器要建立一个安全通道的时候，普通的做法通常是适用VPN/SSH隧道，也就是网络隔绝的方式来实现，来最大可能断绝外界对数据库的攻击探测攻击行为。

实际上在MySQL服务器中也有类似的解决方案，通过实施SSL可以加密在服务器与客户机之间来回传输的所有数据，因而防止广域网或数据中心里面可能出现的窃听或数据嗅探行为。此外，SSL还通过SSL证书提供了身份验证机制，因而可以保护用户，远离可能出现的网络钓鱼攻击。

这两种各有优势，如果短时间内链接或者断开MySQL的非常多的话，VPN/SSH隧道也许是更好的选择，毕竟网络层的转发比较起来SSL的验证，效率要高一些。但是如果是一些MySQL的长连接的话，那么基于SSL的加密可能更加合适一些。另外现在比较常用的是VPN网络分层+SSL加密验证结合在一起使用。Bty：SSL除了官方的版本外，其它分支版本比如MariaDB/Percona等分支也有实现了类似的功能。

 

查看是否有ssl，如果为YES，则表示已经启动起来了。

mysql> SHOW VARIABLES LIKE 'have_ssl';               +---------------+----------+ | Variable_name | Value    | +---------------+----------+ | have_ssl      | DISABLED | +---------------+----------+ 1 row in set (0.00 sec)   mysql>

 

实现的大概流程是：

（1）      先为MySQL服务器创建SSL证书和私钥

（2）      在MySQL里面配置SSL，启动服务

（3）      创建用户的时候，带上SSL标签REQUIRE SSL;

（4）      连接数据库的时候，带上SSL

 

 

2，自动创建证书开启SSL验证

# 去mysql软件目录/usr/local/mysql，生成私钥 [root@03_sdwm mysql]# bin/mysql_ssl_rsa_setup --datadir=/etc/mysql-ssl/ Generating a 2048 bit RSA private key ..................+++ ...............................................................................................................+++ writing new private key to 'ca-key.pem' ----- Generating a 2048 bit RSA private key ...........+++ .......+++ writing new private key to 'server-key.pem' ----- Generating a 2048 bit RSA private key ................................................+++ .....................................................................................................................................................................+++ writing new private key to 'client-key.pem' ----- [root@03_sdwm mysql]#   # 查看 [root@03_sdwm mysql]# ll /etc/mysql-ssl/ total 32 -rw-------. 1 mysql mysql 1675 Dec 30 15:54 ca-key.pem -rw-r--r--. 1 mysql mysql 1074 Dec 30 15:54 ca.pem -rw-r--r--. 1 mysql mysql 1078 Dec 30 15:54 client-cert.pem -rw-------. 1 mysql mysql 1679 Dec 30 15:54 client-key.pem -rw-------. 1 mysql mysql 1675 Dec 30 15:54 private_key.pem -rw-r--r--. 1 mysql mysql  451 Dec 30 15:54 public_key.pem -rw-r--r--. 1 mysql mysql 1078 Dec 30 15:54 server-cert.pem -rw-------. 1 mysql mysql 1679 Dec 30 15:54 server-key.pem [root@03_sdwm mysql]#   # 赋予mysql账号权限 [root@03_sdwm mysql]# chown -R mysql:mysql /etc/mysql-ssl/* [root@03_sdwm mysql]#

 

 

查看ssl参数状态，查看have_ssl，为YES，这表示已经开始支持SSL了：

mysql> show variables like '%ssl%'; +---------------+--------------------------------+ | Variable_name | Value                          | +---------------+--------------------------------+ | have_openssl  | YES                            | | have_ssl      | YES                            | | ssl_ca        | /etc/mysql-ssl/ca.pem          | | ssl_capath    |                                | | ssl_cert      | /etc/mysql-ssl/client-cert.pem |                                                  | ssl_cipher    |                                | | ssl_crl       |                                | | ssl_crlpath   |                                | | ssl_key       | /etc/mysql-ssl/client-key.pem  | +---------------+--------------------------------+ 9 rows in set (0.01 sec)   mysql>

 

 

查看SSL服务：

mysql> \s -------------- mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (x86_64) using readline 5.1   Connection id:          3 Current database: Current user:           root@localhost SSL:                    Not in use Current pager:          stdout Using outfile:          '' Using delimiter:        ; Server version:         5.7.16-log MySQL Community Server (GPL) Protocol version:       10 Connection:             Localhost via UNIX socket Server characterset:    utf8mb4 Db     characterset:    utf8mb4 Client characterset:    utf8mb4 Conn.  characterset:    utf8mb4 UNIX socket:            /var/lib/mysql/mysql.sock Uptime:                 4 min 28 sec   Threads: 2  Questions: 9  Slow queries: 0  Opens: 109  Flush tables: 1  Open tables: 102  Queries per second avg: 0.033 --------------   mysql>

 

3，手动创建SSL证书

在5.7以前的MySQL版本都需要手动创建SSL证书，在5.7的版本中，可以采用自动脚本创建SSL证书。

# 创建目录

mkdir /etc/mysql-ssl

chown -R mysql:mysql /etc/mysql-ssl

 

# 安装openssl

yum install -y openssl

 

PS：博客源地址：http://blog.csdn.net/mchdba/article/details/53946202，作者黄杉，谢绝转载。

 

配置,继续创建CA私钥和证书。下面这些命令将创建ca-key.pem和ca-cert.pem，参考官方文档http://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html：

[root@03_sdwm newcerts]# openssl req -new -x509 -nodes -days 3600 \ >          -key ca-key.pem -out ca.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) [XX]:ch State or Province Name (full name) []:shh Locality Name (eg, city) [Default City]:shh Organization Name (eg, company) [Default Company Ltd]:ys Organizational Unit Name (eg, section) []:ys Common Name (eg, your name or your server's hostname) []:ys Email Address []:[e-mail] [root@03_sdwm newcerts]# # Create server certificate, remove passphrase, and sign it # server-cert.pem = public key, server-key.pem = private key

 

 

为服务器创建私钥, openssl req -sha1 -newkey rsa:2048 -days 730-nodes -keyout server-key.pem > server-req.pem，填写信息同上：

[root@03_sdwm newcerts]# 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) [XX]:ch State or Province Name (full name) []:shh Locality Name (eg, city) [Default City]:shh Organization Name (eg, company) [Default Company Ltd]:ys Organizational Unit Name (eg, section) []:ys Common Name (eg, your name or your server's hostname) []:ys Email Address []:[e-mail]   Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:ys0418 An optional company name []:ys [root@03_sdwm newcerts]#   [root@03_sdwm newcerts]# openssl rsa -in server-key.pem -out server-key.pem writing RSA key [root@03_sdwm newcerts]#     [root@03_sdwm newcerts]# openssl x509 -req -in server-req.pem -days 3600 \ > -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem Signature ok subject=/C=ch/ST=shh/L=shh/O=ys/OU=ys/CN=ys/emailAddress=[e-mail] Getting CA Private Key [root@03_sdwm newcerts]#   # Create client certificate, remove passphrase, and sign it # client-cert.pem = public key, client-key.pem = private key   [root@03_sdwm newcerts]# 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) [XX]:ch State or Province Name (full name) []:shh Locality Name (eg, city) [Default City]:shh Organization Name (eg, company) [Default Company Ltd]:ys Organizational Unit Name (eg, section) []:ys Common Name (eg, your name or your server's hostname) []:ys Email Address []:[e-mail]   Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:ys0418 An optional company name []:ys [root@03_sdwm newcerts]#   [root@03_sdwm newcerts]# openssl rsa -in server-key.pem -out server-key.pem writing RSA key [root@03_sdwm newcerts]#   [root@03_sdwm newcerts]# openssl x509 -req -in client-req.pem -days 3600 \ > -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem Signature ok subject=/C=ch/ST=shh/L=shh/O=ys/OU=ys/CN=ys/emailAddress=[e-mail] Getting CA Private Key [root@03_sdwm newcerts]#     # After generating the certificates, verify them: [root@03_sdwm newcerts]# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem server-cert.pem: C = ch, ST = shh, L = shh, O = ys, OU = ys, CN = ys, emailAddress = [e-mail] error 18 at 0 depth lookup:self signed certificate OK client-cert.pem: C = ch, ST = shh, L = shh, O = ys, OU = ys, CN = ys, emailAddress = [e-mail] error 18 at 0 depth lookup:self signed certificate OK [root@03_sdwm newcerts]#

 

 

使用下面这个命令，将服务器的私钥导出成RSA类型的密钥。

[root@02_sdw mysql]# openssl rsa -in server-key.pem -out server-key.pem writing RSA key [root@02_sdw mysql]#

 

 

4，MySQL里面设置支持SSL

PS：前面step2（自动创建SSL），step3（手动创建ssl），任选一种操作即可。

 

配置mysql服务器的SSL，参考官方文档资料：

http://dev.mysql.com/doc/refman/5.7/en/using-secure-connections.html

·        Server-Side Configuration forSecure Connections

Tostart the MySQL server so that it permits clients to connect securely, useoptions that identify the certificate and key files the server uses whenestablishing a secure connection:

·        --ssl-ca identifiesthe Certificate Authority (CA) certificate.

·        --ssl-cert identifiesthe server public key certificate. This can be sent to the client andauthenticated against the CA certificate that it has.

·        --ssl-key identifiesthe server private key.

For example, start the server with these lines in the my.cnf file,changing the file names as necessary:

[mysqld]

ssl-ca=ca.pem

ssl-cert=server-cert.pem

ssl-key=server-key.pem

 

接下来一一操作实现：

将ca-cert.pem、server-cert.pem和server-key.pem拷贝或移动到/etc目录下

 

 

配置mysqld，vim /usr/local/mysql/my.cnf

[mysqld] ssl-ca=ca-cert.pem ssl-cert=server-cert.pem                                      ssl-key=server-key.pem bind-address=*

 

重启mysqld服务

[root@03_sdwm soft]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL.. SUCCESS! [root@03_sdwm soft]#

 

查看：

mysql> show variables like '%ssl%'; +---------------+--------------------------------+ | Variable_name | Value                          | +---------------+--------------------------------+ | have_openssl  | DISABLED                       | | have_ssl      | DISABLED                       | | ssl_ca        | /etc/mysql-ssl/ca-cert.pem     | | ssl_capath    |                                | | ssl_cert      | /etc/mysql-ssl/server-cert.pem | | ssl_cipher    |                                | | ssl_crl       |                                | | ssl_crlpath   |                                | | ssl_key       | /etc/mysql-ssl/server-key.pem  | +---------------+--------------------------------+ 9 rows in set (0.00 sec)   mysql>

 

去检测mysql的ssl是否安装：

mysql> \s -------------- mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (x86_64) using readline 5.1   Connection id:          4 Current database: Current user:           root@localhost SSL:                    Not in use Current pager:          stdout Using outfile:          '' Using delimiter:        ; Server version:         5.7.16-log MySQL Community Server (GPL) Protocol version:       10 Connection:             Localhost via UNIX socket Server characterset:    utf8mb4 Db     characterset:    utf8mb4 Client characterset:    utf8mb4 Conn.  characterset:    utf8mb4 UNIX socket:            /var/lib/mysql/mysql.sock Uptime:                 6 min 23 sec   Threads: 2  Questions: 14  Slow queries: 0  Opens: 109  Flush tables: 1  Open tables: 102  Queries per second avg: 0.036 --------------   mysql>

 

 

关于这个ssl_cipher没有或者SSL:                    Not in use的状况，刚开始安装的时候，没有值，等做过了SSL登陆校验成功后，会自动有值的。

 

 

 

5，分配SSL用户账号

服务器端的SSL配置完成后，下一步就是创建有权通过SSL访问MySQL服务器的用户。为此，登录进入到MySQL服务器，输入下面内容：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'192.168.%' IDENTIFIED BY 'ys0418' REQUIRE SSL; Query OK, 0 rows affected, 1 warning (0.00 sec)   mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)   mysql>

 

测试：

[root@03_sdwm mysql]# mysql -ussluser -h192.168.121.57 -P3317 -p Enter password: ERROR 1045 (28000): Access denied for user 'ssluser'@'192.168.121.57' (using password: NO)                      [root@03_sdwm mysql]#

 

 

 

6，使用SSL证书登陆验证

 

The --ssl option in negated form overrides other --ssl-xxx options and indicates that encryption should not be used. To do this, specify the option as --ssl=0 or a synonym (--skip-ssl, --disable-ssl). For example, you might have options specified in the [client] group of your option file to use secure connections by default when you invoke MySQL client programs. To use an unencrypted connection instead, invoke the client program with --ssl=0 on the command line to override the options in the option file.   To require use of secure connections by a MySQL account, use CREATE USER to create the account with at least a REQUIRE SSL clause, or use ALTER USER for an existing account to add a REQUIRE clause. Connections for the account will be rejected unless MySQL supports secure connections and the server and client have been started with the proper secure-connection options.

 

先看官方文档http://dev.mysql.com/doc/refman/5.7/en/secure-connection-options.html，里面说了可以使用--ssl或者(--skip-ssl, --disable-ssl).取消ssl验证。在创建账号的时候需要带上 REQUIRE SSL 标示。

 

开始登陆验证：

# 直接登陆，失败 [root@03_sdwm mysql]# mysql -ussluser -h192.168.121.57 -P3317 -pys0418 ERROR 1045 (28000): Access denied for user 'ssluser'@'192.168.121.57' (using password: YES)   # 需要使用ssl证书登陆，才能OK [root@03_sdwm mysql]# mysql -ussluser -h192.168.121.57 -P3317 -pys0418 --ssl-cert=/etc/mysql-ssl/client-cert.pem --ssl-key=/etc/mysql-ssl/client-key.pem Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 24 Server version: 5.7.16-log MySQL Community Server (GPL)   Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> select 48.5+13-(13.5+12); +-------------------+ | 48.5+13-(13.5+12) | +-------------------+ |              36.0 | +-------------------+ 1 row in set (0.00 sec)   mysql>

 

 

7，STATUS里面SSL in use

mysql> show status like 'ssl_cipher';  +---------------+--------------------+ | Variable_name | Value              | +---------------+--------------------+ | Ssl_cipher    | DHE-RSA-AES256-SHA | +---------------+--------------------+ 1 row in set (0.01 sec)   mysql> \s -------------- mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (x86_64) using readline 5.1   Connection id:          27 Current database: Current user:           ssluser@192.168.121.57 SSL:                    Cipher in use is DHE-RSA-AES256-SHA Current pager:          stdout Using outfile:          '' Using delimiter:        ; Server version:         5.7.16-log MySQL Community Server (GPL) Protocol version:       10 Connection:             192.168.121.57 via TCP/IP Server characterset:    utf8mb4 Db     characterset:    utf8mb4 Client characterset:    utf8mb4 Conn.  characterset:    utf8mb4 TCP port:               3317 Uptime:                 1 hour 8 min 26 sec   Threads: 2  Questions: 47  Slow queries: 0  Opens: 123  Flush tables: 1  Open tables: 116  Queries per second avg: 0.011 --------------   mysql>

 

在官方文档里面可以看到Cipher列表，“SSL:                    Cipherin use is DHE-RSA-AES256-SHA”包含在里面如下里面。

MySQLpasses this cipher list to yaSSL:

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

 

 

8，实时关闭开启用户SSL验证

查看mysql.user系统表，可以看到有关于ssl的2个字段，如下：

 

可以查看下这个字段值：

 

在线实时修改用户SSL验证状态，都会在mysql.user里面的这2个字段体现出来：

（1）取消SSL验证alter user ssluser@'192.168.%' require none;

 

（2）启动SSL验证alter user ssluser@'192.168.%' require ssl;

 

9，SSL对性能的影响

SSL性能，因为在建立与数据库的连接的时候，需要经过SSL验证，所以会根据连接数量的增加，总体会下降5%到20%左右的性能，连接数越多，性能下降会多一些。