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编辑过] | 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2014-11-12 14:06:06
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对
发表于 2014-12-4 13:04:05
楼主