mysql5.7启用ssl连接(windows版本)
环境:
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也可以登录登录,默认的--ssl参数为true
mysql --host=192.168.1.39 -ussltest -pmysql -P13306
强制--ssl=0 使用非ssl登录就不能登录了
[root@host135 39ssl]# mysql -h 192.168.1.39 -ussltest -pmysql -P13306 --ssl=0
mysql: [Warning] Using a password on the command line interface can be insecure.
WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.
ERROR 1045 (28000): Access denied for user 'ssltest'@'192.168.1.135' (using password: YES)
6.客户端工具连接
navicate连接,好像需要15版本以上
Navicat Premium 12 可以连接
7.关闭ssl,在my.cnf配置文件添加
[mysqld]
skip_ssl
重新启动
mysql> 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, 1 warning (0.01 sec)
说明:关闭了ssl后使用ssl创建的用户是无法登录服务器的,其他非ssl创建的用户不受影响
