重庆思庄Oracle、Redhat认证学习论坛

标题: mysql 使用加密连接。(ssl) [打印本页]

作者: jikcheng 时间: 2014-11-12 14:06
标题: mysql 使用加密连接。(ssl)
ssl 作为安全链接。对于mysql 数据库的运行。和维护。都有一定的保护作用。
开启ssl功能：
在[mysqld]下面添加ssl 则会开启ssl 功能。

没开启的状态：
(root@localhost) [(none)]> show variables like '%ssl%'
    -> ;
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.00 sec)

(root@localhost) [(none)]> SHOW VARIABLES LIKE 'have_ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+
1 row in set (0.01 sec)

客户端
(win@192.168.154.190) [(none)]> SHOW STATUS LIKE 'ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+
1 row in set (0.00 sec)

开启后的状态

(root@localhost) [(none)]> show variables like '%SSL%'
    -> ;
+---------------+-----------------------------------------+
| Variable_name | Value                                   |
+---------------+-----------------------------------------+
| have_openssl | YES                                     |
| have_ssl      | YES                                     |
| ssl_ca        | /var/lib/mysql/newcerts/ca-cert.pem     |
| ssl_capath    |                                         |
| ssl_cert      | /var/lib/mysql/newcerts/server-cert.pem |
| ssl_cipher    |                                         |
| ssl_crl       |                                         |
| ssl_crlpath   |                                         |
| ssl_key       | /var/lib/mysql/newcerts/server-key.pem |
+---------------+-----------------------------------------+

(root@localhost) [(none)]> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.00 sec)

查看当前是否是加密连接：
(root@localhost) [(none)]> \s
--------------
mysql Ver 14.14 Distrib 5.6.21, for Linux (x86_64) using EditLine wrapper

Connection id:          7
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn. characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 30 sec

Threads: 5 Questions: 774 Slow queries: 0 Opens: 208 Flush tables: 1 Open tables: 201 Queries per second avg: 25.800
--------------

(root@localhost) [(none)]>

mysql Ver 14.14 Distrib 5.6.21, for Win64 (x86_64)

Connection id:          79
Current database:
Current user:           win@192.168.154.1
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.6.21-enterprise-commercial-advanced MySQL Enterprise S
erver - Advanced Edition (Commercial)
Protocol version:       10
Connection:             192.168.154.190 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gbk
Conn. characterset:    gbk
TCP port:               3306
Uptime:                 1 min 6 sec

Threads: 6 Questions: 1689 Slow queries: 0 Opens: 251 Flush tables: 1 Open
tables: 244 Queries per second avg: 25.590
--------------

SSL:                    Cipher in use is DHE-RSA-AES256-SHA 这个就说明是加密连接。

配置ssl 加密连接步骤：
1、安装openssl
2、开启mysql 的ssl
在my.cnf 中的mysqld加入ssl选项。
2、生成秘钥
# Create clean environment
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts
# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 3600 \
-key ca-key.pem -out ca-cert.pem
# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
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
# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
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
After generating the certificates, verify them:
shell> openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

3、生成好的秘钥分别拷贝到客户端上。
F:\mysql-advanced-5.6.21-winx64\cet 譬如放到这个windows 的目录下。
4、配置服务端
#ssl
ssl
ssl-ca=/var/lib/mysql/newcerts/ca-cert.pem
ssl-cert=/var/lib/mysql/newcerts/server-cert.pem
ssl-key=/var/lib/mysql/newcerts/server-key.pem

在 my.cnf 下的 [mysqld] 下添加。重启服务器。
添加ssl用户：
(root@localhost) [(none)]> grant all privileges on world.* to win@192.168.154.190 identified by '123' require ssl
这个用户必须用ssl 方式连接。
5、配置客户端
配置my.ini
在[mysql]下配置。
[mysql]
ssl-ca=F:\mysql-advanced-5.6.21-winx64\cet\ca-cert.pem
ssl-cert=F:\mysql-advanced-5.6.21-winx64\cet\client-cert.pem
ssl-key=F:\mysql-advanced-5.6.21-winx64\cet\client-key.pem
配置好以后。
使用建立好的账户登录：
6、登录以后就可以看到。
mysql Ver 14.14 Distrib 5.6.21, for Win64 (x86_64)

Connection id:          1564
Current database:
Current user:           win@192.168.154.1
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.6.21-enterprise-commercial-advanced MySQL Enterprise S
erver - Advanced Edition (Commercial)
Protocol version:       10
Connection:             192.168.154.190 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gbk
Conn. characterset:    gbk
TCP port:               3306
Uptime:                 17 min 11 sec

Threads: 6 Questions: 40891 Slow queries: 0 Opens: 344 Flush tables: 1 Open
tables: 337 Queries per second avg: 39.661
--------------

(win@192.168.154.190) [(none)]>

到此配置成功。

注意：秘钥的权限一定要对：一般为mysql:mysql

[此贴子已经被作者于2014-11-12 14:20:21编辑过]

作者: 郑全 时间: 2014-12-4 13:04
测试了一下，权限可以为 root:root

作者: jikcheng 时间: 2014-12-4 13:20
秘钥  生成后不能放入root 用户的主目录下。可以放在其他目录下，否则SSL会开启失败。不能使用加密连接。

作者: 郑全 时间: 2015-4-12 23:51
今天又测试了，确实不能放到 /root目录下，否则，ssl会关闭。

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)