526互联

mysql5.7启用ssl连接(windows版本)

发布时间 2023-10-27 10:35:23作者: slnngk

环境:
OS:windows2012
Mysql:5.7.29

 

1.安装mysql
安装步骤省略,mysql5.7默认安装都已经安装好了ssl的,并默认启用了的.
证书在数据目录下

 

D:\mysql57\data
D:\mysql57\data>dir
 驱动器 D 中的卷是 新加卷
 卷的序列号是 7603-6C5B

D:\mysql57\data>dir *.pem
 驱动器 D 中的卷是 新加卷
 卷的序列号是 7603-6C5B

 D:\mysql57\data 的目录

2023-05-04  16:25             1,707 ca-key.pem
2023-05-04  16:25             1,131 ca.pem
2023-05-04  16:25             1,131 client-cert.pem
2023-05-04  16:25             1,707 client-key.pem
2023-05-04  16:25             1,703 private_key.pem
2023-05-04  16:25               461 public_key.pem
2023-05-04  16:25             1,131 server-cert.pem
2023-05-04  16:25             1,703 server-key.pem
               8 个文件         10,674 字节
               0 个目录 275,478,769,664 可用字节

 

2023-05-04  16:25             1,131 ca.pem
2023-05-04  16:25             1,131 client-cert.pem
2023-05-04  16:25             1,707 client-key.pem

我们需要把这三个文件拷贝到客户端即可

查看是否开启
C:\Users\Administrator.WIN-ADSERVER>mysql -h localhost -uroot -pmysql -P13306
mysql> show variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set, 1 warning (0.02 sec)

 

2.也可以在配置文件中[mysqld]栏目加入如下证书配置(这一步需要重启,可以不需要)

[mysqld]
ssl-ca=D:\\mysql57\\data\\ca.pem
ssl-cert=D:\\mysql57\\data\\server-cert.pem
ssl-key=D:\\mysql57\\data\\server-key.pem

[client]
ssl-ca=D:\\mysql57\\data\\ca.pem
ssl-cert=D:\\mysql57\\data\\client-cert.pem
ssl-key=D:\\mysql57\\data\\client-key.pem

 

3.创建启用ssl的账号
C:\Users\Administrator.WIN-ADSERVER>mysql -h localhost -uroot -pmysql -P13306

mysql>grant all privileges on *.* to 'ssltest01'@'%' identified by 'mysql' require ssl;


原来的用户修改使用ssl登录
mysql>alter user 'ssltest'@'%' require ssl;

若是不想启用ssl需要修改下用户,这样只有密码登陆就可以了
mysql>alter user 'ssltest'@'%' require none;

 

4.查看用户使用启用了ssl

mysql> select user,host,ssl_type,ssl_cipher from mysql.user;
+---------------+-----------+----------+------------+
| user          | host      | ssl_type | ssl_cipher |
+---------------+-----------+----------+------------+
| root          | %         |          |            |
| mysql.session | localhost |          |            |
| mysql.sys     | localhost |          |            |
| dmladmin      | %         |          |            |
| udumpmonitor  | %         |          |            |
| ssltest       | %         | ANY      |            |
+---------------+-----------+----------+------------+
6 rows in set (0.00 sec)

 

查看ssl配置情况

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

 

查看状态情况

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.29, for Win64 (x86_64)

Connection id:          172
Current database:
Current user:           root@::1
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Using delimiter:        ;
Server version:         5.7.29-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               13306
Uptime:                 149 days 22 hours 10 min 3 sec

Threads: 3  Questions: 4930  Slow queries: 498  Opens: 450  Flush tables: 323  O
pen tables: 14  Queries per second avg: 0.000
--------------

 

5.登录情况
使用ssl登录
mysql --host=192.168.1.39 --ssl-ca=D:\\mysql57\\data\\ca.pem --ssl-cert=D:\\mysql57\\data\\client-cert.pem --ssl-key=D:\\mysql57\\data\\client-key.pem -ussltest -pmysql -P13306

不指定ssl也可以登录登录
mysql --host=192.168.1.39 -ussltest -pmysql -P13306



6.客户端工具连接
navicate连接,好像需要15版本以上
Navicat Premium 12 可以连接