Window10 MySQL5.7.X 使用SSL连接
-------------------------------------Begin Windows 安装OpenSSL---------------------------------------
https://www.openssl.org/source/
http://slproweb.com/products/Win32OpenSSL.html
http://slproweb.com/download/Win64OpenSSL-1_0_2u.exe
set OpenSSL_HOME=D:\SOFTWARE\OpenSSL\bin\
set OPENSSL_CONF=D:\OpenSSL-Win32\bin\openssl.cfg
cmd 使用管理员身份打开
>D:
>md data
>D:\SOFTWARE\mysql-5.7.28-winx64\bin\mysql_ssl_rsa_setup --datadir=/data
Generating a 2048 bit RSA private key
.........+++
....+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.....................................................................................+++
........................................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
..+++
......+++
writing new private key to 'client-key.pem'
-----
>dir data
2020/03/11 22:27 <DIR> .
2020/03/11 22:27 <DIR> ..
2020/03/11 22:27 1,675 ca-key.pem
2020/03/11 22:27 1,107 ca.pem
2020/03/11 22:27 1,107 client-cert.pem
2020/03/11 22:27 1,675 client-key.pem
2020/03/11 22:27 1,675 private_key.pem
2020/03/11 22:27 451 public_key.pem
2020/03/11 22:27 1,107 server-cert.pem
2020/03/11 22:27 1,675 server-key.pem
8 个文件 10,472 字节
2 个目录 87,897,403,392 可用字节
将基复制到有：D:\SOFTWARE\mysql-5.7.28-winx64\ssl
测试证书是否正确
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK
为mysql 设置证书
[mysqld] # 开启 MySQL 服务器 SSL 特性，注意一在[mysqld]下 ssl # 根证书 ssl-ca= D:\\SOFTWARE\\mysql-5.7.28-winx64\\ssl\\ca.pem # 服务器公钥 ssl-cert= D:\\SOFTWARE\\mysql-5.7.28-winx64\\ssl\\server-cert.pem #服务器私钥 ssl-key=D:\\SOFTWARE\\mysql-5.7.28-winx64\\ssl\\server-key.pem
注意：Windows上使用双斜线,如果有一个不对可能会卡很久
mysql>grant all privileges on *.* to scm1@'192.168.1.5' identified by 'scm' require ssl;
mysql>flush privileges;
D:>mysql -h 192.168.1.5 -uscm1 -p'scm' --ssl-cert=D:\data\client-cert.pem --ssl-key=D:\data\client-key.pem
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.7.28, for Win64 (x86_64)
Connection id: 4
Current database:
Current user: scm1@DESKTOP-I0DD9JJ
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Using delimiter: ;
Server version: 5.7.28 MySQL Community Server (GPL)
Protocol version: 10
Connection: 192.168.1.5 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 6549
Uptime: 23 min 16 sec
Threads: 1 Questions: 13 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.009
--------------
[SQL]create user 'ssl_test'@'%' identified by '123' require SSL;
[Err] 1290 - The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
set global read_only=1;
flush privileges;
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
select version(),
@@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
set global read_only=1;
flush privileges;
create user 'ssl_test'@'%' identified by '123' require SSL;
C:\WINDOWS\system32>mysql -h localhost -ussl_test -p'123' --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 'ssl_test'@'localhost' (using password: YES)
D:\>mysql -h 192.168.1.4 -uwang -p'123' --ssl-cert=D:\data1\client-cert.pem --ssl-key=D:\data1\client-key.pem
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)
解决方法：https://www.dazhuanlan.com/2019/08/16/5d55fc643aa75/
----------------------------创建用户存在-----------------------------------
mysql> create user 'ssl_test'@'%' identified by '123' require SSL;
ERROR 1396 (HY000): Operation CREATE USER failed for 'ssl_test'@'%'
mysql> Delete FROM user Where User='ssl_test'
-> ;
ERROR 1046 (3D000): No database selected
mysql> use mysql
Database changed
mysql> Delete FROM user Where User='ssl_test';
Query OK, 1 row affected (0.00 sec)
mysql> create user 'ssl_test'@'%' identified by '123' require SSL;
ERROR 1396 (HY000): Operation CREATE USER failed for 'ssl_test'@'%'
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'ssl_test'@'%' identified by '123' require SSL;
Query OK, 0 rows affected (0.00 sec)
-------------------------------------------------------------
---------------------------------服务未启动------------
C:\Users\xrl>mysql
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
C:\Users\xrl>net start mysql
--------------------------------------------------------
【注意】：如果用户是采用本地localhost或者sock连接数据库，那么不会使用SSL方式了。
参考资料：
http://blog.itpub.net/30317998/viewspace-2659090/
https://blog.csdn.net/weixin_34200628/article/details/89904819
