MySQL5.7 开启SSL

MySQL5.7配置SSL加密的方式比较简单。

生成证书文件

[root@ ~]# bin/mysql_ssl_rsa_setup --datadir=/data/database/mysql

[root@ ~]# chown mysql:mysql /data/database/mysql -R 

[root@ ~]# ll /data/database/mysql -rt                
.....
-rw------- 1 mysql mysql 1675 Mar 28 16:35 ca-key.pem             # CA 私钥
-rw-r--r-- 1 mysql mysql 1082 Mar 28 16:35 ca.pem                    # 自签的CA证书，客户端连接也需要提供
-rw-r--r-- 1 mysql mysql 1086 Mar 28 16:35 client-cert.pem        # 客户端连接服务端需要提供的证书文件
-rw------- 1 mysql mysql 1675 Mar 28 16:35 client-key.pem         #客户端连接服务端需要提供的私钥文件
-rw------- 1 mysql mysql 1679 Mar 28 16:35 private_key.pem      #私钥公钥的私有成员
-rw-r--r-- 1 mysql mysql  451 Mar 28 16:35 public_key.pem        #私钥公钥的共有成员
-rw-r--r-- 1 mysql mysql 1086 Mar 28 16:35 server-cert.pem       #服务端证书文件
-rw------- 1 mysql mysql 1679 Mar 28 16:35 server-key.pem       #服务端私钥文件

配置：

[root@ ~]# vim /etc/my.cnf
#ssl
ssl-ca=/data/database/mysql/ca.pem
ssl-cert=/data/database/mysql/server-cert.pem
ssl-key=/data/database/mysql/server-key.pem

然后重启一下

查看一下：

(root@localhost) [(none)]> show global variables like '%ssl%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| have_openssl  | YES                                  |
| have_ssl      | YES                                  |
| ssl_ca        | /data/database/mysql/ca.pem          |
| ssl_capath    |                                      |
| ssl_cert      | /data/database/mysql/server-cert.pem |
| ssl_cipher    |                                      |
| ssl_crl       |                                      |
| ssl_crlpath   |                                      |
| ssl_key       | /data/database/mysql/server-key.pem  |
+---------------+--------------------------------------+
9 rows in set (0.01 sec)

(root@localhost) [(none)]> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.19-17, for Linux (x86_64) using  6.0

Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.19-17-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 34 sec

Threads: 1  Questions: 7  Slow queries: 0  Opens: 109  Flush tables: 1  Open tables: 102  Queries per second avg: 0.205

创建一个SSL登陆的账号

(root@localhost) [(none)]>  create user admin@127.0.0.1 identified by '123456' require ssl ;   #require ssl表示强制使用SSL
(root@localhost) [(none)]>  grant all on *.* to admin@127.0.0.1 ;
(root@localhost) [(none)]> flush privileges;

登录

使用unix socket无法加密呀
通过--ssl-cert=/xxx/client-cert.pem --ssl-key=/xxx/client-key.pem 指定客户端证书和key

 mysql -uadmin -p123456 -P3306 -h 127.0.0.1 --ssl-cert=/data/database/mysql/client-cert.pem  --ssl-key=/data/database/mysql/client-key.pem 

查看一下状态：

admin@127.0.0.1) [(none)]> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.19-17, for Linux (x86_64) using  6.0

Connection id:          29
Current database:
Current user:           admin@127.0.0.1
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.19-17-log Source distribution
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 30 min 2 sec

Threads: 1  Questions: 108  Slow queries: 0  Opens: 139  Flush tables: 1  Open tables: 132  Queries per second avg: 0.059
--------------

查看加密方式

(admin@127.0.0.1) [(none)]>  show status like 'ssl_cipher';  
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.00 sec)

查看SSL版本：

(admin@127.0.0.1) [(none)]> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.1 |
+---------------+---------+
1 row in set (0.00 sec)