MySQL基于SSL加密方式登录

1.查看MySQL服务是否以SSL选项启动

mysql>  show variables like 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.05 sec)

2.检查MySQL服务器require_secure_transport系统变量，如果为ON启用此变量后，服务器仅允许使用TLS/SSL加密的TCP/IP连接。

mysql> show variables like 'require_secure_transport';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
+--------------------------+-------+
1 row in set (0.01 sec)

修改客户端使用SSL加密连接
方法一：修改my.cnf文件
vim /etc/my.cnf
.....
require_secure_transport=ON

修改好后重启服务
方法二：配置系统环境变量
mysql> set global require_secure_transport=ON;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like 'require_secure_transport';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | ON    |
+--------------------------+-------+
1 row in set (0.05 sec)

3.以ssl方式登录root用户

mysql -uroot -p --ssl-mode=require

4.使用\s命令查看（SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256）

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.36, for el7 (x86_64) using  EditLine wrapper

Connection id:          98
Current database:
Current user:           kht@localhost
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.36-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 22 hours 23 min 20 sec

Threads: 6  Questions: 131242  Slow queries: 0  Opens: 1928  Flush tables: 1  Open tables: 1017  Queries per second avg: 1.628
--------------

5.创建测试账户进行测试

//create user 'kht' identified by 'Admin@123456' require SSL;(优先级高,即使全局关闭，也必须以加密的方式登录)
//create user 'kht1' identified by 'Admin@123456' require NONE;(指示由该语句指定的所有帐户都没有 SSL 或 X.509 要求。如果用户名和密码有效，则允许未加密的连接。如果客户端拥有正确的证书和密钥文件，则可以根据客户端的选择使用加密连接。)
mysql> create user 'kht' identified by 'Admin@123456' require SSL;
Query OK, 0 rows affected (0.04 sec)

mysql> grant all on *.* to 'kht';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
此时，仅使用 mysql -u kht -p无法登录
例如：
[root@wl config]# mysql -u kht -pAdmin@123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'kht'@'localhost' (using password: YES)
// 正确登录方式：
[root@wl config]# mysql -u kht -p --ssl-mode=require
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 98
Server version: 5.7.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql>